V$BLOCKING_QUIESCE

Oracle 11gR1

V$BLOCKING_QUIESCE indicates if a session is blocking, or would block, a quiesce operation.

Column	Datatype	Description
SID	NUMBER	Session identifier

Note:

A database can be put into a quiesced mode by issuing the statement:

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 (e.g., enqueues). No inactive sessions are allowed to become active. The statement may take a long time for active sessions to become inactive. Querying V$BLOCKING_QUIESCE view returns the sessions that are blocking the quiesce operations.

More information can be revealed by joining with V$SESSION:

select bl.sid, user, osuser, type, program

from v$blocking_quiesce bl, v$session se

where bl.sid = se.sid;

Querying ACTIVE_STATE column of V$INSTANCE view reveals the current state of an instance. Its values are:

• NORMAL: normal unquiesced state.
• QUIESCING: being quiesced, but some non-DBA sessions (username not as SYSTEM or SYS) are still active.
• QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

A quiesced database can be restored to normal operation by issuing the following statement:

ALTER SYSTEM UNQUIESCE;

Oracle dynamic performance views