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;
-----------------------------------------------------------------------------