Oracle数据字典常用查询

心已赠人 2022-08-05 11:19 230阅读 0赞

ORACLE数据字典的常用查询  
  
一、用户

查看当前用户的缺省表空间  
SQL>select username,default\_tablespace from user\_users;

查看当前用户的角色  
SQL>select \* from user\_role\_privs;  
  
查看当前用户的系统权限和表级权限  
SQL>select \* from user\_sys\_privs;  
SQL>select \* from user\_tab\_privs;

二、表  
  
查看用户下所有的表  
SQL>select \* from user\_tables;  
  
查看名称包含log字符的表  
SQL>select object\_name,object\_id from user\_objects where instr(object\_name,'LOG')>0;  
  
查看某表的创建时间  
SQL>select object\_name,created from user\_objects where object\_name=upper('&table\_name');  
  
查看某表的大小  
SQL>select sum(bytes)/(1024\*1024) as size(M) from user\_segments where segment\_name=upper('&table\_name');  
  
查看放在ORACLE的内存区里的表  
SQL>select table\_name,cache from user\_tables where instr(cache,'Y')>0;  
  
给表添加描述  
SQL>comment on table 表 is '表注释';  
  
给字段添加描述  
SQL>comment on column 表.列 is '列注释';  
  
查看当前用户所有表的描述  
SQL>select o.object\_name,c.comments  
from user\_objects o  
left outer join user\_tab\_comments c  
on o.object\_name=c.table\_name  
where object\_type='TABLE'  
/\*where instr(o.object\_name,'TCO\_')>0\*/  
and c.comments like '%部门%'

查看指定用户所有字段的描述  
SQL>select c.table\_name,o.column\_name,o.data\_type,o.data\_length,c.comments  
from user\_tab\_columns o  
left outer join user\_col\_comments c  
on o.TABLE\_NAME=c.table\_name  
and o.COLUMN\_NAME=c.column\_name  
where o.table\_name like upper('OM\_ORGANIZATION%') --指定表名

三、索引  
  
查看索引个数和类别  
SQL>select index\_name,index\_type,table\_name from user\_indexes order by table\_name;  
查看索引被索引的字段  
SQL>select \* from user\_ind\_columns where index\_name=upper('&index\_name');  
  
查看索引的大小  
SQL>select sum(bytes)/(1024\*1024) as size(M) from user\_segments where segment\_name=upper('&index\_name');  
  
四、序列号  
  
查看序列号，last\_number是当前值  
SQL>select \* from user\_sequences;  
  
五、视图  
  
查看视图的名称  
SQL>select view\_name from user\_views;  
  
查看创建视图的select语句  
SQL>select view\_name,text\_length from user\_views;  
SQL>set long 2000;说明：可以根据视图的text\_length值设定set long 的大小  
SQL>select text from user\_views where view\_name=upper('&view\_name');  
  
六、同义词  
  
查看同义词的名称  
SQL>select \* from user\_synonyms;  
  
七、约束条件  
  
查看某表的约束条件  
SQL>select constraint\_name, constraint\_type,search\_condition, r\_constraint\_name from user\_constraints where table\_name = upper('&table\_name');  
  
SQL>select c.constraint\_name,c.constraint\_type,cc.column\_name  
from user\_constraints c,user\_cons\_columns cc  
where c.owner = upper('&table\_owner') and c.table\_name = upper('&table\_name')  
and c.owner = cc.owner and c.constraint\_name = cc.constraint\_name  
order by cc.position;  
  
八、存储函数和过程  
  
查看函数和过程的状态  
SQL>select object\_name,status from user\_objects where object\_type='FUNCTION';  
SQL>select object\_name,status from user\_objects where object\_type='PROCEDURE';  
  
查看函数和过程的源代码  
SQL>select text from all\_source where owner=user and name=upper('&plsql\_name');  
  
九、触发器  
  
查看触发器

setlong50000;

set heading off;

set pagesize 2000;

select

'create or replace trigger ' ||

trigger\_name || '' || chr(10)||

decode( substr( trigger\_type, 1, 1 ),

'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||

chr(10) ||

triggering\_event || chr(10) ||

'ON ' || table\_owner || '.' ||

table\_name || '' || chr(10) ||