获取row chain and row Migration

获取row chain and row Migration 获取row chain and row Migration 1.使用analyze对相应的object分析 SQL ANALYZE TABLE oe.orders COMPUTE STATISTICS; Table Analyzed. SQL SELECT num_rows, avg_row_len, chain_cnt 2 FROM DBA_TABLES 3 WHERE table_nam

获取row chain and row Migration

获取row chain and row Migration

1.使用analyze对相应的object分析

SQL> ANALYZE TABLE oe.orders COMPUTE STATISTICS;

Table Analyzed.

SQL> SELECT num_rows, avg_row_len, chain_cnt

2 FROM DBA_TABLES

3 WHERE table_name='ORDERS';

NUM_ROWS AVG_ROW_LEN CHAIN_CNT

---------- ----------- ----------

1171 67 83

2.也可是使用以下方法获取Migrated Rows：

ANALYZE TABLE … LIST CHAINED ROWS ------不会覆盖当前统计信息

在使用以上命令时需要执行utlchain.sql这个脚本，也可以手工执行：

SQL> CREATE TABLE chained_rows (

2 owner_name VARCHAR2(30),

3 table_name VARCHAR2(30),

4 cluster_name VARCHAR2(30),

5 partition_name VARCHAR2(30),

6 head_rowid ROWID,

7 analyze_timestamp DATE );

用于存储链接行的信息

eg：

SQL> ANALYZE TABLE oe.orders LIST CHAINED ROWS;

Table analyzed.

SQL> SELECT owner_name, table_name, head_rowid

2 FROM chained_rows

3 WHERE table_name = 'ORDERS';

OWNER_NAME TABLE_NAME HEAD_ROWID

---------- ---------- ------------------

SALES ORDER_HIST AAAAluAAHAAAAA1AAA

SALES ORDER_HIST AAAAluAAHAAAAA1AAB

...

消除行迁移：

? Export/import:

– Export the table.

– Drop or truncate the table.

– Import the table.

? MOVE table command:

– ALTER TABLE EMPLOYEES MOVE

所有index在操作后需要rebuilt

Move table command is faster than export and impor t.

但是前提是有足够的空间。

? Online table redefinition

使用 DBMS_REDEFINITION 包需要足够空间。

? Copy migrated rows:

– Find migrated rows by using ANALYZE.

– Copy migrated rows to a new table.

– Delete migrated rows from the original table.

– Copy rows from the new table to the original table.

注意，是否需要禁用相应的外键约束，trigger ，row-level security, and auditing.

script：

/* Clean up from last execution */

SET ECHO OFF

DROP TABLE migrated_rows;

DROP TABLE chained_rows;

/* Create the CHAINED_ROWS table */

@?/rdbms/admin/utlchain

SET ECHO ON

SPOOL fix_mig

/* List the chained & migrated rows */

ANALYZE TABLE &table_name LIST CHAINED ROWS;

/* Copy the chained/migrated rows to another table */

CREATE TABLE migrated_rows AS

SELECT orig.*

FROM &table_name orig, chained_rows cr

WHERE orig.rowid = cr.head_rowid

AND cr.table_name = upper('&table_name');

/* Delete the chained/migrated rows from the original table */

DELETE FROM &table_name

WHERE rowid IN (

SELECT head_rowid

FROM chained_rows);

/* Copy the chained/migrated rows back into the original table */

INSERT INTO &table_name

SELECT *

FROM migrated_rows;

SPOOL OFF