RE: chained rows

Hi,
If you have not already done so run the UTLCHAIN.SQL script in your RDBMSxx\ADMIN directory. This will create a table called CHAINED_ROWS. You then run ANALYZE on each table.

        ANALYZE TABLE <table name> LIST CHAINED ROWS.

All chained rows are copied into the CHAINED_ROWS table including the table name. You must do this in order to get rid of the chained rows (see Oracle Server Tuning Manual Chapter 9 under Allocating Space in Data Blocks). I believe there are some documents on Metalink and Technet.

You can spool the following query to a file and run it:         

	SET ECHO OFF
	SET TRIMSPOOL ON
	SET FEEDBACK OFF
	SET HEADING OFF
	SET PAGESIZE 0	
	SPOOL <path\filename.sql>
	SELECT 'ANALYZE TABLE '||table_name||' LIST CHAINED ROWS;'
	FROM all_tables
	WHERE owner='owner'
            AND ...
	SPOOL OFF
	SET ECHO ON
	SET TRINSPOOL OFF
	SET FEEDBACK ON
	SET HEADING ON
	SET PAGESIZE 20

-
Chris J. Guidry P.Eng.
ATCO Electric, Metering Services
Phone: (780) 420-4142
Fax: (780) 420-3854
Email: chris.guidry_at_atcoelectric.com

> -----Original Message-----
> From: Robin Li [SMTP:rli_at_nyp.org]
> Sent: Tuesday, June 13, 2000 12:22 PM
> To: Multiple recipients of list ORACLE-L
> Subject: chained rows
>
>
> -- Hi all,
>
> Our monitoring tool reports that there are chained rows in the database.
> We analyze the schema once a week and get no rows returned from:
>
> select owner, table_name, chain_cnt/num_rows*100 "chain%"
> from dba_tables
> where num_rows <> 0 and chain_cnt <> 0;
>
> The tech person from the monitoring tool's company told us that they
> determined chained rows by 'select value from v$sysstat where name
> ='table fetch continued row';
>
> We do have a value returned from that query:
>
> VALUE
> ----------
> 24132
>
> My question is: how to find out which table has the chained row?
>
>
> TIA
> Robin
>
> ****************************************************************
> * Robin Li *
> * Technical Specialist, DBA Phone#: (212) 297-3073 *
> * FCG Management Services Fax#: (212) 297-4231 *
> * 333 East 38th Street, 2nd fl. E-mail: rli_at_nyp.org *
> * New York, NY 10016 *
> ****************************************************************
> --
> Author: Robin Li
> INET: rli_at_nyp.org
>
> 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
Received on Tue Jun 13 2000 - 15:19:31 CDT