exchange partition 的用法

iteye_18365

于 2012-02-28 17:15:00 发布

阅读量126

点赞数

本文演示了如何在Oracle数据库中创建带有日期范围分区的表，并通过一个空的分区表进行数据分区交换。首先创建两个临时表t1和t2，接着创建一个分区表t并初始化为空，最后将特定分区的数据与t2表的数据进行交换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create table t1 as select sysdate dt,all_objects.* from all_objects;
create table t2 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 part2003 values less than ( to_date( '01-jan-2006',

'dd-mon-yyyy') ),

partition part2004 values less than ( to_date( '01-jan-2007',

'dd-mon-yyyy') ),

partition part2005 values less than ( to_date( '01-jan-2008',

'dd-mon-yyyy') )

)

as

select sysdate dt, all_objects.* from all_objects where 1=0;

alter table t

exchange partition part2003

with table t2 ;