--
-- Script for exporting production system statistics to test environment
-- Author: Maria Colgan maria.colgan@oracle.com (@SQLMaria)

connect sh/sh

set pages 9999
set lines 150
set echo on

-- Step 1 Create staging table for statistics

BEGIN
  dbms_stats.create_stat_table('SH','STATS_TAB_FROM_PROD');
END;
/

-- Step 2 Export the production statistics (including synopses for partition tables)
-- into staging table STATS_TABLE_FROM_PROD tag them with the id PROD_STATS_FOR_NOV

BEGIN
  dbms_stats.export_schema_stats('SH','STATS_TAB_FROM_PROD','PROD_STATS_FOR_NOV','SH','OBJECT_STATS,SYNOPSES');
END;
/

-- Step 3 Create a directory for the datapump dump file & grant the necessary permissions

create  or replace directory exp_dpump_dir as '/home/oracle/blog';

grant read, write on directory exp_dpump_dir to sh;


-- Step 4 Export the STATS_TAB_FROM_PROD from the production system

-- expdp sh/sh DIRECTORY=EXP_DPUMP_DIR DUMPFILE=prod_stats_for_nov.dmp LOGFILE=prod_stats_for_nov.log TABLES=sh.STATS_TAB_FROM_PROD

-- Step 5 Copy the prod_stats_for_nov.dmp file to the development environment

-- Step 6 Create an oracle directory that point to the datapump dump file

create or replace directory imp_dpump_dir as '/home/oracle/blog';
grant read, write on directory exp_dpump_dir to sh;

-- Step 7 Import the STATS_TAB_FROM_PROD into the test environment

-- impdp sh/sh DIRECTORY=IMP_DPUMP_DIR DUMPFILE=prod_stats_for_nov.dmp TABLES=sh.STATS_TAB_FROM_PROD

-- Step 8 Import the production statistis into the test environments data ditionary

BEGIN
  dbms_stats.import_schema_stats('STATS_TAB_FROM_PROD','PROD_STATS_FOR_NOV','SH',TRUE,TRUE,'OBJECT_STATS,SYNOPSES');
END;
/