Re: Reorganizing SYSTEM tablespace

I love it when he is so succinct and ticked off.

Definitely agree, back the hell of it up using both cold image and restricted exports.
As for the rest, if it does not work, at least you have the backups!

I cannot and will not dispute the wisdom of steps 4,5,6,7.

RSH.
"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CEAA007.8547EB01_at_exesolutions.com...
> 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:38:34 CDT