Oracle 查看用户相关信息

</pre><pre name="code" class="sql">--0 表空间
--查看当前用户下的角色、系统权限、表级权限、所有表
SELECT * FROM USER_ROLE_PRIVS
SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_TABLES;

--1 用户    
--查看当前用户的缺省表空间
SELECT USERNAME, DEFAULT_TABLESPACE FROM USER_USERS
--显示当前会话的所有权限、角色
SELECT * FROM SESSION_PRIVS;
SELECT * FROM SESSION_ROLES;
--显示指定用户所具有的系统权限
SELECT *  FROM DBA_SYS_PRIVS WHERE GRANTEE = 'WCADMIN'

--2 表
--查看名称包含COST字符的表
SELECT OBJECT_NAME, OBJECT_ID FROM USER_OBJECTS WHERE INSTR(OBJECT_NAME, 'COST') > 0
--查看表的信息
SELECT * FROM USER_OBJECTS WHERE INSTR(OBJECT_NAME, 'COST') > 0
--查看表大小
SELECT SUM(BYTE) / (1024 * 1024) AS "SIZE(M)" FROM USER_SEGMENTS WHERE SEGMENT_NAME = UPPER('&COST')
--查看放在ORACLE内存区的表
SELECT TABLE_NAME, CACHE FROM USER_TABLES WHERE INSTR(CACHE, 'Y') > 0;

--3 索引
--查看索引个数和类别
SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME FROM USER_INDEXES ORDER BY TABLE_NAME;
--查看索引被索引的字段
SELECT * FROM USER_IND_COLUMNS WHERE INDEX_NAME = UPPER('ACCOUNT_LOGIN_NAME_UK');
--查看索引的大小 
SELECT SUM(BYTES) / (1024 * 1024) as "SIZE(M)" from USER_SEGMENTS;

--4 序列号    
--查看序列号，last_number是当前值
SELECT * FROM USER_SEQUENCES;

--5 视图     
--查看视图的名称
SELECT VIEW_NAME FROM USER_VIEWS;
--查看创建视图的select语句
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_name');

--6 查看表的约束条件
SELECT CONSTRAINT_NAME,
       CONSTRAINT_TYPE,
       SEARCH_CONDITION,
       R_CONSTRAINT_NAME
  FROM USER_CONSTRAINTS
 WHERE TABLE_NAME = UPPER('COST');
 
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;