If we want only DBA transactions to be allowed in the database for certain important activities, we QUIESCE a database by issuing the command ALETR SYSTEM QUIESCE DATABASE. The advantage of this command is, it will not terminate the sessions of non DBA users and if there are any active transactions for any user those will continue to run until they are completed but once those transaction becomes inactive, it will appear that the sessions are hung and will not allow any transactions at that point of time, only the DBAs can run the transactions. Once the session is UNQUIESCE by issuing the command ALTER SYSTEM UNQUIESCE, the normal users will be able to do the transactions.
In the example below, we have logged into a server and verified that we are able to query from a user called test before quiescing , and when we quiesce database, the session seems to be hung and as soon as we unquiesce the database, the command gets executed successfully.
Querying from test(non DBA user) users schema, since we have not quiesce the database the query ran successfully.
Now we have quiesced the database and tried to run a query from test users, here the sessions seems to be hung for the test users query.
Now we unquiesced the database and the query completed successfully.
Note: Utmost care should be taken while using quiesce/unquiesce since it will prevent any other users transactions.
No comments:
Post a Comment