FAQ
It's a locally created package to wrap dbms_obfuscation.

-----Original Message-----
From: Sony Kristanto
Sent: Tuesday, August 02, 2005 1:06 AM
To: Goulet, Dick; Marquez, Chris; maxim.pakhutkin_at_paetec.com;
oracle-l_at_freelists.org
Subject: RE: monitoring jobs (dbms_job)

BTW, where is 'ENCRYPT.CODE(NVL(RPASS,'8('))RPASS' script?

Original Message -----
From: "Goulet, Dick"
To: "Marquez, Chris",
maxim.pakhutkin_at_paetec.com, oracle-l_at_freelists.org
Subject: RE: monitoring jobs (dbms_job)
Date: Thu, 28 Jul 2005 11:56:32 -0400
It's a table that was locally created, not in any cat script. It's
defined as

create table dba_refresh_pass
(rjowner varchar2(30) not null,
rpass varchar2(30) not null)
tablespace system
storage(initial 128K next 128K pctincrease 0);

-----Original Message-----
From: Marquez, Chris
Sent: Thursday, July 28, 2005 11:45 AM
To: Goulet, Dick; maxim.pakhutkin_at_paetec.com; oracle-l_at_freelists.org
Subject: RE: monitoring jobs (dbms_job)

What cat*.sql script includes the DBA_REFRESH_PASS view?

SQL> desc DBA_REFRESH_PASS
ERROR:
ORA-04043: object DBA_REFRESH_PASS does not exist

SQL> select * from v$version;
BANNER >
--------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

Thanks,

Chris Marquez
Oracle DBA



-----Original Message-----
From: oracle-l-bounce_at_freelists.org

Sent: Thu 7/28/2005 10:51 AM
To: maxim.pakhutkin_at_paetec.com; oracle-l_at_freelists.org
Subject: RE: monitoring jobs (dbms_job)

Max,

The following is the sql that I use to watch jobs. It is also
used to other things as well so there is a little bit more here that I
think you'll not need.

Encrypt is a package created here as a wrapper around
dbms_obfuscation.
Much longer history too.

DBA_REPRESH_PASS is a table where we store the username and encrypted
password of the job's owner.

I think the RUN_TIME column will be of interest. We generate warning
messages for any job that exceeds 30 minutes of run_time.

SELECT /*+ RULE */ SCHEMA_USER, ENCRYPT.CODE(NVL(RPASS,'8('))RPASS,
E.JOB,
NVL(ROUND((SYSDATE-E.THIS_DATE)*1440),0)RUN_TIME,
ROUND((SYSDATE - E.NEXT_DATE )*1440) REFRESH_TIME, SPID,
BROKEN, E.WHAT,
REPLACE(UPPER(INTERVAL),
'SYSDATE','TO_DATE('||CHR(39)||NVL(E.LAST_DATE,
SYSDATE)||CHR(39)||')'),
TO_NUMBER(TO_CHAR(E.NEXT_DATE,'J'))
FROM DBA_REFRESH_PASS A, (SELECT D.JOB, SPID
FROM V$PROCESS B, V$SESSION C,
DBA_JOBS_RUNNING D
WHERE PADDR = ADDR AND C.SID =
D.SID)F, DBA_JOBS E
WHERE A.RJOWNER(+) = rtrim(SCHEMA_USER)
AND F.JOB(+) = E.JOB
AND E.LAST_DATE IS NOT NULL;
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
On Behalf Of Pakhutkin, Maxim
(Max)
Sent: Thursday, July 28, 2005 10:31 AM
To: oracle-l_at_freelists.org
Subject: monitoring jobs (dbms_job)

Has anyone tried monitoring the run duration of jobs scheduled with
dbms_jobs over time to determine trends? I'm thinking of running some
pl/sql in intervals that will, in general, do the following: looking at
jobs that are scheduled to run at least that often, note their next_date
and then, when the monitored job runs next time, compared the noted next
time to the values in last_date. The description of the dba_jobs view
gives me doubts however, as it doesn't seem to correspond to reality in
respect to last_date, last_sec columns. My observation has been that
last_sec shows when the job last completed, but the view description
says it shows when the job last started.

The business need is to prevent jobs from running into the scheduled
cold backups and interfering with them. I've searched the archive and
ask tom, but couldn't come up with any direct hits. If anyone has tried
this before, I would appreciate if you could point out any non-trivial
issues you've encountered, thanks.

mostly 9i's with some 8i's thrown in on solaris.

Max Pakhutkin
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l
--
_______________________________________________
NEW! Lycos Dating Search. The only place to search multiple dating sites
at once.
http://datingsearch.lycos.com

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 2, '05 at 8:19a
activeAug 2, '05 at 8:19a
posts1
users1
websiteoracle.com

1 user in discussion

Goulet, Dick: 1 post

People

Translate

site design / logo © 2022 Grokbase