Oracle 在线重定义（上）
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA)
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 在线重定义（上），欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！
————————————————————————————
微信公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————11g 官方文档：https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11668
19c 官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-3C702CE8-9676-4825-B92A-D4AFE78FE402
关于在线重新定义表
在任何数据库系统中，有时都需要修改表的逻辑或物理结构以提高查询或 DML 的性能、适应应用程序更改或管理存储。您可以使用 DBMS_REDEFINITION 该软件包在线重新定义表。
Oracle 数据库提供了一种机制来修改表结构，而不会显着影响表的可用性。该机制称为在线表重新定义。与重新定义表格的传统方法相比，在线重新定义表格可显着提高可用性。
在线重新定义表时，在大部分重新定义过程中查询和 DML 都可以访问它。通常，表仅在与表的大小和重新定义的复杂性无关的非常小的窗口期间以独占模式锁定，并且对用户完全透明。但是，如果在重新定义期间有许多并发 DML 操作，则可能需要更长的等待时间才能锁定表。
联机表重新定义需要一定量的可用空间，该可用空间量大约等于被重新定义的表所使用的空间。如果添加新列，可能需要更多空间。
您可以使用 Oracle Enterprise Manager Cloud Control (Cloud Control) Reorganize Objects 向导或使用 DBMS_REDEFINITION 包执行在线表重新定义。
DBMS_REDEFINITION 包所需的权限
在 DBMS_REDEFINITION 包中运行子程序需要有执行权限。 对 DBMS_REDEFINITION 包的执行权限被授予 EXECUTE_CATALOG_ROLE。
此外，要让用户使用包重新定义用户架构中的表，必须授予用户以下权限：
CREATE TABLE
CREATE MATERIALIZED VIEW
CREATE TRIGGER 执行该过程也需要该特权 COPY_TABLE_DEPENDENTS。
要让用户使用包重新定义其他模式中的表，必须授予用户以下权限：
CREATE ANY TABLE
ALTER ANY TABLE
DROP ANY TABLE
LOCK ANY TABLE
SELECT ANY TABLECOPY_TABLE_DEPENDENTS 在其他模式中的表上需要执行以下额外权限：
CREATE ANY TRIGGER
CREATE ANY INDEX
GRANT EXECUTE_CATALOG_ROLE TO TEST;
GRANT CREATE TABLE,CREATE MATERIALIZED VIEW TO TEST;
GRANT CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE TO TEST;
GRANT CREATE Y TRIGGER,CREATE ANY INDEX TO TEST;在线重定义表的限制
以下限制适用于表的在线重新定义：
如果要使用主键或伪主键（唯一键或所有组件列不具有空约束的约束）重新定义表，则重新定义后的表必须具有相同的主键或伪主键列。如果要使用 rowid 重新定义表，则该表不能是索引组织表。
重新定义具有物化视图日志的表后，任何依赖物化视图的后续刷新都必须是完全刷新。
此限制有一个例外。当在线表重定义使用 REDEF_TABLE or START_REDEF_TABLE 过程，并且该过程中 refresh_dep_mviews 参数设置为 Y 时，在线表重定义操作过程中会刷新任何为增量刷新配置的依赖物化视图。
可以重新定义在 n 路主配置中复制的表，但不允许水平子集（表中行的子集）、垂直子集（表中列的子集）和列转换。
索引组织表的溢出表不能独立在线重定义。
启用闪回数据归档的表不能在线重新定义。您不能为临时表启用闪回数据存档。
可以在线重新定义具有 LONG 列的表，但必须将这些列转换为 CLOBS. 此外，LONG RAW 列必须转换为 BLOBS. LOB 带有列的表格是可以接受的。
在具有足够并行执行资源的系统上，并且在临时表未分区的情况下，可以并行执行将列重新定义为 LONG 列 LOB，前提是：
用于在临时表中存储LOB列的段属于启用了自动段空间管理 (ASSM) 的本地管理表空间。
从一 LONG 列到一 LOB 列的简单映射，临时表只有一 LOB 列。
在临时表被分区的情况下，适用于分区并行执行的常规方法。
SYS 和 SYSTEM 模式中的表不能在线重新定义。
不能重新定义临时表。
不能重新定义表中的行子集。
在将临时表中的列映射到原始表中的列时，只能使用简单的确定性表达式、序列和 SYSDATE。例如，不允许子查询。
如果 NOT NULL 作为重定义的一部分添加了新列并且这些列没有列映射，则在重定义完成之前不得声明它们。
被重新定义的表和临时表之间不能有任何引用约束。
无法重新定义表 NOLOGGING。
对于物化视图日志和队列表，在线重新定义仅限于物理属性的变化。不允许水平或垂直子集，也不允许任何列转换。列映射字符串的唯一有效值是NULL。
您不能对包含一个或多个嵌套表的分区执行联机重新定义。
您可以使用 CAST 列映射中 VARRAY 的运算符将 a 转换为嵌套表。但是，您不能将嵌套表转换为 VARRAY.
当过程 col_mapping 参数中的列 DBMS_REDEFINITION.START_REDEF_TABLE 包含序列时，orderby_cols 参数必须是 NULL.
对于具有虚拟专用数据库 (VPD) 安全策略的表，当 copy_vpd_opt 参数指定为DBMS_REDEFINITION.CONS_VPD_AUTO 时，适用以下限制：
原始表和临时表之间的列映射字符串必须是NULL或’*’。
临时表上不能存在任何 VPD 策略。
如果表是通过引用分区关联的，那么在线重定义不能同时运行在单独的DBMS_REDEFINITION 会话中的多个表上。
对象表或 XMLType 表的在线重定义可能导致其他表中的悬空 REF，如果这些表有引用重定义表的 REF 列。
无法在线重新定义使用 Oracle Label Security (OLS) 的表。
不能在线重新定义具有细粒度访问控制的表。
无法在线重新定义使用 Oracle Real Application Security 的表。
使用 REDEF_TABLE 过程执行在线重定义
您可以使用包中的 REDEF_TABLE 过程 DBMS_REDEFINITION 来在线重新定义表的存储属性。
REDEF_TABLE 当您想要更改以下属性时， 该过程使您能够在一个步骤中执行在线重新定义表的存储属性：
- 表空间更改，包括表、分区、索引或 LOB 列的表空间更改
- 压缩类型更改，包括表、分区、索引键或 LOB 列的压缩类型更改
- 对于 LOB 列，更改 SECUREFILE 或 BASICFILE 存储
当您的在线重定义操作不限于这些更改时，您必须使用多个步骤执行表的在线重定义。这些步骤包括调用 DBMS_REDEFINITION 包中的多个过程，包括以下过程：CAN_REDEF_TABLE、START_REDEF_TABLE、COPY_TABLE_DEPENDENTS 和FINISH_REDEF_TABLE。
在 DBMS_REDEFINITION 中使用多个过程执行在线重定义
您可以使用 DBMS_REDEFINITION 包中的多个过程来执行表的在线重新定义。
使用多个步骤在线重新定义表：
1）. 选择重定义方式：by key 或 by rowid
By key — 选择用于重新定义的主键或伪主键。伪主键是唯一键，所有组件列都具有NOT NULL 约束。对于这种方法，重定义前后的表版本应该具有相同的主键列。这是重新定义的首选和默认方法。
by rowid — 如果没有可用的键，请使用此方法。在此方法中，将一个名为 M_ROW$$ 的隐藏列添加到表的重新定义后版本。建议在重新定义完成后删除此列或将其标记为未使用。重定义的最后阶段会自动将此列设置为未使用。然后，您可以使用 ALTER TABLE…DROP UNUSED COLUMNS 语句删除它。
您不能在索引组织表上使用此方法。
2）. 通过调用过程验证是否可以在线重新定义表CAN_REDEF_TABLE。如果该表不是在线重新定义的候选对象，则此过程会引发错误，指示无法在线重新定义该表的原因。 3）. 创建具有所有所需逻辑和物理属性的空临时表（与要重新定义的表在同一schema中）。如果要删除列，则不要将它们包括在临时表的定义中。如果要添加列，则将列定义添加到临时表中。如果要修改列，则在临时表中使用所需的属性创建它。
没有必要在重新定义表的所有索引、约束、授权和触发器的情况下创建临时表，因为这些将在您复制相关对象时在步骤 7 中定义。
4）. 如果您使用 rowid 方法重新定义分区表，则在临时表上启用行移动。
ALTER TABLE ...ENABLE ROW MOVEMENT;5). （可选）如果您正在重新定义一个大表并希望通过并行运行来提高下一步的性能，请发出以下语句：
ALTER SESSION FORCE PARALLEL DML PARALLEL degree-of-parallelism;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL degree-of-parallelism;6). 通过调用 START_REDEF_TABLE 启动重新定义过程，提供以下信息：
- 分别在 uname 和 orig_table 参数中重新定义的表的模式和表名
- int_table 参数中的临时表名
- 一个列映射字符串，它将要重新定义的表的列映射到 col_mapping 参数中的临时表的列
- options_flag 参数中的重定义方法
提供包常量用于指定重新定义方法。DBMS_REDEFINITION.CONS_USE_PK 用于指示应该使用主键或伪主键进行重新定义。DBMS_REDEFINITION.CONS_USE_ROWID 用于指示应使用 rowid 进行重新定义。如果省略此参数，则采用默认的重定义方法 ( CONS_USE_PK)。
- orderby_cols 可选的，用于对参数中的行进行排序的列
- part_name 重新定义分区表的一个或多个分区时参数中的一个或多个分区名称
- 处理 copy_vpd_opt 参数中表上定义的虚拟专用数据库 (VPD) 策略的方法
由于此过程涉及复制数据，因此可能需要一段时间。在整个过程中，被重新定义的表仍然可用于查询和 DML。
注意：您可以查询 DBA_REDEFINITION_OBJECTS 视图，列出当前在线重定义中涉及的对象。如果 START_REDEF_TABLE 由于任何原因失败，您必须调用 ABORT_REDEF_TABLE，否则后续重新定义表的尝试将失败。
7). 使用以下两种方法之一将依赖对象（例如触发器、索引、物化视图日志、授权和约束）和统计信息从重新定义的表复制到临时表。方法 1 是首选方法，因为它更自动化，但有时您可能会选择使用方法 2。方法 1 还允许您将表统计信息复制到临时表。
方法一：自动创建依赖对象
使用该 COPY_TABLE_DEPENDENTS 过程在临时表上自动创建依赖对象。此过程还注册相关对象。注册从属对象可以使这些对象及其复制的对应对象的身份在稍后作为重新定义完成过程的一部分自动交换。结果是，当重新定义完成后，依赖对象的名称将与原始依赖对象的名称相同。
方法二：手动创建依赖对象
您可以在临时表上手动创建依赖对象，然后注册它们。
注意：在 Oracle9i 中，您需要在临时表上手动创建触发器、索引、授权和约束，并且可能仍然存在您想要或必须这样做的情况。在这种情况下，任何涉及临时表的引用约束（即临时表是引用约束的父表或子表）都必须禁用。在线重新定义完成后，将自动启用引用约束。此外，在重新定义过程完成或终止之前，
8）执行FINISH_REDEF_TABLE过程完成表的重新定义。在此过程中，原始表在很短的时间内以独占模式锁定，与原始表中的数据量无关。但是，FINISH_REDEF_TABLE在完成重新定义之前，将等待所有待处理的 DML 提交。
您可以在 FINISH_REDEF_TABLE 过程中使用dml_lock_timeout参数来指定过程等待挂起的 DML 提交的时间。该参数指定在过程正常结束之前等待的秒数。当为这个参数指定一个非null值时，可以重新启动FINISH_REDEF_TABLE过程，并从它超时的那一点开始继续。 当参数设置为NULL时，过程不会超时。 在这种情况下，如果手动停止过程，则必须使用ABORT_REDEF_TABLE过程终止在线表重定义，并从步骤6重新开始。
9）等待对临时表的任何长时间运行的查询完成，然后删除临时表。
如果在有针对它的活动查询运行时删除临时表，您可能会遇到ORA-08103错误（“对象不再存在”）。
自动创建依赖对象
您使用该 COPY_TABLE_DEPENDENTS 过程在临时表上自动创建依赖对象。
通过检查 num_errors 输出参数，可以发现在复制依赖对象时是否发生了错误。 如果 ignore_errors 参数设置为 TRUE，即使在创建对象时遇到错误，COPY_TABLE_DEPENDENTS 过程也会继续复制依赖对象。 您可以通过查询 DBA_REDEFINITION_ERRORS 视图来查看这些错误。
错误的原因包括：
- 缺乏系统资源
- 需要重新编码依赖对象的表逻辑结构的更改。
如果 ignore_errors 设置为 FALSE，则 COPY_TABLE_DEPENDENTS 过程会在遇到任何错误时立即停止复制对象。
更正任何错误后，您可以再次尝试通过重新执行该COPY_TABLE_DEPENDENTS过程来复制依赖对象。或者，您可以手动创建对象，然后按照“手动创建相关对象”中的说明注册它们。该 COPY_TABLE_DEPENDENTS 过程可以根据需要多次使用。如果对象已被成功复制，则不会再次复制。
手动创建依赖对象
如果您使用 SQL*Plus 或 Cloud Control 在临时表上手动创建依赖对象，则必须使用该 REGISTER_DEPENDENT_OBJECT 过程来注册依赖对象。注册依赖对象使重新定义完成过程能够将依赖对象名称恢复到重新定义之前的名称。
以下是需要您手动创建依赖对象的示例更改：
- 将索引移动到另一个表空间
- 修改索引的列
- 修改约束
- 修改触发器
- 修改物化视图日志
运行 REGISTER_DEPENDENT_OBJECT 过程时，必须使用 dep_type 参数指定依赖对象的类型。您可以在此参数中指定以下常量：
- DEMS_REDEFINITION.CONS_INDEX当依赖对象是索引时
- DEMS_REDEFINITION.CONS_CONSTRAINT当依赖对象类型是约束时
- DEMS_REDEFINITION.CONS_TRIGGER当依赖对象是触发器时
- DEMS_REDEFINITION.CONS_MVLOG当依赖对象是物化视图日志时
如果 copy_table_dependent_object 过程复制依赖对象失败，并且需要手动干预，那么还可以使用 REGISTER_DEPENDENT_OBJECT 过程。
您可以查询 DBA_REDEFINITION_OBJECTS 视图来确定注册了哪些依赖对象。 这个视图显示了显式注册到 REGISTER_DEPENDENT_OBJECT 过程或隐式注册到 COPY_TABLE_DEPENDENTS 过程的依赖对象视图中只显示当前信息。
UNREGISTER_DEPENDENT_OBJECT 过程可以用于在被重新定义的表和临时表上注销依赖对象。
监控在线表重定义进度
通过查看 V$ONLINE_REDEF 视图，可以监控在线表重定义操作的进度。
在在线重新定义表的过程中，有些操作可能需要很长时间才能执行。 在执行这些操作时，可以通过 V$ONLINE_REDEF 视图查看操作的详细进度。 例如，DBMS_REDEFINITION.START_REDEF_TABLE 过程可能需要很长时间将数据加载到中间表。
V$ONLINE_REDEF 视图在 PROGRESS 列中为操作提供了一个百分比的完整值。 这个视图在 operation 列中显示完成操作所需的总步骤数中的当前步骤。 例如，如果操作中有 10 个步骤，那么这一列可能显示 10 中的步骤 6。 视图还包括一个 SUBOPERATION 列和一个 DETAILED_MESSAGE 列，以获得关于当前操作的更详细的信息。
ONLINE_REDEF 视图中的REFRESH_STATEMENT_SQL_ID 和 REFRESH_STATEMENT 列，可以检查在线表重定义期间自动运行的刷新的进度。 您可以使用 REFRESH_STATEMENT_SQL_ID 列返回的SQL_ID 值来监视视图(如 VSQL 视图和 VSQL_MONITOR 视图)中的刷新进度。
连接到会话中的数据库，该会话与执行在线表重定义的会话是分开的。
查询 V$ONLINE_REDEF 视图。
SELECT * FROM V$ONLINE_REDEF;在线表重定义失败后重启
如果在线重定义表失败，则可以查看 DBA_REDEFINITION_STATUS 视图查看错误信息和可重启信息。
如果 RESTARTABLE 是 Y，那么您可以更正错误并重新启动上次停止的在线重新定义过程。如果 RESTARTABLE 是 N，则必须停止重新定义操作。
在某些情况下，可以在失败后重新启动表的在线重定义。重新启动操作意味着在线重新定义过程从因失败而停止的位置开始，并且没有工作丢失。例如，如果 SYNC_INTERIM_TABLE 由于“无法扩展表空间中的表”错误而导致过程调用失败，则可以通过增加空间不足的表空间大小并重新运行 SYNC_INTERIM_TABLE 过程调用来纠正问题。
如果在线重新定义表失败，那么您可以完成以下步骤重新启动它：
1、查询 DBA_REDEFINITION_STATUS 视图以确定失败的原因以及纠正失败所需的操作。
例如，运行以下查询：
COLUMN BASE_OBJECT_NAME FORMAT A11
COLUMN OPERATION FORMAT A10
COLUMN STATUS FORMAT A10
COLUMN RESTARTABLE FORMAT A11
COLUMN ERR_TXT FORMAT A15
COLUMN ACTION FORMAT A18
SELECT BASE_OBJECT_NAME, OPERATION, STATUS, RESTARTABLE, ERR_TXT, ACTION
FROM DBA_REDEFINITION_STATUS
ORDER BY BASE_TABLE_NAME, BASE_OBJECT_NAME;
BASE_OBJECT OPERATION STATUS RESTARTABLE ERR_TXT ACTION
----------- ---------- ---------- ----------- --------------- ------------------
T1 SYNC_REDEF Failure N ORA-12034: mate Abort redefinition
_TABLE rialized view l
og on "HR"."T1"
younger than last refresh
SELECT BASE_TABLE_NAME,
INTERIM_OBJECT_NAME,
OPERATION,
STATUS,
RESTARTABLE,
ACTION
FROM DBA_REDEFINITION_STATUS; 如果 RESTARTABLE 值为 Y，则可以重新启动操作。如果 RESTARTABLE 值为N，则无法重新开始操作，必须从头开始重新定义。如上在线重定义操作无法重启，因为 RESTARTABLE 在查询结果中， ACTION 列表示必须终止在线表重定义操作。
BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(
uname => 'hr',
orig_table => 't1',
int_table => 'int_t1');
END;
/2、执行上一步查询结果中指定的操作。
3、使用查询结果中指定的操作重新启动在线重定义，并运行所有后续操作完成表的在线重定义。
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('U1', 'ORIG', 'INT');
END;
/
ORA-42009: error occurred while synchronizing the redefinition
ORA-01653: unable to extend table U1.INT by 8 in tablespace my_tbs
ORA-06512: at "SYS.DBMS_REDEFINITION", line 148
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2807
ORA-06512: at line 2
回滚在线表重定义
在线表重定义之后，可以将表回滚到在线表重定义之前的定义，同时保留对表所做的所有数据操作语言(DML)更改。
在某些情况下，您可能希望撤消对表的在线重定义。 例如，表上的操作在重定义之后的性能可能比重定义之前的性能更差。 在这些情况下，可以将表回滚到其原始定义，同时保留重新定义后对表所做的所有DML更改。 在线表重定义回滚主要用于重定义更改表的存储特性，而这些更改意外导致性能下降的情况。
要启用在线表重定义回滚，必须在 DBMS_REDEFINITION 中将 ENABLE_ROLLBACK 参数设置为 TRUE。START_TABLE_REDEF 过程。 当该参数设置为 true 时，Oracle数据库将在重定义完成后维护重定义过程中创建的临时表。 可以运行 SYNC_INTERIM_TABLE 过程，定期同步临时表，将对重新定义的表所做的 DML 更改应用到临时表。 内部实体化视图和实体化视图日志可以维护中间表。 如果您决定回滚在线表重定义，那么将同步临时表，Oracle 数据库将切换回临时表，以便该表具有原始定义。
以下限制适用于在线表重定义回滚:
当原始表的列到临时表的列没有一对一的映射时，重定义时的列映射中不能有运算符或函数。
当原始表的列与临时表的列存在一对一映射时，列映射中可能存在运算符和函数。
为重定义启用回滚后，在回滚或终止在线表重定义之前，无法再次重新定义表。
DBMS_REDEFINITION 包中的 ROLLBACK 过程返回一个表，该表被在线重新定义为原始定义，同时保留 DML 更改。
要使用 ROLLBACK 过程，必须在在线表重定义期间启用在线表重定义回滚。 如果您决定保留在线表重定义所做的更改，那么您可以运行 ABORT_ROLLBACK 过程。
执行一个表的在线重定义，从 START_REDEF_TABLE 过程开始，以 FINISH_REDEF_TABLE 过程结束。
在 START_REDEF_TABLE 过程中，ENABLE_ROLLBACK 参数必须设置为 TRUE。 该参数的默认值为 FALSE。
可选:定期运行SYNC_INTERIM_TABLE过程，将对重定义表所做的DML更改应用到临时表。
如果定期对临时表应用DML更改，就可以提高在线表重定义回滚的性能。
请选择下列选项之一:
如果您想撤销在线表重定义所做的更改并返回到原始表定义，那么在DBMS_REDEFINITION 包中运行 ROLLBACK 过程。
如果希望保留在线表重定义所做的更改，那么在 DBMS_REDEFINITION 包中运行ABORT_ROLLBACK 过程。
终止回滚将停止对中间表的维护，并删除启用回滚的物化视图和物化视图日志。
假设重新定义的表的性能不如预期，并回滚在线重新定义所做的更改。
BEGIN
DBMS_REDEFINITION.ROLLBACK(
uname => 'hr',
orig_table => 'tst_rollback',
int_table => 'int_tst_rollback');
END;
/假设重新定义的表按预期执行，并终止回滚以保留在线表重新定义所做的更改并清理启用回滚的数据库对象。
BEGIN
DBMS_REDEFINITION.ABORT_ROLLBACK(
uname => 'hr',
orig_table => 'tst_rollback',
int_table => 'int_tst_rollback');
END;
/终止在线表重定义和错误后清理
您可以终止在线重新定义过程。这样做会删除与重新定义过程相关的临时日志和表。调用此过程后，您可以删除临时表及其相关对象。
如果在重新定义过程中出现错误，或者如果您选择手动终止重新定义过程，则终止在线重新定义过程：
运行ABORT_REDEF_TABLE程序。
如果必须重新启动在线重新定义过程，如果您不先调用ABORT_REDEF_TABLE，则后续重新定义表的尝试将失败。
如果重定义进程因为 FINISH_REDEF_TABLE 过程超时而停止，则不必调用 ABORT_REDEF_TABLE 过程。FINISH_REDEF_TABLE 过程中的 dml_lock_timeout 参数控制超时时间。
在线表重定义示例
示例说明了表的在线重新定义。
对于以下示例，请参阅_[Oracle Database PL/SQL Packages and Types Reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REDEFINITION.html#GUID-045732D9-6831-4411-BE1C-6FCB2A1F99B9)以了解所有DBMS_REDEFINITION子程序的描述。
例子
通过使用联机重新定义进行多项更改来重新定义表。
例 1
此示例说明使用该 REDEF_TABLE 过程在线重新定义表的存储属性。
原始表 print_ads 在 test 模式中定义如下：
Create table test.print_ads(AD_ID number(6),AD_TEXT clob);
Name Null? Type
----------------------------------------- -------- ----------------------------
AD_ID NUMBER(6)
AD_TEXT CLOB
insert into test.print_ads values (1,'asssccccciihguihuigiugiuyyuu8867#@9998888***7nhgft');
insert into test.print_ads values (2,'asssccccchuhugguigiugiuyyuu8867#@1238456***7nhgft');在此表中，LOB 列ad_text使用 BasicFiles LOB 存储。该表的索引是使用以下 SQL 语句创建的：
CREATE INDEX test.print_ads_ix ON test.print_ads (ad_id) TABLESPACE users;该表重新定义如下：- 使用高级行压缩对表进行压缩。
- 表的表空间由EXAMPLE修改为NEWTBS。 这个例子假设NEWTBS表空间存在。
- 使用COMPRESS 1压缩对索引进行压缩。
- 索引的表空间从EXAMPLE改为NEWIDXTBS。 这个例子假设NEWIDXTBS表空间存在。
- 表中的LOB列使用COMPRESS HIGH compression进行压缩。
- LOB列的表空间从EXAMPLE改为NEWLOBTBS。 这个例子假设NEWLOBTBS表空间存在.
- LOB列更改为SecureFiles LOB存储。
此重新定义中的步骤如下所示。
在 SQL*Plus 中，以具有执行在线重新定义表所需权限的用户身份连接。
BEGIN
DBMS_REDEFINITION.REDEF_TABLE(
uname => 'PM',
tname => 'PRINT_ADS',
table_compression_type => 'ROW STORE COMPRESS ADVANCED',
table_part_tablespace => 'NEWTBS',
index_key_compression_type => 'COMPRESS 1',
index_tablespace => 'NEWIDXTBS',
lob_compression_type => 'COMPRESS HIGH',
lob_tablespace => 'NEWLOBTBS',
lob_store_as => 'SECUREFILE');
END;
/如果发生错误，则临时表将被删除，并且 REDEF_TABLE 必须重新执行该过程。
例 2
此示例说明了通过添加新列和添加分区来在线重定义表。
原始表名为 emp_redef，在 hr 模式中定义如下:
Name Type
--------- ----------------------------
EMPNO NUMBER(5) <--- Primary key
ENAME VARCHAR2(15)
JOB VARCHAR2(10)
DEPTNO NUMBER(3)该表重新定义如下：
- 添加了新列 mgr、hiredate、sal 和 bonus。
- 新列 bonus 初始化为 0（零）。
- 该列 deptno 的值增加了 10。
- 重新定义的表按范围分区 empno。
此重新定义中的步骤如下所示。
在 SQL*Plus 中，以具有执行在线重新定义表所需权限的用户身份连接。
1. 验证该表是否是在线重定义的候选者。在这种情况下，您指定使用主键或伪主键进行重新定义.
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'hr',
tname =>'emp_redef',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/2. 创建一个临时表 hr.int_emp_redef。
CREATE TABLE hr.int_emp_redef
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(0))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);确保指定的表空间存在。3. 开始重新定义过程。
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'hr',
orig_table => 'emp_redef',
int_table => 'int_emp_redef',
col_mapping => 'empno empno, ename ename, job job, deptno+10 deptno,
0 bonus',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/4. 复制依赖对象。 自动创建任何触发器、索引、物化视图日志、授权和约束 hr.int_emp_redef.
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;
/注意，该调用的 ignore_errors 参数被设置为 TRUE。 原因是临时表是用一个主键约束创建的，当 COPY_TABLE_DEPENDENTS 试图从原始表复制主键约束和索引时，会发生错误。 您可以忽略这些错误，但必须运行下一步中显示的查询，以查看是否有其他错误。
5. 查询 DBA_REDEFINITION_ERRORS 视图以检查错误。
SET LONG 8000
SET PAGES 8000
COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A20
COLUMN BASE_TABLE_NAME HEADING 'Base Table Name' FORMAT A10
COLUMN DDL_TXT HEADING 'DDL That Caused Error' FORMAT A40
SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM
DBA_REDEFINITION_ERRORS;
Object Name Base Table DDL That Caused Error
-------------------- ---------- ----------------------------------------
SYS_C006796 EMP_REDEF CREATE UNIQUE INDEX "HR"."TMP$$_SYS_C006
7960" ON "HR"."INT_EMP_REDEF" ("EMPNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GRO
UPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "ADMIN_TBS"
SYS_C006794 EMP_REDEF ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
("ENAME" CONSTRAINT "TMP$$_SYS_C0067940"
NOT NULL ENABLE NOVALIDATE)
SYS_C006795 EMP_REDEF ALTER TABLE "HR"."INT_EMP_REDEF" MODIFY
("DEPTNO" CONSTRAINT "TMP$$_SYS_C0067950
" NOT NULL ENABLE NOVALIDATE)
SYS_C006796 EMP_REDEF ALTER TABLE "HR"."INT_EMP_REDEF" ADD CON
STRAINT "TMP$$_SYS_C0067960" PRIMARY KEY
("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXT
RANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GRO
UPS 1
BUFFER_POOL DEFAULT)
TABLESPACE "ADMIN_TBS" ENABLE NOVALID
ATE这些错误是由临时表上的现有主键约束引起的，可以忽略。请注意，使用这种方法，重新定义后的表上的主键约束和索引的名称会发生变化。另一种避免错误和名称更改的方法是定义没有主键约束的临时表。在这种情况下，主键约束和索引是从原始表中复制的。最好的办法是用主键约束定义临时表，REGISTER_DEPENDENT_OBJECT用来注册主键约束和索引，然后用 COPY_TABLE_DEPENDENTS. 这种方法避免了错误并确保重新定义的表始终具有主键并且依赖对象名称不会更改。
6. （可选）同步临时表 hr.int_emp_redef。
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'hr',
orig_table => 'emp_redef',
int_table => 'int_emp_redef');
END;
/7. 完成重新定义。
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'hr',
orig_table => 'emp_redef',
int_table => 'int_emp_redef');
END;
/表 hr.Emp_redef 仅在此步骤结束时的一个小窗口中被锁定为独占模式。在此之后，调用表 hr.Emp_redef 被重新定义，这样它就具有 hr.int_emp_redef 表的所有属性。
8.等待对临时表的任何长时间运行的查询完成，然后删除临时表。
例 3 4 5 6 7 省略，可查看官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-290254D6-C1C9-4A55-92DF-DF0A32EA0590
例 8
此示例说明使用在线重新定义对表进行多项更改。
要重新定义的表定义如下：
CREATE TABLE testredef.original(
col1 NUMBER PRIMARY KEY,
col2 VARCHAR2(10),
col3 CLOB,
col4 DATE)
ORGANIZATION INDEX;该表重新定义如下：
- 该表使用高级行压缩进行压缩。
- LOB 列更改为 SecureFiles LOB 存储。
- 表的表空间由example变为testredeftbs，表的块大小由 8KB 变为 16KB。
此示例假定数据库块大小为 8KB。此示例还假设 DB_16K_CACHE_SIZE 设置了初始化参数，并且 testredef 创建了具有 16KB 块大小的表空间。例如：
CREATE TABLESPACE testredeftbs
DATAFILE '/u01/app/oracle/oradata/testredef01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16384;- 该表在col1列上进行分区。
- 列col5已添加。
- 该col2列被删除。
- 列col3和col4被重命名，并且它们在表中的位置被改变。
- 列的类型col3从 更改DATE为TIMESTAMP。
- 该表从索引组织表 (IOT) 更改为堆组织表。
- 该表已进行碎片整理。
为了演示碎片整理，必须填充该表。出于本示例的目的，您可以使用此 PL/SQL 块来填充表：
DECLARE
V_CLOB CLOB;
BEGIN
FOR I IN 0..999 LOOP
V_CLOB := NULL;
FOR J IN 1..1000 LOOP
V_CLOB := V_CLOB||TO_CHAR(I,'0000');
END LOOP;
INSERT INTO testredef.original VALUES(I,TO_CHAR(I),V_CLOB,SYSDATE+I);
COMMIT;
END LOOP;
COMMIT;
END;
/执行下面的SQL语句，每隔第三行删除一个表片段:
DELETE FROM testredef.original WHERE (COL1/3) <> TRUNC(COL1/3);您可以使用该 DBMS_SPACE.SPACE_USAGE 过程确认碎片。--https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SPACE.html#GUID-1115B610-8956-426F-B615-9118225F911F
set serveroutput on
variable unf number;
variable unfb number;
variable fs1 number;
variable fs1b number;
variable fs2 number;
variable fs2b number;
variable fs3 number;
variable fs3b number;
variable fs4 number;
variable fs4b number;
variable full number;
variable fullb number;
begin
dbms_space.space_usage('USER','T_TAB1',
'TABLE',
:unf, :unfb,
:fs1, :fs1b,
:fs2, :fs2b,
:fs3, :fs3b,
:fs4, :fs4b,
:full, :fullb);
end;
/
print unf ;
print unfb ;
print fs4 ;
print fs4b;
print fs3 ;
print fs3b;
print fs2 ;
print fs2b;
print fs1 ;
print fs1b;
print full;
print fullb;在 SQL*Plus 中，以具有执行在线重新定义表所需权限的用户身份连接。
(1) 验证该表是否是在线重新定义的候选者。 在这种情况下，您指定使用主键或伪主键进行重新定义
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'testredef',
tname => 'original',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/(2) 创建一个临时表 testredef.interim。
CREATE TABLE testredef.interim(
col1 NUMBER,
col3 TIMESTAMP,
col4 CLOB,
col5 VARCHAR2(3))
LOB(col4) STORE AS SECUREFILE (NOCACHE FILESYSTEM_LIKE_LOGGING)
PARTITION BY RANGE (COL1) (
PARTITION par1 VALUES LESS THAN (333),
PARTITION par2 VALUES LESS THAN (666),
PARTITION par3 VALUES LESS THAN (MAXVALUE))
TABLESPACE testredeftbs
ROW STORE COMPRESS ADVANCED;(3) 开始重新定义过程。
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'testredef',
orig_table => 'original',
int_table => 'interim',
col_mapping => 'col1 col1, TO_TIMESTAMP(col4) col3, col3 col4',
options_flag => DBMS_REDEFINITION.CONS_USE_PK);
END;
/(4) 复制依赖对象。
DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname => 'testredef',
orig_table => 'original',
int_table => 'interim',
copy_indexes => DBMS_REDEFINITION.CONS_ORIG_PARAMS,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => TRUE,
num_errors => num_errors);
END;
/(5) 可选择同步临时表。
BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'testredef',
orig_table => 'original',
int_table => 'interim');
END;
/(6) 完成重新定义。
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'testredef',
orig_table => 'original',
int_table => 'interim');
END;
/参考链接
11g 官方文档：https://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11668
19c 官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-tables.html#GUID-3C702CE8-9676-4825-B92A-D4AFE78FE402
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SPACE.html#GUID-1115B610-8956-426F-B615-9118225F911F全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
欢迎关注我公众号【JiekeXu DBA之路】，第一时间一起学习新知识！以下三个地址可以
找到我，其他地址均属于盗版侵权爬取我的文章，而且代码格式、图片等均有错乱，不方
便阅读，欢迎来我公众号或者墨天轮地址关注我，第一时间收获最新消息。———————————————————————————— 公众号：JiekeXu DBA之路 CSDN ：https://blog.csdn.net/JiekeXu 墨天轮：https://www.modb.pro/u/4347 腾讯云：https://cloud.tencent.com/developer/user/5645107 ————————————————————————————