Home » RDBMS Server » Server Administration » Dropping a schema takes ages
Dropping a schema takes ages [message #51665] Wed, 05 June 2002 07:24 Go to next message
Robin Urff
Messages: 9
Registered: March 2002
Junior Member
Hi all!

Like the subject says: Dropping a schema user takes ages!
When I make a user dump, its about 1GB.
I want to import it in a test environment.
Its necessary to drop the user before I can import.
It took 6 hours until now to get rid of this schema, and it is still in progress.
Does anyone know why it takes this long, what I can do about it?

Thanks in advance,

Robin Urff

Report message to a moderator

Re: Dropping a schema takes ages [message #51668 is a reply to message #51665] Wed, 05 June 2002 10:01 Go to previous messageGo to next message
Trifon Anguelov
Messages: 514
Registered: June 2002
Senior Member
Robin,

Since you are dropping the user with cascade option enabled, a large amount of recursive SQL is being generated. That is not so efficient because of the above reason.

You could check the number of extents for tables and indexes. If there are many extents FET$ and UET$ are probably the bottlenecks for both processes.

Hope that helps,

clio_usa
OCP - DBA

Visit our web site [url=http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi]Click Here

Report message to a moderator

Re: Dropping a schema takes ages [message #51676 is a reply to message #51665] Wed, 05 June 2002 12:36 Go to previous messageGo to next message
Grant
Messages: 578
Registered: January 2002
Senior Member
Why drop the user. Drop the objects. Go to http://www.oriolecorp.com in the scripts section download drop_user_objects.sql. The coalesce the tablespace and import. Your "drop user USER cascade is probably hung. It should not take that long. Maybe 15 minutes at the most.

Report message to a moderator

Re: Dropping a schema takes ages [message #51678 is a reply to message #51665] Wed, 05 June 2002 23:41 Go to previous message
Robin Urff
Messages: 9
Registered: March 2002
Junior Member
Thanks for your help!
You showed me the right path ;).
Thanks again,

Robin Urff

Report message to a moderator

Previous Topic: Re: There are 14 rules about RDBMS, not very sure
Next Topic: log switches
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 16 18:51:35 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.