Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\scripts\db\config\spset.sql

-----------------------------------------------------
-- This script is called after statspack is installed
-----------------------------------------------------
whenever sqlerror exit sql.sqlcode

set echo on;

DECLARE
  iserr BOOLEAN; 
  v VARCHAR2(200);
  v_db_version   VARCHAR2(10);
  db_version_9i  CONSTANT VARCHAR2(10) := '09.0.0.0.0';
  jobno   BINARY_INTEGER;
  ejobno  NUMBER;
  n_dbid  NUMBER;
  n_snap_level NUMBER;
  
BEGIN
   iserr := FALSE;

---------------------------------------------------------------------
   -- Privileges: add statspack objects access privileges to oem_monitor  
   ---------------------------------------------------------------------
   BEGIN
      v := 'GRANT EXECUTE ON statspack TO oem_monitor';
      execute immediate v; 
   EXCEPTION
      when others then  
         iserr := TRUE;
         dbms_output.put_line(SQLERRM);
   END;

BEGIN
     FOR rec IN (
        SELECT 'grant select on ' || table_name ||' to oem_monitor' priv
	  FROM user_tables )
     LOOP	 
       BEGIN
           v := rec.priv;      
	   execute immediate v; 
       EXCEPTION
	  when others then  
             iserr := TRUE;
             dbms_output.put_line(SQLERRM);
       END;
     END LOOP;	   	
   EXCEPTION
      when others then  
        iserr := TRUE;
        dbms_output.put_line(SQLERRM);
   END;

---------------------------
   -- Set the collection level 
   ---------------------------
   BEGIN 
      SELECT LPAD(version, 10, '0') INTO v_db_version FROM v$instance; 
      
      IF (v_db_version >= db_version_9i) THEN
      -- Get the database id
      SELECT dbid INTO n_dbid FROM v$database;
      
      -- For each instance, see if we need to set the snap_level
      FOR instRec IN (SELECT instance_number FROM gv$instance) LOOP
         BEGIN 
            -- Get the snap level of the instance
            SELECT snap_level INTO n_snap_level FROM stats$statspack_parameter 
                WHERE dbid = n_dbid AND instance_number = instRec.instance_number;
         EXCEPTION                 
            when no_data_found then
               n_snap_level := 0;
         END;
   
         -- If snap_level is less than 6, we bring it up to 6
         IF (n_snap_level < 6) THEN
            STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=>6, i_modify_parameter=>'true', i_dbid=>n_dbid, i_instance_number=>instRec.instance_number);
         END IF;   
      END LOOP;   
      END IF;
   EXCEPTION
      when others then  
        iserr := TRUE;
        dbms_output.put_line(SQLERRM);
   END;

-----------------------
   -- Automatic Collection   
   -----------------------
   --
   --  Schedule a snapshot to be run on every instance in gv$instance every hour, on the hour
   BEGIN

FOR instRec IN (SELECT instance_number FROM gv$instance) LOOP

-- If the job already exists
       SELECT count(job) INTO ejobno FROM sys.dba_jobs 
            WHERE lower(what) LIKE '%statspack.snap%' AND instance = instRec.instance_number;
 
       IF (ejobno <= 0) THEN
       -- Not existing, submit a job for the instance
         dbms_job.submit(jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 
                'trunc(SYSDATE+&&cinterval/24,''HH'')', TRUE, instRec.instance_number);
       END IF;        
    END LOOP;   
     
   EXCEPTION
      when others then  
        iserr := TRUE;
        dbms_output.put_line(SQLERRM);
   END;
  
   commit;
   IF iserr THEN
       raise_application_error(-20001, 'Statspack Config failed');
   end if; 
 
END;
/

prompt
prompt  Job queue process
prompt  ~~~~~~~~~~~~~~~~~
prompt  Below is the current setting of the job_queue_processes init.ora
prompt  parameter - the value for this parameter must be greater 
prompt  than 0 to use automatic statistics gathering:
show parameter job_queue_processes
prompt

disconnect

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de