You can do it in 8i as well ... CREATE OR REPLACE PACKAGE BODY Admin_Index AS -- -- Define types TYPE itt_OWNER IS TABLE OF NCS_INDEX_STATS.owner%TYPE INDEX BY BINARY_INTEGER; TYPE itt_NAME IS TABLE OF NCS_INDEX_STATS.name%TYPE INDEX BY BINARY_INTEGER; TYPE itt_PARTITION_NAME IS TABLE OF NCS_INDEX_STATS.partition_name%TYPE INDEX BY BINARY_INTEGER; TYPE itt_HEIGHT IS TABLE OF NCS_INDEX_STATS.height%TYPE INDEX BY BINARY_INTEGER; TYPE itt_BLOCKS IS TABLE OF NCS_INDEX_STATS.blocks%TYPE INDEX BY BINARY_INTEGER; TYPE itt_LF_ROWS IS TABLE OF NCS_INDEX_STATS.lf_rows%TYPE INDEX BY BINARY_INTEGER; TYPE itt_LF_BLKS IS TABLE OF NCS_INDEX_STATS.lf_blks%TYPE INDEX BY BINARY_INTEGER; TYPE itt_LF_ROWS_LEN IS TABLE OF NCS_INDEX_STATS.lf_rows_len%TYPE INDEX BY BINARY_INTEGER; TYPE itt_LF_BLK_LEN IS TABLE OF NCS_INDEX_STATS.lf_blk_len%TYPE INDEX BY BINARY_INTEGER; TYPE itt_BR_ROWS IS TABLE OF NCS_INDEX_STATS.br_rows%TYPE INDEX BY BINARY_INTEGER; TYPE itt_BR_BLKS IS TABLE OF NCS_INDEX_STATS.br_blks%TYPE INDEX BY BINARY_INTEGER; TYPE itt_BR_ROWS_LEN IS TABLE OF NCS_INDEX_STATS.br_rows_len%TYPE INDEX BY BINARY_INTEGER; TYPE itt_BR_BLK_LEN IS TABLE OF NCS_INDEX_STATS.br_blk_len%TYPE INDEX BY BINARY_INTEGER; TYPE itt_DEL_LF_ROWS IS TABLE OF NCS_INDEX_STATS.del_lf_rows%TYPE INDEX BY BINARY_INTEGER; TYPE itt_DEL_LF_ROWS_LEN IS TABLE OF NCS_INDEX_STATS.del_lf_rows_len%TYPE INDEX BY BINARY_INTEGER; TYPE itt_DISTINCT_KEYS IS TABLE OF NCS_INDEX_STATS.distinct_keys%TYPE INDEX BY BINARY_INTEGER; TYPE itt_MOST_REPEATED_KEY IS TABLE OF NCS_INDEX_STATS.most_repeated_key%TYPE INDEX BY BINARY_INTEGER; TYPE itt_BTREE_SPACE IS TABLE OF NCS_INDEX_STATS.btree_space%TYPE INDEX BY BINARY_INTEGER; TYPE itt_USED_SPACE IS TABLE OF NCS_INDEX_STATS.used_space%TYPE INDEX BY BINARY_INTEGER; TYPE itt_PCT_USED IS TABLE OF NCS_INDEX_STATS.pct_used%TYPE INDEX BY BINARY_INTEGER; TYPE itt_ROWS_PER_KEY IS TABLE OF NCS_INDEX_STATS.rows_per_key%TYPE INDEX BY BINARY_INTEGER; TYPE itt_BLKS_GETS_PER_ACCESS IS TABLE OF NCS_INDEX_STATS.blks_gets_per_access%TYPE INDEX BY BINARY_INTEGER; TYPE itt_PRE_ROWS IS TABLE OF NCS_INDEX_STATS.pre_rows%TYPE INDEX BY BINARY_INTEGER; TYPE itt_PRE_ROWS_LEN IS TABLE OF NCS_INDEX_STATS.pre_rows_len%TYPE INDEX BY BINARY_INTEGER; TYPE itt_TIMESTAMP IS TABLE OF NCS_INDEX_STATS.timestamp%TYPE INDEX BY BINARY_INTEGER; TYPE itt_COLL_DURA IS TABLE OF NCS_INDEX_STATS.coll_dura%TYPE INDEX BY BINARY_INTEGER; -- -- Define a record of Table ... TYPE typeRecStats IS RECORD ( OWNER itt_OWNER ,NAME itt_NAME ,PARTITION_NAME itt_PARTITION_NAME ,HEIGHT itt_HEIGHT ,BLOCKS itt_BLOCKS ,LF_ROWS itt_LF_ROWS ,LF_BLKS itt_LF_BLKS ,LF_ROWS_LEN itt_LF_ROWS_LEN ,LF_BLK_LEN itt_LF_BLK_LEN ,BR_ROWS itt_BR_ROWS ,BR_BLKS itt_BR_BLKS ,BR_ROWS_LEN itt_BR_ROWS_LEN ,BR_BLK_LEN itt_BR_BLK_LEN ,DEL_LF_ROWS itt_DEL_LF_ROWS ,DEL_LF_ROWS_LEN itt_DEL_LF_ROWS_LEN ,DISTINCT_KEYS itt_DISTINCT_KEYS ,MOST_REPEATED_KEY itt_MOST_REPEATED_KEY ,BTREE_SPACE itt_BTREE_SPACE ,USED_SPACE itt_USED_SPACE ,PCT_USED itt_PCT_USED ,ROWS_PER_KEY itt_ROWS_PER_KEY ,BLKS_GETS_PER_ACCESS itt_BLKS_GETS_PER_ACCESS ,PRE_ROWS itt_PRE_ROWS ,PRE_ROWS_LEN itt_PRE_ROWS_LEN ,TIMESTAMP itt_TIMESTAMP ,COLL_DURA itt_COLL_DURA); -- -- Global table recStats typeRecStats; ...... PROCEDURE MOVE_STATS_TO_TABLE IS -- BEGIN FORALL i IN recStats.OWNER.first .. recStats.OWNER.last INSERT INTO NCS_INDEX_STATS ( OWNER ,NAME ,PARTITION_NAME ,HEIGHT ,BLOCKS ,LF_ROWS ,LF_BLKS ,LF_ROWS_LEN ,LF_BLK_LEN ,BR_ROWS ,BR_BLKS ,BR_ROWS_LEN ,BR_BLK_LEN ,DEL_LF_ROWS ,DEL_LF_ROWS_LEN ,DISTINCT_KEYS ,MOST_REPEATED_KEY ,BTREE_SPACE ,USED_SPACE ,PCT_USED ,ROWS_PER_KEY ,BLKS_GETS_PER_ACCESS ,PRE_ROWS ,PRE_ROWS_LEN ,TIMESTAMP ,COLL_DURA ) VALUES ( recStats.OWNER(i) ,recStats.NAME(i) ,recStats.PARTITION_NAME(i) ,recStats.HEIGHT(i) ,recStats.BLOCKS(i) ,recStats.LF_ROWS(i) ,recStats.LF_BLKS(i) ,recStats.LF_ROWS_LEN(i) ,recStats.LF_BLK_LEN(i) ,recStats.BR_ROWS(i) ,recStats.BR_BLKS(i) ,recStats.BR_ROWS_LEN(i) ,recStats.BR_BLK_LEN(i) ,recStats.DEL_LF_ROWS(i) ,recStats.DEL_LF_ROWS_LEN(i) ,recStats.DISTINCT_KEYS(i) ,recStats.MOST_REPEATED_KEY(i) ,recStats.BTREE_SPACE(i) ,recStats.USED_SPACE(i) ,recStats.PCT_USED(i) ,recStats.ROWS_PER_KEY(i) ,recStats.BLKS_GETS_PER_ACCESS(i) ,recStats.PRE_ROWS(i) ,recStats.PRE_ROWS_LEN(i) ,recStats.TIMESTAMP(i) ,recStats.COLL_DURA(i)); -- COMMIT; END MOVE_STATS_TO_TABLE; --
This is a snippet from my cron job that runs once a week to analyze all indexes and finds out the ones that need to be rebuilt. HTH Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
*********************************************************************2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *********************************************************************2
