5.1列出已创建的表的清单
select * from all_tables ; select * from dba_tables ; select * from user_tables ;
5.2 列出表的列
select * from all_tab_columns a ; select * from dba_tab_columns a ; select * from user_tab_columns a ;
5.3列出表的索引列
select a.*from all_ind_columns a ; select a.* from dba_ind_columns a ; select a.* from user_ind_columns a
5.4 列出表约束
查询 sys.all_constraints 和 sys.all_cons_columns
select a.TABLE_NAME, a.CONSTRAINT_NAME, b.COLUMN_NAME, a.CONSTRAINT_TYPE from all_constraints a, all_cons_columns b where a.TABLE_NAME = 'EMP' and a.OWNER = b.OWNER and a.TABLE_NAME = b.TABLE_NAME and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME; TABLE_NAME CONSTRAINT_NAME COLUMN_NAME CONSTRAINT_TYPE ------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- EMP FK_DEPTNO DEPTNO R EMP PK_EMP EMPNO P
5.5 列出没有相应索引的外键
列出还有没有被索引的外键的表，例如 判断EMP表中的外键是否被索引。
select a.TABLE_NAME, a.CONSTRAINT_NAME, a.COLUMN_NAME, c.INDEX_NAME from all_cons_columns a, all_constraints b, all_ind_columns c where a.TABLE_NAME = 'EMP' and a.OWNER = 'CRM' and b.CONSTRAINT_TYPE = 'R' and a.OWNER = b.OWNER and a.TABLE_NAME = b.TABLE_NAME and a.CONSTRAINT_NAME = b.CONSTRAINT_NAME and a.OWNER = c.TABLE_OWNER(+) and a.TABLE_NAME = c.TABLE_NAME(+) and a.COLUMN_NAME = c.COLUMN_NAME(+) and c.INDEX_NAME is null; TABLE_NAME CONSTRAINT_NAME COLUMN_NAME INDEX_NAME ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------------------------ EMP FK_DEPTNO DEPTNO
5.6 使用SQL来生成SQL
举例：生成SQL来统计所有表中的行数
select 'select count(1) from ' || table_name || ';' from user_tables ;
5.7 在oracle中描述数据字典视图
列出数据字典视图和他们的用途
select * from dictionary a order by a.TABLE_NAME ;
查询数据字典中的列
select * from dict_columns a where a.TABLE_NAME = 'V$SQL';
