mayur nagarsheth
2010-10-02 16:11:41 UTC
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!
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/
Thanks n Regards,
Mayur Nagarsheth
Cell : (+1)214-364-8271
http://www.linkedin.com/in/mayurnagarsheth
http://lyle.smu.edu/~mnagarshet/