Oracle数据字典学习笔记

1.查看所有用户：
SELECT * FROM DBA_USER;
SELECT * FROM ALL_USERS;
SELECT * FROM USER_USERS;

2.查看对象信息：
SELECT * FROM DBA_OBJECTS WHERE OBJECT_TYPE='TABLE';
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE';
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE';
可以使用该视图查找许多类型的对象，包括群集、数据库链接、目录、函数、索引、库、程序包、
程序包体、Java 类、抽象数据类型、资源计划、序列、同义词、表、触发器、物化视图、LOB 和视图等。
SELECT DISTINCT(OBJECT_TYPE) FROM ALL_OBJECTS ORDER BY OBJECT_TYPE;
OBJECT_TYPE
-------------------
CLUSTER
CONSUMER GROUP
CONTEXT
DIRECTORY
EDITION
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
INDEXTYPE
JAVA CLASS
JAVA DATA
JAVA RESOURCE
JOB
JOB CLASS
LIBRARY
LOB
LOB PARTITION
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
PROGRAM
QUEUE
RESOURCE PLAN
RULE
RULE SET
SCHEDULE
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
UNDEFINED
VIEW
WINDOW
WINDOW GROUP
XML SCHEMA

也可以直接查询某个特定类型对象。
例如，
SELECT * FROM USER_TABLES;
SELECT * FROM USER_INDEXES;
SELECT * FROM USER_SYNONYMS;

3.查看约束信息：
SELECT * FROM DBA_CONSTRAINTS;
SELECT * FROM ALL_CONSTRAINTS;
SELECT * FROM USER_CONSTRAINTS;
Constraint_Type 列的有效值如下：
C CHECK 约束，包括一些NOT NULL
P 主键约束
R 外键(引用)约束:外键约束总是具有R_OWNER和R_CONSTRAINT_NAME这两列的值,指出外键引用了哪个约束。
U 唯一约束
V WITH CHECK OPTION 约束(用于视图)
O WITH READ ONLY 约束(用于视图)
一旦知道了约束的名称和类型，就能通过USER_CONS_COLUMNS视图检查与之相关的列。
例如：

CREATE TABLE S(
  S# NUMBER PRIMARY KEY,
  SNAME NVARCHAR2(10) NOT NULL
);

CREATE TABLE T(
  T# NUMBER PRIMARY KEY,
  TNAME NVARCHAR2(10) NOT NULL
);

CREATE TABLE C(
  C# NUMBER PRIMARY KEY,
  CNAME NVARCHAR2(10) NOT NULL,
  T# NUMBER,
  FOREIGN KEY (T#) REFERENCES T(T#)
);

CREATE TABLE SC(
  S# NUMBER NOT NULL,
  C# NUMBER NOT NULL,
  SCORE NUMBER,
  RANK CHAR(1),
  FOREIGN KEY (S#) REFERENCES S(S#), 
  FOREIGN KEY (C#) REFERENCES C(C#),
  UNIQUE (S#, C#)
);

查看表格SC上的约束。
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='SC';
CONSTRAINT_NAME CONST SEARCH_CONDITION R_CONSTRAINT_NA
--------------- ----- -------------------- ---------------
SYS_C0011224 C "S#" IS NOT NULL
SYS_C0011225 C "C#" IS NOT NULL
SYS_C0011226 U
SYS_C0011227 R SYS_C0011218
SYS_C0011228 R SYS_C0011222

查看外键约束SYS_C0011218参照的列。
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='SYS_C0011218;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
--------------- ---------- --------------- ----------
SYS_C0011218 S S# 1

查看UNIQUE约束参照的列。
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, POSITION FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME='SYS_C0011226;
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
--------------- ---------- --------------- ----------
SYS_C0011226 SC S# 1
SYS_C0011226 SC C# 2

4.查看依赖关系
SELECT * FROM DBA_DEPENDENCIES;
SELECT * FROM ALL_DEPENDENCIES;
SELECT * FROM USER_DEPENDENCIES;

例如，如下创建视图和触发器后，

CREATE OR REPLACE VIEW SCVW(S#,SNAME,C#,CNAME,T#,TNAME,SCORE,RANK) AS
  SELECT SC.S#, S.SNAME, SC.C#, C.CNAME, C.T#, T.TNAME, SC.SCORE, SC.RANK
  FROM SC, S, C, T WHERE SC.S#=S.S# AND SC.C#=C.C# AND C.T#=T.T#;

CREATE OR REPLACE TRIGGER SC_TRIGGER
  BEFORE INSERT ON SC
  FOR EACH ROW
BEGIN
  IF (:NEW.SCORE >= 80) THEN
    :NEW.RANK := 'A';
  ELSIF (:NEW.SCORE >= 60) THEN
    :NEW.RANK := 'B';
  ELSE
    :NEW.RANK := 'C';
  END IF;
END;
/

查找依赖表格SC的对象；

SQL> SELECT NAME, TYPE FROM USER_DEPENDENCIES WHERE REFERENCED_NAME='SC' AND REFRENCED_TYPE='TABLE';

NAME TYPE
-------------------- ----------
SC_TRIGGER TRIGGER
SCVW VIEW

查找触发器SC_TRIGGER依赖的对象。

SQL> SELECT REFERENCED_NAME, REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='
SC_TRIGGER' AND TYPE='TRIGGER';

REFERENCED_NAME REFERENCED
-------------------- ----------
STANDARD PACKAGE
SC TABLE