System Control Statement

      ALTER SYSTEM statement dynamically alter the database instance and user must have ALTER SYSTEM system privilege.

Examples :

Restricting sessions

     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  KILL SESSION clause of the ALTER 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 SID and SERIAL# values from V$SESSION

SQL> ALTER SYSTEM DISCONNECT SESSION '10, 18' POST_TRANSACTION;
Advertisements

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s