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
Received on Thu Feb 14 2002 - 09:15:48 CST
