OraFAQ Forum: Server Administration » disconnect the schema

Home » RDBMS Server » Server Administration » disconnect the schema (19C)

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

disconnect the schema [message #687643]

Mon, 24 April 2023 12:02

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

Report message to a moderator

Re: disconnect the schema [message #687644 is a reply to message #687643]

Mon, 24 April 2023 13:10

Michel Cadot
Messages: 68509
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.

Report message to a moderator

Re: disconnect the schema [message #687645 is a reply to message #687644]

Mon, 24 April 2023 13:34

Michel Cadot
Messages: 68509
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.

Report message to a moderator