Oracle Alter System
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Quote from the online docs at http://tahiti.oracle.com:
"Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance. The settings stay in effect as long as the database is mounted. When you use the ALTER SYSTEM statement in a multitenant container database (CDB), you can specify some clauses to alter the CDB as a whole and other clauses to alter a specific pluggable database (PDB)"
Data Dictionary conn / as sysdba

desc v$parameter

SELECT issys_modifiable, COUNT(*)
FROM v$parameter
GROUP BY issys_modifiable;

SELECT ispdb_modifiable, COUNT(*)
FROM v$parameter
GROUP BY ispdb_modifiable;

SELECT isinstance_modifiable, COUNT(*)
FROM v$parameter
GROUP BY isinstance_modifiable;

-- parameters changed during installation
SELECT ismodified, COUNT(*)
FROM v$parameter
GROUP BY ismodified;

SELECT name, value
FROM v$parameter
WHERE ismodified = 'MODIFIED';

-- parameters changed following installation
SELECT isadjusted, COUNT(*)
FROM v$parameter
GROUP BY isadjusted;

SELECT name, value
FROM v$parameter
WHERE isadjusted = 'TRUE';

-- deprecated parameters: make sure you are not setting any
col name format a32
col value format a49

SELECT name, value
FROM v$parameter
WHERE isdeprecated = 'TRUE';
System Privileges
ALTER SYSTEM    
 
Archive Log ALTER SYSTEM <archivelog clause> follow the link at page bottom
Checkpoint ALTER SYSTEM CHECKPOINT [GLOBAL];
ALTER SYSTEM CHECKPOINT;
Check Datafiles -- only valid with a RAC configuration: Global indicates all instances
ALTER SYSTEM CHECK DATAFILE <GLOBAL | LOCAL>;
ALTER SYSTEM CHECK DATAFILES LOCAL;
Commit ALTER SYSTEM SET <commit clause> follow the TRANSACTIONS link at page bottom
Distributed Recovery ALTER SYSTEM <ENABLE | DISABLE> DISTRIBUTED RECOVERY;
ALTER SYSTEM enable DISTRIBUTED RECOVERY;
ALTER SYSTEM disable DISTRIBUTED RECOVERY;
Disconnect Session ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL#>' [POST_TRANSACTION] [< IMMEDIATE | NOREPLAY>];
set linesize 121

SELECT sid, serial#, osuser, username, schemaname
FROM v_$session
WHERE sid = (SELECT sid FROM v_$mystat WHERE rownum = 1);

ALTER SYSTEM DISCONNECT SESSION '8,694' POST_TRANSACTION;
Flush Buffer Cache ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
Flush Redo ALTER SYSTEM FLUSH REDO TO <target_db_name> [[NO] [CONFIRM] [APPLY]]
ALTER DATABASE CLOSE;

ALTER SYSTEM FLUSH REDO TO orabase2 CONFIRM APPLY;
Flush Shared Pool ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;
Kill Session ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>[, @INSTANCE_NUMBER]' [< IMMEDIATE | NOREPLAY>];
set linesize 121

SELECT sid, serial#, osuser, username, schemaname
FROM v_$session
WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

ALTER SYSTEM KILL SESSION '8,694';

ALTER SYSTEM KILL SESSION '8,694,@2';

ALTER SYSTEM KILL SESSION '8,694,@2' IMMEDIATE;
Determine if a killed session is rolling back a transactions This shows the user who's transaction is being rolled back If this number, xidusn, is decreasing then the transaction is rolling back. If it is increasing then the transaction is moving forward.
SELECT a.sid, a.username, b.xidusn rollback_seg_no,
b.used_urec undo_records, b.used_ublk undo_blocks
FROM gv$session a, gv$transaction b
WHERE a.saddr = b.ses_addr;
Local Listener Registration ALTER SYSTEM SET LOCAL_LISTENER='<listener_name>';
ALTER SYSTEM SET LOCAL_LISTENER = 'CDB_LISTENER';
Quiesce ALTER SYSTEM <QUIESCE [RESTRCTED] | UNQUIESS>;
-- only valid in conjunction with DBMS_RESOURCE_MANAGER
ALTER SYSTEM QUIESCE;
ALTER SYSTEM UNQUIESCE;
Register with the listener ALTER SYSTEM REGISTER;
ALTER SYSTEM REGISTER;
Relocate Client ALTER SYSTEM <relocate client clause> is relevant only with Oracle Flex ASM and is not covered in the Library
Remote Listener Registration ALTER SYSTEM SET REMOTE_LISTENER='<listener_name>';
ALTER SYSTEM SET REMOTE_LISTENER = 'DG_LISTENER';
Restricted Session ALTER SYSTEM <ENABLE | DISABLE> RESTRICTED SESSION;
ALTER SYSTEM enable RESTRICTED SESSION;
ALTER SYSTEM disable RESTRICTED SESSION;
Resume ALTER SYSTEM RESUME;
ALTER SYSTEM RESUME;
Rolling Migration -- only valid in conjuntion with ASM
ALTER SYSTEM <rolling migration clause> follow the ASM link at page bottom
Shutdown Dispatcher -- only valid with a shared server configuration something best avoided and thus this is not covered in the Library
ALTER SYSTEM SHUTDOWN [IMMEDIATE] <dispatcher_name>;
Suspend ALTER SYSTEM SUSPEND;
ALTER SYSTEM SUSPEND;
Switch Logfile ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
Wallet ALTER SYSTEM SET <encyption wallet> follow the WALLET link at page bottom
 
Set Parameter Value
Full Syntax ALTER SYSTEM SET <parameter_name> = <value> [CONTAINER=<ALL | CURRENT>] [DEFERRED]
[SID = '<sid_name>'] [SCOPE = <BOTH | MEMORY | SPFILE>] [COMMENT='<comment_string>'];
Comment
Allow the association of a comment string with this change in the value of the parameter. If SPFILE is specified the comment will be written to the file. ALTER SYSTEM SET <parameter_name> = <value> [COMMENT '<comment_string>'];
ALTER SYSTEM SET audit_sys_operations=TRUE
COMMENT='Begin auditing SYS'
SCOPE=SPFILE;
 
Container
If the ALTER SYSTEM commands will affect a specific PDB rather than all PDBs specify this clause ALTER SYSTEM SET <parameter_name> = <value> [CONTAINER=<ALL | CURRENT>];
conn sys@pdbdev as sysdba

ALTER SYSTEM SET plsql_code_type=NATIVE
CONTAINER=CURRENT;
 
Deferred
Most ALTER SYSTEM commands can use the DEFERRED suffix to modify all future sessions but not affect the current session ALTER SYSTEM SET <parameter_name> = <value> [DEFERRED];
conn sys@pdbdev as sysdba

ALTER SYSTEM SET plsql_code_type=NATIVE DEFERRED;
 
Reset
Return the value of a parameter to its original installation default ALTER SYSTEM RESET <parameter_name>  SCOPE = SPFILE;
ALTER SYSTEM RESET <parameter_name>  [SID = '<sid_name | *>'];
ALTER SYSTEM RESET plsql_code_type SCOPE=SPFILE;

ALTER SYSTEM RESET plsql_code_type SID='*';
 
Scope
Note MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down.

SPFILE indicates that the change is made in the server parameter file and will only take affect after the database is restarted.

BOTH indicates that the change is made in memory and in the server parameter file.
Specifies when the change takes effect. Scope only affects databases using an SPFILE. With databases started using a PFILE all ALTER SYSTEM commands affect only memory. ALTER SYSTEM SET <parameter_name> = <value> [SCOPE = <BOTH | MEMORY | SPFILE>];
ALTER SYSTEM SET  plsql_code_type = 'NATIVE'
SCOPE=MEMORY;

ALTER SYSTEM SET audit_sys_operations=TRUE
SCOPE=SPFILE;

ALTER SYSTEM SET plsql_code_type = 'NATIVE'
SCOPE=BOTH;
 
SID
Use on a RAC cluster to identify a specific instance

* = all
ALTER SYSTEM SET <parameter_name> = <value> [SID = '<sid_name | *>'];
conn / as sysdba

SELECT instance_name
FROM v$instance;

ALTER SYSTEM SET plsql_code_type = 'NATIVE'
SID = 'orabase';
 
Related Demos
Is the SGA performing sizing dynamic SQL> show parameter sga_max_size
SQL> show sga

Total System Global Area 2505338880 bytes
Fixed Size 2405760 bytes
Variable Size 637536896 bytes
Database Buffers 1845493760 bytes
Redo Buffers 19902464 bytes

SQL> ALTER SYSTEM SET sga_max_size=2400M SCOPE=SPFILE;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2505338880 bytes
Fixed Size 2405760 bytes
Variable Size 671091328 bytes
Database Buffers 1811939328 bytes
Redo Buffers 19902464 bytes
Database mounted.
Database opened.

SQL> ALTER SYSTEM SET sga_max_size=180m;
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


Which proves two things. First, if you set SGA_MAX_SIZE to 2400M then the next time you start your instance, a full 2.5GB of RAM is used by the SGA (give or take a bit of rounding up to the next actual granule border). Yet I changed nothing to do with my shared pool, my buffer cache or my large pool ... so clearly, the *used* bit of my SGA can only be the same as it was before ... as is evidenced by the before and after values displayed for the "Database Buffers" and "Redo Buffers" components, for example.

Secondly, how "dynamic" is the SGA when the parameter which sizes it, SGA_MAX_SIZE, can't actually be dynamically altered, as I demonstrate at the end with an attempt to dynamically set it to 180M: It can only be modified with the 'scope=spfile' clause requiring an instance re-start before the new value is read.

Ergo: SGA_MAX_SIZE is not actually dynamic. And SGA_MAX_SIZE steals all of its memory from the operating system regardless of what your caches and pools are set to.

Different operating systems, for example Solaris, may behave differently.

Related Topics
Archive Log
ASM
DBMS_HEAT_MAP
DBMS_SYSTEM
Startup Parameters
Transactions
Wallet

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved