Re: CHAINED ROWS

You also might need/want to disable triggers on the table.

Mike Killough wrote:
>
> Just one thing to add to the instructions to rebuild migrated rows. It says
> don't forget to disable any foreign key constraints. Don't forget the on
> delete cascade constraints too. I made that mistake once.
>
> Mike
>
> >From: "SARKAR, Samir" <Samir.SARKAR_at_nottingham.sema.slb.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: RE: CHAINED ROWS
> >Date: Wed, 09 Jan 2002 01:56:54 -0800
> >
> >Thanks Jacques.......forgot about the Index-organized tables.
> >
> >Samir
> >
> >Samir Sarkar
> >Oracle DBA - Lennon Team
> >SchlumbergerSema
> >Email : samir.sarkar_at_nottingham.sema.slb.com
> > samir.sarkar_at_sema.co.uk
> >Phone : +44 (0) 115 - 95 76217
> >EPABX : +44 (0) 115 - 957 6418 Ext. 76217
> >Fax : +44 (0) 115 - 957 6018
> >
> >-----Original Message-----
> >Sent: 08 January 2002 18:56
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >
> >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-----
> ><mailto:Samir.SARKAR_at_nottingham.sema.slb.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
> >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-----
> ><mailto:bunyamink_at_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.
> >___________________________________________________________________________
>
> _________________________________________________________________
> MSN Photos is the easiest way to share and print your photos:
> http://photos.msn.com/support/worldwide.aspx
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mike Killough
> INET: mwkillough_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: charliem_at_mwh.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).