We have one, there was a bug in 10.2 that causes the temporal to increase,
so before backup and 2 times each day reduced the temporal.
CREATE OR REPLACE PROCEDURE SYS.DB_MNT_REDUCCION_TBS_TEMPORAL
IS
c VARCHAR2(1000);
n NUMBER;
n2 NUMBER := 0;
cRetOYMValida VARCHAR2(2000);
eErrorVersion EXCEPTION;
BEGIN
IF DAZ.PCK_VERSION.ObtieneVerificaVersion = 'T' THEN
cRetOYMValida := DAZ.DB_OYM_VALIDA( 'DB_MNT_REDUCCION_TBS_TEMPORAL',
/**/ 'SYS2014123142131' /**/, 'DAZ001');
RAISE eErrorVersion;
END IF;
WHILE TRUE LOOP
SELECT bytes INTO n FROM V$TEMPFILE;
IF n < 2000000000/*2GB*/ THEN
EXIT;
END IF;
SELECT 'ALTER DATABASE TEMPFILE '''||NAME||''' resize
'||(bytes-500000000/*.5GB*/) INTO c FROM V$TEMPFILE;
EXECUTE IMMEDIATE c;
SELECT bytes INTO n2 FROM V$TEMPFILE;
IF n2=n THEN
EXIT;
END IF;
END LOOP;
EXCEPTION WHEN OTHERS
THEN NULL;
end;
/
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MNT_REDUCCION_TBS_TEMPORAL',
job_type => 'STORED_PROCEDURE',
job_action => 'SYS.DB_MNT_REDUCCION_TBS_TEMPORAL',
start_date => TO_DATE('01042007 06:00','DDMMYYYY HH24:MI'),
repeat_interval => 'FREQ=DAILY;INTERVAL=1'); /* every other day */
DBMS_SCHEDULER.ENABLE('SYS.MNT_REDUCCION_TBS_TEMPORAL');
END;
/
2015-10-22 14:09 GMT-04:00 Jeff Chirco <backseatdba@xxxxxxxxx>:
I was wondering if any of you have certain jobs running to check database
safety from potential crashes. For example I recently add a job to run
every 5 minutes to check the PGA usage of every session to make sure no one
is using more than is normal. If a sessions is using more than 4gb I kill
the session. We recently had some code with an infinite loop that took
down the server. So I am trying to think of all the stuff I can check for
ahead of time.
Is there any other jobs you have running or certain profile settings or
resource manager setups. I would love to her your configurations.
Thanks,
Jeff
