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; 
          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;