How to switch Oracle in Single User-Multi Session Mode

During a migration it may be important, that nobody can connect to the database. You may have two possibilities to achieve this

On Table Level using LOCK TABLE

On Database Level using RESTRICTED SESSION

Exclusive Table Locks (X)

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:

$ sqlplus scott/tiger

SQL> GRANT ALL ON emp TO PUBLIC;
SQL> LOCK TABLE emo IN EXCLUSIVE MODE;

$ sqlplus test/test

SQL> SELECT * FROM scott.emp; (This works)
SQL> DELETE FROM scott.emp WHERE empno = 7369; (Waiting ...)

Permitted Operations

Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.

Prohibited Operations

An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

Lock Duration

Oracle releases all locks acquired by the statements within a transaction when you either commit or roll back the transaction. Oracle also releases locks acquired after a savepoint when rolling back to the savepoint. However, only transactions not waiting for the previously locked resources can acquire locks on the now available resources. Waiting transactions will continue to wait until after the original transaction commits or rolls back completely.

Oracle in Single User Mode

You may use the RESTRICTED SESSION system privilege to switch the database in single user mode for migrations.

RESTRICTED SESSION - Specifies whether logon to Oracle is restricted

ENABLE - Allows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.

DISABLE - Reverses the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle. This is the default.

You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

In a first step (before the migration), you shutdown the database an start it again to be sure that all users are disconnected. Now revoke the RESTRICTED SESSION system privilege from most users, this system privilege is normally not used.

$ svrmgrl
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
svrmgr> STARTUP;
svrmgr> SPOOL revoke_restricted_session;
svrmgr> SELECT 'REVOKE restricted session FROM '
username ';' FROM dba_users
WHERE username NOT IN ('SYS','SYSTEM');
svrmgr> SPOOL OFF;
svrmgr> @revoke_restricted_session.log;
svrmgr> ALTER SYSTEM ENABLE RESTRICTED SESSION;

As user SYS you can now perform the migration. If an ordinary user tries to connect, he will get the following error messge:

sqlplus scott/tiger

ERROR: ORA-01035: ORACLE only available to users
with RESTRICTED SESSION privilege

After the migration to not to forget, to disable the RESTRICTED SESSION system privilege

svrmgr> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Source: www dot akadia dot com