数据字典包含基表和用户可以访问的视图。
数据字典视图的命名规则及分类：
使用数据字典视图从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视图查看用户当前所有的对象信息
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：
delete rule:
status:
例子：查看表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、增加注释
可以为表和列增加注释，可以通过如下数据字典视图查询注释
例子：为表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、总结
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
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |
