ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【TABLE】oracle表在线重定义注意事项

【TABLE】oracle表在线重定义注意事项

翻译 Oracle 作者：xysoul_云龙 时间：2017-06-10 11:50:11 0 删除 编辑

点击(此处)折叠或打开

  1. DBMS_REDEFINITION
  2. ==================

  4. With DBMS_REDEFINITION, you can perform an online reorganization of tables. To
  5. achieve this online reorganization, incrementally maintainable local
  6. materialized views are used. Snapshot logs need to be defined on the master
  7. tables to support incrementally maintainable materialized views. These logs
  8. keep track of the changes to the master tables and are used by the materialized
  9. views during refresh synchronization. To keep table indexes and privileges you must use the
  10. copy_table_dependents procedure.

此处需要注意，如果想改变表的索引、权限，必须执行过程DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS



下面是无法使用在线重定义的一些情况：


点击(此处)折叠或打开

  1. Tables with the following characteristics cannot be redefined online:
  2.   - [9.0.1]Tables with no primary keys
  3.   - Tables that have materialized view logs defined on them
  4.   - [9i] Tables that are materialized view container tables and AQ tables
  5.   - [10g] Tables that are replicated in an n-way master configuration can
  6.     be redefined, but horizontal subsetting (subset of rows in the table),
  7.     vertical subsetting (subset of columns in the table), and column
  8.     transformations are not allowed.
  9.   - The overflow table of an IOT table
  10.   - Tables with fine-grained access control (row-level security)
  11.   - Tables with BFILE columns
  12.   - Tables with LONG columns can be redefined online, but those columns must be --注意LOB相关表,LONG类型必须转换为CLOBS. LONG RAW=>BLOBS
  13.     converted to CLOBS. Also, LONG RAW columns must be converted to BLOBS.
  14.     Tables with LOB columns are acceptable.
  15.   - Tables in the SYS and SYSTEM schema
  16.   - Temporary tables


其他的一些约束：


点击(此处)折叠或打开

  1. Other restrictions:
  2.   - A subset of rows in the table
  3.   - Only simple deterministic expressions, sequences, and SYSDATE can be used
  4.     when mapping the columns in the interim table to those of the original table.
  5.     For example, subqueries are not allowed.
  6.   - If new columns are being added with no column mappings, then they must not
  7.     be declared NOT NULL until the redefinition is complete.
  8.   - There cannot be any referential constraints between the table being redefined
  9.     and the interim table.
  10.   - Table redefinition cannot be done NOLOGGING.
  11.   - [10g] For materialized view logs and queue tables, online redefinition is
  12.     restricted to changes in physical properties.
  13.   - You cannot convert a nested table to a VARRAY.

用户所需权限


点击(此处)折叠或打开

  1. Privileges Required
  2. ====================
  3. Following privileges are needed to run this package:
  4.   - Execute privilege to DBMS_REDEFINITION
  5.   - Create any table
  6.   - Alter any table
  7.   - Drop any table
  8.   - Lock any table
  9.   - Select any table
  10.   - Create any index
  11.   - Create any trigger

下面是相关包的说明


点击(此处)折叠或打开

  1. Summary of DBMS_REDEFINITION Subprograms:
  2. =========================================

  4. CAN_REDEF_TABLE Procedure:
  5. --------------------------
  6. This procedure determines if a given table can be reorganized online. This is
  7. the first step of the online reorganization process. If the table is not a
  8. candidate for online redefinition, an error message is raised.

  10. SYNTAX

  12. DBMS_REDEFINITION.can_redef_table (
  13. uname IN VARCHAR2,
  14. tname IN VARCHAR2);

  16. CAN_REDEF_TABLE Procedure Parameters:

  18. Parameter Description
  19. --------- ------------
  20. uname The schema name of the table.
  21. tname The name of the table to be reorganized.



  25. START_REDEF_TABLE Procedure:
  26. ----------------------------
  27. This procedure initiates the reorganization process. After verifying that the
  28. table can be reorganized online, you create an empty interim table (in the same
  29. schema as the table to be reorganized) with the desired attributes of the
  30. post-reorganization table.

  32. SYNTAX

  34. DBMS_REDEFINITION.start_redef_table (
  35. uname IN VARCHAR2,
  36. orig_table IN VARCHAR2,
  37. int_table IN VARCHAR2,
  38. col_mapping IN VARCHAR2 := NULL);

  40. START_REDEF_TABLE Procedure Parameters:

  42. Parameter Description
  43. ---------- ------------
  44. uname The schema name of the tables.
  45. orig_table The name of the table to be reorganized.
  46. int_table The name of the interim table.
  47. col_mapping The mapping information from the columns in the interim
  48.                    table to the columns in the original table. (This is similar
  49.                    to the column list on the SELECT clause of a query.) If NULL,
  50.                    all the columns in the original table are selected and have
  51.                    the same name after reorganization.

  53. FINISH_REDEF_TABLE Procedure:
  54. ----------------------------
  55. This procedure completes the reorganization process. Before this step you can
  56. create new indexes, triggers, grants, and constraints on the interim table. The
  57. referential constraints involving the interim table must be disabled. After
  58. completing this step, the original table is locked briefly during this
  59. procedure.

  61.  
  62. SYNTAX

  64. DBMS_REDEFINITION.finish_redef_table (
  65. uname IN VARCHAR2,
  66. orig_table IN VARCHAR2,
  67. int_table IN VARCHAR2);

  69. FINISH_REDEF_TABLE Procedure Parameters:

  71. Parameter Description
  72. --------- ------------
  73. uname The schema name of the tables.
  74. orig_table The name of the table to be reorganized.
  75. int_table The name of the interim table.



  79. SYNC_INTERIM_TABLE Procedure:
  80. ----------------------------
  81. This procedure keeps the interim table synchronized with the original table.
  82. This step is useful in minimizing the amount of synchronization needed to be
  83. done by finish_reorg_table before completing the online reorganization. This
  84. procedure can be called between long running operations (such as create index)
  85. on the interim table to sync it up with the data in the original table and
  86. speed up subsequent operations.

  88. SYNTAX

  90. DBMS_REDEFINITION.sync_interim_table (
  91. uname IN VARCHAR2,
  92. orig_table IN VARCHAR2,
  93. int_table IN VARCHAR2);

  95. SYNC_INTERIM_TABLE Procedure Parameters:

  97. Parameters Description
  98. ---------- ------------
  99. uname The schema name of the tables.
  100. orig_table The name of the table to be reorganized.
  101. int_table The name of the interim table.


  104. ABORT_REDEF_TABLE Procedure:
  105. ----------------------------
  106. This procedure cleans up errors that occur during the reorganization process.
  107. This procedure can also be used to abort the reorganization process any time
  108. after start_reorg_table has been called and before finish_reorg_table is called.

  110. SYNTAX

  112. DBMS_REDEFINITION.abort_redef_table (
  113. uname IN VARCHAR2,
  114. orig_table IN VARCHAR2,
  115. int_table IN VARCHAR2);

  117. ABORT_REDEF_TABLE Procedure Parameters:

  119. Parameters Description
  120. ---------- ------------
  121. uname The schema name of the table.
  122. orig_table The name of the table to be reorganized.
  123. int_table The name of the interim table.


  126. If we have an encrypted column which is part of primary key then
  127. follow the steps mentioned in the bug below.

  129. Reference: Bug 6034260 DBMS_REDEFINITION.START_REDEF_TABLE FAILS ORA-32412 BECAUSE
  130. OF ENCRYPTED COLUMN

  132. COPY_TABLE_DEPENDENTS (Procedure)
  133. Copies the dependent objects of the original table to the interim table


  136. COPY_TABLE_DEPENDENTS.dbms_redefinition.copy_table_dependents(
  137. uname IN VARCHAR2,
  138. orig_table IN VARCHAR2,
  139. int_table IN VARCHAR2,
  140. copy_indexes IN PLS_INTEGER := 1,
  141. copy_triggers IN BOOLEAN := TRUE,
  142. copy_constraints IN BOOLEAN := TRUE,
  143. copy_privileges IN BOOLEAN := TRUE,
  144. ignore_errors IN BOOLEAN := FALSE,
  145. num_errors OUT PLS_INTEGER,
  146. copy_statistics IN BOOLEAN := FALSE
  147. copy_mvlog IN BOOLEAN := FALSE);

  149. RELATED DOCUMENTS
  150. -----------------

  152. Oracle9i Database Administrator


可以参考相关文章：
NOTE:1089860.1 - Why are Not Null Constraints not Copied by Dbms_redefinition.copy_table_dependents?
NOTE:807004.1 - How to Disassemble (Uncluster / Decluster) Clustered Tables Online
NOTE:1116785.1 - ORA-1442 Error During Online Redefinition - DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
NOTE:1170351.1 - Secure file Migration and Accessing securefile metadata information [Video]
NOTE:1233204.1 - Cannot Drop Unused Columns Using dbms_redefinition Without PK And Compressed Table
NOTE:251417.1 - How to Convert LONG Column to CLOB Using DBMS_REDEFINITION Package
NOTE:837751.1 - ORA-904 Error During Online Redefinition - DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
NOTE:848298.1 - How To Encrypt/Rekey a Table for TDE and Avoid the Associated Performance Problems and Locks?
NOTE:556283.1 - How to Convert Date Column to Timestamp Using DBMS_REDEFINITION Package?

分区表参考
How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29487349/viewspace-2140567/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【RMAN】RMAN增量备份那点事
下一篇： 【RAC】Oracle11g RAC添加新节点相关事项
请登录后发表评论 登录
全部评论
xysoul_云龙
专家姓名：杨云龙
主要从事Oracle数据库 DBA相关工作，其他操作系统、中间件等也有涉及，热衷分享。岁月老将至，如有幸在江湖中留点踪迹，也是欣慰。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博：文盲筱烨 微信公众号：筱烨视点

注册时间：2014-02-15

  • 博文量
    412
  • 访问量
    1104599

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593