Oracle使用联机重定义来给表增加新列与分区

因为需要将一张上亿我们要记录的表修改为分区表，所以尝试使用联机重定义来给表增加新列与分区的方法来实现，下面是一个测试的例子，操作系统是Oracle Linux 7.1,数据库为12.2.0.1,原始表为emp_redef，该表存储在hr方案中:

创新互联建站专注于通江企业网站建设,响应式网站,商城系统网站开发。通江网站建设公司,为通江等地区提供建站服务。全流程按需规划网站，专业设计，全程项目跟踪，创新互联建站专业和态度为您提供的服务

 
 
 
    
  
  
  
  1. SQL> desc hr.emp_redef 
    2.   
  
  
  2. Name          Type         Nullable Default Comments 
    3.   
  
  
  3. ------------- ------------ -------- ------- -------- 
    4.   
  
  
  4. EMPLOYEE_ID   NUMBER(6) 
    5.   
  
  
  5. FIRST_NAME    VARCHAR2(20) Y 
    6.   
  
  
  6. LAST_NAME     VARCHAR2(25) 
    7.   
  
  
  7. JOB_ID        VARCHAR2(10) 
    8.   
  
  
  8. DEPARTMENT_ID NUMBER(4)    Y 
    9.

表emp_redef将按以下规则来进行联机重定义:

.增加新列mgr,hiredate,sal与bonus

.新列bonus被初始化为0

.列department_id的值由10开始增加

.表将被重定义为范围分区表，分区键为employee_id。

联机重定义操作如下:

1.用要执行联机重定义操作的用户登录数据库

 
 
 
    
  
  
  
  1. SQL> conn pm/pm@jypdb 
    2.   
  
  
  2. Connected. 
    3.

2.验证表emp_redef是否可以执行联机重定义。在这种情况下，可以使用主键或伪主键来来进行验证。

 
 
 
    
  
  
  
  1. SQL> exec dbms_redefinition.can_redef_table(uname=>'HR',tname=>'EMP_REDEF',options_flag=>dbms_redefinition.cons_use_pk); 
    2.   
  
  
  2. PL/SQL procedure successfully completed. 
    3.

3.创建一个中间表hr.int_emp_redef

 
 
 
    
  
  
  
  1. SQL> create table hr.int_emp_redef 
    2.   
  
  
  2.     ( 
    3.   
  
  
  3.       employee_id   NUMBER(6) not null, 
    4.   
  
  
  4.       first_name    VARCHAR2(20), 
    5.   
  
  
  5.       last_name     VARCHAR2(25) not null, 
    6.   
  
  
  6.       job_id        VARCHAR2(10) not null, 
    7.   
  
  
  7.       department_id NUMBER(4) not null, 
    8.   
  
  
  8.       mgr           NUMBER(5), 
    9.   
  
  
  9.       hiredate      DATE DEFAULT(sysdate), 
    10.   
  
  
  10.      sal           NUMBER(7,2), 
    11.   
  
  
  11.      bonus         NUMBER(7,2) DEFAULT(0) 
    12.   
  
  
  12.    ) 
    13.   
  
  
  13.    partition by range(employee_id) 
    14.   
  
  
  14.    ( 
    15.   
  
  
  15.    partition emp200 values less than(200) tablespace users, 
    16.   
  
  
  16.    partition emp400 values less than(400) tablespace users 
    17.   
  
  
  17.    ); 
    18.   
  
  
  18. Table created 
    19.

4.开始重定义操作

 
 
 
    
  
  
  
  1. SQL> begin 
    2.   
  
  
  2.   dbms_redefinition.start_redef_table( 
    3.   
  
  
  3.   uname => 'hr', 
    4.   
  
  
  4.   orig_table => 'emp_redef', 
    5.   
  
  
  5.   int_table => 'int_emp_redef', 
    6.   
  
  
  6.   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', 
    7.   
  
  
  7.   options_flag => DBMS_REDEFINITION.CONS_USE_PK); 
    8.   
  
  
  8.   end; 
    9.   
  
  
  9.   / 
    10.   
  
  
  10. PL/SQL procedure successfully completed. 
    11.

5.复制依赖对象(自动对表hr.int_emp_redef创建任何触发器，索引，物化视图日志，授权与约束)

 
 
 
    
  
  
  
  1. SQL> declare 
    2.   
  
  
  2.     num_errors pls_integer; 
    3.   
  
  
  3.     begin 
    4.   
  
  
  4.     dbms_redefinition.copy_table_dependents( 
    5.   
  
  
  5.       uname => 'hr', 
    6.   
  
  
  6.       orig_table => 'emp_redef', 
    7.   
  
  
  7.       int_table => 'int_emp_redef', 
    8.   
  
  
  8.       copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS, 
    9.   
  
  
  9.       copy_triggers => TRUE, 
    10.   
  
  
  10.      copy_constraints => TRUE, 
    11.   
  
  
  11.      copy_privileges => TRUE, 
    12.   
  
  
  12.      ignore_errors => TRUE, 
    13.   
  
  
  13.      num_errors => num_errors); 
    14.   
  
  
  14.    end; 
    15.   
  
  
  15.    / 
    16.   
  
  
  16.  
    17.   
  
  
  17. PL/SQL procedure successfully completed. 
    18.

注意，在调用这个过程时ignore_errors参数需要设置为TRUE。原因是中间表创建了主键约束，并且当执行copye_table_dependents过程来试图从原始表复制主键约束与索引时会发生错误。可以忽略这些错误，但必须执行下一步操作中的查询来查看是否还存在其它错误。

6.查询dba_redefinition_errors视图来查看错误信息

 
 
 
    
  
  
  
  1. SQL> set long 8000 
    2.   
  
  
  2. SQL> set pages 8000 
    3.   
  
  
  3. SQL> column object_name heading 'object name' format a20 
    4.   
  
  
  4. SQL> column base_table_name heading 'base table name' format a10 
    5.   
  
  
  5. SQL> column ddl_txt heading 'ddl that caused error' format a40 
    6.   
  
  
  6. SQL> select object_name, base_table_name, ddl_txt from dba_redefinition_errors; 
    7.   
  
  
  7.  
    8.   
  
  
  8. object name          base table ddl that caused error 
    9.   
  
  
  9. -------------------- ---------- ---------------------------------------- 
    10.   
  
  
  10. SYS_C0023200         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY 
    11.   
  
  
  11.                                 ("LAST_NAME" CONSTRAINT "TMP$$_SYS_C0023 
    12.   
  
  
  12.                                 2000" NOT NULL ENABLE NOVALIDATE) 
    13.   
  
  
  13.  
    14.   
  
  
  14. SYS_C0023201         EMP_REDEF  ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY 
    15.   
  
  
  15.                                 ("JOB_ID" CONSTRAINT "TMP$$_SYS_C0023201 
    16.   
  
  
  16.                                 0" NOT NULL ENABLE NOVALIDATE) 
    17.   
  
  
  17.  
    18.   
  
  
  18.  
    19.   
  
  
  19. 2 rows selected. 
    20.

上面的错误信息是说中间表的last_name与job_id列为not null，而原因表为null，这种错误可以忽略。

7.同步中间表hr.int_emp_redef

 
 
 
    
  
  
  
  1. SQL> begin 
    2.   
  
  
  2.     dbms_redefinition.sync_interim_table( 
    3.   
  
  
  3.       uname => 'hr', 
    4.   
  
  
  4.       orig_table => 'emp_redef', 
    5.   
  
  
  5.       int_table => 'int_emp_redef'); 
    6.   
  
  
  6.     end; 
    7.   
  
  
  7.     / 
    8.   
  
  
  8. PL/SQL procedure successfully completed. 
    9.

8.完成重定义操作

 
 
 
    
  
  
  
  1. SQL> begin 
    2.   
  
  
  2.   dbms_redefinition.finish_redef_table( 
    3.   
  
  
  3.      uname => 'hr', 
    4.   
  
  
  4.      orig_table => 'emp_redef', 
    5.   
  
  
  5.      int_table => 'int_emp_redef'); 
    6.   
  
  
  6.     end; 
    7.   
  
  
  7.     / 
    8.   
  
  
  8. PL/SQL procedure successfully completed. 
    9.

表hr.emp_redef只会以排他模式被锁定很短的时间来结束重定义操作。在操作完成后，表hr.emp_redef将使用hr.int_emp_redef表的所有属性来重定义。

 
 
 
    
  
  
  
  1. SQL> desc hr.emp_redef 
    2.   
  
  
  2. Name          Type         Nullable Default   Comments 
    3.   
  
  
  3. ------------- ------------ -------- --------- -------- 
    4.   
  
  
  4. EMPLOYEE_ID   NUMBER(6) 
    5.   
  
  
  5. FIRST_NAME    VARCHAR2(20) Y 
    6.   
  
  
  6. LAST_NAME     VARCHAR2(25) 
    7.   
  
  
  7. JOB_ID        VARCHAR2(10) 
    8.   
  
  
  8. DEPARTMENT_ID NUMBER(4) 
    9.   
  
  
  9. MGR           NUMBER(5)    Y 
    10.   
  
  
  10. HIREDATE      DATE         Y        (sysdate) 
    11.   
  
  
  11. SAL           NUMBER(7,2)  Y 
    12.   
  
  
  12. BONUS         NUMBER(7,2)  Y        (0) 
    13.   
  
  
  13.  
    14.   
  
  
  14. SQL> select dbms_metadata.get_ddl(object_type =>'TABLE',name =>'EMP_REDEF',schema => 'HR') from dual; 
    15.   
  
  
  15.  
    16.   
  
  
  16. DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'TABLE',NAME=>'EMP_REDEF',SCHEMA=>'HR') 
    17.   
  
  
  17. -------------------------------------------------------------------------------- 
    18.   
  
  
  18.  
    19.   
  
  
  19.   CREATE TABLE "HR"."EMP_REDEF" 
    20.   
  
  
  20.    (    "EMPLOYEE_ID" NUMBER(6,0) NOT NULL ENABLE, 
    21.   
  
  
  21.         "FIRST_NAME" VARCHAR2(20), 
    22.   
  
  
  22.         "LAST_NAME" VARCHAR2(25) NOT NULL ENABLE, 
    23.   
  
  
  23.         "JOB_ID" VARCHAR2(10) NOT NULL ENABLE, 
    24.   
  
  
  24.         "DEPARTMENT_ID" NUMBER(4,0) NOT NULL ENABLE, 
    25.   
  
  
  25.         "MGR" NUMBER(5,0), 
    26.   
  
  
  26.         "HIREDATE" DATE DEFAULT (sysdate), 
    27.   
  
  
  27.         "SAL" NUMBER(7,2), 
    28.   
  
  
  28.         "BONUS" NUMBER(7,2) DEFAULT (0), 
    29.   
  
  
  29.          CONSTRAINT "EMP_REDEF_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID") 
    30.   
  
  
  30.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    31.   
  
  
  31.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
    32.   
  
  
  32.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
    33.   
  
  
  33.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
    34.   
  
  
  34.   TABLESPACE "USERS"  ENABLE 
    35.   
  
  
  35.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
    36.   
  
  
  36.   STORAGE( 
    37.   
  
  
  37.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
    38.   
  
  
  38.   TABLESPACE "USERS" 
    39.   
  
  
  39.   PARTITION BY RANGE ("EMPLOYEE_ID") 
    40.   
  
  
  40.  (PARTITION "EMP200"  VALUES LESS THAN (200) SEGMENT CREATION IMMEDIATE 
    41.   
  
  
  41.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
    42.   
  
  
  42.  NOCOMPRESS LOGGING 
    43.   
  
  
  43.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
    44.   
  
  
  44.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
    45.   
  
  
  45.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
    46.   
  
  
  46.   TABLESPACE "USERS" , 
    47.   
  
  
  47.  PARTITION "EMP400"  VALUES LESS THAN (400) SEGMENT CREATION IMMEDIATE 
    48.   
  
  
  48.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
    49.   
  
  
  49.  NOCOMPRESS LOGGING 
    50.   
  
  
  50.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 
    51.   
  
  
  51.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 
    52.   
  
  
  52.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 
    53.   
  
  
  53.   TABLESPACE "USERS" ) 
    54.  
 
 
 
 
 
    
  
  
  
  1. row selected. 
    2.

可以看到表hr.emp_redef已经成功能联机重定义

9.等任何查询中间表的语句执行完成后将其删除，而且中间表在重定义后其结构就变成了原始表的表结构

 
 
 
    
  
  
  
  1. SQL> desc hr.int_emp_redef 
    2.   
  
  
  2. Name          Type         Nullable Default Comments 
    3.   
  
  
  3. ------------- ------------ -------- ------- -------- 
    4.   
  
  
  4. EMPLOYEE_ID   NUMBER(6)    Y 
    5.   
  
  
  5. FIRST_NAME    VARCHAR2(20) Y 
    6.   
  
  
  6. LAST_NAME     VARCHAR2(25) 
    7.   
  
  
  7. JOB_ID        VARCHAR2(10) 
    8.   
  
  
  8. DEPARTMENT_ID NUMBER(4)    Y 
    9.   
  
  
  9.  
    10.   
  
  
  10. SQL> drop table hr.int_emp_redef purge; 
    11.   
  
  
  11. Table dropped 
    12.

到此，联机重定义表hr.emp_redef就操作完成。

当前标题：Oracle使用联机重定义来给表增加新列与分区
文章来源：http://www.shufengxianlan.com/qtweb/news27/79277.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

用户体验知识

同城分类信息

雨棚定制　　 　成都酒店服定制　　 　联通机柜租用　　 　成都模板网站　　 　四川成都网站制作　　 　证企宝企业服务　　 　上海除甲醛　　 　成都封阳台　　 　三台网站建设　　 　云南护栏打桩机　　 　外贸网站设计方案　　 　企业网站建设　　 　公司宣传册设计　　 　成都展柜制作　　 　主机租用　　 　企业网站设计　　 　免备案空间　　 　微信小程序公众号开发　　 　自贡网站建设　　 　企业网站制作