Re: a heated discussion between Development Team & "a" DBA: reorg or not to reog

ibodogan wrote:
> > That's why recreating the database works, you are essentially
> > defragmenting those tables (and probably why your #5 blowups happened,
> i don't think export/import defrags the tables; it just combines thes
> extents next to each other defragmenting the tablespace not the
> tables.if you have a 2G table, 1500M free and the rest is used, you'll
> still get 1500M free space in the table after export/import. That's why
> i did alter table move before the export/import to defrags the tables.

That's what I suspect is going wrong - are you sure you are combining extents? For the problem tables, how many extents do you have? For the system tables, how many extents do you have? If you don't have more than 1000 in either during the problem times, I'll go crawl back under the bridge with the other trolls, and you definitely need to do some profiling to find out what's wrong besides the app doing deletes. Maybe freelist contention...

>
> i just want to know what is the advantage of export/drop db/create
> db/import against export/drop users/create users/import...

The recreation of the FET$ and related tables. If you are just making lots of extents in those tables, you are not fixing the problem either way.
You must either LMT or finally fix the app table storage definitions and then recreate the database.

>
> > extent size - you still have crap extent info in your system tables
> i don't get this..how can i still have crap after i drop the users..
> there was no user table after i drop all the users that have any object

But you aren't dropping the problem tables which are in SYSTEM. That is why LMT's were created in the first place. Perhaps crap is too strong a word.

>
> > 2. Listen to Sybrand. (Except
> i know i'm a big fan of LMTs but it is out of question at this point.
>
> > There are unsupported methods of not having to do the database
> > recreation, but if terms like FET$ don't mean anything to you, I
> > wouldn't recommend them.
> i know fet$, uet$..that is why i will not mess with them..
>
> i.d.

jg

-- 
@home.com is bogus.
http://crapart.spacebar.org/