If this is RAC then also check gv$session. On Sat, Oct 2, 2010 at 11:28 AM, Saurabh Sood <email2sood@xxxxxxxxx> wrote: > Hi, > > you can set 10046 trace before using drop user command and check what are > the recursive statements that are getting executed while this drop user > command. > > Regards, > Saurabh Sood > www.askdba.org/weblog > > > On Sat, Oct 2, 2010 at 9:41 PM, mayur nagarsheth < > mayurpnagarsheth@xxxxxxxxx> wrote: > >> Hello, >> >> >> >> I am working on an issue where I need to drop users. These users have no >> objects, no active sessions but still it gives me below error: L >> >> >> >> *ORA-01940: cannot drop a user that is currently connected* >> >> >> >> Has any one come across such an issue or does any one have suggestions >> other than bouncing the database? I am still searching for other >> alternative. >> >> >> >> I have rasied SR with Oracle but nothing seems to be effective until now >> and only suggests for startup restrict and drop the user. >> >> >> FYI, I checked the following just to ensure several things pertaining to >> the user >> >> >> >> 1) select s.sid, s.serial#, s.status, p.spid >> >> from v$session s, v$process p >> >> where s.username = 'ACOSTA' >> >> and p.addr (+) = s.paddr; >> >> - no rows selected >> >> >> >> 2) Checked that the user is not associated with any active jobs: >> >> >> >> select job from dba_jobs where log_user='ACOSTA'; >> >> - no rows selected >> >> >> >> >> >> 3) Finally, checked that the user is not associated with any Streams >> replication queues: >> >> >> >> select >> >> queue_table, >> >> qid >> >> from >> >> dba_queues >> >> where owner='ACOSTA'; >> >> - no rows selected >> >> >> >> select >> >> apply_name >> >> from >> >> dba_apply >> >> where >> >> queue_owner='ACOSTA'; >> >> - no rows selected >> >> >> >> select >> >> capture_name, >> >> queue_name, >> >> from >> >> dba_capture >> >> where >> >> queue_owner='ACOSTA'; >> >> - no rows selected >> >> >> >> select >> >> propagation_name >> >> from >> >> dba_propagation >> >> where >> >> source_queue_owner='ACOSTA' >> >> or >> >> destination_queue_owner='ACOSTA'; >> >> - no rows selected >> >> >> >> >> >> Thank you! >> >> >> >> >> >> -- >> Thanks n Regards, >> Mayur Nagarsheth >> Cell : (+1)214-364-8271 >> http://www.linkedin.com/in/mayurnagarsheth >> >> http://lyle.smu.edu/~mnagarshet/ <http://lyle.smu.edu/%7Emnagarshet/> >> >> > > > -- > SAURABH SOOD > ORA-DBA > -- Thanks & Regards, Taral Desai