oracle 数据字典

• user_recyclebin

 

drop 一个表后，实际上并没有真正删除，放在回收上中，通过user_recyclebin可以进行恢复。

具体用法如下：

 

select * from user_recyclebin;
flashback table "BIN$1ygq8CJTQQm2mzqm90N/rg==$0"  to before drop ;

 

• user_constraints
  

外键的删除，启用，禁用

select 'alter table ' || table_name || ' disable constraint ' ||
       constraint_name || ';'
  from user_constraints where constraint_type='R';

select 'alter table ' || table_name || ' enable constraint ' ||
       constraint_name || ';'
  from user_constraints where constraint_type='R';
  
  select 'alter table ' || table_name || ' drop constraint ' ||
       constraint_name || ';'
  from user_constraints where constraint_type='R';

 

说明：constraint_type R表示外键 forgien key，P表示主键 primary，C表示check

• user_indexs

创建数据库中所有索引如下：

select 'create index ' || ui.index_name || ' on ' || ui.table_name || '(' ||
       uic.COLUMN_NAME || ' ' || uic.DESCEND || ') ;'
  from user_indexes ui, user_ind_columns uic
 where ui.index_name = uic.index_name;

 以上存在一个问题，假如一个表有联合索引，在user_ind_columns中会有多条记录，但索引名都一样，只是列名不同，在创建索引时会提出"名称已由现有对象使用"的错误,以下SQL会解决这个问题：

select 'create index ' || ui.index_name || ' on ' || ui.table_name || '(' ||
       listagg(uic.COLUMN_NAME, ',') within group(order by uic.COLUMN_POSITION) || ' ' || uic.DESCEND || ') ;'
  from user_indexes ui, user_ind_columns uic
 where ui.index_name = uic.index_name
 group by ui.index_name, ui.table_name, uic.DESCEND;

  注意：listagg函数只能在11G中使用

 

更改索引的表空间如下：

select 'alter index ' || index_name ||
       ' rebuild tablespace newTablespace;'
  from user_indexes

 

 

• all_sequences

重新创建指定用户下所有索引如下：

select 'drop SEQUENCE ' || x.sequence_owner || '.' || x.sequence_name || ';'
  from all_sequences x
 where x.sequence_owner = 'OWNER';

select 'create SEQUENCE ' || x.sequence_owner || '.' || x.sequence_name ||
       ' MINVALUE 1 MAXVALUE 99999999999999999999 INCREMENT BY 1 START WITH ' ||
       x.last_number || ' CACHE 20 NOORDER NOCYCLE' || ';'
  from all_sequences x
 where x.sequence_owner = 'OWNER';

 

• all_objects

重新编译存储过程如下：

select 'alter procedure ' || object_name || ' compile;'
  From all_objects where status = 'INVALID' and object_type = 'PROCEDURE';

 

• all_tables ,dba_segments,all_tab_comments

查看所有表的名称，大小，注释等信息

 select z.table_name, z.num_rows, bytes / 1024 / 1024 MByte, A.COMMENTS
   from all_tables z, all_tab_comments A, dba_segments s
  where A.owner = 'OWNER'
    AND Z.TABLE_NAME = A.TABLE_NAME
    and z.table_name = s.segment_name
  order by num_rows desc;

 

重新建立同义词如下：

select 'DROP SYNONYM ' || A.TABLE_NAME ||';'
  from all_tables A
 where owner in ('OWNER');

select 'create or replace synonym ' || A.TABLE_NAME || ' for ' || A.OWNER || '.' ||
       A.TABLE_NAME || ';'
  from all_tables A
 where owner in ('OWNER');

 

• user_tables

更改所有表空间：

 

select 'alter table  ' || table_name || '  move tablespace newTablespace;'
  from user_tables;

 

• all_tab_comments

查看所有表的注释：

 select 'comment on table ' || owner || '.' || table_name  || ' is ''' ||
       replace(replace(comments, chr(9), ''), chr(10), ' ') || ''';'
  from all_tab_comments
 where owner = 'OWNER'

 

• all_col_comments

查看所有列的注释：

 select 'comment on table ' || owner || '.' || table_name  || ' is ''' ||
       replace(replace(comments, chr(9), ''), chr(10), ' ') || ''';'
  from all_tab_comments
 where owner = 'OWNER'