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