Diagnose and resolve row chained and migrated

Diagnose and solve row chained and migrated Listing Chained Rows of Tables and Clusters1.execute the UTLCHAIN. SQL or UTLCHN1. SQL script. the script is located at: @ oracle_home \ rdbms \ admin \. The script content is rem $ Header: utlchain. SQL 07-may-96.19: 40: 01 sbasu Exp $ rem Rem Copyright (c) 1990,199 5, 1996,199 8 by Oracle configurationrem NAMEREM UTLCHAIN. SQLRem FUNCTIONRem Creates the default table for storing the output of theRem analyze list chai Ned rows commandRem NOTESRem MODIFIEDRem syeung 06/17/98-add subpartition_name Rem mmonajje 05/21/96-Replace timestamp col name with descrisbasu 05/07/96-Remove echo settingRem ssamu 08/14/95-merge PTI objectsRem ssamu 07/24/95-add field for partition nameRem glumpkin 10/19/92-Renamed from CHAINROW. SQL Rem ggatlin 03/09/92-add set echo on Rem rlim 04/29/91-change Char to varchar2 Rem Klein 01/10/91-add owner name for chained rowsRem Klein 12/04/90-CreationRemcreate table CHAINED_ROWS (owner_name varchar2 (30), table_name varchar2 (30 ), cluster_name varchar2 (30), partition_name varchar2 (30), subpartition_name varchar2 (30), head_rowid rowid, analyze_timestamp date); 2. Perform analyze cluster emp_dept list chained rows into begin; analyze table order_his T list chained rows; 3. query the analysis results. SELECT * FROM CHAINED_ROWSWHERE TABLE_NAME = 'order _ HIST '; OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP -------------------------... ------------------ --------- SCOTT ORDER_HIST... AAAAluAAHAAAAA1AAA 04-MAR-96SCOTT ORDER_HIST... AAAAluAAHAAAAA1AAB 04-MAR-96SCOTT ORDER_HIST... AAAAluAAHAAAAA1AAC 04-MAR-96 The output lists all rows that are either migrated or chained.4. Create an intermediary table and temporarily store migrated Or chained row data Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows: create table int_order_hist as select * FROM order_hist where rowid in (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'order _ HIST '); 5. delete the migrated and chained rows from the original table DELETE the migrated and chained rows FROM the existing table: Delete from order_hist WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'order _ HIST '); 6. Insert the rows IN the intermediary table into the original table again. Insert the rows of the intermedia te table into the existing table: insert into order_hist SELECT * FROM int_order_hist; 7. disable the mediation table. drop the intermediate table: drop table int_order_history; 8. delete the information in the chained_rows table. Delete the information collected in step 1 from the output table: delete from CHAINED_ROWS WHERE TABLE_NAME = 'order _ HIST '; -------- to completely solve chained rows, increase the data block size. Row chained is too likely to be avoided in most cases. It is almost impossible to avoid it when the table has a long or large CHAR or VARCHAR2 field.