Oracle 12.2使用对象数据类型来重定义表

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

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