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 |