来源于Asktom: 普通表转分区表

如何将多个结构相同的普通表转换为一个分区表?

测试 Oracle Version 9.2.0.4
-----------------------------------
文件名: part.sql
/*
 from :
 */
create table t1 as
select sysdate dt, all_objects.*
from all_objects
/

create table t2 as
select add_months(sysdate, -12) dt, all_objects.*
from all_objects
/

create table t3 as
select add_months(sysdate, -24) dt, all_objects.*
from all_objects
/

create table t
(dt, owner, object_name, subobject_name, object_id,
 data_object_id, object_type, created, last_ddl_time, timestamp, status,
 temporary, generated, secondary)
 partition by range(dt)(
 partition part2004 values less than (to_date('01-jan-2005','dd-mon-yyyy')),  
 partition part2005 values less than (to_date('01-jan-2006','dd-mon-yyyy')),  
 partition part2006 values less than (to_date('01-jan-2007','dd-mon-yyyy'))  
 )
 as
 select sysdate dt, all_objects.*
 from all_objects
 where 1=0
/

alter table t
exchange partition part2006
with table t1
without validation
/

alter table t
exchange partition part2005
with table t2
without validation
/

alter table t
exchange partition part2004
with table t3
without validation
/

-----------------------------------
以下为运行结果:
SQL> @part.sql
DOC> from :
DOC> */

Table created.


Table created.


Table created.


Table created.


Table altered.


Table altered.


Table altered.

SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
T                                                                               
T1                                                                              
T2                                                                              
T3                                                                              

SQL> select count(*) from t1;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

SQL> select count(*) from t2;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

SQL> select count(*) from t3;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

SQL> select count(*) from t;

  COUNT(*)                                                                      
----------                                                                      
     30162                                                                      

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> drop table t3;

Table dropped.

SQL> select table_name from user_tables;

TABLE_NAME                                                                      
------------------------------                                                  
T