Oracle: SQL statement ALTER SYSTEM

set events

Starting with Oracle 11g, setting an event systemwide affects all connected sessions, not only the new ones.

alter system set events '…';

For example: the following command causes the systemstate to be dumped when an ORA-00054 error is thrown:

alter system set events '54 trace name systemstate level 258';

Modify init parameter

alter system set init-parameter-name = new-value;
alter system set init-parameter-name = new-value scope = …;
alter system set init-parameter-name = new-value comment = '…';
alter system set init-parameter-name = new-value sid = '…';
alter system set init-parameter-name = new-value container = …;

Restore init-parameter to default value:

alter system reset init-parameter-name;

scope is one of

memory
spfile
both

sid is either the value of a sid or *.

container is one of

current
all

Flush …

alter system flush shared_pool;
alter system flush global context;
alter system flush buffer_cache;
alter system flush flash_cache;
alter system flush redo to … …;
alter system flush passwordfile_metadata_cache;

Terminating sessions

Sessions can be ended with the following statements:

alter system disconnect session 'sid,serial'                   [ immediate | noreplay ];
alter system disconnect session 'sid,serial' post_transaction  [ immediate | noreplay ];
alter system kill       session 'sid,serial'                   [ immediate | noreplay ];
alter system kill       session 'sid,serial,@instance_id'      [ immediate | noreplay ];

23c also comes with the force option to terminate a session more forcefully than using immediately (i. e. not waiting for transactions to be rolled back, session locks to be released and the session state to be recovered):

alter system kill       session 'sid,serial'                     force;

The values for sid and serial can be found in v$session

alter system kill session performs the following actions:

Terminates the session
Rolls back its transaction
Releases its locks
Frees its resources

Sessions that perform network I/O (for example because they're waiting for a replay of a remote database) or are rolling back a transaction cannot be immediately terminated.

In such a case, the session that executes alter system kill session waits up to 60 seconds. If this time period is reached, it receives a messages that indicates that the session is marked to be terminated (MOS Notes 107686.1, 161794.1).

As per MOS Note 1020720.102 (ALTER SYSTEM KILL Session Marked for Killed Forever), a killed session waits for for a SQLNet message from client to which it can respond with ORA-00028: your session has been killed. Only when this message is received, PMON will take ownership of the process and clean up any resources allocated by that process.

Killing one of the background processes DBWR, LGWR, SMON or PMON will crash the instance.

X$KZVDVCLAUSE

select *
from
   x$kzvdvclause
order by
   clause_name,
   parameter_name;

ADG_REDIRECT_DML
ADG_REDIRECT_PLSQL
ADVISE
AFFINITY
ARCHIVE LOG	ALL
ARCHIVE LOG	CHANGE
ARCHIVE LOG	CURRENT
ARCHIVE LOG	GROUP
ARCHIVE LOG	LOGFILE
ARCHIVE LOG	NEXT
ARCHIVE LOG	SEQUENCE
CHECK DATAFILES
CHECKPOINT
CLOSE DATABASE LINK
COMMIT IN PROCEDURE
COPY LOGFILE
DISTRIBUTED RECOVERY
DUMP	DATAFILE
DUMP	FLASHBACK
DUMP	LOGFILE
DUMP	REDO
DUMP	TEMPFILE
DUMP	UNDO
END SESSION	DISCONNECT SESSION
END SESSION	KILL SESSION
EXCLUSIVE INSTANCE
FLUSH	BUFFER_CACHE
FLUSH	FLASH_CACHE
FLUSH	GLOBAL CONTEXT
FLUSH	PASSWORDFILE_METADATA_CACHE
FLUSH	REDO
FLUSH	SHARED_POOL
GUARD
ILM	ROW ACCESS TRACKING
ILM	ROW MODIFICATION TRACKING
LOGICAL REPLICATION
PARALLEL DDL
PARALLEL DML
PARALLEL QUERY
QUIESCE	QUIESCE RESTRICTED
QUIESCE	UNQUIESCE
REFRESH	LDAP_REGISTRATION
REGISTER
RESET
RESUMABLE
RESUME
SECURITY	RESTRICTED SESSION
SECURITY	SET ENCRYPTION KEY
SECURITY	SET ENCRYPTION WALLET
SET	APPLICATION ACTION
SET	APPLICATION MODULE
SET	CONSTRAINTS
SET	CONTAINER
SET	CURRENT_SCHEMA
SET	EDITION
SET	ERROR_ON_OVERLAP_TIME
SET	EVENTS
SET	FLAGGER
SET	GLOBAL_TOPIC_ENABLED
SET	ILM VISIBILITY
SET	INSTANCE
SET	ISOLATION_LEVEL
SET	LDAP_REGISTRATION_ENABLED
SET	LDAP_REG_SYNC_INTERVAL
SET	LOG_READ_ONLY_VIOLATIONS
SET	SINGLETASK DEBUG
SET	SQL_TRANSLATION_PROFILE
SET	STANDBY_MAX_DATA_DELAY
SET	TIME_ZONE
SET	USE_PRIVATE_OUTLINES
SET	USE_STORED_OUTLINES
SET
SHARD DDL
SHARD OPERATIONS
SHUTDOWN
SUSPEND
SWITCH LOGFILE
SYNC WITH PRIMARY
TX RECOVERY