OCP课程13：SQL之使用数据字典视图管理对象

stonebox

数据字典包含基表和用户可以访问的视图。

数据字典视图的命名规则及分类：

                               
登录/注册后可看大图

使用数据字典视图从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