Re: ORA-01940: cannot drop a user that is currently connected

On Mon, Oct 4, 2010 at 8:15 AM, Taral Desai <taral.desai_at_gmail.com> wrote:

> If this is RAC then also check gv$session.
>
> On Sat, Oct 2, 2010 at 11:28 AM, Saurabh Sood <email2sood_at_gmail.com>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_at_gmail.com> 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/

--
http://www.freelists.org/webpage/oracle-l