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 ...)
Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.
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.
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.
Specifies whether logon to Oracle is restricted
Qllows only users with RESTRICTED SESSION system privilege to log on to Oracle. Existing sessions are not terminated.
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.
svrmgr> CONNECT SYS AS sysdba;
svrmgr> SHUTDOWN IMMEDIATE;
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:
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;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60576/，如需转载，请注明出处，否则将追究法律责任。