Re: Reorganizing SYSTEM tablespace

I can't believe I managed to merge two unrelated issues.

Well, it was done in a spirit of helping in any case.

RSH.
"RSH" <RSH_Oracle_NOSPAM_at_worldnet.att.net> wrote in message news:GyRG8.36609$Vm2.1877524_at_bgtnsc04-news.ops.worldnet.att.net...
> 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:41:32 CDT