Re: Q: How to re-insert chained rows?

Vince Laurent wrote:
> 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

You must first create a copy of the table containing the 'offending' rows. I'd also create a copy of the table as it now exists:

create table rfblg_save as select * from rfblg;

create table rfblg_temp as select * From rfblg where 0=1;

insert into rfblg_temp
select * from rfblg
where rowid in (select rowid from chained_rows where table_name = 'RFBLG'); commit;

delete from rfblg
where rowid in (select rowid from chained_rows where table_name = 'RFBLG'); commit;

alter table rfblg pctused 50 pctfree 50; <-- or other appropriate values

insert into rfblg
select * from rfblg_temp;

commit;

drop table rfblg_temp;

This, of course, only affects any new extents/blocks created for RFBLG.  The ideal solution is to export the table, drop the table, recreate the table with adjusted pctfree/pctused values then import the data. This ensures the entire table is now using your adjusted pctused/pctfree settings.

David Fitzjarrell Received on Tue Aug 09 2005 - 12:05:07 CDT