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明，然後再做操作。