x

Is there any way to speed up dropping a user when they have a large number of tables etc?

I currently have a

DROP USER SAMPLE_USER CASCADE;

statement to drop the user SAMPLE_USER and all child tables/object/etc.

SAMPLE_USER contains approx 1200 tables and, using SQLDeveloper, can take several minutes to drop the user and all associated tables. Is there an alteration to this statement that can be used to speed up dropping user SAMPLE_USER?

more ▼

asked Oct 29, 2009 at 09:11 AM in Default

Cee gravatar image

Cee
153 13 13 15

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Jonah is correct. But oddly, there are some bugs on Tablespace Quotas that slow down drop user. If you remove any tablespace quotas first then quite often the drop user is faster. This is particularly evident if dropping many users. So you could try that.

more ▼

answered Nov 04, 2009 at 07:41 AM

Shaun 2 gravatar image

Shaun 2
201

(comments are locked)
10|1200 characters needed characters left

In addition to handling the normal UNDO transactional semantics of the DROP, I don't believe there's much you can do, because Oracle has to internally perform removal of all dependency information and invalidate any/all objects that reference ones you are dropping.

Some people say you can cheat by dropping/truncating tables/tablespaces/data files first, but the sum of time spent is generally equivalent and it's more complex and error-prone.

more ▼

answered Oct 29, 2009 at 01:01 PM

Jonah H. Harris gravatar image

Jonah H. Harris
395 3

(comments are locked)
10|1200 characters needed characters left

Ok - it's a longshot, but trying REALLY hard to think outside the box...

If your db in question has only the 1 major schema, shutdown your database remove the files and create a new one. It's pretty drastic I know, but otherwise I can't think of anything to add to Jonah's answer.

more ▼

answered Oct 29, 2009 at 03:32 PM

oakesgr gravatar image

oakesgr
66

That's a little too nuclear for my liking...
Nov 03, 2009 at 01:55 PM Cee
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x41
x11
x9
x2
x2

asked: Oct 29, 2009 at 09:11 AM

Seen: 1560 times

Last Updated: Oct 29, 2009 at 09:11 AM