If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The script has been tested and appears to work as intended. However, you should always test any script before relying on it.

PROOFREAD THIS SCRIPT PRIOR TO USING IT! Due to differences in the way text editors, email packages and operating systems handle text formatting (i.e., 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.

=========
Abstract:
=========

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!

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 EXAMPLE:
REM N/A
REM
REM -------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM --------------------------------------------------------------------------
REM Main text of script follows: