Re: Reorganizing SYSTEM tablespace

Daniel, I wasn't accusing you of being sarcastic; it was all good advice.

It would make me bloody furious on my watch to be stuck in Sunni's position.

Most people on my teams know not to experiment with signing on SYS or SYSTEM and know the perils of doing such, including explaining it to me. I confess that I frequently or usually am logged in as the Oracle owner to UNIX and often as SYSTEM, or else as RSH with DBA privileges. Lesson #27, "know who you are, where you are, what you can do, and why you are there, and do you really need to be who you are and where you are to do it?".

But I've never presumed to touch the internal tables except as queries.

Nor created any objects under SYS or SYSTEM.

There are those rare things like dealing with the TRUNCATE TABLE issue where people have ventured forth and created enormous security risks by building stored procedures under SYS or SYSTEM, without at least rudimentary underpinnings such as a table of allowed calling users, and schemas and tables each are permitted to manipulate.

Or selecting count(*) from DUAL and getting a number > 1 as a result. And trying to find out how that situation was created.

But I hope you did not take any of my words as criticism; they were not meant as such, nor were any of mine meant that way to anyone else.

In the original question, I was speaking of TRUNCATE on the flat, feeder tables, not the derivative tables that you are updating, inserting, and such, to. We have found that using SQLLDR to load to a first set of flat tables gives us the power to use SQPLPLUS and other tools to analyze and test the incoming feeder data, and then used Oracle mechanisms such as PL/SQL, SQL*PLUS to validate and transform that data into your derivative tables; I was not suggesting you make a practice of truncating your secondary tables!! We would use our feeder tables to do trial balance analysis and such, before sending that data along to our data warehouse; it seemed to be a reasonable approach when dealing with millions of rows. But I bow to the experience of others here that have given wise hints.

With the advent of external tables, which your version of Oracle does not support, you can do much of the same things, but I do question whether the performance is comparable to loading into 'real' Oracle tables, even avoiding the SQL*LDR step; but having never done it that way, I do not want to mislead you into not trying that method.

RSH. "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CEAA542.C872E1F2_at_exesolutions.com...
> 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 Wed May 22 2002 - 13:36:22 CDT