DROP PACKAGE BODY ADRMGT.STATS_UTIL;

CREATE OR REPLACE PACKAGE BODY ADRMGT.STATS_UTIL

-- ###############################################################################
-- #  Description: 1750 - ADR create 1 table and 4 SPs for export/import stats
-- #
-- #   Usage:            Called by run_wrapper.sql
-- #   Input Parameters:
-- #     1) None
-- #   Output:
-- #     1) Terminal
-- #   Internal Script Parameters:
-- #     1) None
-- #
-- #
-- ###########################################################################
-- # Change History
-- #
-- # DATE       User Name         DESCRIPTION
-- # ---------- ----------------- -------------------------------------------------
-- # 01/30/2013 Richard Do        Created
-- #
-- #
-- ###########################################################################
AS


/************************************************************************/
/*                                                                        */
/* VerifyTable: This function is to validate table                      */
/*                                                                 */
/************************************************************************/

FUNCTION VerifyTable (
    p_schemaname IN VARCHAR2,
    p_tablename  IN VARCHAR2)

   RETURN NUMBER

IS
    VN_row_cnt NUMBER := 0;
BEGIN

   VC_msg_log := 'Verifing table existed or not.';

   SELECT COUNT(1)
       INTO vn_row_cnt
   FROM  all_tables
   WHERE table_name = UPPER(p_tablename)
   AND   owner      = UPPER(p_schemaname);

   RETURN vn_row_cnt;

   EXCEPTION

      WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE('EXCEPTION (VerifyTable SP): '||VC_msg_log||' - ERRMSG '||SQLERRM);


END VerifyTable;
/************************************************************************/
/*                                                                        */
/* VerifySchema: This function is to validate schema                      */
/*                                                                 */
/************************************************************************/

FUNCTION VerifySchema (p_schemaname IN VARCHAR2)

   RETURN NUMBER

IS

  VN_row_cnt NUMBER := 0;

BEGIN


    VC_msg_log := 'Verifing schema owner existed or not.';

   SELECT COUNT(1)
       INTO vn_row_cnt
   FROM all_tables
   WHERE owner = UPPER(p_schemaname)
   AND rownum <= 1;

   RETURN vn_row_cnt;

   EXCEPTION

      WHEN OTHERS THEN

           DBMS_OUTPUT.PUT_LINE('EXCEPTION (VerifySchema SP): '||VC_msg_log||' - ERRMSG '||SQLERRM);


END VerifySchema;

/************************************************************************************/
/*                                                                             */
/* CreateStatTable: This procedure is to create a staging table to hold stattistics */
/*               ..                                                     */
/************************************************************************************/
PROCEDURE CreateStatTable(
    p_stattab VARCHAR2,
    p_statowner VARCHAR2)

IS
VN_row_cnt NUMBER := 0;
BEGIN

    VC_msg_log := 'Verifing table existed or not.';

    SELECT COUNT(1)
         INTO vn_row_cnt
    FROM all_tables
    WHERE TABLE_NAME= VC_stattab
    AND owner = VC_stattab_owner;

    IF vn_row_cnt > 0 THEN

        DBMS_OUTPUT.PUT_LINE('INFORMATION - Staging table '||VC_stattab||' already existed.');

    ELSE

        VC_msg_log := 'Creating staging table '|| VC_stattab;

        DBMS_STATS.CREATE_STAT_TABLE(ownname =>VC_stattab_owner ,stattab => VC_stattab , tblspace => VC_stattab_tbs);

    END IF;


   EXCEPTION

       WHEN OTHERS THEN

         RAISE_APPLICATION_ERROR(-20035,'EXCEPTION (CreateStatTable SP): '||VC_msg_log||' - ERRMSG '||SQLERRM);


END CreateStatTable;


/************************************************************************************/
/*                                                            */
/* ExportTabStats: This procedure is to export statistics of a specified table to   */
/*       a staging table..                                     */
/************************************************************************************/
PROCEDURE ExportTabStats(
        p_ownname IN VARCHAR2,
        p_tabname IN VARCHAR2)


IS

BEGIN

  VC_msg_log := 'Begin export table stats.';

  DBMS_STATS.EXPORT_TABLE_STATS(ownname => p_ownname, tabname => p_tabname, stattab => VC_stattab, statown => VC_stattab_owner, cascade => true);


  EXCEPTION

       WHEN OTHERS THEN

          RAISE_APPLICATION_ERROR(-20036,'EXCEPTION (ExportTabStats SP): '||VC_msg_log||' - ERRMSG '||SQLERRM);

END ExportTabStats;

/*************************************************************************************/
/*                                                                  */
/* ImportTabStats: This procedure is to import statistics of to a specified table  */
/*        .                                              */
/*************************************************************************************/

PROCEDURE ImportTabStats(p_ownname IN VARCHAR2, p_tabname IN VARCHAR2)

IS


BEGIN

   VC_msg_log := 'Begin importing table stats.';

   DBMS_STATS.IMPORT_TABLE_STATS(ownname => p_ownname, tabname => p_tabname, stattab => VC_stattab, statown => VC_stattab_owner, cascade => true);


   EXCEPTION

       WHEN OTHERS THEN

         RAISE_APPLICATION_ERROR(-20038,'EXCEPTION (ImportTabStats SP): '||VC_msg_log||' - ERRMSG '||SQLERRM);

END ImportTabStats;


/****************************************************************************/
/*                                                         		    */
/* ExportSchemaStats: This procedure is to export statistics of a schema    */
/*                                             				    */
/****************************************************************************/

PROCEDURE ExportSchemaStats(p_schemaname IN VARCHAR2)

IS

BEGIN

   VC_msg_log := 'Begin exporting schema stats.';

   DBMS_STATS.EXPORT_SCHEMA_STATS(ownname=> p_schemaname, stattab => VC_stattab, statown => VC_stattab_owner);


EXCEPTION

       WHEN OTHERS THEN

           RAISE_APPLICATION_ERROR(-20050,'EXCEPTION (ExportSchemaStats SP): '||VC_msg_log||' - ERRMSG '||SQLERRM);


END ExportSchemaStats;

/****************************************************************************/
/*                                                                          */
/* ImportSchemaStats: This procedure is to import statistics of a schema    */
/*                                                                          */
/****************************************************************************/

PROCEDURE ImportSchemaStats(p_schemaname IN VARCHAR2)

IS


BEGIN

   VC_msg_log := 'Begin importing schema stats.';

   DBMS_STATS.IMPORT_SCHEMA_STATS(ownname => p_schemaname, stattab => VC_stattab, statown => VC_stattab_owner);


EXCEPTION

    WHEN OTHERS THEN

       RAISE_APPLICATION_ERROR(-20055,'EXCEPTION (ImportSchemaStats SP): '||VC_msg_log||' - ERRMSG '||SQLERRM);

END ImportSchemaStats;

END STATS_UTIL;
/


CREATE OR REPLACE PUBLIC SYNONYM STATS_UTIL FOR ADRMGT.STATS_UTIL;


GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ADR;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ADRAAP;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ADREXT;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ADRFILE;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ADRREF;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ADRTMP;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO BENS;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO CIS;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO EESRV;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ESRJMS;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO IDHADM02;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO LOBR;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO ODM;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO PSIM;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO VBR;

GRANT EXECUTE ON ADRMGT.STATS_UTIL TO VOA;
