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

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