-
Drop user takes long time...
Hi Friends,
I have a user in peoplesoft environment with some 10,000 tables but almost 6000 tables are empty. I'm trying to drop this user by using cascade option but it is taking too long...it's more than one hour since I have issued the command.
Could anyone please tell me why drop user is taking so much time.
Thanks and Advance
Regards
Sandy
"Greatest Rewards come only with Greatest Commitments!"
-
maybe there are some tables which have many extents which need to be de-allocated first. Or maybe you are breaking some constraints somewhere and it will come back and tell you sooner or later
-
What version of Oracel are you using?
In versions prior to 8.1.6 it could take a long time as the process was very ineffecient.
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
Thanks for your quick reply friends...
I'm using 8.1.7 on Solaris 7. I think there is no constraint problem as peoplesoft takes care of all the constraints automatically...we don't have any customized data also...
Or I think probably this may be the because of the large amount of data...??
Still the drop is going on...
Can there be any Table Locking problem?
"Greatest Rewards come only with Greatest Commitments!"
-
Well my uneducated guess is that regardless of how much data is in the table the object and all it's branches have to be written out of the data dictionary. So, if there are 10 bazillion tables and 20 bazillion indexes, synonyms, views ect..., well, you do the math.
I still maintain that it is much easier to just shoot the users.
MH
I remember when this place was cool.
-
With the figures you have given about your tables, my guess is it would take you around 2 hours. Apart from cleaning up your tablespace, system tablespace would also undergo corresponding amount of cleanup time. With around 2000 objects, it takes me almost 10 minutes. Maybe someone else will share their timings.
-- Dilip
-
consdier writing an sqr to truncate all the tables and then issuing
the drop user, we do that to reload test from prod and seems to work well
steve
I'm stmontgo and I approve of this message
-
Try to :
Disable constraints for all tables in the schema
Truncate all tables
Drop tables
Drop user
One, who thinks that the other one who thinks that know and does not know, does not know either!
-
Originally posted by patel_dil
With the figures you have given about your tables, my guess is it would take you around 2 hours. Apart from cleaning up your tablespace, system tablespace would also undergo corresponding amount of cleanup time. With around 2000 objects, it takes me almost 10 minutes. Maybe someone else will share their timings.
When DROP, TRUNCATE, ALTER is issued, actual clean up of data or whatsoever will not happen.
Oracle will only drop in the POINTERS pointing to the Mem Locations, This should not take much time..
Actual Problem might be in droping the Pointers itself, as i faced some problems in droping user & got internal error ORA 600, because Oracle was unable to DROP a object in that user.
What i would suggest is try droping all objects, and you will know which object is the culprit.
You could loop and use DYNAMIC SQL exec to drop all objects in that schema.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Originally posted by abhaysk
When DROP, TRUNCATE, ALTER is issued, actual clean up of data or whatsoever will not happen.
Oracle will only drop in the POINTERS pointing to the Mem Locations,
Oh, it takes much more for Oracle to drop a database object than just to "drop some poiters pointing to mem locations". It takes *many* updates/deletes/inserts into data dictionary tables and/or fixed tables to drop a single table.
For example, you drop a table, and oracle has to do:
- delete one row from OBJ$
- delete one or more rows from COL$
- probably delte some rows from IND$
- delete one or more rows from SEG$
- delete one or more rows from UET$
- insert one or more row into FET$
- probably delete some rows from IDL_* tables
- ...
And all those tables are quite heavily indexed and I have named just a few dictionary tables (perhaps less than 10%) that must be taken care of just for one simple DROP TABLE statement. So you can immagine all this work might take quite a significant amount of time. Not to speak about all the work that SMON has to perform afterwards to actually clean up the space in tablespaces.
So it is much more to do than just to drop few "pointers".
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|