因为需要将一张上亿我们要记录的表修改为分区表，所以尝试使用联机重定义来给表增加新列与分区的方法来实现，下面是一个测试的例子，操作系统是Oracle Linux 7.1,数据库为12.2.0.1,原始表为emp_redef，该表存储在hr方案中:
SQL>deschr.emp_redef
NameType NullableDefaultComments
------------- ------------ -------- ------- --------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20) Y
LAST_NAME VARCHAR2(25)
JOB_ID VARCHAR2(10)
DEPARTMENT_ID NUMBER(4) Y
表emp_redef将按以下规则来进行联机重定义:
.增加新列mgr,hiredate,sal与bonus
.新列bonus被初始化为0
.列department_id的值由10开始增加
.表将被重定义为范围分区表，分区键为employee_id。
联机重定义操作如下:
1.用要执行联机重定义操作的用户登录数据库
SQL> conn pm/pm@jypdb
Connected.
2.验证表emp_redef是否可以执行联机重定义。在这种情况下，可以使用主键或伪主键来来进行验证。
SQL>execdbms_redefinition.can_redef_table(uname=>'HR',tname=>'EMP_REDEF',options_flag=>dbms_redefinition.cons_use_pk);
PL/SQL proceduresuccessfully completed.
3.创建一个中间表hr.int_emp_redef
SQL>createtablehr.int_emp_redef
(
employee_id NUMBER(6) notnull,
first_name VARCHAR2(20),
last_name VARCHAR2(25) notnull,
job_id VARCHAR2(10) notnull,
department_id NUMBER(4) notnull,
mgr NUMBER(5),
hiredate DATEDEFAULT(sysdate),
sal NUMBER(7,2),
bonus NUMBER(7,2) DEFAULT(0)
)
partition byrange(employee_id)
(
partition emp200 valuesless than(200) tablespace users,
partition emp400 valuesless than(400) tablespace users
);
Tablecreated
4.开始重定义操作
SQL>begin
dbms_redefinition.start_redef_table(
uname => 'hr',
orig_table => 'emp_redef',
int_table => 'int_emp_redef',
col_mapping => 'employee_id employee_id, first_name first_name,last_name last_name, job_id job_id, department_id+10 department_id,0 bonus',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
end;
/
PL/SQL proceduresuccessfully completed.
5.复制依赖对象(自动对表hr.int_emp_redef创建任何触发器，索引，物化视图日志，授权与约束)
SQL>declare
num_errors pls_integer;
begin
dbms_redefinition.copy_table_dependents(
uname => 'hr',
orig_table => 'emp_redef',
int_table => 'int_emp_redef',
copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => TRUE,
num_errors => num_errors);
end;
/
PL/SQL proceduresuccessfully completed.
注意，在调用这个过程时ignore_errors参数需要设置为TRUE。原因是中间表创建了主键约束，并且当执行copye_table_dependents过程来试图从原始表复制主键约束与索引时会发生错误。可以忽略这些错误，但必须执行下一步操作中的查询来查看是否还存在其它错误。
6.查询dba_redefinition_errors视图来查看错误信息
SQL>setlong 8000
SQL> setpages 8000
SQL> columnobject_name heading'object name'format a20
SQL> columnbase_table_name heading'base table name'format a10
SQL> columnddl_txt heading'ddl that caused error'format a40
SQL> selectobject_name, base_table_name, ddl_txtfromdba_redefinition_errors;
object namebasetableddl that caused error
-------------------- ---------- ----------------------------------------
SYS_C0023200 EMP_REDEF ALTERTABLE"HR"."INT_EMP_REDEF"MODIFY
("LAST_NAME"CONSTRAINT"TMP$$_SYS_C0023
2000" NOTNULLENABLE NOVALIDATE)
SYS_C0023201 EMP_REDEF ALTERTABLE"HR"."INT_EMP_REDEF"MODIFY
("JOB_ID"CONSTRAINT"TMP$$_SYS_C0023201
0" NOTNULLENABLE NOVALIDATE)
2 rowsselected.
上面的错误信息是说中间表的last_name与job_id列为not null，而原因表为null，这种错误可以忽略。
7.同步中间表hr.int_emp_redef
SQL>begin
dbms_redefinition.sync_interim_table(
uname => 'hr',
orig_table => 'emp_redef',
int_table => 'int_emp_redef');
end;
/
PL/SQL proceduresuccessfully completed.
8.完成重定义操作
SQL>begin
dbms_redefinition.finish_redef_table(
uname => 'hr',
orig_table => 'emp_redef',
int_table => 'int_emp_redef');
end;
/
PL/SQL proceduresuccessfully completed.
表hr.emp_redef只会以排他模式被锁定很短的时间来结束重定义操作。在操作完成后，表hr.emp_redef将使用hr.int_emp_redef表的所有属性来重定义。
SQL>deschr.emp_redef
NameType NullableDefaultComments
------------- ------------ -------- --------- --------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20) Y
LAST_NAME VARCHAR2(25)
JOB_ID VARCHAR2(10)
DEPARTMENT_ID NUMBER(4)
MGR NUMBER(5) Y
HIREDATE DATEY (sysdate)
SAL NUMBER(7,2) Y
BONUS NUMBER(7,2) Y (0)
SQL> selectdbms_metadata.get_ddl(object_type =>'TABLE',name=>'EMP_REDEF',schema=>'HR')fromdual;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'EMP_REDEF',SCHEMA=>'HR')
--------------------------------------------------------------------------------
CREATETABLE"HR"."EMP_REDEF"
( "EMPLOYEE_ID"NUMBER(6,0)NOTNULLENABLE,
"FIRST_NAME"VARCHAR2(20),
"LAST_NAME"VARCHAR2(25)NOTNULLENABLE,
"JOB_ID"VARCHAR2(10)NOTNULLENABLE,
"DEPARTMENT_ID"NUMBER(4,0)NOTNULLENABLE,
"MGR"NUMBER(5,0),
"HIREDATE"DATEDEFAULT(sysdate),
"SAL"NUMBER(7,2),
"BONUS"NUMBER(7,2)DEFAULT(0),
CONSTRAINT"EMP_REDEF_EMP_ID_PK"PRIMARYKEY("EMPLOYEE_ID")
USING INDEXPCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTESTATISTICS
STORAGE(INITIAL 65536 NEXT1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
TABLESPACE "USERS"ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
TABLESPACE "USERS"
PARTITION BYRANGE ("EMPLOYEE_ID")
(PARTITION "EMP200"VALUESLESS THAN (200) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
TABLESPACE "USERS",
PARTITION "EMP400"VALUESLESS THAN (400) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 8388608 NEXT1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
TABLESPACE "USERS")
row selected.
可以看到表hr.emp_redef已经成功能联机重定义
9.等任何查询中间表的语句执行完成后将其删除，而且中间表在重定义后其结构就变成了原始表的表结构
SQL>deschr.int_emp_redef
NameType NullableDefaultComments
------------- ------------ -------- ------- --------
EMPLOYEE_ID NUMBER(6) Y
FIRST_NAME VARCHAR2(20) Y
LAST_NAME VARCHAR2(25)
JOB_ID VARCHAR2(10)
DEPARTMENT_ID NUMBER(4) Y
SQL> droptablehr.int_emp_redef purge;
Tabledropped
到此，联机重定义表hr.emp_redef就操作完成。
【编辑推荐】
【责任编辑：武晓燕 TEL：(010)68476606】
点赞 0
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
