Re: How to re-insert chained rows?

"Vince Laurent" <vincelaurent_at_REMOVETHISsbcglobal.net> wrote in message news:1emhf19l12jfu04l4cs6008f8v6bgk3vrq_at_4ax.com...
>I had an error and found this solution on metalink:
> -------------------------------------------------------------------
> This error can indicate that the second row piece of a chained row is
> held in a block that can not accept any more transactions due to
> current activity (MAXTRANS may have been reached.)
>
> Check for chained/migrated rows using :
>
> ANALYZE TABLE <table_name> LIST CHAINED ROW INTO Chained_Rows;
>
> If found, consider increasing PCTFREE to avoid additional chaining
> and then re-insert the chained rows highlighted above.
> -------------------------------------------------------------------
> I did
>
> @$ORACLE_HOME/rdbms/admin/utlchn1.sql
>
> then
>
> SQL> ANALYZE TABLE "RFBLG" LIST CHAINED ROWS INTO Chained_Rows;
>
> Then
>
> set linesize 120
> col owner_name format a10
> col table_name format a15
> col cluster_name format a7
> col partition_name format a9
> col subpartition_name format a12
> col head_rowid format a20
>
> SELECT * FROM chained_rows;
>
> And got:
>
> OWNER_NAME TABLE_NAME CLUSTER PARTITION SUBPARTITION HEAD_ROWID
> A
> NALYZE_T
> ---------- ----------- ------- --------- ------------
> -------------------- -
> --------
> SAPR3 RFBLG N/A
> AAAHFxAALAAAMD7AAD 0
> 9-AUG-05
>
> 5061 rows selected.
>
> So.... How do I:
> "...re-insert the chained rows highlighted above."
>
>
> -----------------------------------------------------
> Come race with us!
> http://www.mgpmrc.org

This is what I do it for tables with a high percentage of chained rows (note : you must have enough free space to hold a copy of the table) :

ALTER TABLE ... PCTFREE <5% higher>;
ALTER TABLE ... MOVE; (please note that this will lock the table during the operation)
ALTER INDEX ... REBUILD for all indexes of the table (they have become invalid after the move)
Re-compute the table and index statistics (they have been removed after the move).
Check if there are any chained rows left : SELECT CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME = ... HTH
Matthias Hoys Received on Tue Aug 09 2005 - 14:46:34 CDT