B
Control File Fixed View Changes

This appendix briefly describes changes from version 7, release 7.3 to the version 8 server Control File Fixed Views.

Date Columns in Control File Views

In version 7, all control file views date columns are VARCHAR2(20) strings in 'MM/DD/YY HH24:MI:SS' format.

In version 8, every new date column is a real DATE column. In contrast to the previous VARCHAR2(20) string, DATE provides users the following benefits:

Consistency because all date columns are in DATE datatype.
Easier to perform date arithmetic (including sorting) in SQL and PL/SQL with dates.
Enables users to set their date format using NLS_DATE_FORMAT.
Users who want to see dates in the old format can set NLS_DATE_FORMAT to MM/DD/YY HH24:MI:SS.
Avoids two-digit year numbers to avoid problems at the year 2000 and beyond.

This appendix lists the names of the views and the names of the columns in the views that have been changed or added to version 8.

See Also:

Oracle8 Reference for descriptions of each column and for a complete listing of the columns in the tables.

Obsolete Views Kept in Version 8

V$ARCHIVE still shows logs that must be archived.

V$LOG was superceded by V$LOG_HISTORY, which is superseded by the new version 8 view, V$ARCHIVED_LOG. In version 8, however, V$LOG is still provided and still lists all archived logs in its ARCHIVED column.

The obsolete V$LOGHIST view is retained for historical compatibility.

V$LOG_HISTORY Retained and Upgraded

In version 8, several V$LOG_HISTORY columns have been renamed, as shown in the following table. Further, the (version 7) ARCHIVE_NAME column is not present in version 8.

Version 8 Column	Version 7 Column
FIRST_TIME	TIME
FIRST_CHANGE#	LOW_CHANGE#
NEXT_CHANGE#	HIGH_CHANGE#
[not present]	ARCHIVE_NAME

V$ARCHIVED_LOG Replaces V$LOG_HISTORY

The new version 8 view, V$ARCHIVED_LOG, shows archived logs, including their names. If a log is never archived, the V$ARCHIVED_LOG view does not return any row for the log. If a log is archived twice, V$ARCHIVED_LOG returns two rows for the log.

Certain columns in this new view correspond to version 7 V$LOG_HISTORY names and use formats that match the column names in other version 8 views. The V$ARCHIVED_LOG view shows archived log information from the control file:

Column Name	Column Type
RECID	NUMBER
STAMP	NUMBER
NAME	VARCHAR2(512)
THREAD#	NUMBER
SEQUENCE#	NUMBER
RESETLOGS_CHANGE#	NUMBER
RESETLOGS_TIME	DATE
FIRST_CHANGE#	NUMBER
FIRST_TIME	DATE
NEXT_CHANGE#	NUMBER
NEXT_TIME	DATE
BLOCKS	NUMBER
BLOCK_SIZE	NUMBER
COMPLETION_TIME	DATE
DELETED	VARCHAR2(3)

An archive log record is inserted each time the online redo log is archived successfully or cleared-the name column is null when the log is cleared. If the log is archived twice, two archive log records are inserted with the same thread#, sequence#, and first_change#, but with different names. An archive log record also is inserted when an archive log is restored from a backup set or a copy.

V$BACKUP_CORRUPTION

The V$BACKUP_CORRUPTION view shows information about corruptions in datafile backups from the control file. Corruptions are not tolerated in control file and archived log backups.

Column Name	Column Type
RECID	NUMBER
STAMP	NUMBER
SET_STAMP	NUMBER
SET_COUNT	NUMBER
PIECE#	NUMBER
FILE#	NUMBER
BLOCK#	NUMBER
BLOCKS	NUMBER
CORRUPTION_CHANGE#	NUMBER
MARKED_CORRUPT	VARCHAR2(3)

V$BACKUP_DATAFILE

The V$bBACKUP_DATAFILE view shows backup datafile and backup control file information from the control file. A datafile backup set can contain one control file and multiple datafiles.

Column Name	Column Type
RECID	NUMBER
STAMP	NUMBER
SET_STAMP	NUMBER
SET_COUNT	NUMBER
FILE#	NUMBER
CREATION_CHANGE#	NUMBER
CREATION_TIME	DATE
RESETLOGS_CHANGE#	NUMBER
RESETLOGS_TIME	DATE
INCREMENTAL_LEVEL	NUMBER
INCREMENTAL_CHANGE#	NUMBER
CHECKPOINT_CHANGE#	NUMBER
CHECKPOINT_TIME	DATE
ABSOLUTE_FUZZY_CHANGE#	NUMBER
MARKED_CORRUPT	NUMBER
MEDIA_CORRUPT	NUMBER
LOGICALLY_CORRUPT	NUMBER
DATAFILE_BLOCKS	NUMBER
BLOCKS	NUMBER
BLOCK_SIZE	NUMBER
OLDEST_OFFLINE_RANGE	NUMBER

V$BACKUP_DEVICE

The new version 8 view, V$BACKUP_DEVICE, shows information about supported backup devices. If a device type does not support named devices, one row with the device type and a null device name is returned for that device type. If a device type supports named devices, one row is returned for each available device of that type. The special device type DISK is not returned by this view because it is always available.

Column Name	Column Type
DEVICE_TYPE	VARCHAR2(17)
DEVICE_NAME	VARCHAR2(512)

V$BACKUP_PIECE

The new version 8 view, V$BACKUP_PIECE, shows information about backup pieces stored in the control file. Each backup set consist of one or more backup pieces.

Column Name	Column Type
RECID	NUMBER
STAMP	NUMBER
SET_STAMP	NUMBER
SET_COUNT	NUMBER
PIECE#	NUMBER
DEVICE_TYPE	VARCHAR2(17)
HANDLE	VARCHAR2(513)
COMMENTS	VARCHAR2(81)
MEDIA	VARCHAR2(65)
CONCUR	VARCHAR2(3)
TAG	VARCHAR2(32)
DELETED	VARCHAR2(3)

V$BACKUP_REDOLOG

The V$BACKUP_REDOLOG view shows information about archived logs in backup sets from the control file. Online redo logs cannot be backed up directly, they first must be archived to disk and then backed up. An archive log backup set can contain one or more archived logs.

V$BACKUP_SET

The V$BACKUP_SET view shows backup set information from the control file. A backup set record is inserted after the backup set is completed successfully.

Column Name	Column Type
RECID	NUMBER
STAMP	NUMBER
SET_STAMP	NUMBER
SET_COUNT	NUMBER
BACKUP_TYPE	VARCHAR2(1)
CONTROLFILE_INCLUDED	VARCHAR2(3)
INCREMENTAL_LEVEL	NUMBER
PIECES	NUMBER
COMPLETION_TIME	DATE
BLOCK_SIZE	NUMBER

V$CONTROLFILE_RECORD_SECTION

The new version 8 view, V$CONTROLFILE_RECORD_SECTION, shows information about the control file record sections.

Column name	Column type
TYPE	VARCHAR2(17)
RECORD_SIZE	NUMBER
RECORDS_TOTAL	NUMBER
RECORDS_USED	NUMBER
FIRST_INDEX	NUMBER
LAST_INDEX	NUMBER
LAST_RECID	NUMBER

V$COPY_CORRUPTION

The new version 8 view, V$COPY_CORRUPTION, shows information about datafile copy corruptions from the control file.

Column name	Column type
RECID	NUMBER
STAMP	NUMBER
COPY_RECID	NUMBER
COPY_STAMP	NUMBER
FILE#	NUMBER
BLOCK#	NUMBER
BLOCKS	NUMBER
CORRUPTION_CHANGE#	NUMBER
MARKED_CORRUPT	VARCHAR2(3)

V$DATABASE New Columns

The V$DATABASE view shows database information from the control file. The following V$DATABASE columns are new to version 8:

Column Name	Value
DBID	NUMBER
RESETLOGS_CHANGE#	NUMBER
RESETLOGS_TIME	DATE
CONTROLFILE_TYPE	VARCHAR2(7)
CONTROLFILE_CREATED	DATE
CONTROLFILE_SEQUENCE#	NUMBER
CONTROLFILE_CHANGE#	NUMBER
nCONTROLFILE_TIME	DATE
OPEN_RESETLOGS	VARCHAR2(11)

V$DATAFILE New Columns

The V$DATAFILE view shows datafile information from the control file. The version 8 V$DATAFILE view shows the following new columns, in addition to the columns of the existing version 7 V$DATAFILE view. See also the V$DATAFILE_HEADER view, which shows information from datafile headers.

Column Name	Column Type
CREATION_CHANGE#	NUMBER
CREATION_TIME	DATE
TS#	NUMBER
RFILE#	NUMBER
CHECKPOINT_TIME	DATE
UNRECOVERABLE_CHANGE#	NUMBER
UNRECOVERABLE_TIME	DATE
LAST_CHANGE#	NUMBER
LAST_TIME	DATE
OFFLINE_CHANGE#	NUMBER
ONLINE_CHANGE#	NUMBER
ONLINE_TIME#	DATE
BLOCKS	NUMBER
BLOCK_SIZE	NUMBER

V$DATAFILE_COPY

The V$DATAFILE_COPY view shows datafile copy information from the control file. A datafile copy record is inserted after the datafile (or datafile copy) is copied successfully or restored from a backup set.

Column Name	Column Type
RECID	NUMBER
STAMP	NUMBER
NAME	VARCHAR2(512)
TAG	VARCHAR2(32)
FILE#	NUMBER
RFILE#	NUMBER
CREATION_CHANGE#	NUMBER
CREATION_TIME	DATE
RESETLOGS_CHANGE#	NUMBER
RESETLOGS_TIME	DATE
CHECKPOINT_CHANGE#	NUMBER
CHECKPOINT_TIME	DATE
ABSOLUTE_FUZZY_CHANGE#	NUMBER
RECOVERY_FUZZY_CHANGE#	NUMBER
RECOVERY_FUZZY_TIME	DATE
ONLINE_FUZZY	VARCHAR2(3)
BACKUP_FUZZY	VARCHAR2(3)
MARKED_CORRUPT	NUMBER
MEDIA_CORRUPT	NUMBER
LOGICALLY_CORRUPT	NUMBER
BLOCKS	NUMBER
BLOCK_SIZE	NUMBER
OLDEST_OFFLINE_RANGE	NUMBER
COMPLETION_TIME	DATE
DELETED	VARCHAR2(3)

V$DATAFILE_HEADER

The new version 8 view, V$DATAFILE_HEADER, shows datafile information from the datafile head.

Column name	Column type
FILE#	NUMBER
STATUS	VARCHAR2(7)
ERROR	VARCHAR2(18)
RECOVER	VARCHAR2(3)
FUZZY	VARCHAR2(3)
CREATION_CHANGE#	NUMBER
CREATION_TIME	DATE
TABLESPACE_NAME	VARCHAR2(30)
TS#	NUMBER
RFILE#	NUMBER
RESETLOGS_CHANGE#	NUMBER
RESETLOGS_TIME	DATE
CHECKPOINT_CHANGE#	NUMBER
CHECKPOINT_TIME	DATE
CHECKPOINT_COUNT	NUMBER
BYTES	NUMBER
BLOCKS	NUMBER
NAME	VARCHAR2(512)

V$DELETED_OBJECT

The new version 8 view, V$DELETED_OBJECT, shows information about deleted archived logs, datafile copies, and backup pieces from the control file. The purpose of this view is to optimize the recovery catalog resync operation. When an archived log, datafile copy, or backup piece is deleted, the corresponding record is marked deleted.

Column Name	Column Type
RECID	NUMBER
STAMP	NUMBER
TYPE	VARCHAR2(13)
OBJECT_RECID	NUMBER
OBJECT_STAMP	NUMBER

V$INSTANCE

The new version 8 view, V$INSTANCE, shows status information for the database instance. Returning only one row, it is incompatible with the version 7 view, V$INSTANCE, which returns one row per value. The STARTUP_TIME, LOGINS, and SHUTDOWN_PENDING columns return the same information as the version 7 version of the V$INSTANCE:

SVRMGR> select startup_time, logins, shutdown_pending from v$instance; 







STARTUP_TIME
  



LOGINS
  



SHUTDOWN_PENDING 
  




96.05.29 20:29:38
  



ALLOWED
  



 NO  
  










SQLDBA> select * from v$instance; 







KEY
  



VALUE      
  




RESTRICTED MODE
  



0 
  




SHUTDOWN PENDING
  



0 
  




STARTUP TIME - JULIAN 
  



2450215 
  




STARTUP TIME - SECONDS
  



50407 
  




4 rows selected.

Although the version 8 V$INSTANCE view is not compatible with the version 7 V$INSTANCE view, it provides all of the same information, as well as additional information:

Column Name	Column Type
INSTANCE_NUMBER	NUMBER
INSTANCE_NAME	VARCHAR2(16)
HOST_NAME	VARCHAR2(64)
VERSION	VARCHAR2(17)
STARTUP_TIME	DATE
STATUS	VARCHAR2(7)
PARALLEL	VARCHAR2(3)
THREAD#	NUMBER
ARCHIVER	VARCHAR2(7)
LOG_SWITCH_WAIT	VARCHAR2(11)
LOGINS	VARCHAR2(10)
SHUTDOWN_PENDING	VARCHAR2(3)

If the instance is not started, a query from this view signals ORA-1034.

V$OFFLINE_RANGE

The V$OFFLINE_RANGE view shows offline range information from the control file. The most recent offline range of each datafile is stored in the datafile record. V$OFFLINE_RANGE: Datafile offline range information from control file:

Column Name	Value
RECID	NUMBER
STAMP	NUMBER
FILE#	NUMBER
OFFLINE_CHANGE#	NUMBER
ONLINE_CHANGE#	NUMBER
ONLINE_TIME#	DATE

The last offline range of each datafile is kept in the datafile record; see the V$DATAFILE view.

V$RESOURCE_LIMIT

The new version 8 view, V$RESOURCE_LIMIT, displays information about global resource use for some of the system resources.

Column Name	Column Type
RESOURCE_NAME	VARCHAR2(30)
CURRENT_UTILIZATION	NUMBER
MAX_UTILIZATION	NUMBER
INITIAL_ALLOCATION	VARCHAR2(10)
LIMIT_VALUE	VARCHAR2(10)

V$TABLESPACE

The new version 8 view, V$TABLESPACE, shows tablespace information from the control file:

Column Name	Value	Description
TS#	NUMBER	Tablespace number
NAME	VARCHAR2(30)	Tablespace name

V$THREAD

The V$THREAD view contains thread information from the control file.

Column Name	Value	Description
ENABLE_CHANGE#	NUMBER	SCN at which thread was enabled
ENABLE_TIME	DATE	Time of enable SCN
DISABLE_CHANGE#	NUMBER	SCN at which thread was disabled
DISABLE_TIME	DATE	Time of disable SCN

Changed Column Types

In version 8, the datatype of the following time stamp columns have been changed to DATE from version 7 VARCHAR2(20).

View Name	Column
V$BACKUP	TIME
V$DATABASE	CREATED
V$LOG	FIRST_TIME
V$LOG_HISTORY	FIRST_TIME (used to be TIME)
V$LOGHIST	FIRST_TIME
V$RECOVER_FILE	TIME
V$RECOVERY_STATUS	RECOVERY_CHECKPOINT
V$RECOVERY_STATUS	TIME_NEEDED
V$THREAD	OPEN_TIME
V$THREAD	CHECKPOINT_TIME

Database Scheduling Facilities

The primary goal of the Database Scheduling feature is to provide more control over the following:

Changed Fixed Views

V$SESSION has SCHEDULER_CLASS column added
V$MYSESSION has SCHEDULER_CLASS column added
DBA_USERS has DEFAULT_SCHEDULER_CLASS column added
USER_USERS has DEFAULT_SCHEDULER_CLASS column added

New Fixed Views

V$SERVER_THRESHOLD: data about current server thresholds
V$SCHEDULER_CLASS: data about currently active scheduler classes
V$COMMON_SERVER: data about currently active common servers
V$COMMON_SERVER_REQUEST: data about types of requests currently being processed
V$COMMON_SERVER_REQUEST_HISTORY: data about types of requests that have been processed
V$COMMON_SERVER_QUEUE: data about queues of currently active common servers
DBA_SCHEDULER_CLASS_PRIVS: new DBA view showing all scheduler classes and users/roles to which they have been granted
DBA_SCHEDULER_CLASSES: new DBA view showing all scheduler classes that exist in the database
DBA_SCHEDULER_PLANS: new DBA view showing all scheduler plans that exist in the database
DBA_SCHEDULER_PLAN_ENTRIES: new DBA view showing all scheduler plan entries that exist in the database
SCHEDULER_CLASS_POLICIES: new DBA view showing all available scheduling policies for scheduler classes
SCHEDULER_PLAN_POLICIES: new DBA view showing all available scheduling policies for scheduler plans
USER_SCHEDULER_CLASS_PRIVS: new PUBLIC view showing all scheduler classes granted to the user

Table (View) Name Changes

Pre-Version 8 View Name Version 8 Table Name

ALL_HISTOGRAMS

ALL_TAB_HISTOGRAMS

DBA_HISTOGRAMS

DBA_TAB_HISTOGRAMS

USER_HISTOGRAMS

USER_TAB_HISTOGRAMS

KEY	VALUE
RESTRICTED MODE	0
SHUTDOWN PENDING	0
STARTUP TIME - JULIAN	2450215
STARTUP TIME - SECONDS	50407
4 rows selected.

Pre-Version 8 View Name	Version 8 Table Name
ALL_HISTOGRAMS	ALL_TAB_HISTOGRAMS
DBA_HISTOGRAMS	DBA_TAB_HISTOGRAMS
USER_HISTOGRAMS	USER_TAB_HISTOGRAMS

BControl File Fixed View Changes