Tuesday, July 28, 2009

V$DATAFILE

V$DATAFILE displays datafile information from the control file.

Column

Datatype

Description

FILE#

NUMBER

File identification number

CREATION_CHANGE#

NUMBER

Change number at which the datafile was created

CREATION_TIME

DATE

Timestamp of the datafile creation

TS#

NUMBER

Tablespace number

RFILE#

NUMBER

Tablespace relative datafile number

STATUS

VARCHAR2(7)

Type of file (system or user) and its status. Values: OFFLINE, ONLINE, SYSTEM, RECOVER, SYSOFF (an offline file from the SYSTEM tablespace)

ENABLED

VARCHAR2(10)

Describes how accessible the file is from SQL:

· DISABLED - No SQL access allowed

· READ ONLY - No SQL updates allowed

· READ WRITE - Full access allowed

· UNKNOWN - should not occur unless the control file is corrupted

CHECKPOINT_CHANGE#

NUMBER

SCN at last checkpoint

CHECKPOINT_TIME

DATE

Timestamp of the checkpoint#

UNRECOVERABLE_CHANGE#

NUMBER

Last unrecoverable change number made to this datafile. If the database is in ARCHIVELOG mode, then this column is updated when an unrecoverable operation completes. If the database is not in ARCHIVELOG mode, this column does not get updated.

UNRECOVERABLE_TIME

DATE

Timestamp of the last unrecoverable change. This column is updated only if the database is in ARCHIVELOG mode.

LAST_CHANGE#

NUMBER

Last change number made to this datafile (null if the datafile is being changed)

LAST_TIME

DATE

Timestamp of the last change

OFFLINE_CHANGE#

NUMBER

Offline change number of the last offline range. This column is updated only when the datafile is brought online.

ONLINE_CHANGE#

NUMBER

Online change number of the last offline range

ONLINE_TIME

DATE

Online timestamp of the last offline range

BYTES

NUMBER

Current datafile size (in bytes); 0 if inaccessible

BLOCKS

NUMBER

Current datafile size (in blocks); 0 if inaccessible

CREATE_BYTES

NUMBER

Size when created (in bytes)

BLOCK_SIZE

NUMBER

Block size of the datafile

NAME

VARCHAR2(513)

Name of the datafile

PLUGGED_IN

NUMBER

Describes whether the tablespace is plugged in. The value is 1 if the tablespace is plugged in and has not been made read/write, 0 if not.

BLOCK1_OFFSET

NUMBER

Offset from the beginning of the file to where the Oracle generic information begins. The exact length of the file can be computed as follows: BYTES + BLOCK1_OFFSET.

AUX_NAME

VARCHAR2(513)

Auxiliary name that has been set for this file via CONFIGURE AUXNAME

FIRST_NONLOGGED_SCN

NUMBER

First nonlogged SCN

FIRST_NONLOGGED_TIME

DATE

First nonlogged time

FOREIGN_DBID

NUMBER

???

FOREIGN_CREATION_CHANGE#

NUMBER

???

FOREIGN_CREATION_TIME

DATE

???

PLUGGED_READONLY

VARCHAR2(3)

???

PLUGIN_CHANGE#

NUMBER

???

PLUGIN_RESETLOGS_CHANGE#

NUMBER

???

PLUGIN_RESETLOGS_TIME

DATE

???

Note:

  1. Script to generate the script for DBV

select 'dbv file=' || name || ' blocksize='|| block_size || ' logfile=' ||

substr(name, instr(name, '/', -1, 1) +1) || '.' || file# || '.log'

from v$datafile;

  1. Script to get database file I/O information

select name, phywrts, phyrds

from v$datafile a, v$filestat b

where a.file# = b.file#

order by 1;

  1. Script to list the details of database growth per month

select to_char(creation_time, 'RRRR Month') "Month",

round(sum(bytes)/1024/1024/1024) "Growth in GB"

from v$datafile

where creation_time > SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

select creation_time, name, round(bytes/1024/1024/1024, 1) size_GB

from v$datafile

where creation_time > SYSDATE-365

order by creation_time, name;

  1. script to perform end backup on all datafiles

select 'alter database datafile ''' || name || ''' end backup;'

from v$datafile d, v$backup b

where d.file# = b.file#

and b.status = 'ACTIVE';

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 28, 2009