update : 29-04-2017

DBA Script : sp_auto_15.sql

-- | FILE : sp_auto_15.sql |
-- +----------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : sp_auto_15.sql |
-- | CLASS : Statspack |
-- | PURPOSE : This script is responsible to configure a DBMS Job to be run |
-- | every 15 minutes starting at the quarter of each hour. For |
-- | example, if this script is run at 10:32 pm, the first job will |
-- | be run at 10:45 pm, then the next job at 11:00 pm and so on |
-- | every 15 minutes. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
PROMPT
PROMPT =========================================================================
PROMPT The following script will create a new DBMS Job to be run at the quarter
PROMPT of each hour. (i.e. Every 15 minutes). The job will perform a Statspack
PROMPT snapshot using the Oracle supplied STATSPACK package.
PROMPT
PROMPT Note that this script should be run as the owner of the
PROMPT STATSPACK repository. (i.e. PERFSTAT)
PROMPT This script will PROMPT you for the PERFSTAT password.
PROMPT
PROMPT Also note that in order to submit and run a job, the init.ora parameter
PROMPT job_queue_processes must be set to a value greater than zero.
PROMPT =========================================================================
PROMPT
PROMPT Hit [ENTER] to continue or CTRL-C to cancel ...
pause
PROMPT Supply the password for the PERFSTAT user:
CONNECT perfstat
-- +------------------------------------------------------------------------+
-- | SCHEDULE A SNAPSHOT TO BE RUN EVERY 15 MINUTES. |
-- +------------------------------------------------------------------------+
VARIABLE jobno NUMBER;
VARIABLE instno NUMBER;
BEGIN
SELECT instance_number into :instno
FROM v$instance;
DBMS_JOB.SUBMIT( :jobno
, 'statspack.snap;'
, TRUNC(sysdate,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,'MI'))/15)+1)*15)/(24*60)
, 'TRUNC(sysdate,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,''MI''))/15)+1)*15)/(24*60)'
, TRUE
, :instno);
COMMIT;
END;
/
PROMPT
PROMPT
PROMPT +----------------------------------+
PROMPT | JOB NUMBER |
PROMPT |------------------------------------------------------------------+
PROMPT | The following job number should be noted as it will be required |
PROMPT | when modifying or removing PROMPT the job: |
PROMPT +------------------------------------------------------------------+
PROMPT
PRIMT jobno
PROMPT
PROMPT
PROMPT +----------------------------------+
PROMPT | JOB QUEUE PROCESS CONFIGURATION |
PROMPT |------------------------------------------------------------------+
PROMPT | Below is the current setting of the job_queue_processes init.ora |
PROMPT | parameter - the value for this parameter must be greater than 0 |
PROMPT | to use automatic statistics gathering: |
PROMPT +------------------------------------------------------------------+
PROMPT
SHOW PARAMETER job_queue_processes
PROMPT
PROMPT
PROMPT +----------------------------------+
PROMPT | NEXT SCHEDULED RUN |
PROMPT |------------------------------------------------------------------+
PROMPT | The next scheduled run for this job is: |
PROMPT +------------------------------------------------------------------+
PROMPT
SELECT job, next_date, next_sec
FROM user_jobs
WHERE job = :jobno;

End Script

Oracle Database Error Messages



Oracle Database High Availability Any organization evaluating a database solution for enterprise data must also evaluate the High Availability (HA) capabilities of the database. Data is one of the most critical business assets of an organization. If this data is not available and/or not protected, companies may stand to lose millions of dollars in business downtime as well as negative publicity. Building a highly available data infrastructure is critical to the success of all organizations in today's fast moving economy.

Well, the reason for above error is that i have taken the above script from a 11g database and running it on 10g database. 11g has bring some changes in password management. Below code is executed on 11g and user created successfully, which is expected result.