Oracle 12.2使用对象数据类型来重定义表,下面的例子将重定义表将列改变为对象属性，原始表为customer,定义如下:
SQL> create table jy.customer( 2 cid number not null, 3 name varchar2(30), 4 street varchar2(100), 5 city varchar2(30), 6 state varchar2(2), 7 zip number(5) 8 ); Table created SQL> alter table jy.customer add constraint customer_pk primary key(cid); Table altered
创建新的对象类型
SQL> create type jy.addr_t as object 2 ( 3 street varchar2(100), 4 city varchar2(30), 5 state varchar2(2), 6 zip number(5,0) 7 ); 8 9 / Type created
联机重定义操作如下:
1.用要执行联机重定义操作的用户登录数据库
SQL> conn jy/jy@jypdb Connected.
2.验证表是否可以执行联机重定义,可以使用主键或伪主键来执行验证操作。
SQL> begin 2 dbms_redefinition.can_redef_table( 3 uname => 'jy', 4 tname =>'customer', 5 options_flag => DBMS_REDEFINITION.CONS_USE_PK); 6 end; 7 / PL/SQL procedure successfully completed
3.创建中间表jy.int_customer
SQL> create table jy.int_customer 2 ( 3 cid number, 4 name varchar2(30), 5 addr addr_t 6 ); Table created
4.因为customer是一个非常大的表，为了让下一步操作启用并行执行以下语句:
SQL> alter session force parallel dml parallel 4; Session altered SQL> alter session force parallel query parallel 4; Session altered
5.使用主键来执行重定义操作
SQL> begin 2 dbms_redefinition.start_redef_table( 3 uname => 'jy', 4 orig_table => 'customer', 5 int_table => 'int_customer', 6 col_mapping => 'cid cid, name name, 7 addr_t(street, city, state, zip) addr'); 8 end; 9 / PL/SQL procedure successfully completed
6.复制依赖对象
SQL> declare 2 num_errors pls_integer; 3 begin 4 dbms_redefinition.copy_table_dependents( 5 uname => 'jy', 6 orig_table => 'customer', 7 int_table => 'int_customer', 8 copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 9 copy_triggers => TRUE, 10 copy_constraints => TRUE, 11 copy_privileges => TRUE, 12 ignore_errors => FALSE, 13 num_errors => num_errors, 14 copy_statistics => TRUE); 15 end; 16 / PL/SQL procedure successfully completed
7.可选操作同步中间表
SQL> begin 2 dbms_redefinition.sync_interim_table( 3 uname => 'jy', 4 orig_table => 'customer', 5 int_table => 'int_customer'); 6 end; 7 / PL/SQL procedure successfully completed
8.完成重定义操作
SQL> begin 2 dbms_redefinition.finish_redef_table( 3 uname => 'jy', 4 orig_table => 'customer', 5 int_table => 'int_customer'); 6 end; 7 / PL/SQL procedure successfully completed
SQL> select dbms_metadata.get_ddl(object_type =>'TABLE',name =>'CUSTOMER',schema => 'JY') from dual;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'CUSTOMER',SCHEMA=>'JY')
--------------------------------------------------------------------------------
CREATE TABLE "JY"."CUSTOMER"
( "CID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(30),
"ADDR" "JY"."ADDR_T" ,
CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CID")
USING INDEX 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 "TEST" 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 "TEST"
1 row selected.
可以看到表jy.customer已经成功能联机重定义
9.等待任何查询中间表的语句执行完成后将其删除
SQL> desc jy.customer Name Type Nullable Default Comments ---- ------------ -------- ------- -------- CID NUMBER NAME VARCHAR2(30) Y ADDR ADDR_T Y SQL> drop table jy.customer purge; Table dropped
到此重定义操作就完成了。
