创建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明,然后再做操作。