OraFAQ Forum: Server Administration » ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT

Home » RDBMS Server » Server Administration » ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT [message #452288]

Wed, 21 April 2010 02:18

Frank
Messages: 7901
Registered: March 2000

Senior Member

Part of the rebuild process of our development environment is dropping and recreating the database user the AppServer uses to connect.
Since the application server is configured to reconnect as soon as a connection drops, I thought I'd shutdown the database and restart it in restricted mode.
However, yesterday I still ran into an "ORA-01940: cannot drop a user that is currently connected" error.
The only thing I can think of is that at startup, the database starts rolling forward & back any unfinished transactions.
Could that be the issue?
(and no, the user does not have restricted session privs Smile

)

Report message to a moderator

Re: ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT [message #452297 is a reply to message #452288]

Wed, 21 April 2010 04:04

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

Hi Frank,

Please let me know the DB version?

Thanks-
P

Report message to a moderator

Re: ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT [message #452299 is a reply to message #452297]

Wed, 21 April 2010 04:20

Frank
Messages: 7901
Registered: March 2000

Senior Member

11g Enterprise Edition Release 11.2.0.1.0

Is there a difference in behaviour between the various versions in this aspect?

[Edit: changed version. Took version of my local db initially Embarassed

]

[Updated on: Wed, 21 April 2010 04:24]

Report message to a moderator

Re: ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT [message #452305 is a reply to message #452288]

Wed, 21 April 2010 04:35

pokhraj_d
Messages: 117
Registered: December 2007

Senior Member

First check if there is no duplicate of SQLPLUS_PRODUCT_PROFILE and/or DUAL.

SQL> SELECT owner, object_id, object_type, object_name, status
FROM dba_objects
WHERE object_name = 'SQLPLUS_PRODUCT_PROFILE';

SQL> SELECT owner, object_id, object_type, object_name, status
FROM dba_objects
WHERE object_name = 'DUAL';
If no duplicate row then this is Bug#3799696

Thanks-
P

Report message to a moderator

Re: ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT [message #452333 is a reply to message #452305]

Wed, 21 April 2010 07:00

Frank
Messages: 7901
Registered: March 2000

Senior Member

I guess you got the wrong bug number, since mentioned bug applies to versions < 10.2, which I don't have. (both the version I initially provided and the version I edited it to)
Besides, the bug description has nothing to do with what I described.

Report message to a moderator

Re: ORA-01940 Cannot drop user that is currently connected after STARTUP RESTRICT [message #452337 is a reply to message #452333]

Wed, 21 April 2010 07:33

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Three things that come to my mind:

1) Are there any queues/advanced replication involved? (Is the user a "owner" of anything in dba_queues, dba_apply, dba_capture, dba_propagation ?)

2) I guess you already checked if the user is in v$session.

3) Are there any jobs / schedules configured with that user that might start after you start the db?

Report message to a moderator

Previous Topic:	Calculation of initial extent size of table
Next Topic:	Oracle JDBC classes12.jar file - unable to extract

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 09:42:31 CST 2025