ALTER SYSTEM statement dynamically alter the database instance and user must have
ALTER SYSTEM system privilege.
While performing application maintenance, generally all sessions need to close and restricts new session. Use below statement to restrict sessions.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
You can kill any existing sessions by
SESSION clause of the
SYSTEM statement. You can see all active sessions by issuing below statement.
SQL> SELECT sid, serial#, username FROM v$session;
ALTER SYSTEM KILL SESSION '29, 34';
After completion of maintenance, You can allow users to create sessions by issuing below statement.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
Clearing the Shared Pool
Before beginning performance analysis, you might want to clear the shared pool. Issue the below statement to clear shared spool.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
Forcing a Checkpoint
Issue the below statement to force check point.
SQL> ALTER SYSTEM CHECKPOINT;
Enabling Resource Limits
Issue the below statement to enable resource limit.
SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
Forcing a Log Switch
While renaming or moving the log file, it is not possible for the current logging file. Then we need to switch the log file manually. Issue the below statement to force log switch.
SQL> ALTER SYSTEM SWITCH LOGFILE;
Disconnecting a Session
Issue the below statement to disconnects user’s session, using the
SERIAL# values from
SQL> ALTER SYSTEM DISCONNECT SESSION '10, 18' POST_TRANSACTION;