If your chained rows are inside an Index-Organized table, be sure to read the Oracle manual on the "Analyze" command. There are two versions of the "chained-row" table, for index-organized tables you should create the "chained-row" table with universal rowids (head_rowid has datatype urowid). The "chained-row" table with universal rowids is the one named "...1.sql", i.e. has a 1 at the end of the file name.
-----Original Message-----
From: SARKAR, Samir [mailto:[EMAIL PROTECTED]]
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
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.
-----Original Message-----
From: Bunyamin K. Karadeniz [mailto:[EMAIL PROTECTED]]
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 ?
