Archive

Archive for the ‘RESTRICTED SESSION’ Category

RESTRICTED SESSION

February 24, 2011 Leave a comment
SQL> select logins from v$instance;
LOGINS
----------
ALLOWED

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> select logins from v$instance;
LOGINS
----------
RESTRICTED

$ sqlplus <username>/<password>@<serviceid>
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 24 09:26:48 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
System altered.

alter system enable restricted session‘ does not “kick out” user automatically. It just blocks new user who does not have the ‘restrict session’ privilege to login, similar to ‘ALTER SYSTEM QUIESCE RESTRICTED’, which blocks new non-dba users. However, I can scarely see the instances when non-dba users have been granted to the ‘restrict session’ privilege.

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active. For example, If a user issues a SQL query in an attempt to force an inactive session to become active, the query will appear to be hung. When the database is later unquiesced, the session is resumed, and the blocked action is processed.

The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

All non-DBA activity is allowed to proceed. In an Oracle Real Application Clusters environment, this statement is not required to be issued from the same session, or even the same instance, as that which quiesced the database. If the session issuing the ALTER SYSTEM UNQUIESCE statement terminates abnormally, then the Oracle Database server ensures that the unquiesce operation completes.

The ACTIVE_STATE column of the V$INSTANCE view to see the current state of an instance. The column values has one of these values:

  • NORMAL: Normal unquiesced state.
  • QUIESCING: Being quiesced, but some non-DBA sessions are still active.
  • QUIESCED: Quiesced; no non-DBA sessions are active or allowed.
Advertisements