查看Oracle当前用户下的信息（用户，表视图，索引，表空间，同义词，存储过程函数，约束条件）

我会带着你远行 2022-06-17 00:16 209阅读 0赞

# 查看Oracle当前用户下的（表视图，同义词） #

作者： alsace\_008　和 [**oracle**][oracle]相关

**0、表空间**  
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;  
**1、用户**  
查看当前用户的缺省表空间  
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 session\_privs;  
显示指定用户所具有的系统权限  
SQL>select \* from dba\_sys\_privs where grantee='GAME';  
**　　2、表**  
查看用户下所有的表  
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;  
**　3、索引**  
查看索引个数和类别  
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');  
**　4、序列号**  
查看序列号，last\_number是当前值  
SQL>select \* from user\_sequences;  
**　　5、视图**  
查看视图的名称  
SQL>select view\_name from user\_views;  
查看创建视图的select语句  
SQL>set 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');  
**6、同义词**  
查看同义词的名称  
SQL>select \* from user\_synonyms;  
**　　7、约束条件**  
查看某表的约束条件  
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;  
**　　8、存储函数和过程**  
查看函数和过程的状态  
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');

### 相关资料： ###

*  [oracle][]｜[SQL\*Loader简介][SQL_Loader]
 *  [oracle][]｜[在RHAS3上成功安装Oracle9204][RHAS3_Oracle9204]
 *  [oracle][]｜[oracle10g安装添加组件操作步骤][oracle10g]
 *  [oracle][]｜[查看Oracle当前用户下的（表视图，同义词）][Oracle]
 *  [oracle][]｜[PL/SQL流程控制语句][PL_SQL]
 *  [oracle][]｜[Oracle的物化视图][Oracle 1]
 *  [oracle][]｜[把游标说透][Link 1]

**查看Oracle当前用户下的（表视图，同义词）**来源网络，如有侵权请告知，即处理！

[oracle]: http://biancheng.dnbcw.net/oracle/
[SQL_Loader]: http://biancheng.dnbcw.net/oracle/336009.html
[RHAS3_Oracle9204]: http://biancheng.dnbcw.net/oracle/336010.html
[oracle10g]: http://biancheng.dnbcw.net/oracle/336144.html
[Oracle]: http://biancheng.dnbcw.net/oracle/335929.html
[PL_SQL]: http://biancheng.dnbcw.net/oracle/335928.html
[Oracle 1]: http://biancheng.dnbcw.net/oracle/335751.html
[Link 1]: http://biancheng.dnbcw.net/oracle/335750.html