建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網營銷解決方案
Oracle使用聯(lián)機重定義來給表增加新列與分區(qū)
因為需要將一張上億我們要記錄的表修改為分區(qū)表，所以嘗試使用聯(lián)機重定義來給表增加新列與分區(qū)的方法來實現(xiàn)，下面是一個測試的例子，操作系統(tǒng)是Oracle Linux 7.1,數(shù)據(jù)庫為12.2.0.1,原始表為emp_redef，該表存儲在hr方案中:
創(chuàng)新互聯(lián)建站專注于通江企業(yè)網站建設,響應式網站,商城系統(tǒng)網站開發(fā)。通江網站建設公司,為通江等地區(qū)提供建站服務。全流程按需規(guī)劃網站，專業(yè)設計，全程項目跟蹤，創(chuàng)新互聯(lián)建站專業(yè)和態(tài)度為您提供的服務
- 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將按以下規(guī)則來進行聯(lián)機重定義:
.增加新列mgr,hiredate,sal與bonus
.新列bonus被初始化為0
.列department_id的值由10開始增加
.表將被重定義為范圍分區(qū)表，分區(qū)鍵為employee_id。
聯(lián)機重定義操作如下:
1.用要執(zhí)行聯(lián)機重定義操作的用戶登錄數(shù)據(jù)庫
- SQL> conn pm/pm@jypdb
- Connected.
2.驗證表emp_redef是否可以執(zhí)行聯(lián)機重定義。在這種情況下，可以使用主鍵或偽主鍵來來進行驗證。
- 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.創(chuàng)建一個中間表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創(chuàng)建任何觸發(fā)器，索引，物化視圖日志，授權與約束)
- 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參數(shù)需要設置為TRUE。原因是中間表創(chuàng)建了主鍵約束，并且當執(zhí)行copye_table_dependents過程來試圖從原始表復制主鍵約束與索引時會發(fā)生錯誤?？梢院雎赃@些錯誤，但必須執(zhí)行下一步操作中的查詢來查看是否還存在其它錯誤。
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已經成功能聯(lián)機重定義
9.等任何查詢中間表的語句執(zhí)行完成后將其刪除，而且中間表在重定義后其結構就變成了原始表的表結構
- 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
到此，聯(lián)機重定義表hr.emp_redef就操作完成。
網頁題目：Oracle使用聯(lián)機重定義來給表增加新列與分區(qū)
標題URL：http://www.5511xx.com/article/dpgcsgi.html
