Re: Reorganizing SYSTEM tablespace

RSH wrote:

> 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
> >

I'm going to have to publish a road map as to how to interpret when I am attempting sarcastic/humorous remarks. I guess they go over better when the smile is clearly visible.

Sorry if the pointers weren't clear on this one but this would only tick me off if it happened on my watch.

Daniel Morgan Received on Tue May 21 2002 - 14:51:41 CDT