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

olddba missed the obvious (unless I missed something obvious too :-) - with a DMT tablespace, the system tablespace uses tables to track the segments. Those tables grow way too large when you have thousands of extents. That's why recreating the database works, you are essentially defragmenting those tables (and probably why your #5 blowups happened, as you've figured out). And why it doesn't work when you have some of those tables describing objects which are changed to have a proper extent size - you still have crap extent info in your system tables.

You need to do two things after your next database recreation:

1. create the tables before you import them, with proper extent sizes.
2. Listen to Sybrand. (Except, you need to be sure the system tablespace is LMT, since it is possible to upgrade previous versions and keep DMT there).

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. In fact, even if they do mean something to you, better to fix the actual problem.

Check these out:
http://www.google.com/search?as_q=rebuilding+FET+system+tablespace+oracle&num=10&hl=en&c2coff=1&btnG=Google+Search&as_epq=&as_oq=&as_eq=&lr=&as_ft=i&as_filetype=&as_qdr=all&as_occt=any&as_dt=i&as_sitesearch=&safe=images especially the ixora and asktom.

Also, be sure you understand the compress option with export, easy to forget since it has changed over time.

jg

--
@home.com is bogus.
http://www.theregister.co.uk/2005/06/23/indian_call_centre_fraud_probe/