oracle 分区_oracle普通表改造分区表(在线重定义)

1.备份要改造表的元数据

expdp xxx/xxx tables=NMOSDB.TAD_TASKS_STEP directory=TMP dumpfile=TAD_TASKS_STEP.dmp logfile=TAD_TASKS_STEP.log content=metadata_only cluster=no

impdp XXX/XXX tables=NMOSDB.TAD_TASKS_STEP directory=TMP dumpfile=TAD_TASKS_STEP.dmp logfile=TAD_TASKS_STEP_IMP.log cluster=no content=metadata_only sqlfile=TAD_TASKS_STEP.sql

2.检查

（1）检查表并行度

select t.owner||'.'||t.table_name,t.degree from dba_tables t where t.owner||'.'||t.table_name='NMOSDB.TAD_TASKS_STEP';

（2）检查索引并行度

select t.owner||'.'||t.index_name index_name,t.table_name,t.degree,t.status,t.partitioned from dba_indexes t where t.table_owner||'.'||t.table_name='NMOSDB.TAD_TASKS_STEP';

（3）检查源表约束

select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,INDEX_NAME,R_CONSTRAINT_NAME from dba_constraints where OWNER||'.'||TABLE_NAME='NMOSDB.TAD_TASKS_STEP';

（4）检查触发器

select t.table_name,t.owner,t.trigger_name,t.status from dba_triggers t where t.table_owner='NMOSDB' and t.table_name='TAD_TASKS_STEP';

（5）检查表空间是否够用（省略）

3.操作步骤

（1）打开session并行

ALTER SESSION FORCE PARALLEL DML PARALLEL 8;

ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

（2）创建中间表

CREATE TABLE "NMOSDB"."TAD_TASKS_STEP_MID"

( "ALARM_FP0" NUMBER(10,0),

"ALARM_FP1" NUMBER(10,0),

"ALARM_FP2" NUMBER(10,0),

"ALARM_FP3" NUMBER(10,0),

"STEP_ID" NUMBER(*,0),

"STEP_NAME" VARCHAR2(150 BYTE),

"STEP_INFO1" VARCHAR2(2000 BYTE),

"STEP_INFO2" VARCHAR2(2000 BYTE),

"STEP_INFO3" VARCHAR2(2000 BYTE),

"STEP_INFO4" VARCHAR2(2000 BYTE),

"RESULRINFO" VARCHAR2(4000 BYTE),

"FLAG" NUMBER(*,0),

"EXECUT_TIME" DATE DEFAULT sysdate

)

PARTITION BY RANGE (EXECUT_TIME) INTERVAL (NUMTODSINTERVAL (5, 'day'))

(partition part_t01 values less than(to_date('2019-08-01', 'yyyy-mm-dd')))

TABLESPACE "NMOSDBS" ;

CREATE INDEX "NMOSDB"."TAD_TASKS_STEP_INDEX11" ON "NMOSDB"."TAD_TASKS_STEP_MID" ("ALARM_FP0", "ALARM_FP1", "ALARM_FP2", "ALARM_FP3", "EXECUT_TIME")

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "NMOS_INXDBS" PARALLEL 1 ;

（3）把中间分区表设成nologging

alter table NMOSDB.TAD_TASKS_STEP_MID nologging;

（4）验证源表是否符合在线重定义

exec dbms_redefinition.can_redef_table('NMOSDB','TAD_TASKS_STEP',DBMS_REDEFINITION.cons_use_rowid);

（5）开始在线重定义

Oracle在线重定义的基础是物化视图技术。此时，我们检查试图user_mviews，可以看到有一个新的物化视图生成，并且存在对应的物化视图日志。

因为重定义时间较长，所以要写到后台运行

sqlplus / as sysdba << EOF

set timing on

set time on

BEGIN

DBMS_REDEFINITION.start_redef_table(uname=> 'NMOSDB',

orig_table => 'TAD_TASKS_STEP',

int_table => 'TAD_TASKS_STEP_MID',

options_flag =>dbms_redefinition.cons_use_rowid

);

END;

/

EXIT

EOF

（6）中间表在线COPY依赖对像

sqlplus / as sysdba << EOF

set timing on

set time on

set serveroutput on;

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS

(

uname => 'NMOSDB',

orig_table => 'TAD_TASKS_STEP',

int_table => 'TAD_TASKS_STEP_MID',

copy_indexes => 0,

copy_triggers => TRUE,

copy_constraints => TRUE,

copy_privileges => TRUE,

ignore_errors => FALSE,

num_errors => num_errors,

copy_statistics => TRUE);

END;

/

EXIT

date

EOF

（7）在线重定义检测错误信息

select * from DBA_REDEFINITION_ERRORS;

（8）检查在线重定义结果

确认表并行度，索引并行度，约束，权限。如发现不符合，可在完成在线重定义之后进行修改。

（9）结束在线重定义

BEGIN

dbms_redefinition.finish_redef_table(uname => 'NMOSDB',

orig_table => 'TAD_TASKS_STEP',

int_table => 'TAD_TASKS_STEP_MID');

END;

（10）改回索引名

alter index NMOSDB.TAD_TASKS_STEP_INDEX1 rename to NMOSDB.TAD_TASKS_STEP_INDEX10;

alter index NMOSDB.TAD_TASKS_STEP_INDEX11 rename to NMOSDB.TAD_TASKS_STEP_INDEX1;

（11）收集统计信息

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'NMOSDB',tabname=>'TAD_TASKS_STEP',estimate_percent=>100,no_invalidate=>false,method_opt=>'for all columns size auto',cascade=>true,degree => 16);

（12）删除临时中间表

drop table NMOSDB.TAD_TASKS_STEP_MID purge;

4.回退步骤

EXEC dbms_redefinition.abort_redef_table('NMOSDB','TAD_TASKS_STEP','TAD_TASKS_STEP_MID');