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

联机重定义操作如下:

SQL> conn jy/jy@jypdbConnected.

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 alteredSQL> 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.customerName Type         Nullable Default Comments---- ------------ -------- ------- --------CID  NUMBERNAME VARCHAR2(30) YADDR ADDR_T       YSQL> drop table jy.customer purge;Table dropped

到此重定义操作就完成了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-2142200/，如需转载，请注明出处，否则将追究法律责任。