How to find chained rows in a table ?

A chained row is a row that does not fit into a single database block. For example, if you try to add a row of size 16KB to a database with a block size of 8KB, this record will use 3 blocks. Because it does not fit into one block, this row is called a chained row.

Tables that can contain chained rows are as follows:

• Tables whose row size exceeds the database block size.
• Tables containing columns of type long and long raw.
• Tables containing more than 255 columns

Analyze and list chained rows :

First, we create the necessary table as shown below.

SQL> @$ORACLE_HOME/rdbms/admin/utlchain.sql; T

Table created.

The table then needs to be analyzed as follows.

SQL> ANALYZE TABLE tablename LIST CHAINED ROWS;

After these operations, the chained_rows table will display information if there are chained rows in that table.

SQL> SELECT * FROM chained_rows;

We can also query tables containing chained rows with the following query.

SQL> SELECT owner, table_name, chain_cnt FROM dba_tables WHERE chain_cnt > 0;

If you receive an error like the one below during table analysis, it means the chained_rows table does not exist for the user schema you are running the analysis with. In this case, you need to perform the analysis with the user who created the table using utlchain.sql.

ORA-01495: specified chain row table not found