In this series, I will cover some of the Oracle features that can be easily implemented to optimize our PeopleSoft environment.
Resumable Space
In todayworld, we will hardly find a database that is not monitored for space usage. But there is still a possibility for our process failing because it ran out of space in the UNDO tablespace or user defined tablespace or the DBA was too late to allocate the required space. Majority of PeopleSoft processes have restart capability. However, there might be instances where you just cannot afford to let the process fail and restart from the last commit/rollback step, example – data conversion during cutover.
The Resumable Space feature provides the facility to suspend transactions when they hit the space errors. The transaction will resume when the error is corrected.
Below are the steps to implement this feature for a SQR process.
1. Modify the SQR to include the following procedure which will be called at the start of the process.
1. Modify the SQR to include the following procedure which will be called at the start of the process.
begin-procedure SetResumable ! Set RESUMABLE in current session
begin-SQL
ALTER SESSION ENABLE RESUMABLE;
end-SQL
ALTER SESSION ENABLE RESUMABLE;
end-SQL
end-procedure
2. Create an AFTER SUSPEND trigger to monitor any transactions which have been suspended
CREATE OR REPLACE TRIGGER resumable_alert_notifier
AFTER SUSPEND
ON DATABASE
AFTER SUSPEND
ON DATABASE
BEGIN
– LOG ERROR
INSERT INTO RESUMABLE_ERR_LOG (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM USER_RESUMABLE
WHERE SESSION_ID = (SELECT DISTINCT(SID) FROM V$MYSTAT));
– Send Email notification using UTL_SMTP
– Code not shown
INSERT INTO RESUMABLE_ERR_LOG (
SELECT SQL_TEXT, ERROR_MSG, SUSPEND_TIME
FROM USER_RESUMABLE
WHERE SESSION_ID = (SELECT DISTINCT(SID) FROM V$MYSTAT));
– Send Email notification using UTL_SMTP
– Code not shown
END;
You can determine the best strategy (notification or timeout or abort or log error) when transaction is suspended by coding in the AFTER SUSPEND trigger.
Summary
Resumable Space is a cool feature and I recommend it be considered for implementation in your production environment to avoid business critical processes from failing when it encounters space issues. As with any implementation the mantra is “Understand > Plan > Implement in non-production db > Test > Test > Test > implement in production > relax”.
Read More About Oracle Features
Resumable Space is a cool feature and I recommend it be considered for implementation in your production environment to avoid business critical processes from failing when it encounters space issues. As with any implementation the mantra is “Understand > Plan > Implement in non-production db > Test > Test > Test > implement in production > relax”.
Read More About Oracle Features
