【redefined】在线重定义概览与详细使用


【redefined】在线重定义概览与详细使用
 
在线重定义
In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:
Improve the performance of queries or DML
Accommodate application changes
Manage storage
---在以下情况,常需要使用到在线重定义:
l 希望提高查询或dml语句的执行效率
l 适应应用的改变
l 对表的存储属性进行管理
 
Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called online table redefinition. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.
---oracle数据库提供了在线重定义的方法，使得在不影响表的使用的前提下对表的结构进行修改。与传统的修改方法相比，这种方法增加了可用性
 
When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.
---对一个表做在线重定义的大部分时间里，你可以继续对表进行查询和DML操作。期间，只有在非常小的一段时间内，需要进行锁表，这一小段时间的长短取决于表的大小和复杂度，但这小段时间对用户来讲是完全透明的
 
Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.
---在线重定义的一个特点是，需要和原表大小差不多的额外空间来完成这个操作。当然，如果你在新定义的表中添加了一些新列，那么就需要更多的空间
 
Online table redefinition enables you to:
Modify the storage parameters of a table or cluster
Move a table or cluster to a different tablespace in the same schema
Add, modify, or drop one or more columns in a table or cluster
Add or drop partitioning support (non-clustered tables only)
Change partition structure
Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
Add support for parallel queries
Re-create a table or cluster to reduce fragmentation
Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
Convert a relational table into a table with object columns, or do the reverse.
Convert an object table into a relational table or a table with object columns, or do the reverse.
---利用在线重定义，你可以做以下事情：
l 修改表的存储属性
l 将表移动到另一个表空间，但其schema不变
l 添加、修改、删除表中的某个字段
l 对表添加或删除一个分区（聚簇表不支持）
l 改变分区的结构
l 对分区表中单独的一个区进行物理属性的修改，其中就包括将单独的一个区移到其它表空间下
l 增加并行查询选项
l 重建表来减少碎片
l 将普通表转换成索引组织表，或者将索引组织表转换成普通表（堆表）
l 关系表和对象表间的转换
 
Performing Online Redefinition with DBMS_REDEFINITION
---下面将使用dbms_redefinition包进行在线重定义的过程操作：
Choose the redefinition method: by key or by rowed
---在线重定义有两种方法：基于主键或基于rowid
By key—Select a primary key or pseudo-primary key to use for the redefinition. Pseudo-primary keys are unique keys with all component columns having NOT NULL constraints. For this method, the versions of the tables before and after redefinition should have the same primary key columns. This is the preferred and default method of redefinition.
---基于主键：使用主键或伪主键，所谓伪主键就是指有unique、not null约束列的组合。使用这种方法，需要重定义前后的表中有相同的主键列。这是默认的重定义方法
By rowid—Use this method if no key is available. In this method, a hidden column named M_ROW$$ is added to the post-redefined version of the table. It is recommended that this column be dropped or marked as unused after the redefinition is complete. You cannot use this method on index-organized tables.
---基于rowid：这种方法就不需要使用主键列。使用这种方法后，新定义的表中将会有一个隐藏列m_row$$。Oracle建议你将这列删除或标记为不可用。这种方法的一个限制是不能在索引组织表中使用。
Verify that the table can be redefined online by invoking the CAN_REDEF_TABLE procedure. If the table is not a candidate for online redefinition, then this procedure raises an error indicating why the table cannot be redefined online.
---在在线重定义开始前，使用can_redef_table包进行验证，确认是否能对表进行在线重定义操作，如果不能，将会返回报错表明原因。
Create an empty interim table (in the same schema as the table to be redefined) with all of the desired logical and physical attributes. If columns are to be dropped, do not include them in the definition of the interim table. If a column is to be added, then add the column definition to the interim table. If a column is to be modified, create it in the interim table with the properties that you want.
---然后在同一个用户下创建一个中间表，你可以按照你所希望的表的结构进行创建，并不需要和原表相同，你可以添加新列，删除旧列，改变原有列的属性。
It is not necessary to create the interim table with all the indexes, constraints, grants, and triggers of the table being redefined, because these will be defined in step 6 when you copy dependent objects.
---这一步中，我们可以先不在中间表上创建那些应有的索引、约束和触发器。我们可以第6步中使用另一个包来完成这些操作
(Optional)If you are redefining a large table and want to improve the performance of the next step by running it in parallel, issue the following statements:
---(可选)如果你对一个很大的表进行了在线重定义操作，你可以使用下面语句来设置并行操作来加快在线重定义的的过程
alter session force parallel dml paralleldegree-of-parallelism;
alter session force parallel query paralleldegree-of-parallelism;
 
Start the redefinition process by calling START_REDEF_TABLE。
Because this process involves copying data, it may take a while. The table being redefined remains available for queries and DML during the entire process.
---调用dbms_redefinition.start_redef_table()过程，这个过程提供如下参数：被重定义的表的名称，中间表的名称，列的映射规则，重定义方法。如果没有给出列的映射规则，则认为所有包括在中间表中的列用于表的重定义。如果给出了映射规则，则按照映射规则。
因为在此过程中需要进行原始表和中间表间的数据拷贝，所以可能需要较长的时间。但此阶段不会对查询和dml操作产生影响
Note:If START_REDEF_TABLE fails for any reason, you must call ABORT_REDEF_TABLE, otherwise subsequent attempts to redefine the table will fail.
---注释：如果调用dbms_redefinition.start_redefinition_table ()过程失败，不论任何原因，必须调用一次dbms_redefinition.abort_redef_table()，否则后续的操作可能会继续失败
Copy dependent objects (such as triggers, indexes, grants, and constraints) and statistics from the table being redefined to the interim table, using one of the following two methods. Method 1 is the preferred method because it is more automatic, but there may be times that you would choose to use method 2. Method 1 also enables you to copy table statistics to the interim table.
---拷贝原始表上的依赖对象（触发器、索引、约束、授权）到中间表上。你可以通过如下两种方法，其中方法1更自动化一些，因