Oracle使用聯機重定義來給表增加新列與分區

作者：eric0435 2017-07-04 11:02:40

因為需要將一張上億我們要記錄的表修改為分區表，所以嘗試使用聯機重定義來給表增加新列與分區的方法來實現。

[[195729]]

因為需要將一張上億我們要記錄的表修改為分區表，所以嘗試使用聯機重定義來給表增加新列與分區的方法來實現，下面是一個測試的例子，操作系統是Oracle Linux 7.1,數據庫為12.2.0.1,原始表為emp_redef，該表存儲在hr方案中:

SQL> desc hr.emp_redef 
Name          Type         Nullable Default Comments 
------------- ------------ -------- ------- -------- 
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> exec dbms_redefinition.can_redef_table(uname=>'HR',tname=>'EMP_REDEF',options_flag=>dbms_redefinition.cons_use_pk); 
PL/SQL procedure successfully completed.

3.創建一個中間表hr.int_emp_redef

SQL> create table hr.int_emp_redef 
    ( 
      employee_id   NUMBER(6) not null, 
      first_name    VARCHAR2(20), 
      last_name     VARCHAR2(25) not null, 
      job_id        VARCHAR2(10) not null, 
      department_id NUMBER(4) not null, 
      mgr           NUMBER(5), 
      hiredate      DATE DEFAULT(sysdate), 
     sal           NUMBER(7,2), 
     bonus         NUMBER(7,2) DEFAULT(0) 
   ) 
   partition by range(employee_id) 
   ( 
   partition emp200 values less than(200) tablespace users, 
   partition emp400 values less than(400) tablespace users 
   ); 
Table created

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 procedure successfully 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 procedure successfully completed.

注意，在調用這個過程時ignore_errors參數需要設置為TRUE。原因是中間表創建了主鍵約束，并且當執行copye_table_dependents過程來試圖從原始表復制主鍵約束與索引時會發生錯誤。可以忽略這些錯誤，但必須執行下一步操作中的查詢來查看是否還存在其它錯誤。

6.查詢dba_redefinition_errors視圖來查看錯誤信息

SQL> set long 8000 
SQL> set pages 8000 
SQL> column object_name heading 'object name' format a20 
SQL> column base_table_name heading 'base table name' format a10 
SQL> column ddl_txt heading 'ddl that caused error' format a40 
SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors; 
 
object name          base table ddl that caused error 
-------------------- ---------- ---------------------------------------- 
SYS_C0023200         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY 
                                ("LAST_NAME" CONSTRAINT "TMP$$_SYS_C0023 
                                2000" NOT NULL ENABLE NOVALIDATE) 
 
SYS_C0023201         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY 
                                ("JOB_ID" CONSTRAINT "TMP$$_SYS_C0023201 
                                0" NOT NULL ENABLE NOVALIDATE) 
 
 
2 rows selected.

上面的錯誤信息是說中間表的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 procedure successfully completed.

8.完成重定義操作

SQL> begin 
  dbms_redefinition.finish_redef_table( 
     uname => 'hr', 
     orig_table => 'emp_redef', 
     int_table => 'int_emp_redef'); 
    end; 
    / 
PL/SQL procedure successfully completed.

表hr.emp_redef只會以排他模式被鎖定很短的時間來結束重定義操作。在操作完成后，表hr.emp_redef將使用hr.int_emp_redef表的所有屬性來重定義。

SQL> desc hr.emp_redef 
Name          Type         Nullable Default   Comments 
------------- ------------ -------- --------- -------- 
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      DATE         Y        (sysdate) 
SAL           NUMBER(7,2)  Y 
BONUS         NUMBER(7,2)  Y        (0) 
 
SQL> select dbms_metadata.get_ddl(object_type =>'TABLE',name =>'EMP_REDEF',schema => 'HR') from dual; 
 
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'EMP_REDEF',SCHEMA=>'HR') 
-------------------------------------------------------------------------------- 
 
  CREATE TABLE "HR"."EMP_REDEF" 
   (    "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE, 
        "FIRST_NAME" VARCHAR2(20), 
        "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE, 
        "JOB_ID" VARCHAR2(10) NOT NULL ENABLE, 
        "DEPARTMENT_ID" NUMBER(4,0) NOT NULL ENABLE, 
        "MGR" NUMBER(5,0), 
        "HIREDATE" DATE DEFAULT (sysdate), 
        "SAL" NUMBER(7,2), 
        "BONUS" NUMBER(7,2) DEFAULT (0), 
         CONSTRAINT "EMP_REDEF_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") 
  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 "USERS"  ENABLE 
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE( 
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "USERS" 
  PARTITION BY RANGE ("EMPLOYEE_ID") 
 (PARTITION "EMP200"  VALUES LESS THAN (200) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "USERS" , 
 PARTITION "EMP400"  VALUES LESS THAN (400) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
  TABLESPACE "USERS" )

row selected.

可以看到表hr.emp_redef已經成功能聯機重定義

9.等任何查詢中間表的語句執行完成后將其刪除，而且中間表在重定義后其結構就變成了原始表的表結構

SQL> desc hr.int_emp_redef 
Name          Type         Nullable Default Comments 
------------- ------------ -------- ------- -------- 
EMPLOYEE_ID   NUMBER(6)    Y 
FIRST_NAME    VARCHAR2(20) Y 
LAST_NAME     VARCHAR2(25) 
JOB_ID        VARCHAR2(10) 
DEPARTMENT_ID NUMBER(4)    Y 
 
SQL> drop table hr.int_emp_redef purge; 
Table dropped

到此，聯機重定義表hr.emp_redef就操作完成。

責任編輯：武曉燕來源： Linux社區

Oracle 新列分區