| Alter System |
| 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. |
| DEFERRED |
Most ALTER SYSTEM commands can use the DEFERRED suffix to modify all future sessions but not affect the current session. |
| SCOPE |
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.
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. |
| |
| Flush Buffer Cache |
ALTER SYSTEM FLUSH BUFFER_CACHE; |
| ALTER SYSTEM FLUSH BUFFER_CACHE; |
| Flush Shared Pool |
ALTER SYSTEM FLUSH SHARED_POOL; |
| ALTER SYSTEM FLUSH SHARED_POOL; |
| Disconnect Session |
ALTER SYSTEM DISCONNECT SESSION '<SID>,<SERIAL#>' [POST_TRANSACTION]; |
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; |
| Kill Session |
ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>[, @INSTANCE_NUMBER]' [IMMEDIATE]; |
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; |
| Local Listener Registration |
ALTER SYSTEM SET LOCAL_LISTENER='<listener_name>'; |
| ALTER SYSTEM SET LOCAL_LISTENER = 'CDBLISTENER'; |
| Register with the listener |
ALTER SYSTEM REGISTER; |
| ALTER SYSTEM REGISTER; |
| Remote Listener Registration |
ALTER SYSTEM SET REMOTE_LISTENER='<listener_name>'; |
| ALTER SYSTEM SET REMOTE_LISTENER = 'PDBLISTENER'; |
| Determine If Killed Session Is Rolling Back 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; |
| |
| Is SGA sizing dynamic |
SQL> show sga
Total System Global Area 289406976 bytes
Fixed Size 788808 bytes
Variable Size 99612344 bytes
Database Buffers 188743680 bytes
Redo Buffers 262144 bytes
SQL> ALTER SYSTEM SET sga_max_size=250M SCOPE=SPFILE;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 264241152 bytes
Fixed Size 788448 bytes
Variable Size 238024736 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET sga_max_size=180m;
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 250M then the next time you start your instance,
a full 250MB 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 back to 180M, but can only be modified with the 'scope=spfile' clause tacked on
(or by editing an init.ora) - thus 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 actually set to.
Different operating systems, for example Solaris, may behave differently. |
