create table SAP_E_DSOQQZT_TMP
(
id NUMBER not null,
request NUMBER,
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 16K
next 8K
minextents 1
maxextents unlimited
)
partition by range(request) interval(1000000)
(
partition SAP_E_DSOQQZT_part values less than(700000000)
);
-- Add comments to the columns
comment on column SAP_E_DSOQQZT_TMP.id
is 'ID';
comment on column SAP_E_DSOQQZT_TMP.request
is '請求號';
-- Create/Recreate indexes
create index FCREATETIMEA on SAP_E_DSOQQZT_TMP ( REQUEST)
tablespace ZHFXBEMS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 1847M
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SAP_E_DSOQQZT_TMP
add constraint PK_SAP_E_DSOQQZTA primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 112M
next 1M
minextents 1
maxextents unlimited
);
第二步：判斷表是否可以在線從新定義
begin
dbms_redefinition.can_redef_table('ERPSU','SAP_E_DSOQQZT',DBMS_REDEFINITION.CONS_USE_PK);
end;
第三步：開始執行數據的遷移（30分鐘）
begin
DBMS_REDEFINITION.START_REDEF_TABLE('ERPSU','SAP_E_DSOQQZT', 'SAP_E_DSOQQZT_TMP');
end;
第四步：進行權限對象的遷移
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('ERPSU','SAP_E_DSOQQZT', 'SAP_E_DSOQQZT_TMP',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
第五步：結束整個重定義
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('ERPSU','SAP_E_DSOQQZT', 'SAP_E_DSOQQZT_TMP');
END;
第六步，刪除臨時表 SAP_E_DSOQQZT_TMP
萬一哪一步出錯，執行：
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => 'ERPSU',
orig_table => 'SAP_E_DSOQQZT',
int_table => 'SAP_E_DSOQQZT_TMP'
);
END;
-----------------------------------------------------------------------------