CURRENT_SCN and CHECKPOINT_CHANGE#


The V$DATABASE view provides the current SCN at the database level.
The V$DATAFILE view provides the SCN at which the last checkpoint was issued for each datafile.
For example :
SQL> select current_scn from v$database;
CURRENT_SCN
---------------
 5288289
SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
 5287941 14
SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
 5287941 14
SQL> 
In this case, V$DATABASE's current_scn is ahead of those of the datafiles last checkpoint. We know that this is acceptable -- because the database SCN is continuously being incremented while datafiles are checkpointed only on occasion. The above values are after a database instance startup.
What if I checkpoint selective datafiles ?
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
 5288402 14
SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
 5288402 14
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter tablespace users online;
Tablespace altered.
SQL> select checkpoint_change#, count(*) from v$datafile group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
 5288402 13
 5288445 1
SQL> select checkpoint_change#, count(*) from v$datafile_header group by checkpoint_change#;
CHECKPOINT_CHANGE# COUNT(*)
------------------ ---------------
 5288402 13
 5288445 1
SQL>

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: