update : 28-06-2017

DBA Script : sp_statspack_custom_pkg_9i.sql

-- | FILE : sp_statspack_custom_pkg_9i.sql |
-- +-------------------------------------------------------------------------------------------------+
-- | |
-- | |
-- | www.high-oracle.com |
-- |-------------------------------------------------------------------------------------------------|
-- | www.high-oracle.com |
-- |-------------------------------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : sp_statspack_custom_pkg_9i.sql |
-- | CLASS : Statspack |
-- | PURPOSE : Custom package to be used in managing Statspack. This version has been modified to |
-- | work with Oracle9i. |
-- | |
-- | EXAMPLES: |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Perform a Statspack Snapshot. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.snap; |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Schedule a snapshot job to run once every 15 minutes starting at the next 15 minute interval. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.snap_schedule( |
-- | 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)'); |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Schedule a snapshot job to run once every 5 minutes starting at the next 5 minute interval. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.snap_schedule_5; |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Schedule a snapshot job to run once every 15 minutes starting at the next 15 minute interval. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.snap_schedule_15; |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Schedule a snapshot job to run once every 30 minutes starting at the next 30 minute interval. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.snap_schedule_30; |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Purge all snapshots older than 30 days. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.purge(30); |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Schedule a purge job to run once a day and purge snapshots older than 30 days. The job will |
-- | start at the begining of the next hour. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.purge_schedule(30, trunc(sysdate+1/24,'HH'), 'SYSDATE+1'); |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Schedule a purge job to run once a day at midnight to purge snapshots older than 30 days. The |
-- | job will start at the next midnight and continue to run daily at midnight. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.purge_schedule(30, trunc(sysdate+1), 'SYSDATE+1'); |
-- | |
-- | ----------------------------------------------------------------------------------------------- |
-- | Schedule a purge job to run once every day at midnight, removing snapshot records older than 30 |
-- | days. The job will start at the next midnight and continue to run daily at midnight. |
-- | ----------------------------------------------------------------------------------------------- |
-- | statspack_custom.purge_schedule_midnight(30); |
-- | |
-- | NOTE : As with any code, ensure to test this script in a development environment before |
-- | attempting to run it in production. |
-- +-------------------------------------------------------------------------------------------------+
set feedback on
set timing on
set verify on
set serveroutput on
spool sp_statspack_custom_9i.lst
prompt
prompt
prompt COMPILING statspack_custom (specification)...
prompt ============================================
prompt
set termout off
-- +----------------------------------------------------------------------------+
-- | ************************************************************************** |
-- | * *** PACKAGE SPECIFICATION *** * |
-- | * * |
-- | * statspack_custom * |
-- | ************************************************************************** |
-- +----------------------------------------------------------------------------+
CREATE OR REPLACE PACKAGE statspack_custom
IS
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap |
-- | |
-- | Wrapper procedure (wraps the original snap() |
-- | procedure in the STATSPACK package) used to |
-- | perform a Statspack snapshot. |
-- +-----------------------------------------------------------+
PROCEDURE snap;
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule( in_start_date IN DATE
, in_interval IN VARCHAR2
);
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule_5 |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call every 5 minutes and |
-- | starts at the next 5 minute interval. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule_5;
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule_15 |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call every 15 minutes and |
-- | starts at the next 15 minute interval. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule_15;
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule_30 |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call every 30 minutes and |
-- | starts at the next 30 minute interval. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule_30;
-- +-----------------------------------------------------------+
-- | PROCEDURE - purge |
-- | |
-- | Used to purge old records from the Statspack |
-- | repository. |
-- +-----------------------------------------------------------+
PROCEDURE purge(in_days_older_than IN INTEGER);
-- +-----------------------------------------------------------+
-- | PROCEDURE - purge_schedule |
-- | |
-- | Utility procedure used to schedule the |
-- | "purge()" procedure - removing a given number |
-- | of obsolete records by the provided number of |
-- | days parameter. |
-- +-----------------------------------------------------------+
PROCEDURE purge_schedule( in_days_older_than IN INTEGER
, in_start_date IN DATE
, in_interval IN VARCHAR2
);
-- +-----------------------------------------------------------+
-- | PROCEDURE - purge_schedule_midnight |
-- | |
-- | Utility procedure used to schedule the |
-- | "purge()" procedure to run every day at |
-- | midnight - removing a given number of |
-- | obsolete records by the provided number of |
-- | days parameter. |
-- +-----------------------------------------------------------+
PROCEDURE purge_schedule_midnight(in_days_older_than IN INTEGER);
END statspack_custom;
/
set termout on
show errors
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
prompt
prompt
prompt COMPILING statspack_custom (body)...
prompt ============================================
prompt
set termout off
-- +----------------------------------------------------------------------------+
-- | ************************************************************************** |
-- | * *** PACKAGE BODY *** * |
-- | * * |
-- | * statspack_custom * |
-- | ************************************************************************** |
-- +----------------------------------------------------------------------------+
CREATE OR REPLACE PACKAGE BODY statspack_custom
IS
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap |
-- | |
-- | Wrapper procedure (wraps the original snap() |
-- | procedure in the STATSPACK package) used to |
-- | perform a Statspack snapshot. |
-- +-----------------------------------------------------------+
PROCEDURE snap
IS
BEGIN
statspack.snap;
commit;
END;
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule( in_start_date IN DATE
, in_interval IN VARCHAR2)
IS
v_ModuleName VARCHAR2(48);
v_ActionContext VARCHAR2(100);
v_ErrorMessage VARCHAR2(1000);
v_JobNumber NUMBER;
v_InstanceNumber NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- -------------------------------------------------------------------------
-- Register and initialize program module plus set the Client Info
-- -------------------------------------------------------------------------
v_ModuleName := 'statspack_custom.snap_schedule';
v_ActionContext := 'Begin';
DBMS_APPLICATION_INFO.SET_MODULE( module_name => v_ModuleName
, action_name => v_ActionContext);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => 'PERFSTAT');
-- -------------------------------------------------------------------------
-- Get instance number
-- -------------------------------------------------------------------------
v_ActionContext := 'Get instance number';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
SELECT instance_number INTO v_InstanceNumber FROM v$instance;
-- -------------------------------------------------------------------------
-- Submit job
-- -------------------------------------------------------------------------
v_ActionContext := 'Submit job';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
DBMS_JOB.SUBMIT( job => v_JobNumber
, what => 'statspack_custom.snap;'
, next_date => in_start_date
, interval => in_interval
, no_parse => TRUE
, instance => v_InstanceNumber);
-- -------------------------------------------------------------------------
-- Successful end to module.
-- -------------------------------------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE( module_name => null
, action_name => null);
COMMIT;
EXCEPTION
WHEN others THEN
v_ErrorMessage := sqlerrm;
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'ERROR: ' || v_ActionContext);
RAISE_APPLICATION_ERROR(-20000, v_ActionContext || chr(10) || v_ErrorMessage);
END;
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule_5 |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call every 5 minutes and |
-- | starts at the next 5 minute interval. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule_5
IS
v_ModuleName VARCHAR2(48);
v_ActionContext VARCHAR2(100);
v_ErrorMessage VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- -------------------------------------------------------------------------
-- Register and initialize program module plus set the Client Info
-- -------------------------------------------------------------------------
v_ModuleName := 'statspack_custom.snap_schedule_5';
v_ActionContext := 'Begin';
DBMS_APPLICATION_INFO.SET_MODULE( module_name => v_ModuleName
, action_name => v_ActionContext);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => 'PERFSTAT');
-- -------------------------------------------------------------------------
-- Submit job to run every 5 minutes
-- -------------------------------------------------------------------------
v_ActionContext := 'Submit job every 5 minutes';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
statspack_custom.snap_schedule(
TRUNC(sysdate,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,'MI'))/5)+1)*5)/(24*60)
, 'TRUNC(sysdate,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,''MI''))/5)+1)*5)/(24*60)');
-- -------------------------------------------------------------------------
-- Successful end to module.
-- -------------------------------------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE( module_name => null
, action_name => null);
COMMIT;
EXCEPTION
WHEN others THEN
v_ErrorMessage := sqlerrm;
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'ERROR: ' || v_ActionContext);
RAISE_APPLICATION_ERROR(-20000, v_ActionContext || chr(10) || v_ErrorMessage);
END;
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule_15 |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call every 15 minutes and |
-- | starts at the next 15 minute interval. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule_15
IS
v_ModuleName VARCHAR2(48);
v_ActionContext VARCHAR2(100);
v_ErrorMessage VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- -------------------------------------------------------------------------
-- Register and initialize program module plus set the Client Info
-- -------------------------------------------------------------------------
v_ModuleName := 'statspack_custom.snap_schedule_15';
v_ActionContext := 'Begin';
DBMS_APPLICATION_INFO.SET_MODULE( module_name => v_ModuleName
, action_name => v_ActionContext);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => 'PERFSTAT');
-- -------------------------------------------------------------------------
-- Submit job to run every 15 minutes
-- -------------------------------------------------------------------------
v_ActionContext := 'Submit job every 15 minutes';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
statspack_custom.snap_schedule(
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)');
-- -------------------------------------------------------------------------
-- Successful end to module.
-- -------------------------------------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE( module_name => null
, action_name => null);
COMMIT;
EXCEPTION
WHEN others THEN
v_ErrorMessage := sqlerrm;
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'ERROR: ' || v_ActionContext);
RAISE_APPLICATION_ERROR(-20000, v_ActionContext || chr(10) || v_ErrorMessage);
END;
-- +-----------------------------------------------------------+
-- | PROCEDURE - snap_schedule_30 |
-- | |
-- | Utility procedure used to schedule a |
-- | "snap()" procedure call every 30 minutes and |
-- | starts at the next 30 minute interval. |
-- +-----------------------------------------------------------+
PROCEDURE snap_schedule_30
IS
v_ModuleName VARCHAR2(48);
v_ActionContext VARCHAR2(100);
v_ErrorMessage VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- -------------------------------------------------------------------------
-- Register and initialize program module plus set the Client Info
-- -------------------------------------------------------------------------
v_ModuleName := 'statspack_custom.snap_schedule_30';
v_ActionContext := 'Begin';
DBMS_APPLICATION_INFO.SET_MODULE( module_name => v_ModuleName
, action_name => v_ActionContext);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => 'PERFSTAT');
-- -------------------------------------------------------------------------
-- Submit job to run every 30 minutes
-- -------------------------------------------------------------------------
v_ActionContext := 'Submit job every 30 minutes';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
statspack_custom.snap_schedule(
TRUNC(sysdate,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,'MI'))/30)+1)*30)/(24*60)
, 'TRUNC(sysdate,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(sysdate,''MI''))/30)+1)*30)/(24*60)');
-- -------------------------------------------------------------------------
-- Successful end to module.
-- -------------------------------------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE( module_name => null
, action_name => null);
COMMIT;
EXCEPTION
WHEN others THEN
v_ErrorMessage := sqlerrm;
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'ERROR: ' || v_ActionContext);
RAISE_APPLICATION_ERROR(-20000, v_ActionContext || chr(10) || v_ErrorMessage);
END;
-- +-----------------------------------------------------------+
-- | PROCEDURE - purge |
-- | |
-- | Used to purge old records from the Statspack |
-- | repository. |
-- +-----------------------------------------------------------+
PROCEDURE purge(in_days_older_than IN INTEGER)
IS
e_NoRecordsFound EXCEPTION;
v_ModuleName VARCHAR2(48);
v_ActionContext VARCHAR2(100);
v_ErrorMessage VARCHAR2(1000);
v_DbId sys.v_$database.dbid%TYPE;
v_DbName sys.v_$database.name%TYPE;
v_InstanceNumber sys.v_$instance.instance_number%TYPE;
v_InstanceName sys.v_$instance.instance_name%TYPE;
v_LowSnapId NUMBER;
v_HiSnapId NUMBER;
v_BeginTime DATE;
v_EndTime DATE;
v_tempRowCount NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- -------------------------------------------------------------------------
-- Register and initialize program module plus set the Client Info
-- -------------------------------------------------------------------------
v_ModuleName := 'statspack_custom.purge';
v_ActionContext := 'Begin';
DBMS_APPLICATION_INFO.SET_MODULE( module_name => v_ModuleName
, action_name => v_ActionContext);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => 'PERFSTAT');
-- -------------------------------------------------------------------------
-- Get database and instance currently connected to. This will be used later
-- in the report along with other metadata to lookup snapshots.
-- -------------------------------------------------------------------------
v_ActionContext := 'Gather instance / DB information';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
SELECT
d.dbid
, d.name
, i.instance_number
, i.instance_name
INTO
v_DbId
, v_DbName
, v_InstanceNumber
, v_InstanceName
FROM
v$database d
, v$instance i;
DBMS_OUTPUT.PUT_LINE('v_DbId : ' || v_DbId);
DBMS_OUTPUT.PUT_LINE('v_DbName : ' || v_DbName);
DBMS_OUTPUT.PUT_LINE('v_InstanceNumber : ' || v_InstanceNumber);
DBMS_OUTPUT.PUT_LINE('v_InstanceName : ' || v_InstanceName);
-- -------------------------------------------------------------------------
-- Obtain the MIN and MAX Snapshot IDs to be removed from the range of IDs
-- order than "days_to_purge days".
-- -------------------------------------------------------------------------
v_ActionContext := 'Gather MIN/MAX Snapshot IDs';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
SELECT
NVL(MIN(snap_id),0)
, NVL(MAX(snap_id),0)
INTO
v_LowSnapId
, v_HiSnapId
FROM stats$snapshot
WHERE snap_time < (sysdate - in_days_older_than);
DBMS_OUTPUT.PUT_LINE('v_LowSnapId : ' || v_LowSnapId);
DBMS_OUTPUT.PUT_LINE('v_HiSnapId : ' || v_HiSnapId);
-- -------------------------------------------------------------------------
-- Check if any Statspack Records exist for the given number of days old.
-- -------------------------------------------------------------------------
v_ActionContext := 'Checking HiSnapId > 0';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
IF (v_HiSnapId = 0) THEN
v_ActionContext := 'No snapshots older than ' || in_days_older_than || ' days in STATS$SNAPSHOT';
DBMS_OUTPUT.PUT_LINE('No snapshots found older than ' || in_days_older_than || ' days in STATS$SNAPSHOT');
RAISE e_NoRecordsFound;
END IF;
-- -------------------------------------------------------------------------
-- Get begin and end snapshot times - these are used to delete undostat.
-- -------------------------------------------------------------------------
v_ActionContext := 'Get Begin / End SS Times';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
SELECT b.snap_time
INTO v_BeginTime
FROM stats$snapshot b
WHERE b.snap_id = v_LowSnapId
AND b.dbid = v_DbId
AND b.instance_number = v_InstanceNumber;
SELECT e.snap_time
INTO v_EndTime
FROM stats$snapshot e
WHERE e.snap_id = v_HiSnapId
AND e.dbid = v_DbId
AND e.instance_number = v_InstanceNumber;
DBMS_OUTPUT.PUT_LINE('v_BeginTime : ' || TO_CHAR(v_BeginTime, 'MM/DD/YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('v_EndTime : ' || TO_CHAR(v_EndTime, 'MM/DD/YYYY HH24:MI:SS'));
-- -------------------------------------------------------------------------
-- Deleting obsolete Statspack records.
-- -------------------------------------------------------------------------
v_ActionContext := 'Deleting obsolete SP records';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
DELETE FROM stats$snapshot
WHERE instance_number = v_InstanceNumber
AND dbid = v_DbId
AND snap_id between v_LowSnapId and v_HiSnapId;
v_tempRowCount := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('stats$snapshot DELETE %ROWCOUNT : ' || v_tempRowCount);
-- -------------------------------------------------------------------------
-- Delete any dangling SQLtext. The following statement deletes any dangling
-- SQL statements which are no longer referred to by ANY snapshots. By
-- default, Oracle comments this statement out as it can be very resource
-- intensive.
-- -------------------------------------------------------------------------
v_ActionContext := 'Delete dangling SQLtext';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
EXECUTE IMMEDIATE 'ALTER SESSION SET hash_area_size=1048576';
DELETE --+ index_ffs(st)
FROM stats$sqltext st
WHERE
( hash_value
, text_subset
)
NOT IN
( select --+ hash_aj full(ss) no_expand
hash_value
, text_subset
from stats$sql_summary ss
where ( (
snap_id < v_LowSnapId
or
snap_id > v_HiSnapId
)
and dbid = v_DbId
and instance_number = v_InstanceNumber
)
or
(
dbid != v_DbId
or
instance_number != v_InstanceNumber
)
);
v_tempRowCount := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('stats$sqltext DELETE %ROWCOUNT : ' || v_tempRowCount);
-- -------------------------------------------------------------------------
-- (OPTIONAL) - STATS$SEG_STAT_OBJ delete statement
-- -------------------------------------------------------------------------
v_ActionContext := 'Delete STATS$SEG_STAT_OBJ';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
DELETE --+ index_ffs(sso)
FROM stats$seg_stat_obj sso
WHERE ( dbid
, dataobj#
, obj#
)
NOT IN
(
select --+ hash_aj full(ss) no_expand
dbid
, dataobj#
, obj#
from
stats$seg_stat ss
where ( ( snap_id < v_LowSnapId
or
snap_id > v_HiSnapId
)
and dbid = v_DbId
and instance_number = v_InstanceNumber
)
or
( dbid != v_DbId
or
instance_number != v_InstanceNumber
)
);
v_tempRowCount := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('stats$seg_stat_obj DELETE %ROWCOUNT : ' || v_tempRowCount);
-- -------------------------------------------------------------------------
-- Delete from stats$undostat - Undostat rows that cover the snap times
-- -------------------------------------------------------------------------
v_ActionContext := 'Delete from stats$undostat';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
DELETE from stats$undostat
WHERE dbid = v_DbId
AND instance_number = v_InstanceNumber
AND end_time < v_EndTime;
v_tempRowCount := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('stats$undostat DELETE %ROWCOUNT : ' || v_tempRowCount);
-- -------------------------------------------------------------------------
-- Delete from stats$database_instance.
-- Dangling database instance rows for that startup time
-- -------------------------------------------------------------------------
v_ActionContext := 'Delete from stats$database_instance';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
DELETE from stats$database_instance di
WHERE instance_number = v_InstanceNumber
AND dbid = v_DbId
AND NOT EXISTS (select 1
from stats$snapshot s
where s.dbid = di.dbid
and s.instance_number = di.instance_number
and s.startup_time = di.startup_time);
v_tempRowCount := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('stats$database_instance DELETE %ROWCOUNT : ' || v_tempRowCount);
-- -------------------------------------------------------------------------
-- Delete from stats$statspack_parameter
-- Dangling statspack parameter rows for the database / instance
-- -------------------------------------------------------------------------
v_ActionContext := 'Delete from stats$statspack_parameter';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
DELETE from stats$statspack_parameter sp
WHERE instance_number = v_InstanceNumber
AND dbid = v_DbId
AND NOT EXISTS (select 1
from stats$snapshot s
where s.dbid = sp.dbid
and s.instance_number = sp.instance_number);
v_tempRowCount := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('stats$statspack_parameter DELETE %ROWCOUNT : ' || v_tempRowCount);
-- -------------------------------------------------------------------------
-- Successful end to module.
-- -------------------------------------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE( module_name => null
, action_name => null);
COMMIT;
EXCEPTION
WHEN e_NoRecordsFound THEN
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'WARNING: ' || v_ActionContext);
WHEN others THEN
v_ErrorMessage := sqlerrm;
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'ERROR: ' || v_ActionContext);
RAISE_APPLICATION_ERROR(-20000, v_ActionContext || chr(10) || v_ErrorMessage);
END;
-- +-----------------------------------------------------------+
-- | PROCEDURE - purge_schedule |
-- | |
-- | Utility procedure used to schedule the |
-- | "purge()" procedure - removing a given number |
-- | of obsolete records by the provided number of |
-- | days parameter. |
-- +-----------------------------------------------------------+
PROCEDURE purge_schedule( in_days_older_than IN INTEGER
, in_start_date IN DATE
, in_interval IN VARCHAR2)
IS
v_ModuleName VARCHAR2(48);
v_ActionContext VARCHAR2(100);
v_ErrorMessage VARCHAR2(1000);
v_JobNumber NUMBER;
v_InstanceNumber NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- -------------------------------------------------------------------------
-- Register and initialize program module plus set the Client Info
-- -------------------------------------------------------------------------
v_ModuleName := 'statspack_custom.purge_schedule';
v_ActionContext := 'Begin';
DBMS_APPLICATION_INFO.SET_MODULE( module_name => v_ModuleName
, action_name => v_ActionContext);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => 'PERFSTAT');
-- -------------------------------------------------------------------------
-- Get instance number
-- -------------------------------------------------------------------------
v_ActionContext := 'Get instance number';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
SELECT instance_number INTO v_InstanceNumber FROM v$instance;
-- -------------------------------------------------------------------------
-- Submit job
-- -------------------------------------------------------------------------
v_ActionContext := 'Submit job';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
DBMS_JOB.SUBMIT( job => v_JobNumber
, what => 'statspack_custom.purge(' || in_days_older_than || ');'
, next_date => in_start_date
, interval => in_interval
, no_parse => TRUE
, instance => v_InstanceNumber);
-- -------------------------------------------------------------------------
-- Successful end to module.
-- -------------------------------------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE( module_name => null
, action_name => null);
COMMIT;
EXCEPTION
WHEN others THEN
v_ErrorMessage := sqlerrm;
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'ERROR: ' || v_ActionContext);
RAISE_APPLICATION_ERROR(-20000, v_ActionContext || chr(10) || v_ErrorMessage);
END;
-- +-----------------------------------------------------------+
-- | PROCEDURE - purge_schedule_midnight |
-- | |
-- | Utility procedure used to schedule the |
-- | "purge()" procedure to run every day at |
-- | midnight - removing a given number of |
-- | obsolete records by the provided number of |
-- | days parameter. |
-- +-----------------------------------------------------------+
PROCEDURE purge_schedule_midnight(in_days_older_than IN INTEGER)
IS
v_ModuleName VARCHAR2(48);
v_ActionContext VARCHAR2(100);
v_ErrorMessage VARCHAR2(1000);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
-- -------------------------------------------------------------------------
-- Register and initialize program module plus set the Client Info
-- -------------------------------------------------------------------------
v_ModuleName := 'statspack_custom.purge_schedule_midnight';
v_ActionContext := 'Begin';
DBMS_APPLICATION_INFO.SET_MODULE( module_name => v_ModuleName
, action_name => v_ActionContext);
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info => 'PERFSTAT');
-- -------------------------------------------------------------------------
-- Submit midnight job
-- -------------------------------------------------------------------------
v_ActionContext := 'Submit midnight job';
DBMS_APPLICATION_INFO.SET_ACTION(action_name => v_ActionContext);
statspack_custom.purge_schedule(in_days_older_than, trunc(sysdate+1), 'SYSDATE+1');
-- -------------------------------------------------------------------------
-- Successful end to module.
-- -------------------------------------------------------------------------
DBMS_APPLICATION_INFO.SET_MODULE( module_name => null
, action_name => null);
COMMIT;
EXCEPTION
WHEN others THEN
v_ErrorMessage := sqlerrm;
DBMS_APPLICATION_INFO.SET_MODULE(v_ModuleName, 'ERROR: ' || v_ActionContext);
RAISE_APPLICATION_ERROR(-20000, v_ActionContext || chr(10) || v_ErrorMessage);
END;
END statspack_custom;
/
SET TERMOUT ON
SHOW ERRORS
PROMPT
SPOOL OFf

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.