Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Identifying Oracle Tables with Migrated/Chained Rows

Don Burleson 

This article pertains to tables with variable length columns and shows how rows may increase in length as they are updated, the row spilling into the free space defined by PCTREEE, and ultimately chaining onto multiple data blocks..  We also see chained rows in cases where the CLOB or BLOB data columns exceeds the database block size.  The identification of these tables that do have migrated/chained rows is important because of their use of CLOB, BLOB, RAW and LONG RAW data columns. 

As far as performance goes, the presence of migrated or chained rows can severely affect performance.  With SQL, for example, any statements that either query or create/update migrated or chained rows will be doubling I/O overhead, thus degrading performance.  It is best to quickly identify and rectify situations involving migrated or chained rows.  Chained rows can also add overhead to the performance of indexes (index range scans and index fast full scans) and full-table scans.

Migrated rows and chained rows are similar, but each has a different performance impact on the database.  Attempting to update a row with data that will make it too large for the block causes the data to be migrated to a space where it will fit after the update. All that is left in the original block is the forwarding information.

Simply put, chained rows happen when a row is too big to fit into a single database block. Chained rows usually result from an insert.  For example, if the blocksize for the database is 4 kilobytes and there is an 8 kilobyte row to be inserted, Oracle will break the data into pieces and store it in 3 different blocks that are chained together.  There is forwarding information available to allow the database to collect all of the bits of chained row information from multiple blocks.

 There are a couple of other conditions that can contribute to chained rows:

  • Tables with large objects (BLOB, CLOB, NCLOB, RAW and LONG RAW) columns are historically more prone to ending up with  chained rows.

  • Super-wide tables also end up with chained rows.  Tables with in excess of 255 columns may end up with chained rows.  These wide tables get broken up into pieces, with part of the row residing on two data blocks. 

Specific information on migrated or chained rows is available as follows:

  • Listing 5-5 shows a list of all tables that contain migrated/chained rows.

  • Listing 5-6 shows the script that generates the migrated/chained rows report.  Note that this script does not include tables that contain RAW or LONG column datatypes, since such columns commonly span database blocks and will chain rows regardless of a database reorganization.

A properly tuned database should not have any row chaining, so the report shown in Listing 5-5 may be used as a database integrity check. As you can see, all of the migrated/chained rows are located in user-defined tables beginning with the letter "Z".

Owner     Table        PCTFREE PCTUSED avg row         Rows       Chains  Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
TSTR3     ZG_TAB            10      40      80        5,003        1,487  .30
TSTR3     ZMM               10      40     422       18,309          509  .03
TSTR3     Z_Z_TBLS          10      40      43          458           53  .12
TSTR3     USR03             10      40     101          327           46  .14
TSTR3     Z_BURL            10      40     116        1,802           25  .01
TSTR3     ZGO_CITY          10      40      56        1,133           10  .01 
6 rows selected.

Listing 5-5. A report of tables with migrated or chained rows

The following code generated the report on migrated/chained rows.  Note that the use of this script is predicated on the use of Oracle's ANALYZE command to populate the chain_cnt and num_rows columns of the dba_tables data dictionary view.

spool chain.lst;
set pages 9999;
column c1 heading "Owner"   format a9;
column c2 heading "Table"   format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows"    format 999,999,999;
column c7 heading "Chains"  format 999,999,999;
column c8 heading "Pct"     format .99; 
set heading off;
select 'Tables with migrated/chained rows and no RAW columns.' from dual;
set heading on;
   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
owner not in ('SYS','SYSTEM')
table_name not in
 (select table_name from dba_tab_columns
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
chain_cnt > 0
order by chain_cnt desc

Listing 5-6. The chain.sql script to detect tables with migrated/chained rows

Because of the large row length of some of the cluster tables, you will see a lot of chained rows in tables that contain BLOB, CLOB, RAW and LONG RAW columns.  These chained rows are caused by the row length exceeding the db_block_size, forcing the huge rows to chain onto many blocks. 

  • Listing 5-7 shows tables that contain chained rows caused by row length in excess of db_block_size.

  • Listing 5-8 contains the script that generates the report containing tables with chained rows caused by excessive row lenght compared to db_block_size.

These types of chained row tables are commonly found in the specific tablespace(s), and database reorganization will not reduce the chained rows. Unfortunately, there is no remedy to these chained rows, provided that you are using the maximum supported Oracle blocksize for your version of UNIX.

For example, from listing 5-7, we see that the D010L table has an average row length of over 13k. Because most UNIX operating systems do not support blocksizes greater than 8k, and as a result, there will be chained rows.

Owner     Table        PCTFREE PCTUSED avg row         Rows       Chains  Pct
--------- ------------ ------- ------- ------- ------------ ------------ ----
TSTR3     KOCLU             40      60   1,981      597,125      472,724  .79
TSTR3     CDCLS             40      60     809      712,810      328,989  .46
TSTR3     VBFCL             40      60   1,398      340,917      285,930  .84
TSTR3     EDIDOC            40      60   2,211      158,426      114,859  .73
TSTR3     D010S             10      40   5,129       76,635       43,791  .57
TSTR3     TST03             40      60   6,559       44,596       22,298  .50
TSTR3     T512CLU           40      60   1,055       24,393       21,344  .88
TSTR3     D020L             10      40   1,629       84,968        6,294  .07
TSTR3     EUDB              40      60   3,068       13,910        6,028  .43
TSTR3     D010L             10      40  13,454        4,747        4,747   1
TSTR3     D022S             10      40     774      113,835        3,794  .03
TSTR3     D010Y             10      40   5,435        5,497        2,998  .55
TSTR3     D010Q             10      40   7,351        5,768        2,596  .45
TSTR3     D010T             40      60   1,220       39,550        1,720  .04
TSTR3     DSYO2             40      60   2,946          627          289  .46
TSTR3     SFHOA             40      60   3,247          319          152  .48
TSTR3     DSYO1             40      60   2,695          426           56  .13
TSTR3     RFDT              40      60   2,629          740           26  .04
TSTR3     INDX              40      60   2,288          302           20  .07
TSTR3     MACOB             40      60     694          103            2  .02
TSTR3     USR04             40      60      74          434            1  .00

Listing 5-7. A report of migrated/chained rows in tables that contain RAW columns.

set heading off;
select 'Tables with migrated/chained rows that contain RAW columns.' from dual;
set heading on;
   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
owner not in ('SYS','SYSTEM')
table_name in
 (select table_name from dba_tab_columns
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
chain_cnt > 0
order by chain_cnt desc
spool off;

Listing 5-8. The chain1.sql script to identify migrated/chained rows in tables  that contain RAW or LONG RAW columns.

Bobby Durrett has this nice script to detect if a current session is accessing high volume of chained rows (table fetch continued row fetch):

--  get the required session ID
variable monitored_sid number;


   sid into :monitored_sid




   b.value "table fetch continued rows"

   v$sesstat b
   b.SID = :monitored_sid

   b.statistic# =

      name='table fetch continued row');

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training





Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.