Oracle Alter System Version 21c |
---|
General Information | ||||
Library Note |
|
|||
Purpose | Quote from the online docs at http://docs.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 |
|||
System Privileges |
|
|||
Affinity | ALTER SYSTEM <DISABLE | ENABLE> AFFINITY [schema_name.]<table_name> [SERVICE <service_name>]; |
|||
ALTER SYSTEM ENABLE AFFINITY uwclass.servers SERVICE pdbdev; |
||||
Archive Log | ALTER SYSTEM <archivelog clause> |
|||
Follow the "ARCHIVELOG" Link in the Related Topics Section at Page Bottom |
||||
Cancel SQL | ALTER SYSTEM CANCEL SQL '<session_id,serial_number>[,@instance_id][SQL_ID]'; |
|||
ALTER SYSTEM CANCEL SQL '6,51659,@1'; |
||||
Checkpoint | ALTER SYSTEM CHECKPOINT [GLOBAL]; |
|||
ALTER SYSTEM CHECKPOINT; |
||||
Check Datafiles | -- only valid with a RAC configuration: Global indicates all instances |
|||
ALTER SYSTEM CHECK DATAFILES LOCAL; |
||||
Commit | ALTER SYSTEM <commit clause> |
|||
Follow the "TRANSACTIONS" Link in the Related Topics Section at Page Bottom |
||||
Distributed Recovery | ALTER SYSTEM <ENABLE | DISABLE> DISTRIBUTED RECOVERY; |
|||
ALTER SYSTEM enable DISTRIBUTED RECOVERY; |
||||
Disconnect Session | ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL#>' [POST_TRANSACTION] [< IMMEDIATE | NOREPLAY>]; |
|||
set linesize 121 |
||||
Flush Buffer Cache | ALTER SYSTEM FLUSH BUFFER_CACHE; |
|||
ALTER SYSTEM FLUSH BUFFER_CACHE; |
||||
Flush Buffer Pool | ALTER SYSTEM FLUSH BUFFER_POOL <DEFAULT | KEEP | RECYCLE | POOL_2K | POOL_4K | POOL_8K | POOL_16K | POOL_32K | ALL>; |
|||
ALTER SYSTEM FLUSH BUFFER_POOL DEFAULT; |
||||
Flush Flash Cache | ALTER SYSTEM FLUSH FLASH_CACHE; |
|||
ALTER SYSTEM FLUSH FLASH_CACHE; |
||||
Flush Flash Global Context | ALTER SYSTEM FLUSH GLOBAL CONTEXT; |
|||
ALTER SYSTEM FLUSH GLOBAL CONTEXT; |
||||
Flush Global Context | ALTER SYSTEM FLUSH GLOBAL CONTEXT; |
|||
ALTER SYSTEM FLUSH GLOBAL CONTEXT; |
||||
Flush Password File Metadata Cache | ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE; |
|||
ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE; |
||||
Flush Redo | ALTER SYSTEM FLUSH REDO TO <target_db_name> [[NO] [CONFIRM] [APPLY]] |
|||
SELECT name |
||||
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 |
||||
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, |
||||
Listener Registration | ALTER SYSTEM REGISTER; |
|||
ALTER SYSTEM REGISTER; |
||||
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 |
||||
Relocate Client | ALTER SYSTEM <relocate client clause> is relevant only with Oracle Flex ASM and is not covered in the Library |
|||
TBD | ||||
Remote Listener Registration | ALTER SYSTEM SET REMOTE_LISTENER='<listener_name>'; |
|||
ALTER SYSTEM SET REMOTE_LISTENER = 'DG_LISTENER'; |
||||
Reset | ALTER SYSTEM SET <parameter_name> RESET; |
|||
See the "Reset Parameter Value" section of this page below |
||||
Restricted Session | ALTER SYSTEM <ENABLE | DISABLE> RESTRICTED SESSION; |
|||
ALTER SYSTEM ENABLE RESTRICTED SESSION; |
||||
Resume | ALTER SYSTEM RESUME; |
|||
ALTER SYSTEM RESUME; |
||||
Rolling Migration (Start) | ALTER SYSTEM START ROLLING MIGRATON TO '<asm_version>' |
|||
TBD | ||||
Rolling Migration (Stop) | ALTER SYSTEM STOP ROLLING MIGRATION |
|||
ALTER SYSTEM STOP ROLLING PATCH; |
||||
Rolling Patch | ALTER SYSTEM <START | STOP> ROLLING PATCH |
|||
ALTER SYSTEM START ROLLING PATCH; |
||||
Set | ALTER SYSTEM SET <parameter_name> = <value> COMMENT, SID, CONTAINER, SCOPE |
|||
Follow the "STARTUP PARAMETERS" Link in the Related Topics Section at Page Bottom |
||||
Shutdown Dispatcher | -- only valid with a shared server configuration something best avoided and thus this is not covered in the Library |
|||
Suspend | ALTER SYSTEM SUSPEND; |
|||
ALTER SYSTEM SUSPEND; |
||||
Switch Logfile | ALTER SYSTEM SWITCH LOGFILE; |
|||
ALTER SYSTEM SWITCH LOGFILE; |
||||
Wallet Close | ALTER SYSTEM SET <encyption wallet> CLOSE IDENTIFIED BY "<wallet_password | hsm_aut_string>" |
|||
Follow the "STARTUP "WALLET" Link in the Related Topics Section at Page Bottom |
||||
Wallet Open | ALTER SYSTEM SET <encyption wallet> OPEN IDENTIFIED BY "<wallet_password | hsm_aut_string>" |
|||
Follow the "STARTUP "WALLET" Link in the Related Topics Section at Page Bottom |
||||
Wallet Set Encryption Key | ALTER SYSTEM SET ENCRYPTION KEY ... |
|||
Follow the "STARTUP "WALLET" Link in the Related Topics Section at Page Bottom |
||||
Set Parameter Value | ||||
Full Syntax | ALTER SYSTEM SET <parameter_name> = <value> |
|||
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 |
||||
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 |
||||
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 |
||||
Reset | ||||
Return the value of a parameter to its original installation default | ALTER SYSTEM RESET <parameter_name> SCOPE = SPFILE; |
|||
ALTER SYSTEM RESET plsql_code_type SCOPE=SPFILE; |
||||
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' |
||||
SID | ||||
Use on a RAC cluster to identify a specific instance * = all |
ALTER SYSTEM SET <parameter_name> = <value> [SID = '<sid_name | *>']; |
|||
conn / as sysdba |
||||
Related Demos | ||||
Is the SGA performing sizing dynamic | SQL> show parameter sga_max_size 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 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. 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 |
What's New In 21c |
What's New In 23c |
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-2023 Daniel A. Morgan All Rights Reserved | |||||||||
|
||||||||||