DBA_EXTERNAL_TABLES

DBA_EXTERNAL_TABLES describes all external tables in the database. Its columns are the same as those in ALL_EXTERNAL_TABLES.

Related Views

• ALL_EXTERNAL_TABLES describes the external tables accessible to the current user.
• USER_EXTERNAL_TABLES describes the external tables owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the external table
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the external table
TYPE_OWNER	CHAR(3)		Owner of the implementation type for the external table access driver
TYPE_NAME	VARCHAR2(30)	NOT NULL	Name of the implementation type for the external table access driver
DEFAULT_DIRECTORY_OWNER	CHAR(3)		Owner of the default directory for the external table
DEFAULT_DIRECTORY_NAME	VARCHAR2(30)	NOT NULL	Name of the default directory for the external table
REJECT_LIMIT	VARCHAR2(40)		Reject limit for the external table, or UNLIMITED
ACCESS_TYPE	VARCHAR2(7)		Type of access parameters for the external table: BLOB CLOB
ACCESS_PARAMETERS	CLOB		Access parameters for the external table
PROPERTY	VARCHAR2(10)		Property of the external table: REFERENCED - Referenced columns ALL - All columns

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 30, 2009

DBA_EXTERNAL_LOCATIONS

DBA_EXTERNAL_LOCATIONS describes the locations (data sources) of all external tables in the database. Its columns are the same as those in ALL_EXTERNAL_LOCATIONS.

Related Views

• ALL_EXTERNAL_LOCATIONS describes the locations (data sources) of the external tables accessible to the current user.
• USER_EXTERNAL_LOCATIONS describes the locations (data sources) of the external tables owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the external table location
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name of the corresponding external table
LOCATION	VARCHAR2(4000)		External table location clause
DIRECTORY_OWNER	CHAR(3)		Owner of the directory containing the external table location
DIRECTORY_NAME	VARCHAR2(30)		Name of the directory containing the external table location

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 30, 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;

2. 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;

3. 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;

4. 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