統計情報の収集、バックアップとリストア

収集

$ cat pre_analyze.sql
set pagesize 0
set echo off
set heading off
set feedback off
set verify off
set timing off

spool analyze.sql

select 'spool analyze.log' from dual;

select 'analyze table '||table_name||' estimate statistics;'
from user_tables;

select 'analyze index '||index_name||' estimate statistics;'
from user_indexes;

select 'spool off' from dual;
select 'exit' from dual;

spool off
exit

$ cat analyze.sh
rm analyze.sql
sqlplus scott/tiger@orcl @pre_analyze.sql
sqlplus scott/tiger@orcl @analyze.sql

バックアップ

$ cat pre_stats_export.sql
set pagesize 0
set echo off
set heading off
set feedback off
set verify off
set timing off
set linesize 2000


spool stats_export.sql

select 'spool stats_export.log' from dual;

select 'execute dbms_stats.export_schema_stats (ownname => '''||
user||''' ,stattab => ''STATTAB'');'
from dual;

select 'spool off' from dual;
select 'exit' from dual;

spool off
exit


$ cat stats_export.sh
rm stats_export.sql
sqlplus scott/tiger@orcl @pre_stats_export.sql
sqlplus scott/tiger@orcl @stats_export.sql
exp scott/tiger FILE=stats.dmp TABLES=(STATTAB)

リストア

$ cat pre_stats_import.sql
set pagesize 0
set echo off
set heading off
set feedback off
set verify off
set timing off
set linesize 2000


spool stats_import.sql

select 'spool stats_import.log' from dual;

select 'execute dbms_stats.import_schema_stats (ownname => '''||
user||''' ,stattab => ''STATTAB'');'
from dual;

select 'spool off' from dual;
select 'exit' from dual;

spool off
exit


$ cat stats_import.sh
rm stats_import.sql
sqlplus scott/tiger@orcl @pre_stats_import.sql
sqlplus scott/tiger@orcl @stats_import.sql