How to monitor DROP USER statement is hung or running?

Hi All,

We were doing a refresh activity in one of our data warehouse environments and we had to drop the existing schema in that database and recreate that user and fill with the latest dump.

So as usually i issued the DROP USER command and it had been running after 30-40 minutes. I worried and was trying to check whether that’s running, hung or any other anomaly was happening…

I didn’t see anything in the v$session_longops even but i didn’t see the record corresponding to this drop operation.

Then i used another method. Of course it doesn’t looks elegant but i liked it very much.

I opened another session and was just issuing

select count(0) from dba_objects where owner=<OWNER>;

And i was keep on issuing it…see the result below

10:20:16 SYS@CLDTRNG2 > select count(0) from dba_objects where owner=<OWNER>;

  COUNT(0)
———-
    129656

10:20:50 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    129647

10:20:53 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    129643

10:20:55 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    126252

10:27:15 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    103607

10:33:11 SYS@CLDTRNG2 > /

  COUNT(0)
———-
    103607

10:33:13 SYS@CLDTRNG2 > /

  COUNT(0)
———-
     95615

So, when it becomes 0, we can say the user is dropped. The other session too reports that “USER DROPPED”.

This just help us to realize that DROP USER command is working perfectly in the background and will save us from standing on toes 🙂

Hope this helps.

I’m welcome to your feedback and suggestions.

CSM

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: