Oracle7 Server Administrator's Guide

Viewing Information About Datafiles

The following data dictionary views provide useful information about the datafiles of a database:

USER_EXTENTS, DBA_EXTENTS

USER_SEGMENTS, DBA_SEGMENTS

USER_FREE_SPACE, DBA_FREE_SPACE

DBA_USERS

DBA_TS_QUOTAS

USER_TABLESPACES, DBA_TABLESPACES

DBA_DATA_FILES

V$DATAFILE

Listing Status Information About Datafiles: Example

The following example illustrates how to use a view not already illustrated in other chapters of this manual. Assume you are using a database that contains two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1 (100MB) and FILE2 (200MB); the tablespace has been taken offline normally. Here, you query V$DATAFILE to view status information about datafiles of a database:

SELECT name,

   file#,

   status,

   checkpoint_change# "CHECKPOINT"   FROM v$datafile;

NAME                                FILE# STATUS  CHECKPOINT

----------------------------------- ----- ------- ----------

filename1                           1     SYSTEM        3839

filename2                           2     OFFLINE       3782

filename3                           3     OFFLINE       3782

FILE# lists the file number of each datafile; the first datafile in the SYSTEM tablespace, created with the database, is always file 1. STATUS lists other information about a datafile. If a datafile is part of the SYSTEM tablespace, its status is SYSTEM (unless it requires recovery). If a datafile in a non-SYSTEM tablespace is online, its status is ONLINE. If a datafile in a non-SYSTEM tablespace is offline, its status can be either OFFLINE or RECOVER. CHECKPOINT lists the final SCN written for a datafile's most recent checkpoint.