Martin Zahn, Akadia AG Information Technology, CH-3672 Oberdiessbach
15.09.2007
Overview
Using ALTER SYSTEM you can clear all data from the SGA (system global area). This is useful to clear existing data and re-load fresh data. In Oracle 10, it becomes possible for users to flush even the cache buffer.
Flush Buffer Cache
Use the following statement to flush the buffer cache.
SELECT o.owner, o.object_type, substr(o.object_name,1,10) objname, b.objd, b.status, count(b.objd) FROM v$bh b, dba_objects o WHERE b.objd = o.data_object_id AND o.owner not in ('SYS','SYSTEM','SYSMAN') GROUP BY o.owner, o.object_type, o.object_name, b.objd, b.status;
SELECT o.owner, o.object_type, substr(o.object_name,1,10) objname, b.objd, b.status, count(b.objd) FROM v$bh b, dba_objects o WHERE b.objd = o.data_object_id AND o.owner not in ('SYS','SYSTEM','SYSMAN') GROUP BY o.owner, o.object_type, o.object_name, b.objd, b.status;
However, note that this clause is intended for use only on a test database. It is not advisable to use this clause on a production database, because subsequent queries will have no hits, only misses.
Flush Shared Pool
The FLUSH SHARED POOL clause of ALTER SYSTEM lets you clear all data from the shared pool in the SGA (system global area). This is a useful feature to clear existing data and re-load fresh data.
SQL> alter system flush buffer_pool;
Initiate Checkpoint
A checkpoint performs the following three operations:
Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk. It's the DBWR that writes all modified database blocks back to the datafiles.
The latest SCN is written (updated) into the datafile header.
The latest SCN is also written to the controlfiles.
The following events trigger a checkpoint.
Redo log switch
LOG_CHECKPOINT_TIMEOUT has expired
LOG_CHECKPOINT_INTERVAL has been reached
DBA requires so (alter system checkpoint)
Additionally, if a tablespace is hot backuped, a checkpoint for the tablespace in question is taking place. While redo log switches cause a checkpoint, checkpoints don't cause a log switch.
SQL> alter system checkpoint;
Switch Logfile
A log switch occurs when one online redo log has been filled and the next online redo log is going to be filled. A log switch always triggers a checkpoint.