'; zhtm += ''; zhtm += '

' + pPage + ''; zhtm += ''; figDoc.write(zhtm); figDoc.close(); popUpWin.focus(); } //-->

Oracle Certification, Database Administration, SQL, Application, Programming Reference Books
The Oracle8 Data Dictionary

Oracle stores information about all the objects defined by the users, structural information about the database, and so on in its internal tables. These Oracle internal tables and associated objects are collectively referred as the data dictionary. The data dictionary is owned by the user SYS and always resides in the SYSTEM tablespace.
 
 

Data dictionary tables are created when the database is created
Oracle automatically updates these tables whenever it needs to. Users should never update any table in the data dictionary. Several Oracle and non-Oracle tools also create some objects in the data dictionary that are used for storing operational, reference, and configuration information.

Information stored in the data dictionary is available to users through data dictionary views. A database administrator or a user can use the data dictionary to view the following information:

Oracle's data dictionary views can broadly be defined in the following classes: Table 1.3 lists important Oracle8 data dictionary views. Similar views with DBA and ALL prefixes are available.

Table 1.3  Important data dictionary views

View Name:
Description:
USER_ALL_TABLES Contains descriptions of all tables available to the user
USER_CLUSTERS Contains information about clusters created by the user
USER_CONSTRAINTS Contains information about the constraint defined by the user
USER_DB_LINKS Contains information about the database link created by the user
USER_ERRORS Gives all current errors on all stored objects for the user
USER_EXTENTS Lists all the extents used by the objects owned by the user
USER_FREE_SPACE Lists all free extents in the tablespaces on which the user has privilege
USER_INDEXES Gives information about indexes created by the user
USER_IND_COLUMNS Gives the name of all the columns on which the user has created indexes
USER_JOBS Gives all jobs in the job queue owned by the user
USER_RESOURCE_LIMITS Gives resource limits applicable for the user
USER_SEGMENTS Gives information about all segments owned by the user
USER_SEQUENCES Lists information about all sequences owned by the user
USER_SNAPSHOTS Gives information about all snapshots the user can view
USER_SYNONYMS Gives the name of all private synonyms for the user
USER_TAB_COLUMNS Gives the name of all columns in all tables the user owns
USER_TAB_PARTITIONS Gives information about all table partitions owned by the user
USER_TABLES Gives information about all tables the user owns
USER_TRIGGERS Gives information for all triggers created by the user

Statistics and the Data Dictionary

Several data dictionary views contain columns with statistics information for the object. For example, the USER_TABLES view contains columns NUM_ROWS (number of rows in the table), BLOCKS (number of data blocks used in the table), AVG_ROW_LEN (average row length of a row in the table), and so on. These columns are populated only when you analyze the object by using the ANALYZE command. You should analyze the objects at regular intervals to keep the statistics up-to-date.

Dynamic Performance Tables

An Oracle instance maintains comprehensive information about its current configuration and activity. These statistics are accessible to the database administrator through dynamic performance views. Most of these views are based on in-memory table-like structures known as virtual tables (because they aren't real tables). The majority of these views have names starting with V$. These virtual tables don't require disk storage space and aren't stored in any tablespace. By default, the dynamic performance views are accessible to the SYS user or to the users having a SYSDBA role. Contents of these views are updated continuously while the instance is active.
 
 
Use TIMED_STATISTICS to gather timing information
Many dynamic performance views contain columns, such as WAIT_TIME and TOTAL_WAITS, that contain timing information. Such columns are populated by Oracle only when the TIMED_STATISTICS parameter is set to TRUE.

Table 1.4 describes important dynamic performance views. These views are for Oracle8; some may not exist in Oracle7.

Table 1.4  Dynamic performance views

View Name:
Description:
V$ACCESS Displays information about locked database objects and the sessions accessing them
V$CONTROLFILE Lists names of the database control files
V$DATABASE Contains miscellaneous database information such as database name creation date, archive/no archive log mode, and so on
V$DATAFILE Contains information about the data files that are part of the database (This information is from the control file.)
V$DATAFILE_HEADER Similar to V$DATAFILE, except that information is based on the contents of each data file header
V$DB_LINK Lists information about all active database links
V$FILESTAT Displays read/write statistics for each database data file
V$FIXED_TABLE Contains names of all fixed tables in the database
V$FIXED_VIEW_DEFINITION Lists definitions of all the dynamic performance views; you can see how Oracle creates dynamic performance views based on its internal x$ tables; these x$ tables are known as fixed tables
V$LICENSE Lists license-related information
V$LOCK Shows the locks held and requested; information in this view useful while tuning the database performance or hanging issues
V$LOCKED_OBJECT Lists all the objects locked in the database and the sessions that are locking the objects
V$LOG Lists information about the online redo logs
V$LOG_HISTORY Contains information about the archived redo log file
V$MYSTAT Lists statistics about the current session
V$PARAMETER Lists current values of the initialization parameters; the ISDEFAULT column indicates whether the parameter value is the default
V$PROCESS Lists all Oracle processes; a value of 1 in the BACKGROUND column indicates that the process is an Oracle background process; a NULL value in this column indicates a normal user process
V$RECOVER_FILE Used to query the information about the files needing media recovery; this view can be queried after the instance mounts the database
V$ROLLNAME Lists names of all the online rollback segments
V$ROLLSTAT Lists statistics for all online rollback segments
V$SESSION Contains information about all the current sessions; this view, one of the most informative, has about 35 columns
V$SESSION_EVENT Contains information about waits each session has incurred on events; use this view if you're experiencing slow performance
V$SESSION_WAIT Lists the events and resources Oracle is waiting on; information in this view can be used to detect performance bottlenecks
V$SESSTAT Contains performance statistics for each active session
V$SESS_IO Lists I/O statistics about each active session
V$STATNAME Gives names of Oracle statistics displayed in V$SESSTAT and V$SYSSTAT
V$SYSSTAT Contains performance statistics for the whole instance
V$SYSTEM_EVENT Contains information for various Oracle events
V$TABLESPACE Lists names of all tablespaces in the database
V$TRANSACTION Lists statistics related to transactions in the instance
V$WAITSTAT Contains block contention statistics
Global dynamic performance views
In a parallel server environment, every V$ view has a corresponding GV$ view. These views, known as global dynamic performance views, contain information about all active instances of an Oracle parallel server environment. The INST_ID column displays the instance number to which the information displayed in the GV$ view belongs.
Use fixed tables with caution!
Oracle doesn't encourage the use of fixed tables listed in V$FIXED_TABLE because their structure isn't published and can be changed.

Exit to : Oracle Hints and Tips