| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CHAINED ROWS
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.
>___________________________________________________________________________
-- 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).Received on Wed Jan 09 2002 - 05:11:02 CST
 |
 |