Identifying Tables with Chained Rows

[vijay8282]

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

[stecal]

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;

[dave777]

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;


/

[vijay8282]

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