查看用户对象_用户下对象-优快云博客

本文详细介绍如何使用Oracle数据库的内置视图来查询用户信息、表、索引、序列号等对象的详细信息，帮助数据库管理员更好地管理和维护Oracle数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、用户

查看当前用户的缺省表空间
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;
          select   table_ name   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> 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> 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');

六、同义词
查看同义词的名称
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');

九、触发器
查看触发器
set   long   50000;
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')   ¦ ¦
triggering_event   ¦ ¦   chr(10)   ¦ ¦
            'ON   "'   ¦ ¦   table_owner   ¦ ¦   '"."'   ¦ ¦
table_name   ¦ ¦   '"'   ¦ ¦   chr(10)   ¦ ¦
decode(   instr(   trigger_type,   'EACH   ROW'   ),   0,   null,   'FOR   EACH   ROW'   )   ¦ ¦   chr(10)   ,
            trigger_body
from   user_triggers;