RE: Oracle 7.3.4 problem... SLOW Drop user
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Aug 2004 14:44:09 -0400
.......and after you get control back, there are a few things that *might*
work for you:
If the user's objects are not entangled in tablespaces with the objects of
other users, for example, you can take the tablespace offline and drop it
offline including contents (which ignores the slow on-line bulletproof
return of space to fet$).
Frankly, I would generate a list of index drop commands, followed by a list
of table drop commands, and then when the user really doesn't have anything
left, drop the user.
Now from that list of stuff the user owns, you can get a pretty good idea of
which objects will be trouble from the (shudder) number of extents.
If you can't take the tablespace containing them offline for the sneaky
quick drop, you might want to start up parallel sessions of dropping the
pieces.
The other thing that may be strangely faster is if you have space to copy
out to another tablespace the things NOT owned by this user, then you can
use the offline drop of the whole tablespace.
Hmm. I can't remember if truncate does this quickly. If it does, then
truncate followed by drop might help. I guess I'd try that if dropping the
whole tablespace can't be done without the copy elsewhere for other people's
stuff. Then the copy elsewhere and drop tablespace might be fastest.
The more I think about it, the more I think truncate might work quickly in
7.3, since that came out after the VLDB complained bitterly about this
feature.
I don't have a 7.3 handy to test that theory on, and yes, I realize all this
totally debases the user friendliness of drop user cascade.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Cary Millsap
Sent: Wednesday, August 04, 2004 2:22 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Oracle 7.3.4 problem... SLOW Drop user
The break will probably take a while. The whole reason for the O(n^2)
algorithm is so that you CAN break into it without corrupting anything.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 8/10 Boston, 9/14 San Francisco, 10/5 =
Charlotte
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
Hartford
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Jared.Still@xxxxxxxxxxx
Sent: Wednesday, August 04, 2004 1:12 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Oracle 7.3.4 problem... SLOW Drop user
> Jared / Everyone
>=20
> Many Thanks for the input.
> One quick question, the situation makes me nervous. Can I kill the =
drop
> session? Will the user still be there?
>=20
Yes, it may take awhile for oracle to recognize the break, as it will
be busy dropping a segment.
The user will still be there, minus some tables and whatnot.
I don't know what the internal procedures are in 7x for dropping a user,
other than what Cary just told us about.
I do know that it was not unusual for a drop user to cause an ORA-36,
which is essentially 'recursion too deep'.
Jared
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
