There are no jobs existing with these users. Also, it is not on RAC. On Mon, Oct 4, 2010 at 8:15 AM, Taral Desai <taral.desai@xxxxxxxxx> wrote: > 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 > -- Thanks n Regards, Mayur Nagarsheth Cell : (+1)214-364-8271 http://www.linkedin.com/in/mayurnagarsheth http://lyle.smu.edu/~mnagarshet/