From Samir.SARKAR@nottingham.sema.slb.com Wed, 09 Jan 2002 02:13:00 -0800
From: "SARKAR, Samir" <Samir.SARKAR@nottingham.sema.slb.com>
Date: Wed, 09 Jan 2002 02:13:00 -0800
Subject: RE: CHAINED ROWS
Message-ID: <F001.003EACEC.20020109015654@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: RE: CHAINED ROWS



Thanks Jacques.......forgot 
about the Index-organized tables. 
 
Samir
 
Samir Sarkar 
Oracle DBA - Lennon 
Team Schlumberger<FONT 
face=Impact color=#800000>Sema 
Email :  
samir.sarkar@nottingham.sema.slb.com <FONT face=Verdana 
color=#000080 
size=1>            
samir.sarkar@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: Jacques Kilchoer 
  [mailto:Jacques.Kilchoer@quest.com]Sent: 08 January 2002 
  18:56To: Multiple recipients of list ORACLE-LSubject: 
  RE: CHAINED ROWS
  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 [<A 
  href="mailto:Samir.SARKAR@nottingham.sema.slb.com">mailto:Samir.SARKAR@nottingham.sema.slb.com] 
  
  If Oracle 8i is ur current version, run the utlchain1.sql 
  script available in ur ORACLE_HOME/rdbms/admin <FONT 
  size=2>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; <FONT 
  size=2>  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 <FONT 
  size=2>import the data back.   <FONT 
  size=2>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>' <FONT 
  size=2> and owner_name = '<owner_name>'); <FONT 
  size=2>  Then delete the chained rows from the 
  main table in the following way :   
   delete from <table_name> <FONT 
  size=2> where rowid in (select head_rowid from 
  chained_rows where table_name = '<table_name>' <FONT 
  size=2> and owner_name = '<owner_name>'); <FONT 
  size=2>  Next, re-insert the chained rows into 
  the table :   insert into 
  table_name     select * from 
  <temp_table_name>;   <FONT 
  size=2>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----- <FONT 
  size=2>From: Bunyamin K. Karadeniz [<A 
  href="mailto:bunyamink@havelsan.com.tr">mailto:bunyamink@havelsan.com.tr] 
  
  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 ? 

___________________________________________________________________________
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.
___________________________________________________________________________