Oracle使用聯機重定義來給表增加新列與分區
作者：eric0435
因為需要將一張上億我們要記錄的表修改為分區表，所以嘗試使用聯機重定義來給表增加新列與分區的方法來實現。
因為需要將一張上億我們要記錄的表修改為分區表，所以嘗試使用聯機重定義來給表增加新列與分區的方法來實現，下面是一個測試的例子，操作系統是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社區
