来源于asktom: 普通表转分区表-凯发app官方网站

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

测试 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

阅读(983) | 评论(0) | 转发(0) |

上一篇：oracle事务管理

下一篇：linux c 线程等待测试

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们