Using Data Dictionary and Dynamic Performance Views

Using Data Dictionary and Dynamic Performance Views

Built-In Database Objects

Other objects created with the database:

• Data dictionary

• Performance tables

• PL/SQL packages

• Database event triggers

Built-In Database Objects

Data Dictionary(所有对象的所在和描述)

• Central to every Oracle database

• Describes the database and its objects

• Contains read-only tables and views

• Stored in the SYSTEM tablespace

• Owned by the user SYS

• Maintained by the Oracle server

• Accessed with SELECT

Base Tables and Data Dictionary Views

The data dictionary contains two parts:

• Base tables

– Stores description of the database

– Created with CREATE DATABASE（实际上是由oracle server运行sql.bsq script后生成的）

• Data dictionary views（如果手动创建数据库则必须手工创建）

– Used to simplify the base table information

– Accessed through public synonyms

– Created with the catalog.sql script

Base Tables

Base tables are underlying tables, which store information about the database. The base tables are the first objects created in any Oracle database. They are automatically created when the Oracle server runs the sql.bsq script at the time the database is created. Only the Oracle server should write to these tables. Users rarely access them directly, because most of the data is stored in a cryptic format. Never use DML commands to update the base tables directly, with the exception of the AUD$ table. An example of a base table is the IND$ table, which contains information about the indexes in the database.

Data Dictionary Views

Data dictionary views are base table summaries, which provide for a more useful display of base table information. For example, in the data dictionary views, object names are used instead of only object numbers. The data dictionary views are created using the catalog.sql script which is run after the CREATE DATABASE command.

Creating Data Dictionary Views

you may need to run them again when upgrading to a new release of the Oracle server.人工创建时这两个脚本都要手动运行

These scripts must be run as the user SYS with the SYSDBA privilege.

The scripts are located in the following directories:

UNIX: $ORACLE_HOME/rdbms/admin

NT: %ORACLE_HOME%\rdbms\admin

Data Dictionary Contents

The data dictionary provides information about:

• Logical and physical database structures（The definitions of all schema objects in the database (tables, views, indexes, clusters,synonyms, sequences, procedures, functions, packages, triggers, and so on)）

• Definitions and space allocations of objects（How much space has been allocated for, and is currently used by, the schema objects）

• Integrity constraints

• Users

• Roles

• Privileges

• Auditing

How the Data Dictionary Is Used

Primary uses:

• Oracle server uses it to find information about

– Users

– Schema objects

– Storage structures

• Oracle server modifies it when a DDL statement is

executed.一般来说，dd只能由ddl的操作引起改变

• Users and DBAs use it as a read-only reference for

information about the database.

Data Dictionary View Categories

• Three sets of static views

• Distinguished by their scope:

– DBA: What is in all the schemas（所有的）

– ALL: What the user can access（当前用户可以访问的，也包括其他用户的）

– USER: What is in the user’s schema（当前用户拥有的）

Data Dictionary Examples

• General overview: DICTIONARY, DICT_COLUMNS

• Schema objects: DBA_TABLES, DBA_INDEXES,

DBA_TAB_COLUMNS, DBA_CONSTRAINTS

• Space allocation: DBA_SEGMENTS, DBA_EXTENTS

• Database structure: DBA_TABLESPACES,

DBA_DATA_FILES

To get an overview of the data dictionary views, the DICTIONARY view or its synonym DICT can be queried. For example:

SQL> SELECT * FROM dictionary;

Include the WHERE clause to narrow your responses:

SQL> SELECT * FROM dictionary

2 WHERE table_name LIKE ‘DBA_SEG%’

To get a list of columns within a view, use the DESCRIBE keyword:

SQL> DESCRIBE dba_users;

To get an overview of the columns in the data dictionary views, you can query the

DICT_COLUMNS view.

To view the contents of a data dictionary view, use the SELECT command.

SQL> SELECT * FROM dba_users;

Dynamic Performance Tables

• Virtual tables

• Record current database activity

• Continually updated while the database is

operational

• Information is accessed from memory and control

file

• Used to monitor and tune the database

• Owned by SYS user

• Synonyms begin with V$

• Listed in V$FIXED_TABLE

Throughout its operation, the Oracle server records current database activity in a set of virtual tables called dynamic performance views. These virtual tables exist in memory only when the database is running, to reflect real-time conditions of the database operation.

They point to actual sources of information in memory and the control file.

These tables are not true tables, and are not to be accessed by most users; however, DBA can query, grant the SELECT privilege, and create views on these views. These views are sometimes called fixed views because they cannot be altered or removed by the DBA.

The dynamic performance tables are owned by SYS, and their names all begin with V_$.

Views are created on these tables, and then public synonyms are created for the views. The synonym names begin with V$. For example, the V$DATAFILE view contains information about the database’s data files, and the V$FIXED_TABLE view contains information about all of the dynamic performance tables and views in the database.

The dynamic performance tables answer questions such as:

• Is the object online and available?

• Is the object open?

• What locks are being held?

• Is the session active?

Dynamic Performance Examples

• V$CONTROLFILE

• V$DATABASE

• V$DATAFILE

• V$INSTANCE

• V$PARAMETER：内存中的参数

• V$SESSION

• V$SGA

• $SPPARAMETER：spfile文件中参数

• V$TABLESPACE

• V$THREAD

• V$VERSION

The V$FIXED_TABLE view can also be queried to get a listing of the dynamic

performance views:

SQL> SELECT * FROM V$FIXED_TABLE;

To get a list of columns within a view, use the DESCRIBE keyword:

SQL> DESCRIBE V$INSTANCE;

To view the contents of the view, use the SELECT command.

SQL> SELECT * from V$INSTANCE;

Administrative Script Naming Conventions

The administrative scripts can be separated into categories by their filenames:

cat*.sql

These scripts create the data dictionary views. In addition to the catalog.sql and catproc.sql scripts, there are scripts that create information for Oracle utilities. For example, the catadt.sql script creates data dictionary views for showing metadata information for types and other object features in the ORDBMS. The catnoadt.sql script drops these tables and views.

dbms*.sql and prvt*plb:

These scripts create objects for predefined Oracle packages that extend the Oracle server functionality. These programs simplify the task of administering the database. Most SQL scripts are run during the execution of the catproc.sql script. A few additional scripts must be executed by the database administrator. An example is the dbmspool.sql script, which enables you to display the sizes of objects in the shared pool and mark them to be kept or removed in the SGA in order to reduce shared pool fragmentation.

utl*.sql

These scripts must be run when the database needs additional views and tables. For example, the utlxplan.sql script creates a table used to view the execution plan of a SQL statement.

Note: Most of these scripts must be executed under the user SYS with SYSDBA privilege.The database administrator should examine the scripts to determine which user account must be used to run the scripts.