Home » RDBMS Server » Server Administration » disconnect the schema (19C)
disconnect the schema [message #687643] Mon, 24 April 2023 12:02 Go to next message
deepakdot
Messages: 89
Registered: July 2015
Member
Hi

Is there a way, I can stop (disconnect all the sessions) from a schema and don't allow new connections to the schema .

If I use, "ALTER USER scott account lock", its not allowing new connections. But with existing connections before lock, I am able to access table. I need both not to work.

Finding all the sessions from v$session for a schema and kill them, will take time. is there a single command which will disconnect all existing sessions and not allow new sessions to a schema ?

Regards,
Deepak

Re: disconnect the schema [message #687644 is a reply to message #687643] Mon, 24 April 2023 13:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no single command but it is easy to create a procedure that will execute this task in a few seconds.
SQL> create or replace procedure stop_user (p_user in varchar2)
  2  is
  3  begin
  4    execute immediate 'alter user '||dbms_assert.schema_name(p_user)||' account lock';
  5    for sess in (select sid||','||serial# sid from v$session where username = dbms_assert.schema_name(p_user))
  6    loop
  7      execute immediate 'alter system disconnect session '''||sess.sid||''' immediate';
  8    end loop;
  9  end;
 10  /

Procedure created.

SQL> exec stop_user('SCOTT')

PL/SQL procedure successfully completed.
The owner must have ALTER USER and ALTER SYSTEM privileges directly granted to it.

Re: disconnect the schema [message #687645 is a reply to message #687644] Mon, 24 April 2023 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Of course, you'd add some exception blocks to handle "expected" exceptions like a session has disappeared between querying v$session and alter system.

Re: disconnect the schema [message #687646 is a reply to message #687645] Mon, 24 April 2023 15:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and some controls to prevent from locking some important accounts and killing important sessions.
And maybe some outputs...

Re: disconnect the schema [message #687647 is a reply to message #687646] Tue, 25 April 2023 00:00 Go to previous message
deepakdot
Messages: 89
Registered: July 2015
Member
Thanks Michel. I think the pl/sql program you suggested would work for me.
Previous Topic: No SCN in controlfile after BACKUP TO TRACE
Next Topic: Database 23c Free now available
Goto Forum:
  


Current Time: Thu Mar 28 17:56:53 CDT 2024