Types:

1. Data dictionary tables ---> reflect the database's information; describes the database and its objects

2. Dynamic performance tables ---> reflect the instance's information

[@more@]

Data Dictionary Tables Contents:

--- Logical and physical database structure

--- Definitions and space allocations of objects

--- Integrity constraints

--- Users

--- Roles

--- Privileges

--- Auditing

All of them shows that it describes the Oracle Database not instance

Types of Data Dictionary Tables:

1. Base tables

--- Store description of database

--- Created with CREATE DATABASE

2. Data Dictionary views

--- Summarize base table information

--- Created using catalog.sql script

Data Dictionary Tables Usage:

--- The Oracle server uses it to find informationabout: (1)Users,(2)Schema objects,(3)Storage structures.

--- The Oracle server modifies it when a DDL statement is executed.

--- Users and DBAs can use it as a read-only reference for information about the database.

Data Dictionary View Categories:

--- DBA: What is in all the schemas --->DBA_xxx

--- ALL: What the user can access --->ALL_xxx

--- USER: What is in the user's schema --->USER_xxx

Dynamic Performance Tables: ---> V_$xxx | V$xxx | Vxxx

--- Dynamic performance views record current database activity(that is the instance).

--- Views are continually updated while the database is operational.

--- Information is accessed from: Memory,Control file

--- Dynamic views are owned by SYS user

--- DML is not allowed

--- DBA uses dynamic views to monitor and tune the database

Querying the Data Dictionary and Dynamic Performance Views:

1. To get an overview of the data dictionary and dynamic performance views, the DICTIONARY view or its synonym DICT can be queried.

eg:

SELECT * FROM dictionary;

SELECT * FROM dictionary WHERE table_name like 'DBA_%';

--- The V$FIXED_TABLE view can also be queried to get a listing of the views:

eg:

SELECT * FROM V$FIXED_TABLE;

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

eg:

DESCRIBE V$INSTANCE;

3. To get an overview of the columns in the data dictionary and dynamic performance views,the DICT_COLUMNS view can be queried.

eg:

SELECT * FROM DICT_COLUMNS ORDER BY TABLE_NAME;

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

eg:

SELECT * from V$INSTANCE;

ITPub博客

1Z0-031.5. 数据字典的内容与用法

最新文章