資料字典包含基表和使用者可以訪問的檢視。

資料字典檢視的命名規則及分類：

使用資料字典檢視從dictionary檢視開始，這個檢視裡麵包含了所有的資料字典表和檢視的名字和描述。透過他來查詢其他的資料字典檢視，是查詢資料字典的入口。

SQL> conn / as sysdba

SQL> desc dictionary

Name Null? Type

----------------------------------------- -------- ----------------------------

TABLE_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

SQL> select * from dictionary where table_name='USER_OBJECTS';

TABLE_NAME COMMENTS

------------------------------ ------------------------------

USER_OBJECTS Objects owned by the user

dict為dictionary的同義詞，實際當中用dict更多一些。

SQL> select * from dictionary where table_name='DICT';

TABLE_NAME COMMENTS

------------------------------ ------------------------------

DICT Synonym for DICTIONARY

例子：查詢記憶體元件相關的資料字典檢視

SQL> select * from dict where table_name like '%MEMORY%';

TABLE_NAME COMMENTS

------------------------------ ----------------------------------------

DBA_HIST_MEMORY_RESIZE_OPS Memory Resize Operations History

DBA_HIST_MEMORY_TARGET_ADVICE Memory Target Advice History

V$SQL_SHARED_MEMORY Synonym for V_$SQL_SHARED_MEMORY

V$MEMORY_TARGET_ADVICE Synonym for V_$MEMORY_TARGET_ADVICE

V$MEMORY_RESIZE_OPS Synonym for V_$MEMORY_RESIZE_OPS

V$MEMORY_CURRENT_RESIZE_OPS Synonym for V_$MEMORY_CURRENT_RESIZE_OPS

V$MEMORY_DYNAMIC_COMPONENTS Synonym for V_$MEMORY_DYNAMIC_COMP

1、物件資訊

user_objects和all_objects

使用user_objects檢視可以檢視你擁有的所有物件，列出在你模式下的物件名字，狀態等資訊
使用all_objects檢視可以檢視你可以訪問的所有物件

例子：透過user_objects檢視檢視使用者當前所有的物件資訊

SQL> conn hr/hr

Connected.

SQL> select object_name,object_type,created,status

2 from user_objects order by object_type;

OBJECT_NAME OBJECT_TYPE CREATED STATUS

-------------------- ------------------- ------------ -------

COUNTRY_C_ID_PK INDEX 24-SEP-15 VALID

DEPT_ID_PK INDEX 24-SEP-15 VALID

JHIST_EMPLOYEE_IX INDEX 24-SEP-15 VALID

例子：檢視當前使用者下所有無效的物件

SQL> select object_name,object_type,status from user_objects where status <> 'VALID';

no rows selected

2、表資訊

例子：透過user_tables檢視檢視使用者當前所有表資訊

SQL> desc user_tables;

SQL> select table_name from user_tables;

TABLE_NAME

------------------------------

LOCATIONS

DEPARTMENTS

tabs為user_tables的同義詞，實際當中tabs用得更多一些

SQL> select * from dict where table_name like 'TABS';

TABLE_NAME COMMENTS

------------------------------ ----------------------------------------

TABS Synonym for USER_TABLES

例子：使用user_tab_columns檢視錶欄位的資訊

SQL> desc user_tab_columns;

SQL> select column_name,data_type,data_length,data_precision,data_scale,nullable

2 from user_tab_columns where table_name='EMPLOYEES';

COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE N

------------------------------ ---------- ----------- -------------- ---------- -

EMPLOYEE_ID NUMBER 22 6 0 N

例子：檢視哪些表有department_id欄位

SQL> select table_name from user_tab_columns where column_name='DEPARTMENT_ID';

TABLE_NAME

------------------------------

DEPARTMENTS

3、約束資訊

user_constraints檢視描述了表的約束定義

user_cons_columns檢視描述了約束對應的列

例子：檢視錶employees的約束資訊

SQL> desc user_constraints;

SQL> select constraint_name,constraint_type,search_condition,r_constraint_name,delete_rule,status

2 from user_constraints where table_name='EMPLOYEES';

CONSTRAINT_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RU STATUS

------------------------------ - -------------------- ------------------------------ --------- --------

EMP_SALARY_MIN C salary > 0 ENABLED

constraint type：

C（check constraint on table,or not null)
P(primary key)
U(unique key)
R(referential integrity)
V(with check option,on a view)
O(with read-only,on a view)

delete rule:

cascade
set null
no action

status:

enable
disable

例子：檢視錶employees中約束對應的列

SQL> desc user_cons_columns;

SQL> select constraint_name,column_name

2 from user_cons_columns

3 where table_name='EMPLOYEES';

CONSTRAINT_NAME COLUMN_NAME

------------------------------ ------------------------------

EMP_SALARY_MIN SALARY

4、檢視資訊

例子：檢視當前使用者有哪些檢視

SQL> desc user_views;

SQL> select distinct view_name from user_views;

VIEW_NAME

------------------------------

EMPVU10

例子：檢視檢視的建立語句

SQL> select text from user_views

2 where view_name='EMP_DETAILS_VIEW';

TEXT

--------------------------------------------------------------------------------

SELECT

e.employee_id,

e.job_id,

e.manager_id,

e.department_id,

d.locat

沒顯示完，因為這個型別是一個long型別，需要設定一下

SQL> set long 2000

SQL> select text from user_views

2 where view_name='EMP_DETAILS_VIEW';

TEXT

--------------------------------------------------------------------------------

SELECT

e.employee_id,

e.job_id,

e.manager_id,

e.department_id,

d.location_id,

l.country_id,

e.first_name,

e.last_name,

e.salary,

e.commission_pct,

TEXT

--------------------------------------------------------------------------------

d.department_name,

j.job_title,

l.city,

l.state_province,

c.country_name,

r.region_name

FROM

employees e,

departments d,

jobs j,

locations l,

TEXT

--------------------------------------------------------------------------------

countries c,

regions r

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

AND l.country_id = c.country_id

AND c.region_id = r.region_id

AND j.job_id = e.job_id

WITH READ ONLY

如果使用PL/SQL的話，就很簡單了，不需要去設定了。

5、序列資訊

例子：檢視當前使用者序列的資訊

SQL> desc user_sequences;

SQL> select sequence_name,min_value,max_value,increment_by,last_number

2 from user_sequences;

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER

------------------------------ ---------- ---------- ------------ -----------

DEPARTMENTS_SEQ 1 9990 10 280

其中，如果使用了nocache，LAST_NUMBER是下一個可用的序列值

我們也可以所有user_sequences的同義詞seq

SQL> select * from dict where table_name='SEQ';

TABLE_NAME COMMENTS

------------------------------ ------------------------------

SEQ Synonym for USER_SEQUENCES

6、同義詞資訊

例子：檢視當前使用者同義詞的資訊

SQL> conn / as sysdba

Connected.

SQL> desc user_synonyms;

SQL> select * from user_synonyms;

SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK

-------------------- -------------------- -------------------- --------------------

DEF$_AQCALL SYSTEM DEF$_AQCALL

SQL> select owner,synonym_name from all_synonyms where length(synonym_name)<4;

OWNER SYNONYM_NAME

------------------------------ --------------------

APEX_030200 HTF

例子：為Database Link建立一個同義詞

SQL> create database link dblink_from_stone connect to hr identified by hr using '192.168.230.139:1521/stone';

Database link created.

SQL> create synonym dblink for dblink_from_stone;

Synonym created.

7、增加註釋

可以為表和列增加註釋，可以透過如下資料字典檢視查詢註釋

ALL_TAB_COMMENTS
ALL_COL_COMMENTS
USER_TAB_COMMENTS
USER_COL_COMMENTS

例子：為表employees增加註釋

SQL> conn hr/hr

Connected.

SQL> comment on table employees is 'Employee Information';

Comment created.

SQL> select table_name,comments from user_tab_comments where table_name='EMPLOYEES';

TABLE_NAME COMMENTS

-------------------- ----------------------------------------

EMPLOYEES Employee Information

8、總結

DICTIONARY
USER_OBJECTS
USER_TABLES
USER_TAB_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_VIEWS
USER_SEQUENCES

9、相關習題

（1）Which two statements are true? (Choose two.）

A.The USER_SYNONYMS view can provide information about private synonyms.

B.The user SYSTEM owns all the base tables and user?accessible views of the data dictionary.

C.All the dynamic performance views prefixed with V$ are accessible to all the database users.

D.The USER_OBJECTS view can provide information about the tables and views created by the user only.

E.DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

答案：AE

（2）Which statements are true？(Choose all that apply.)
A.The data dictionary is created and maintained by the database administrator.
B.The data dictionary views can consist of joins of dictionary base tables and user-defined tables.
C.The usernames of all the users including the database administrators are stored in the data dictionary.
D.The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
E.Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
F.Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary

答案：CDF

（3）Which view would you use to display the column names and DEFAULT values for a table ？

A.DBA_TABLES
B.DBA_COLUMNS
C.USER_COLUMNS
D.USER_TAB_COLUMNS

答案：D

（4）Which SQL statement would display the view names and definitions of all the views owned by you?

A. SELECT view_name, text FROM user_view;

B. SELECT view_name, text FROM user_object ;

C. SELECT view_name, text FROM user_objects;

D. SELECT view_name, text FROM user_views;

答案：D

（5）Which two statements are true？(Choose two.)
A.The USER_SYNONYMS view can provide information about private synonyms.
B.The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C.All the dynamic performance views prefixed with V$ are accessible to all the database users.
D.The USER_OBJECTS view can provide information about the tables and views created by the user only.
E.DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

答案：AE

（6）Evaluate the following SELECT statement and view the Exhibit to examineits output: SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS ;Which two statements are true about the output? (Choose two.)

A.In the second column,C indicates a check constraint.

B.The STATUS column indicates whether the table is currently in use.

C.The R_CONSTRAINT_NAME column gives the alternative name for the constraint.

D.The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

答案：AD

OCP課程13：SQL之使用資料字典檢視管理物件

相關文章