oracle 9i提供了dbms_redefinition包來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)的表的在線重定義功能。在實(shí)際的應(yīng)用上，我們可以利用這個(gè)包來(lái)進(jìn)行：

(1)堆表與分區(qū)之間進(jìn)行轉(zhuǎn)換。(2)重建表以減少HWM。10g能shrink，9i如果用move tablespace and rebuild index在move的時(shí)候會(huì)鎖表，如果想實(shí)現(xiàn)在線降低HWM，只能用這個(gè)了。

(3)在線更改表結(jié)構(gòu)，如更改列的前后順序，將column_a，column_b改成column_b，column_a

1.創(chuàng)建測(cè)試表

create table rebuild_table as select * from dba_objects;

insert into rebuild_table select * from rebuild_table;

insert into rebuild_table select * from rebuild_table;

insert into rebuild_table select * from rebuild_table;

commit；

delete from rebuild_table;

insert into rebuild_table select * from dba_objects;

insert into rebuild_table select * from rebuild_table;

insert into rebuild_table select * from rebuild_table;

commit;

update rebuild_table set object_id=rownum;

alter? table rebuild_table add CONSTRAINT P_YY PRIMARY KEY (OBJECT_ID);

select * from rebuild_table;

2.創(chuàng)建分區(qū)表

create table REBUILD_PA_TABLE

(

STATIS_DATE??? NUMBER(8),

OWNER????????? VARCHAR2(30),

OBJECT_NAME??? VARCHAR2(128),

SUBOBJECT_NAME VARCHAR2(30),

OBJECT_ID????? NUMBER not null,

DATA_OBJECT_ID NUMBER,

OBJECT_TYPE??? VARCHAR2(18),

CREATED??????? DATE,

LAST_DDL_TIME? DATE,

TIMESTAMP????? VARCHAR2(19),

STATUS???????? VARCHAR2(7),

TEMPORARY????? VARCHAR2(1),

GENERATED????? VARCHAR2(1),

SECONDARY????? VARCHAR2(1)

)

partition by? list (STATIS_DATE)

(

partition p20111031 values (20111031)

);

alter table REBUILD_PA_TABLE add partition p20111101 values(20111101);

;

alter table REBUILD_PA_TABLE

add constraint Pa_YYY primary key (OBJECT_ID)

;

-- 打開(kāi)重定義

begin

dbms_redefinition.CAN_REDEF_TABLE('boc_rdm','REBUILD_TABLE');

end;

-- 開(kāi)始重定義

begin

dbms_redefinition.START_REDEF_TABLE('boc_rdm','REBUILD_TABLE','REBUILD_PA_TABLE');

end;

-- 進(jìn)行數(shù)據(jù)同步

begin

dbms_redefinition.sync_interim_table('BOC_RDM','REBUILD_TABLE','REBUILD_PA_TABLE');

end;

-- 重命名表，把非分區(qū)表轉(zhuǎn)化為分區(qū)表

begin

dbms_redefinition.finish_redef_table('BOC_RDM','REBUILD_TABLE','REBUILD_PA_TABLE');

end;

select * from REBUILD_TABLE partition(p20111031)

總結(jié)

以上是生活随笔為你收集整理的oracle 分区字符转换,Oracle 普通表与分区表转换的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。