消除行链接、行迁移

    行迁移和行链接是经常接触到的一个概念。行链接是记录太大，一个数据块无法存储，oracle就会将其存储在相链接的块中，如果记录中数据类型包括：LONG、LONG RAW、LOB等数据类型时，行链接则无法避免了，可以在AWR实例活动统计部分的table fetch continued row分析可以看出当前数据库中链接行的数量；行迁移是指在update时，数据块已满，记录更新后记录长度增加了，oracle会将整条记录迁移到新的块中，行迁移后ROWID是不变的。参考ID 102989.1消除行链接和行迁移。

How to Find and Eliminate Migrated and Chained Rows (文档 ID 102989.1)

 

修改时间:2013-3-2类型:BULLETIN

 

***Checked for relevance on 11-Jan-2012*** PURPOSE ------- How to find and eliminate migrated or chained rows. SCOPE & APPLICATION ------------------- Step by step example. How to find and eliminate Migrated and Chained rows --------------------------------------------------- CONCEPTS: * A row Migrates when a block was found to have the space available for a row which underwent an update that increased its size over and beyond its block's available space. * A Chained row occurs when there is no block which can hold the row after it underwent an update which increased its size beyond the available free space in its hosting block. The solution is to split the row over several blocks. CAUSES and EFFECTS: * Causes for migrated and chained rows: Inserts, updates and deletes over periods of time * Results from migrated and chained rows: Degraded response for queries. SOLUTION: 1) Analyze the table: To prevent an ORA-1495 (specified chained row table not found), run the $ORACLE_HOME/rdbms/admin/utlchain.sql script. TRUNCATE TABLE CHAINED_ROWS; ANALYZE TABLE <table name> LIST CHAINED ROWS; 2) List the Migrated or Chained rows. From SQL*Plus: col owner_name format a10 col table_name format a20 col head_rowid format a20 select owner_name, table_name, head_rowid from chained_rows; 3) You can now eliminate the Migrated or Chained rows by Create Table as Select (CTAS), exporting and then importing the table or by following the next steps: A) Create an empty copy of the table that has the Migrated or Chained rows. CREATE TABLE <temporary table name> AS SELECT * FROM <table name> WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='<table name'>'); B) Now delete the Migrated and Chained rows from the table. DELETE FROM <table name> WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='<table name>'); C) Insert the rows back to the table. INSERT INTO <table name> SELECT * FROM <temporary table name>; Truncate the chained_rows table and drop the temporary table. Alternatively, you can move the table to a tablespace if the row cannot fit in the block and you need a tablespace with a larger block size: alter table <table_name> move <tablespace>; Note: Check the SQL Reference guide for your release, for details on the 'alter table..move..' command.

 

转载于:https://www.cnblogs.com/bwdata/p/3844820.html