Re: Reorganizing SYSTEM tablespace

"Chuck" <chuckhNOSPAM_at_softhome.net> wrote in message news:ace34l$p1n6u$1_at_ID-85580.news.dfncis.de...
> Oracle 8.1.7.3.0
>
> Is it possible to reorg the system tablespace without recreating the
> database?

In one word, no.

Reorganisation usually means export, drop/truncate, import. You try dropping anything in SYSTEM and you'll be in deepest doo-doo.

You have 8i, though. So you could try 'alter table fet$ move'. That will move the table to the end of the file. Do it again and it *may* go back to the beginning. However, I doubt it, and the thought of doing that on the data dictionary tables is not funny.

In which case, you're stuffed. But what's a gigabyte these days? If rebuilding the database is that problematic, just live with it.

Incidentally, you don't 'grant connect, resource' to people do you? If so, don't. If you don't use those two roles, no-one gets any tablespace quota on any tablespace whatsoever, unless you explicitly grant it (the dba role is an exception, of course, as is SYSDBA). No real user should ever have quota on SYSTEM, except on a case by case basis.

Regards
HJR
>
> I recently discovered that my system tablespace had grown from 100m to
1.6g
> because someone added 1.5g of rollback segments to it. I dropped the
> rollback segments but now want to shrink the file back down to 100m. The
> problem is that after the rollback segments got created, additional
extents
> were allocated at the end of the datafile. Is it possible to move those
> extents to the beginning of the free spaces in the file and then shrink
the
> file. Those extents belong to SYS objects.
>
> TIA
> --
> Chuck Hamilton
> To reply remove "_NOSPAM" from the address
>
>
Received on Tue May 21 2002 - 15:37:59 CDT