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>
Advertisements