DBA_DATA_FILES

Oracle 11gR1

DBA_DATA_FILES describes database files.

Column	Datatype	NULL	Description
FILE_NAME	VARCHAR2(513)		Name of the database file
FILE_ID	NUMBER	NOT NULL	File identifier number of the database file
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace to which the file belongs
BYTES	NUMBER		Size of the file in bytes
BLOCKS	NUMBER	NOT NULL	Size of the file in Oracle blocks
STATUS	VARCHAR2(9)		File status: AVAILABLE or INVALID (INVALID means that the file number is not in use, for example, a file in a tablespace that was dropped)
RELATIVE_FNO	NUMBER		Relative file number
AUTOEXTENSIBLE	VARCHAR2(3)		Autoextensible indicator
MAXBYTES	NUMBER		Maximum file size in bytes
MAXBLOCKS	NUMBER		Maximum file size in blocks
INCREMENT_BY	NUMBER		Number of tablespace blocks used as autoextension increment. Block size is contained in the BLOCK_SIZE column of the DBA_TABLESPACES view.
USER_BYTES	NUMBER		The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata.
USER_BLOCKS	NUMBER		Number of blocks which can be used by the data
ONLINE_STATUS	VARCHAR2(7)		Online status of the file: · SYSOFF · SYSTEM · OFFLINE · ONLINE · RECOVER

Oracle data dictionary views

V$LOGFILE

Oracle 11gR1

V$LOGFILE contains information about redo log files.

Column	Datatype	Description
GROUP#	NUMBER	Redo log group identifier number
STATUS	VARCHAR2(7)	Status of the log member: · INVALID - File is inaccessible · STALE - File's contents are incomplete · DELETED - File is no longer used · null - File is in use
TYPE	VARCHAR2(7)	Type of the logfile: · ONLINE · STANDBY
MEMBER	VARCHAR2(513)	Redo log member name
IS_RECOVERY_DEST_FILE	VARCHAR2(3)	Indicates whether the file was created in the flash recovery area (YES) or not (NO)

Oracle dynamic performance views

V$FIXED_VIEW_DEFINITION

Oracle 11gR1

V$FIXED_VIEW_DEFINITION contains the definitions of all the fixed views (views beginning with V$). Use this table with caution. Oracle tries to keep the behavior of fixed views the same from release to release, but the definitions of the fixed views can change without notice. Use these definitions to optimize your queries by using indexed columns of the dynamic performance tables.

Column	Datatype	Description
VIEW_NAME	VARCHAR2(30)	Name of the fixed view
VIEW_DEFINITION	VARCHAR2(4000)	Definition of the fixed view

Oracle dynamic performance views

V$DATAFILE_HEADER

Oracle 11gR1

V$DATAFILE_HEADER displays datafile information from the datafile headers.

Column	Datatype	Description
FILE#	NUMBER	Datafile number (from control file)
STATUS	VARCHAR2(7)	ONLINE | OFFLINE (from control file)
ERROR	VARCHAR2(18)	NULL if the datafile header read and validation were successful. If the read failed then the rest of the columns are NULL. If the validation failed then the rest of columns may display invalid data. If there is an error then usually the datafile must be restored from a backup before it can be recovered or used.
FORMAT	NUMBER	Indicates the format for the header block. The possible values are 6, 7, 8, or 0. 6 - indicates Oracle Version 6 7 - indicates Oracle Version 7 8 - indicates Oracle Version 8 0 - indicates the format could not be determined (for example, the header could not be read)
RECOVER	VARCHAR2(3)	File needs media recovery (YES | NO)
FUZZY	VARCHAR2(3)	File is fuzzy (YES | NO)
CREATION_CHANGE#	NUMBER	Datafile creation change#
CREATION_TIME	DATE	Datafile creation timestamp
TABLESPACE_NAME	VARCHAR2(30)	Tablespace name
TS#	NUMBER	Tablespace number
RFILE#	NUMBER	Tablespace relative datafile number
RESETLOGS_CHANGE#	NUMBER	Resetlogs change#
RESETLOGS_TIME	DATE	Resetlogs timestamp
CHECKPOINT_CHANGE#	NUMBER	Datafile checkpoint change#
CHECKPOINT_TIME	DATE	Datafile checkpoint timestamp
CHECKPOINT_COUNT	NUMBER	Datafile checkpoint count
BYTES	NUMBER	Current datafile size in bytes
BLOCKS	NUMBER	Current datafile size in blocks
NAME	VARCHAR2(513)	Datafile name
SPACE_HEADER	VARCHAR2(40)	The amount of space currently being used and the amount that is free, as identified in the space header
LAST_DEALLOC_SCN	VARCHAR2(16)	Last deallocated SCN

Oracle dynamic performance views

V$VERSION

Oracle 11gR1

V$VERSION displays version numbers of core library components in the Oracle Database. There is one row for each component.

Column	Datatype	Description
BANNER	VARCHAR2(80)	Component name and version number

Note:

The following query checks the current Oralce Database release number:

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

PL/SQL Release 11.1.0.6.0 - Production

CORE 11.1.0.6.0 Production

TNS for Solaris: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production

SQL> select * from product_component_version;

PRODUCT VERSION STATUS

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

NLSRTL 11.1.0.6.0 Production

Oracle Database 11g Enterprise Edition 11.1.0.6.0 64bit Production

PL/SQL 11.1.0.6.0 Production

TNS for Solaris: 11.1.0.6.0 Production

Oracle dynamic performance views

V$LICENSE

V$LICENSE displays information about license limits.

Column	Datatype	Description
SESSIONS_MAX	NUMBER	Maximum number of concurrent user sessions allowed for the instance
SESSIONS_WARNING	NUMBER	Warning limit for concurrent user sessions for the instance
SESSIONS_CURRENT	NUMBER	Current number of concurrent user sessions
SESSIONS_HIGHWATER	NUMBER	Highest number of concurrent user sessions since the instance started
USERS_MAX	NUMBER	Maximum number of named users allowed for the database
CPU_COUNT_CURRENT	NUMBER	Current number of logical CPUs or processors on the system
CPU_CORE_COUNT_CURRENT	NUMBER	Current number of CPU cores on the system (includes subcores of multicore CPUs, as well as single-core CPUs)
CPU_SOCKET_COUNT_CURRENT	NUMBER	Current number of CPU sockets on the system (represents an absolute count of CPU chips on the system, regardless of multithreading or multicore architectures)
CPU_COUNT_HIGHWATER	NUMBER	Highest number of logical CPUs or processors on the system since the instance started
CPU_CORE_COUNT_HIGHWATER	NUMBER	Highest number of CPU cores on the system since the instance started (includes subcores of multicore CPUs, as well as single-core CPUs)
CPU_SOCKET_COUNT_HIGHWATER	NUMBER	Highest number of CPU sockets on the system since the instance started (represents an absolute count of CPU chips on the system, regardless of multithreading or multicore architectures)

Note:

1. The availability of the CPU core count and CPU socket count statistics is subject to the operating system platform on which the Oracle Database is running. If a statistic is unavailable, the view will return NULL for the statistic value.
2. License usage in the database

SELECT sessions_current, sessions_highwater,        (select value from V$SYSSTAT where name = 'logons cumulative') cumulative_logins FROM   V$LICENSE;

SELECT rpad(c.name||':',11) || rpad(' current logons=' ||        (to_number(b.sessions_current)),20) || 'cumulative logons=' ||        rpad(substr(a.value,1,10),10) || 'highwater mark=' || b.sessions_highwater ||        '   startup time=' || to_char(d.startup_time,'YYYY/MM/DD HH24:MI:SS') Information FROM   V$SYSSTAT a, V$LICENSE b, V$DATABASE c, V$INSTANCE d WHERE  a.name = 'logons cumulative';

SELECT sessions_max, sessions_warning, sessions_current, sessions_highwater, users_max FROM   V$LICENSE;

3.       v$license and v$session

·    Session_highwater is derived from session_current, because if session_current is greater than session_highwater then session_highwater is set to session_current.

·    v$license and v$session are derived from the same sources.

·    Oracle background processes are included in v$session, but not in v$license. SNP processes are an exception to this rule.

4. Oracle errors

ORA-00018: maximum number of sessions exceeded Cause: You tried to execute a statement that requested a resource. Since the maximum number of sessions has been reached, Oracle will not process any new resource requests. Action: The options to resolve this Oracle error are: 1.        You can wait a few minutes and try to re-execute the statement(s). 2.        You can shut down Oracle, increase the SESSIONS parameter in the initialization parameter file, and restart Oracle.

Last updated: October 5, 2009 Monday

Oracle data dictionary views

Oracle dynamic performance views

More Oracle DBA tips, please visit Oracle DBA Tips 

V$CONTROLFILE

Oracle 11gR1

V$CONTROLFILE displays the names of the control files.

Column	Datatype	Description
STATUS	VARCHAR2(7)	INVALID if the name cannot be determined (which should not occur); NULL if the name can be determined
NAME	VARCHAR2(513)	Name of the control file
IS_RECOVERY_DEST_FILE	VARCHAR2(3)	Indicates whether the file was created in the flash recovery area (YES) or not (NO)
BLOCK_SIZE	NUMBER	Control file block size
FILE_SIZE_BLKS	NUMBER	Control file size (in blocks)

Oracle dynamic performance views

V$BGPROCESS

Oracle 11gR1

V$BGPROCESS displays information about the background processes.

Column	Datatype	Description
PADDR	RAW(4 | 8)	Address of the process state object
PSERIAL#	NUMBER	Process state object serial number
NAME	VARCHAR2(5)	Name of this background process
DESCRIPTION	VARCHAR2(64)	Description of the background process
ERROR	NUMBER	Error encountered

Example:

select * from V$BGPROCESS; --Oracle 10gR2ee

PADDR	PSERIAL#	NAME	DESCRIPTION	ERROR
00	0	ARB0	ASM Rebalance 0	0
00	0	ARB1	ASM Rebalance 1	0
00000007B772D4D0	1	ARC0	Archival Process 0	0
00000007BE71AC18	1	ARC1	Archival Process 1	0
00	0	ASMB	ASM Background	0
00000007B96EA648	1	CJQ0	Job Queue Coordinator	0
00000007B86F0380	1	CKPT	checkpoint	0
00	0	CTWR	Change Tracking Writer	0
00000007B772BD18	1	DBW0	db writer process 0	0
00000007BE719460	1	DBW1	db writer process 1	0
00	0	DBW2	db writer process 2	0
00	0	DIAG	diagnosibility process	0
00	0	DMON	DG Broker Monitor Process	0
00	0	EMN0	Event Monitor Process 0	0
00	0	FMON	File Mapping Monitor Process	0
00	0	GMON	diskgroup monitor	0
00	0	INSV	Data Guard Broker INstance SlaVe Process	0
00	0	LCK0	Lock Process 0	0
00000007B96E9E60	1	LGWR	Redo etc.	0
00	0	LMD0	global enqueue service daemon 0	0
00	0	LMON	global enqueue service monitor	0
00	0	LMS0	global cache service process 0	0
00	0	LNS0	Network Server 0	0
00	0	LNS1	Network Server 1	0
00	0	LSP0	Logical Standby	0
00	0	LSP1	Dictionary build process for Logical Standby	0
00	0	LSP2	Set Guard Standby Information for Logical Standby	0
00000007B86EFB98	1	MMAN	Memory Manager	0
00000007B772CCE8	1	MMNL	Manageability Monitor Process 2	0
00000007B86F0B68	1	MMON	Manageability Monitor Process	0
00	0	MRP0	Managed Standby Recovery	0
00	0	NSV0	Data Guard Broker NetSlave Process 0	0
00000007BE718C78	1	PMON	process cleanup	0
00000007B96E9678	1	PSP0	process spawner 0	0
00000007B86F2B08	3	QMNC	AQ Coordinator	0
00	0	RBAL	ASM Rebalance master	0
00000007BE719C48	1	RECO	distributed recovery	0
00	0	RSM0	Data Guard Broker Resource Guard Process 0	0
00	0	RSM1	Data Guard Broker Resource Guard Process 1	0
00	0	RVWR	Recovery Writer	0
00000007B772C500	1	SMON	System Monitor Process	0

Oracle dynamic performance views