It's a locally created package to wrap dbms_obfuscation.
-- --Original Message-- -- From: Sony Kristanto [mailto:zettira@(protected)] Sent: Tuesday, August 02, 2005 1:06 AM To: Goulet, Dick; Marquez, Chris; maxim.pakhutkin@(protected); oracle-l@(protected) Subject: RE: monitoring jobs (dbms_job)
BTW, where is 'ENCRYPT.CODE(NVL(RPASS,'8('))RPASS' script?
-- -- Original Message -- -- From: "Goulet, Dick" <DGoulet@(protected)> To: "Marquez, Chris" <cmarquez@(protected)>, maxim.pakhutkin@(protected), oracle-l@(protected) 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 [mailto:cmarquez@(protected)] > Sent: Thursday, July 28, 2005 11:45 AM > To: Goulet, Dick; maxim.pakhutkin@(protected); oracle-l@(protected) > Subject: RE: monitoring jobs (dbms_job) > > What cat*.sql script includes the DBA_REFRESH_PASS view? > > SQL> desc DBA_REFRESH_PASS > ERROR: > ORA-04043 (See ORA-04043.ora-code.com): 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@(protected) > [mailto:oracle-l-bounce@(protected)] > Sent: Thu 7/28/2005 10:51 AM > To: maxim.pakhutkin@(protected); oracle-l@(protected) > 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@(protected) > [mailto:oracle-l-bounce@(protected)] On Behalf Of Pakhutkin, Maxim > (Max) > Sent: Thursday, July 28, 2005 10:31 AM > To: oracle-l@(protected) > 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
