SQL Script

Export Table, Index & Partition Statistics

Updated: 31-Dec-2002
Version: 1.0

Description

This script copies all the statistics captured using the DBMS_STATS package into a table. 

This then allows the table to be exported across another database to be imported. 

This means that any Explain plans will be consistent across the two databases. 

Before running this script, create the stats table as follows

SQL> execute dbms_stats.create_stat_table (ownname=>'MYUSER',stattab=>'MYSTATS'
,tblspace=>'USERS');

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 export_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

FOR tab_rec IN (SELECT owner, table_name
                FROM all_tables 
                WHERE owner NOT IN ('SYS','SYSTEM')
               ) LOOP

sql_stmt := 'BEGIN dbms_stats.export_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

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.export_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;

-- 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.export_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;

END;
/

spool off

exit

Site Map

SQL Script

Export Table, Index & Partition Statistics

Updated: 31-Dec-2002Version: 1.0

Description

Parameters

SQL Source

Related Links

Site Map

Updated: 31-Dec-2002
Version: 1.0