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