alter system dump datafile …;
alter system dump logfile …
alter system dump redo dba min … … dba max … … con_id …;
alter system dump undo_header 'segment_name';
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 a the systemstate to be dumped when an ORA-00054 error is thrown:
alter system set events '54 trace name systemstate level 258';
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
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.