Identify Chained Rows & elimination

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.

Results from chained rows: Degraded response for queries.

Following query finds the chained rows:

SELECT   owner c1, table_name c2, pct_free c3, pct_used c4, avg_row_len c5,

         num_rows c6, chain_cnt c7, chain_cnt / num_rows c8

    FROM dba_tables

   WHERE owner NOT IN ('SYS', 'SYSTEM')

     AND table_name NOT IN (SELECT table_name

                              FROM dba_tab_columns

                             WHERE data_type IN ('RAW', 'LONG RAW'))

     AND chain_cnt > 0

ORDER BY chain_cnt DESC

How To Rectify:

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 LIST CHAINED ROWS;

2)    List the Chained rows.

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 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 TEMP_TAB AS SELECT * FROM

WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='TEST');

B) Now delete the Migrated and Chained rows from the table.

DELETE FROM TEST

WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME='TEMP_TAB');

A)   Insert the rows back to the table.

INSERT INTO TEST SELECT * FROM ;

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 move ;