Oracle8 Migration Release 8.0 A58243-01 |
Library | Product | Contents | Index

# 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
- New Fixed Views

### 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

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index