Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle 7.3.4 problem... SLOW Drop user

RE: Oracle 7.3.4 problem... SLOW Drop user

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 4 Aug 2004 14:44:09 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKGEGFFCAA.mwf@rsiz.com>


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

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


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US