Re: ORA-01940: cannot drop a user that is currently connected
- From: Taral Desai <taral.desai@xxxxxxxxx>
- To: email2sood@xxxxxxxxx
- Date: Mon, 4 Oct 2010 08:15:12 -0500
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
Other related posts:
