This script will remove chained rows from a table and is maintained for backward
compatibility purposes only.  If running Oracle8i and up, it is recommended to use the
"alter table ... move" command to reorganize a table to remove chained rows.

Please see Note:110246.1 - How to reorganize tables, for complete syntax.

Execution Environment:
     SQL, SQL*Plus

Access Privileges:
     Requires CREATE TABLE, INSERT/SELECT/DELETE privileges on the chained table.

Usage:
     sqlplus user/

Instructions:
     Copy the script to a file and execute it from SQL*Plus.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text 
editors, e-mail packages, and operating systems handle text formatting (spaces, 
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

The following script was adapted from a demo script which created a table with  
chained rows.  It then showed how to eliminate the chaining.  As modified, 
this script performs the following actions: 
 
	1.  Accepts a table name (which has chained rows) 
	2.  ANALYZEs the table and stores the rows in CHAINED_ROWS 
	3.  CREATEs AS SELECT a temporary table with the chained rows 
	4.  DELETEs the rows from the original table 
	5.  INSERTs the rows from the temp table back into the original 
 
This script will NOT help if the rows of the table are actually too large to  
fit in a single block.
 
All new scripts should be tested before use in a critical environment, but  
since this script actually modifies data in your database, TEST THIS SCRIPT ON  
A NON-PRODUCTION SERVER OR TABLE FIRST.

SET ECHO off 
REM NAME:   TFSCHAIN.SQL 
REM USAGE:"@path/tfschain chained_table" 
REM -------------------------------------------------------------------------- 
REM REQUIREMENTS: 
REM    CREATE TABLE, INSERT/SELECT/DELETE on the chained table 
REM    The following script was adapted from a demo script which created a 
REM    table with chained rows, then showed how to eliminate the chaining. 
REM    As modified, this script performs the following actions: 
REM 
REM	1.  Accepts a table name (which has chained rows) 
REM	2.  ANALYZEs the table and store the rows in CHAINED_ROWS 
REM	3.  CREATEs AS SELECT a temporary table with the chained rows 
REM	4.  DELETEs the rows from the original table 
REM	5.  INSERTs the rows from the temp table back into the original 
REM 
REM    This script will NOT work if the rows of the table are actually  
REM    too large to fit in a single block. 
REM -------------------------------------------------------------------------- 
REM Main text of script follows: 
 
set ECHO off  
  
ACCEPT chaintabl PROMPT 'Enter the table with chained rows: '  
  
drop table chaintemp;  
drop table chained_rows; 
  
start $ORACLE_HOME/rdbms/admin/utlchain  
 
set ECHO OFF 
  
REM  **********************************************  
REM  **********************************************  
REM  ANALYZE table to locate chained rows  
  
analyze table &chaintabl   
list chained rows into chained_rows;   
  
REM  **********************************************  
REM  **********************************************  
REM  CREATE Temporary table with the chained rows  
  
create table chaintemp as  
select *  
from &chaintabl  
where rowid in (select head_rowid  
		from chained_rows);  
  
REM  **********************************************  
REM  **********************************************  
REM  DELETE the chained rows from the original table  
  
delete from &chaintabl  
where rowid in (select head_rowid  
		from chained_rows); 
  
REM  **********************************************  
REM  **********************************************  
REM  INSERT the formerly chained rows back into table  
  
insert into &chaintabl  
select *  
from chaintemp; 
  
REM  **********************************************  
REM  **********************************************  
REM  DROP the temporary table  
  
drop table chaintemp;  
 

 
==============
Sample Output: 
==============

SQL> @savedscript
Enter the table with chained rows: empchain

Table created.

old   1: analyze table &chaintabl
new   1: analyze table empchain

Table analyzed.

old   3: from &chaintabl
new   3: from empchain

Table created.

old   1: delete from &chaintabl
new   1: delete from empchain

0 rows deleted.

old   1: insert into &chaintabl
new   1: insert into empchain

0 rows created.

Table dropped.


* Note that the example table did not have any chained rows.

EXCEPT WHERE EXPRESSLY PROVIDED OTHERWISE, THE INFORMATION, SOFTWARE,
PROVIDED ON AN "AS IS" AND "AS AVAILABLE" BASIS. ORACLE EXPRESSLY DISCLAIMS
ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT
LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
PURPOSE AND NON-INFRINGEMENT. ORACLE MAKES NO WARRANTY THAT: (A) THE RESULTS
THAT MAY BE OBTAINED FROM THE USE OF THE SOFTWARE WILL BE ACCURATE OR
RELIABLE; OR (B) THE INFORMATION, OR OTHER MATERIAL OBTAINED WILL MEET YOUR
EXPECTATIONS. ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE DOWNLOADED OR
OTHERWISE OBTAINED IS DONE AT YOUR OWN DISCRETION AND RISK. ORACLE SHALL HAVE
NO RESPONSIBILITY FOR ANY DAMAGE TO YOUR COMPUTER SYSTEM OR LOSS OF DATA THAT
RESULTS FROM THE DOWNLOAD OF ANY CONTENT, MATERIALS, INFORMATION OR SOFTWARE.

ORACLE RESERVES THE RIGHT TO MAKE CHANGES OR UPDATES TO THE SOFTWARE AT ANY
TIME WITHOUT NOTICE.

IN NO EVENT SHALL ORACLE BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
SPECIAL OR CONSEQUENTIAL DAMAGES, OR DAMAGES FOR LOSS OF PROFITS, REVENUE,
DATA OR USE, INCURRED BY YOU OR ANY THIRD PARTY, WHETHER IN AN ACTION IN
CONTRACT OR TORT, ARISING FROM YOUR ACCESS TO, OR USE OF, THE SOFTWARE.

SOME JURISDICTIONS DO NOT ALLOW THE LIMITATION OR EXCLUSION OF LIABILITY.
ACCORDINGLY, SOME OF THE ABOVE LIMITATIONS MAY NOT APPLY TO YOU.