| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle 7.3.4 problem... SLOW Drop user
.......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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
Sent: Wednesday, August 04, 2004 2:22 PM
To: oracle-l_at_freelists.org
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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jared.Still_at_radisys.com
Sent: Wednesday, August 04, 2004 1:12 PM
To: oracle-l_at_freelists.org
Subject: RE: Oracle 7.3.4 problem... SLOW Drop user
> Jared / Everyone
>=20
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed Aug 04 2004 - 13:41:20 CDT
 |
 |