Rem
Rem $Header: emll/admin/scripts/execocm.sql /main/9 2008/09/10 13:26:09 glavash Exp $
Rem
Rem execocm.sql
Rem
Rem Copyright (c) 2006, 2008, Oracle. All rights reserved.
Rem
Rem    NAME
Rem      execocm.sql - EXECute Oracle Configuration Manager job.
Rem
Rem    DESCRIPTION
Rem      This script submits and runs the database configuration collection
Rem      job as part of database creation.
Rem
Rem    NOTES
Rem      Create directory object for use by the job to create the configuration
Rem      file at.
Rem      This script should be run while connected as "SYS".
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glavash     08/20/08 - grant required prives to user
Rem    dkapoor     07/31/07 - remove stats job
Rem    dkapoor     05/04/07 - stop old job
Rem    dkapoor     01/04/07 - drop job before creating one
Rem    dkapoor     09/20/06 - give priv only if not given to public
Rem    dkapoor     09/13/06 - grant execute on dbms_scheduler
Rem    dkapoor     07/26/06 - do not use define 
Rem    dkapoor     07/21/06 - use create_replace_dir 
Rem    dkapoor     06/06/06 - move directory creation after installing the 
Rem                           packages 
Rem    dkapoor     05/23/06 - Created
Rem


-- Create directory object if higher than 9.0.1
DECLARE
  l_vers            v$instance.version%TYPE;
  l_dirobj_priv_cnt   NUMBER;
  l_priv_cnt   NUMBER;
BEGIN
   BEGIN
     select count(*) into l_priv_cnt from dba_tab_privs where 
       GRANTEE ='ORACLE_OCM' and TABLE_NAME='UTL_FILE' and 
       upper(PRIVILEGE) = 'EXECUTE';
     IF l_priv_cnt = 0 THEN
        -- Grant priv only if its not already given.
        execute immediate 'GRANT EXECUTE ON SYS.UTL_FILE TO ORACLE_OCM';
     END IF;

     select count(*) into l_priv_cnt from dba_tab_privs where 
       GRANTEE ='ORACLE_OCM' and TABLE_NAME='DBMS_SCHEDULER' and 
       upper(PRIVILEGE) = 'EXECUTE';
     IF l_priv_cnt = 0 THEN
        -- Grant priv only if its not given.
        execute immediate 'GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO ORACLE_OCM';
     END IF;

     ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
     select count(*) into l_dirobj_priv_cnt from dba_tab_privs where GRANTEE ='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR' and upper(PRIVILEGE) = 'READ';
     IF l_dirobj_priv_cnt = 0 THEN
      execute immediate 'GRANT READ ON DIRECTORY ORACLE_OCM_CONFIG_DIR TO ORACLE_OCM';
     END IF;
     select count(*) into l_dirobj_priv_cnt from dba_tab_privs where GRANTEE ='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR' and upper(PRIVILEGE) = 'WRITE';
     IF l_dirobj_priv_cnt = 0 THEN
       execute immediate 'GRANT WRITE ON DIRECTORY ORACLE_OCM_CONFIG_DIR TO ORACLE_OCM';
     END IF;
     COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
       raise_application_error(-20007,SQLERRM);
  END;
END;
/


-- remove old dba jobs, if exists
DECLARE
job_num NUMBER;
CURSOR job_cursor is
    SELECT job
    FROM dba_jobs
    WHERE schema_user = 'ORACLE_OCM'
    AND (what like 'ORACLE_OCM.MGMT_CONFIG.%' 
     OR what like 'ORACLE_OCM.MGMT_DB_LL_METRICS.%');
BEGIN
   FOR r in job_cursor LOOP
     sys.DBMS_IJOB.REMOVE(r.job);
     COMMIT;
   END LOOP;
END;
/

#Rem stop the job 
BEGIN
   BEGIN
     -- call to stop the job
     ORACLE_OCM.MGMT_CONFIG.stop_job;   
   EXCEPTION
     WHEN OTHERS THEN
       -- ignore any exception
       null;
  END;
END;
/

#Rem submit the job and run now
execute ORACLE_OCM.MGMT_CONFIG.submit_job;
execute ORACLE_OCM.MGMT_CONFIG.run_now;