如何将多个结构相同的普通表转换为一个分区表?
测试 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
