SQL Script

Import Table/Index and Partiton Statistics

Updated: 31-Dec-2002
Version: 1.0

Description

Uses the DBMS_STATS package to import all statistics from a table to the data dictionary.

Parameters

1 - Stat Table Owner
2 - Stat Table Name

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set echo on
set feed on
set timing on
set verify off
set serveroutput on size 1000000

spool import_stats.log

ACCEPT sown PROMPT 'Enter Value for Stat Table Owner : '
ACCEPT stab PROMPT 'Enter Value for Stat Table Name : '

DECLARE

sql_stmt    VARCHAR2(1024);
l_stattab VARCHAR2(30) := '&stab';
l_statown VARCHAR2(12) := '&sown';

BEGIN

-- index partitions

FOR ind_rec IN (SELECT index_owner,index_name, partition_name
                FROM all_ind_partitions
                WHERE index_owner not in ('SYS','SYSTEM')
               ) LOOP

sql_stmt := 'BEGIN dbms_stats.import_index_stats (ownname=>:1, indname =>:2, partname=>:3, stattab=>:4,statown=>:5, statid=>:6); END;';

EXECUTE IMMEDIATE sql_stmt USING ind_rec.index_owner, ind_rec.index_name
                               , ind_rec.partition_name
                               , l_stattab, l_statown, 'IND_PART_STAT' ;

END LOOP;

-- table partitions (casade=True also means that local index partition stats are imported)

FOR part_rec IN (SELECT table_owner, table_name, partition_name
                FROM all_tab_partitions
                WHERE table_owner NOT IN ('SYS','SYSTEM')
               ) LOOP

sql_stmt := 'BEGIN dbms_stats.import_table_stats (ownname=>:1, tabname =>:2, partname=>:3, stattab=>:4,statown=>:5, statid=>:6, cascade=>TRUE); END;';

EXECUTE IMMEDIATE sql_stmt USING part_rec.table_owner, part_rec.table_name
                               , part_rec.partition_name
                               , l_stattab, l_statown, 'TAB_PART_STAT' ;

END LOOP;

-- table stats (omitting partitioned tables)

FOR tab_rec IN (SELECT owner, table_name
                FROM all_tables 
                WHERE owner not in ('SYS','SYSTEM')
                AND table_name NOT IN (select table_name 
                                      from dba_part_tables)
               ) LOOP

sql_stmt := 'BEGIN dbms_stats.import_table_stats (ownname=>:1, tabname =>:2, stattab=>:3,statown=>:4, statid=>:5, cascade=>TRUE); END;';

EXECUTE IMMEDIATE sql_stmt USING tab_rec.owner, tab_rec.table_name
                               , l_stattab, l_statown ,'TAB_STAT';

END LOOP;

-- table partitions (casade=True also means that local index partition stats are imported, same as earlier but from a different STATID)

FOR part_rec IN (SELECT table_owner, table_name, partition_name
                FROM all_tab_partitions
                WHERE table_owner NOT IN ('SYS','SYSTEM')
               ) LOOP

sql_stmt := 'BEGIN dbms_stats.import_table_stats (ownname=>:1, tabname =>:2, partname=>:3, stattab=>:4,statown=>:5, statid=>:6, cascade=>TRUE); END;';

EXECUTE IMMEDIATE sql_stmt USING part_rec.table_owner, part_rec.table_name
                               , part_rec.partition_name
                               , l_stattab, l_statown, 'TAB_STAT' ;

END LOOP;
END;
/

spool off

exit

Site Map

SQL Script

Import Table/Index and Partiton Statistics

Updated: 31-Dec-2002Version: 1.0

Description

Parameters

SQL Source

Related Links

Site Map

Updated: 31-Dec-2002
Version: 1.0