Re: Reorganizing SYSTEM tablespace

Chuck wrote:

> Do what though? What command can I issue to move extents in a tablespace?
> Only thing I can think of is expor/drop/import. Since we're talking about
> the data dictionary here, I'm not so sure dropping is possible.
>
> --
> Chuck Hamilton
> To reply remove "NOSPAM" from the address
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:3CEA9881.30F_at_yahoo.com...
> > Chuck wrote:
> > >
> > > Oracle 8.1.7.3.0
> > >
> > > Is it possible to reorg the system tablespace without recreating the
> > > database?
> > >
> > > 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
> >
> > I had a similar problem (when someone wrote an endless-loop in a plsql
> > code generator - several million objects later...)
> >
> > A consultation with Oracle support was concluded with: "Take a backup,
> > do it, and restore if it does not work". They did suggest doing as much
> > as possible to avoid recursive txns (disable ddl triggers et al)
> >
> > hth
> > connor
> >
> >
> > --
> > ==============================
> > Connor McDonald
> >
> > http://www.oracledba.co.uk
> >
> > "Some days you're the pigeon, some days you're the statue..."

Just a thought but how about the following:

1. Back up the whole thing
2. Create a new system tablespace in the same directory as the old one with a different datafile name.
3. Use the transportable tablespace option to transport all of the tablespaces from the old system tablespace to the new
4. Dump the old system tablespace
5. Dump the employee that caused the problem
6. Dump the DBA that gave the employee the privileges required to make the mess
7. Or dump anyone in management that over-ruled the DBA and told them to allow something so risky

Daniel Morgan Received on Tue May 21 2002 - 14:29:23 CDT