drop user <username> cascade... way too long...

[webfuture]

When I drop a user with cascade on an oracle9i (2 xeon 3gig). it takes forever (30 minutes plus). While this is going, my CPU are idle and memory is stable (no swap).

Is there something I can do, check to make it faster?

Thanks in advance,

Simon

 

[SantaMufasa]

Simon, When a "DROP USER..." takes forever (where "forever" >= "my patience"), that usually means that the user owned many objects with many extents. In a DROP USER scenario, dropping associated extents is what accounts for 99% of the time consumption. You can possibly monitor the progress of the long-running DROP USER by iteratively executing this command:

select count(*) from dba_extents where owner = '<name of DROPping user>';

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[webfuture]

OK I ran the above command and got 822. Waited 2 minutes and got the same. All this while droping a user with cascade.

Anything more to try?

Thanks,

Simon

 

[webfuture]

I waited 10 minutes and now the count is 699... good I think but still slow. I have around 12 users to drop! at more than 50 minutes each, I think I will sleep at the office todays!!!

Simon

 

[Turkbear]

Hi,
Unless you need the space, just disable tghe user's accounts.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

Turkbear's suggestion is great if your DROPs are to prevent users from accessing the accounts.

I cannot guess what is taking so long. You should also be able to monitor space reclamation with my "Freespace.sql" script that you can copy from thread186-956867 or thread185-1034245.

Let us know how things progress.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[webfuture]

Thanks to all... I will check this out this weekend and get back to you... need to run now

Simon

 

[webfuture]

Ok I ran the freespace script... Here's the result...

                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ----
CWMLITE            3   34,359,721,984       20,971,520        9,830,400   34,349,891,584     99 Yes  'E:\ORACL
DRSYS              4   34,359,721,984       20,971,520       10,158,080   34,349,563,904     99 Yes  'E:\ORACLE\
EXAMPLE            5   34,359,721,984      155,975,680      155,844,608   34,203,877,376     99 Yes  'E:\ORACL
INDX               6   34,359,721,984       26,214,400           65,536   34,359,656,448     99 Yes  'E:\ORACLE\ORADATA\
INFOSILEM         11    2,147,483,648    2,147,450,880    2,147,131,392          352,256      0 Yes  'E:\ORACLE\ORADATA
INFOSILEM         12    2,147,483,648    2,147,450,880    1,865,023,488      282,460,160     13 Yes  'E:\ORACLE\
INFOSILEM         13    2,147,483,648    1,566,195,712    1,447,026,688      700,456,960     32 Yes  'E:\ORACLE\
INFOSILEM         14    2,147,483,648      565,026,816      547,667,968    1,599,815,680     74 Yes  'E:\ORACLE\
ODM                7   34,359,721,984       20,971,520        9,764,864   34,349,957,120     99 Yes  'E:\ORACLE\OR
SYSTEM             1   34,359,721,984      849,346,560      845,938,688   33,513,783,296     97 Yes  'E:\ORACLE
TOOLS              8   34,359,721,984       10,485,760        6,356,992   34,353,364,992     99 Yes  'E:\ORACLE\
UNDOTBS1           2   34,359,721,984    1,373,634,560       82,247,680   34,277,474,304     99 Yes  'E:\ORACL
USERS              9   34,359,721,984       26,214,400           65,536   34,359,656,448     99 Yes  'E:\ORACLE\ORADATA
XDB               10   34,359,721,984       49,152,000       46,399,488   34,313,322,496     99 Yes  'E:\ORACLE\OR
                     ---------------- ---------------- ---------------- ----------------
sum                   352,187,154,432    8,980,062,208    7,173,521,408  345,013,633,024

I restarted the oracle server, check the disk space (166 gig free) and it still take more than 30 minutes for most users.

Any more ideas, hints from oracle guru...

Thanks,

Simon