Oracle 普通Table进行Reorg

创建pp1实例表.
SQL> create table pp1 as select * from pp;

Table created.

SQL> create index pp1_index on pp1(id);

Index created.

SQL> execute dbms_stats.gather_table_stats('scott','pp1');

PL/SQL procedure successfully completed.

SQL> select count(*) from pp1;

  COUNT(*)
----------
  10000000

现在开始重建table。
1.将原来pp1 table的statistics export出来

SQL> exec dbms_stats.create_stat_table('scott','stat_table');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.export_table_stats('scott','pp1',null,'stat_table');

PL/SQL procedure successfully completed.

2.将pp1 table的数据insert到pp1_copy上
SQL> edit
Wrote file afiedt.buf

  1  create table pp1_copy
  2  tablespace users
  3  nologging
  4  parallel 2
  5  as
  6* select /*+parallel(a,2)*/ * from pp1 a where dt>to_date('2014/01/01','yyyy/mm/dd')
SQL>
SQL> /

Table created.

3.在pp1_copy上创建  和在pp1上相同的index

SQL> create index pp1_copy_index on pp1_copy(id) nologging parallel 2;

Index created.

4.更改table,index为logging,noparallel模式

SQL> alter table pp1_copy logging noparallel;

Table altered.

SQL> alter index pp1_copy_index logging noparallel;

Index altered.

5.删除原来的pp1 table

SQL> drop table pp1 purge;

Table dropped.

6.将pp1_copy table和index rename为pp1以及相应的index

SQL> alter table pp1_copy rename to pp1;

Table altered.

SQL> alter index pp1_copy_index rename to pp1_index;

Index altered.

7.将export的statistics import到pp1中
SQL> exec dbms_stats.import_table_stats(ownname=>'scott',tabname=>'pp1',stattab=>'stat_table');

PL/SQL procedure successfully completed.

8.编译原来存在package,view,procedure

alter package package_name compile;
alter procedure procedure_name compile;
alter view view_name compile;

=========================================================================================
*其中可以将pp1先rename为其他table明，然后再做操作。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-1852372/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24237320/viewspace-1852372/