DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: Drop user takes long time...

  1. #1
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203

    Angry 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!"

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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!

  4. #4
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    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!"

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    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.

  6. #6
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    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

  7. #7
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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

  8. #8
    Join Date
    Nov 2001
    Posts
    335
    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!

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width