Quiescing the database.

Quiesce means pausing or altering the state of running processes.

Some operations on database objects will fail if non-DBA queries reference the object during the operation, such as moving a table to a new schema.To prevent users being affected by these operations DBAs often shutdown the database and open it in restricted mode. This has an obvious affect on availability as users are locked out of the system until the restriction is lifted.

Why do we quiesce our database?

1.) The quiesce feature is useful when performing table maintenance or complicated data maintenance.

2.) The main advantage of this method is that users do not loose their sessions during the process.

3.) The shared pool does not have to "warm up" after a shutdown so performance should return to normal instantly.

4.) This method is advantageous when performing ALTER TABLE, CREATE OR REPLACE PACKAGE and EXP/IMP operations.

5.) Without the ability to quiesce the database, you would need to shut down the database and reopen it in restricted mode. This is a serious restriction, especially for systems requiring 24 x 7 availability. Quiescing a database is much a smaller restriction, because it eliminates the disruption to users and the downtime associated with shutting down and restarting the database.

How to quiesce the database?

Connect to the database as sys or system user and type the below command.

SQL>ALTER SYSTEM QUIESCE RESTRICTED;

As soon as the above statement is issued, all attempts to activate an inactive non-DBA session are blocked. Once finished the database can be returned to a fully available state using:

How to unquiesce the database?

SQL>ALTER SYSTEM UNQUIESCE;

When you type the above command everything will back to normal;

Enjoy:-)