RE: CHAINED ROWS

From: SARKAR, Samir <Samir.SARKAR_at_nottingham.sema.slb.com>
Date: Tue, 08 Jan 2002 08:22:21 -0800
Message-ID: <F001.003E9966.20020108073036@fatcity.com>

If Oracle 8i is ur current
version, run the utlchain1.sql script available in ur ORACLE_HOME/rdbms/admin
directory. This will create
the chained_rows table for u.
Now analyze the affected
table using the command :
 

analyze table
<table_name> list chained rows into chained_rows;
 

Now when u select from the
chained_rows table, u will get the rowid of all the rows that r chained in the table as
<FONT
face=Arial>head_rowid.
 

The best way to deal with
chained rows is to export the table's data, rebuild the table with a higher pctfree and
import the data
back.
 

Otherwise, copy the chained
rows into a temporary work table in the following way :
 

create table
<temp_table_name> as
select * from
<table_name> where rowid in
(select head_rowid from
chained_rows where table_name = '<table_name>'
 and owner_name =

'<owner_name>');
 

Then delete the chained
rows from the main table in the following way :
 
 delete from

<table_name>
 where rowid

in
(select head_rowid from
chained_rows where table_name = '<table_name>'
 and owner_name =

'<owner_name>');
 

Next, re-insert the chained
rows into the table :
 

insert into
table_name
    select *

from <temp_table_name>;
 

Commit ur work. Remember to
disable any foreign key constraints during the deletion stage and re-enable them

again after re-insertion of
the rows.
This should eliminate most
of ur chained rows.
 

Hope this
helps.
 

Samir
 

Samir Sarkar
Oracle DBA - Lennon
Team Schlumberger<FONT
face=Impact color=#800000>Sema
Email : 
samir.sarkar_at_nottingham.sema.slb.com <FONT face=Verdana color=#000080
size=1>           
samir.sarkar_at_sema.co.uk <FONT face=Verdana color=#000080 size=1>Phone : +44 (0) 115 - 95 76217 EPABX : +44 (0) 115 - 957 6418
Ext. 76217 Fax
: +44 (0) 115 - 957
6018           

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Bunyamin K. Karadeniz   [mailto:bunyamink_at_havelsan.com.tr]Sent: 08 January 2002   13:36To: Multiple recipients of list ORACLE-LSubject:   CHAINED ROWS
  I have seen that There are some number of    chained rows in several tables of a schema in my database .   What is it done in such a situation ?   

  Thank you
  Bunyamin

---

This email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of SchlumbergerSema.
If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited.

If you have received this email in error please notify the SchlumbergerSema Helpdesk by telephone on +44 (0) 121 627 5600.

---

Received on Tue Jan 08 2002 - 10:22:21 CST