It's a locally created package to wrap dbms_obfuscation.
-----Original Message-----
From: Sony Kristanto [mailto:zettira_at_lycos.com]
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" <DGoulet_at_vicr.com>
To: "Marquez, Chris" <cmarquez_at_collegeboard.org>,
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 [mailto:cmarquez_at_collegeboard.org]
> 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
> [mailto: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
> [mailto: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
Received on Tue Aug 02 2005 - 08:19:00 CDT
