Row Chaining

SQL> show parameter db_block_size

NAME TYPE VALUE

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

db_block_size integer 8192

SQL> create table row_chain

(col1 char(2000),col2 char(2000),col3 char(2000),col4 char(2000));

Table created.

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

Table analyzed.

SQL> select num_rows,chain_cnt

from dba_tables

where table_name='ROW_CHAIN';

NUM_ROWS CHAIN_CNT

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

0 0

SQL> INSERT INTO row_chain values('a','b','c','d');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

Table analyzed.

SQL> select num_rows,chain_cnt

from dba_tables

where table_name='ROW_CHAIN';

NUM_ROWS CHAIN_CNT

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

1 1

Using the CHAINED_ROWS table, you can find out the tables with chained or migrated rows.

SQL> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlchain.sql

SQL> ANALYZE TABLE row_chain LIST CHAINED ROWS;

Table analyzed.

SQL> SELECT owner_name,

table_name,

count(head_rowid) row_count

FROM chained_rows

GROUP BY owner_name,table_name;

OWNER_NAME TABLE_NAME ROW_COUNT

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

SYS ROW_MIG_CHAIN_DEMO 3

SQL>Alter system set db_16K_cache_size=8m

SQL> create index i_row_chain on row_chain(col1);

Index created.

SQL> create tablespace row_chain

2 datafile 'C:\oracle\product\10.2.0\oradata\dba20\ROW_CHAIN_01.DBF'

3 SIZE 3M BLOCKSIZE 16K;

Tablespace created.

SQL> alter table row_chain move tablespace row_chain;

Table altered.

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

ANALYZE TABLE row_chain COMPUTE STATISTICS

*

ERROR at line 1:

ORA-01502: index 'SYS.I_ROW_CHAIN' or partition of such index is in unusable

State

SQL> alter index i_row_chain rebuild;

Index altered.

SQL> ANALYZE TABLE row_chain COMPUTE STATISTICS;

Table analyzed.

SQL> select num_rows,chain_cnt

from dba_tables

where table_name='ROW_CHAIN';

NUM_ROWS CHAIN_CNT

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

1 0