Oracle-在线迁移表到新表空间

文档中心

前言:

在Oracle线上系统中，我们经常会需要迁移表到新表空间或者通过move tablespace的方式去清理高水位等类似迁移场景，但通过move操作会导致锁表的发生，这在线上系统是不可接受的，在Oracle11g提供了在线重定义的方式，通过dbms_redefinition进行在线表迁移，避免了长时间锁表的发生、影响业务的连续性。

在线迁移场景:

测试通过在线重定义将表t1从表空间users迁移到newtbs。

在线迁移步骤:

在线迁移实施步骤：

1 检查一下t1表能否进行重定义，如果执行成功，则表示可以，否则将会报错显示错误原因

注意:在线重定义需要表有主键，没有主键无法执行在线重定义，会报ORA-12089: cannot online redefine table ""."" with no primary key

exec dbms_redefinition.can_redef_table('TEST','T1');---成功执行代表符合PL/SQL procedure successfully completed.

2 获取原始表的ddl

---获取原始表的ddl语句，索引，约束，权限set pagesize 5000set long 50000set longc 50000select dbms_metadata.get_ddl('TABLE','T1','TEST')   from dualunion allselect dbms_metadata.get_dependent_ddl('CONSTRAINT','T1','TEST')  from dualunion allselect dbms_metadata.get_dependent_ddl('INDEX','T1','TEST')   from dualunion allselect dbms_metadata.get_dependent_ddl('OBJECT_GRANT','T1','TEST')  from dual;---执行结果  CREATE TABLE "TEST"."T1"   (    "ID" NUMBER(10,0), "CREATE_DATE" DATE, "NAME" VARCHAR2(100), "NAM1" CLOB,  PRIMARY KEY ("ID")  USING INDEX 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 "USERS"  ENABLE   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  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 "USERS" LOB ("NAM1") STORE AS SECUREFILE (  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));  ALTER TABLE "TEST"."T1" ADD PRIMARY KEY ("ID")  USING INDEX 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 "USERS"  ENABLE;  CREATE UNIQUE INDEX "TEST"."SYS_C007597" ON "TEST"."T1" ("ID")  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 "USERS"  CREATE UNIQUE INDEX "TEST"."SYS_IL0000074011C00004$$" ON "TEST"."T1" (  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 "USERS"  PARALLEL (DEGREE 0 INSTANCES 0)  CREATE INDEX "TEST"."IND_T1" ON "TEST"."T1" ("CREATE_DATE")  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  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 "USERS";  GRANT SELECT ON "TEST"."T1" TO "TEST_1";

3 将获取语句中的表名t1，替换为T1_NEW,表空间users，替换为newtbs

---主键约束可以不用手动添加，因为创建表语句里面已经包含---对于SYS_IL..$$的lob字段索引也可以不用手动添加，因为创建表语句里面已经包含---对于索引，如果表数据太大，可以选择全量数据同步之后再创建索引  CREATE TABLE "TEST"."T1_NEW"   (    "ID" NUMBER(10,0), "CREATE_DATE" DATE, "NAME" VARCHAR2(100), "NAM1" CLOB,  PRIMARY KEY ("ID")  USING INDEX 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 "NEWTBS"  ENABLE   ) SEGMENT CREATION IMMEDIATE  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  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 "NEWTBS" LOB ("NAM1") STORE AS SECUREFILE (  TABLESPACE "NEWTBS" ENABLE STORAGE IN ROW CHUNK 8192  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES  STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));    CREATE INDEX "TEST"."IND_T1_NEW" ON "TEST"."T1_NEW" ("CREATE_DATE")  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  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 "NEWTBS";  GRANT SELECT ON "TEST"."T1_NEW" TO "TEST_1";

4 开始全量数据初始化，此操作期间对于要迁移的数据，如果存在行上锁还未释放，操作会产生产生等待TX 6锁

SQL> BEGINDBMS_REDEFINITION.start_redef_table(uname => 'TEST', orig_table => 'T1',int_table => 'T1_NEW');END;/

5 手动触发增量数据到新表，避免增量数据差异过大，即使finish_redef_table也会再一次同步数据(可选)

SQL> BEGINdbms_redefinition.sync_interim_table(uname => 'TEST', orig_table => 'T1',int_table => 'T1_NEW');END;/

6 查看重定义的状态

---可以通过以下视图查看在线重定义表的状态select * from DBA_REDEFINITION_ERRORS;select * from DBA_REDEFINITION_OBJECTS;select * from DBA_REDEFINITION_STATUS;主要关注当前的操作，以及操作状态，如果当前没有正在进行的redefiniton操作，则视图都为空

7 进行表切换，此操作期间需要短暂获取TM 6锁(正常应用无感知)，以完成表切换

SQL> BEGINdbms_redefinition.finish_redef_table(uname => 'TEST',orig_table => 'T1',int_table => 'T1_NEW');END;/

8 确认T1是否已经迁移到新的表空间newtbs

select owner,table_name,tablespace_namefrom dba_tableswhere owner='TEST' and table_name='T1';select owner,index_name,tablespace_namefrom dba_indexeswhere owner='TEST' and table_name='T1';select segment_name,table_name,tablespace_namefrom dba_lobswhere owner='TEST' and table_name='T1'; select grantee,owner,table_name from dba_tab_privs where table_name='T1';OWNER     TABLE_NAME  TABLESPACE_NAME---------- ------------------------------ ------------------------------------------------------------TEST     T1   NEWTBSOWNER     INDEX_NAME    TABLESPACE_NAME---------- ----------------------------------- ------------------------------------------------------------TEST     SYS_IL0000074174C00004$$      NEWTBSTEST     SYS_C007598     NEWTBSTEST     IND_T1_NEW     NEWTBSSEGMENT_NAME TABLE_NAME    TABLESPACE_NAME------------------------------ ------------ ------------------------------------------------------------SYS_LOB0000074174C00004$$  T1NEWTBSGRANTEE     OWNER    TABLE_NAME---------------- ---------- ----------------------------------------TEST_1      TEST     T1

9 查看表依赖对象是否无效

---如果无效需要手动重新编译select a.owner,a.name,a.type,a.REFERENCED_OWNER,a.REFERENCED_NAME,b.status  from dba_DEPENDENCIES a,dba_objects b where a.REFERENCED_OWNER='TEST' and a.REFERENCED_NAME='T1' and a.NAME=b.object_name;OWNER     NAMETYPE    REFERENCED_OWNER    REFERENCED STATUS---------- ---------- ------------------------------ -------------------- ---------- --------------TEST     P1_T1      PROCEDURE   TEST      T1VALID

10 执行统计信息

exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TEST',tabname=>'T1',ESTIMATE_PERCENT=>20,method_opt=>'for all columns size 1',cascade=>true,force=>true,degree=>2,no_invalidate=>FALSE);

11 验证数据

SQL> select count(*) from test.t1 where id  select count(*) from test.t1_new where id < 3002822;  COUNT(*)----------    302821

冰雪之城

Oracle-在线迁移表到新表空间

公告

标签

Oracle-在线迁移表到新表空间

相关问题

公告

标签