Identifying Tables with Chained Rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Identifying Tables with Chained Rows

  1. #1
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    I ran UTLBSTAT/ESTAT and in table fetch continued row i found 33000+ rows. This means chained rows/migrated rows.

    Now I need to identify the tables with can probebly hv chained rows. I hv 20,000+ tables in my application (SAP)

    What I hv done is that i hv taken a list of following fields form dba_tables

    TABLE_NAME
    PCT_FREE
    PCT_USED
    FREELISTS
    NUM_ROWS
    BLOCKS
    AVG_ROW_LEN

    I guess with the help of these I can shortlist further some tables on which i can run analyze table.... list chained rows to get the rows.

    Can I use the above info. Eg for two table values are as below :


    D010L
    10
    40
    1
    74632
    224296
    8076

    VRSMODISRC
    10
    40
    1
    218
    81
    2322


    Any Ideas ??

    Vijay
    --------------------------
    The Time has come ....

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Remove Chained Rows from a Table

    ===========
    Disclaimer:
    ===========

    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!


    =============
    Requirements:
    =============

    CREATE TABLE, INSERT/SELECT/DELETE privileges on the chained table

    ================
    Version Testing
    ================

    This script was tested on Oracle Versions 7.3.4.X, 8.0.5.X, 8.1.5.X

    =======
    Script:
    =======

    ----------- cut ---------------------- cut -------------- cut --------------

    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:

    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;


  3. #3
    Join Date
    May 2002
    Posts
    37
    You can get chained rows report by running following stored proc.

    CREATE OR REPLACE PROCEDURE RPT_CHAINED_ROW (v_schema in char)
    IS
    num_chained_rows NUMBER;
    target_table VARCHAR2(30);
    statement VARCHAR2(256);
    my_cursor INTEGER;
    ignore INTEGER;
    -- Define Cursor to LOOP Thru each user table
    CURSOR all_tables_cur IS
    SELECT table_name
    FROM dba_tables
    where owner = v_schema;

    BEGIN
    -- Increase Buffer Size
    dbms_output.enable(1000000);
    -- Print Title
    dbms_output.put_line(chr(10));
    dbms_output.put_line(chr(10)||'Chained Row(s) on User Tables for '||v_schema||' schema');
    dbms_output.put_line('----------------------------------------');
    -- Print Header
    DBMS_OUTPUT.PUT_LINE(RPAD('Table Name',20)||
    'Chained Row(s)');

    dbms_output.put_line('----------------------------------------');

    DELETE
    FROM CHAINED_ROWS;
    FOR t_name IN all_tables_cur
    LOOP
    target_table := RTRIM(t_name.table_name);
    -- Define SELECT statement for dynamic query
    statement := 'ANALYZE TABLE ' ||v_schema||'.'||target_table ||
    ' LIST CHAINED ROWS';
    my_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(my_cursor,statement,DBMS_SQL.V7);
    ignore := DBMS_SQL.EXECUTE(my_cursor);
    DBMS_SQL.CLOSE_CURSOR(my_cursor);
    SELECT COUNT(*)
    INTO num_chained_rows
    FROM CHAINED_ROWS
    WHERE table_name = target_table;

    DBMS_OUTPUT.PUT_LINE(RPAD(target_table,20)||
    num_chained_rows);

    END LOOP;

    END;


    /

  4. #4
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Hi,

    Thanks for the replies.

    What I meant was that do i need to analyze all the tables with LIST CHAINED ROWS OPTION.

    or i can use some other stuff.

    vijay
    --------------------------
    The Time has come ....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width