declare
cursor csr_jobs is
select * from dba_jobs
where job = nvl( '&jno', job );
ls_env dba_jobs.nls_env%type;
ls_param dba_jobs.nls_env%type;
i_brk_point integer;
i_ctr integer;
cursor csr_runig( xn_jno in number ) is
select count(*)
from dba_jobs_running
where job = xn_jno;
n_runig number;
s_notis varchar2( 200 );
begin
dbms_output.put_line( chr(10) );
for i_csr_jobs in csr_jobs loop
dbms_output.put_line( chr(9) );
open csr_runig( i_csr_jobs.job );
fetch csr_runig into n_runig;
close csr_runig;
s_notis := ' ';
if n_runig > 0 then
s_notis := s_notis || ' \Running/ ';
end if;
if i_csr_jobs.broken = 'Y' then
s_notis := s_notis || ' * * * Broken * * * ';
else
s_notis := s_notis || ' Not broken. ';
end if;
if i_csr_jobs.failures > 0 then
s_notis := s_notis || ' * * * Has failed ' || i_csr_jobs.failures || ' times * * * ';
else
s_notis := s_notis || ' No failures. ';
end if;
dbms_output.put_line( '=======================================================================================' );
dbms_output.put_line( 'Job No : ' || i_csr_jobs.job || ' ' || s_notis );
dbms_output.put_line( '=======================================================================================' );
dbms_output.put_line( 'Log User : ' || i_csr_jobs.log_user );
dbms_output.put_line( 'Priv User : ' || i_csr_jobs.priv_user );
dbms_output.put_line( 'Schema User : ' || i_csr_jobs.schema_user );
dbms_output.put_line( 'Last Date : ' || to_char( i_csr_jobs.last_date, 'hh24:mi dd/mm/yy' ) );
dbms_output.put_line( 'Last Sec : ' || i_csr_jobs.last_sec );
dbms_output.put_line( 'This Date : ' || to_char( i_csr_jobs.this_date, 'hh24:mi dd/mm/yy' ) );
dbms_output.put_line( 'This Sec : ' || i_csr_jobs.this_sec );
dbms_output.put_line( 'Next Date : ' || to_char( i_csr_jobs.next_date, 'hh24:mi dd/mm/yy' ) );
dbms_output.put_line( 'Next Sec : ' || i_csr_jobs.next_sec );
dbms_output.put_line( 'Total Time : ' || i_csr_jobs.total_time );
-- dbms_output.put_line( 'Broken : ' || i_csr_jobs.broken );
dbms_output.put_line( 'Interval : ' || i_csr_jobs.interval );
-- dbms_output.put_line( 'Failures : ' || i_csr_jobs.failures );
dbms_output.put_line( 'What : ' || i_csr_jobs.what );
-- dbms_output.put_line( 'Current Session Label : ' || i_csr_jobs.current_session_label );
-- dbms_output.put_line( 'Clearance HI : ' || i_csr_jobs.clearance_hi );
-- dbms_output.put_line( 'Clearance LO : ' || i_csr_jobs.clearance_lo );
-- dbms_output.put_line( 'NLS Env : ' || i_csr_jobs.nls_env );
dbms_output.put_line( 'Environment : ' );
dbms_output.put_line( '--------------' );
ls_env := i_csr_jobs.nls_env;
i_ctr := 0;
while ls_env is not null loop
i_brk_point := instr( ls_env, chr(32) );
if i_brk_point = 0 then
dbms_output.put_line( chr(9) || ls_env );
exit;
end if;
ls_param := substr( ls_env, 1, i_brk_point );
dbms_output.put_line( chr(9) || ls_param );
ls_env := substr( ls_env, i_brk_point + 1 );
i_ctr := i_ctr + 1;
if i_ctr > 10 then
dbms_output.put_line( 'hath exceeded the tenth....');
dbms_output.put_line( ls_env );
exit;
end if;
end loop;
-- dbms_output.put_line( 'Misc Env : ' || i_csr_jobs.misc_env );
end loop;
end;
/
Search This Blog
Total Pageviews
Monday, 9 August 2010
Subscribe to:
Post Comments (Atom)
Oracle DBA
anuj blog Archive
- ► 2011 (362)
-
▼
2010
(115)
-
▼
August
(30)
-
▼
Aug 09
(15)
- Oracle invalid objects
- Oracle Number of Objects in Tablespaces
- Oracle Instance up time
- Oracle Col name search
- Oracle user details
- user detail
- Oracle User Object Summary
- Oracle Transaction Monitor
- Oracle Resource Intensive SQL
- Partition Table Info
- Oracle Job Schedules detail
- Review Oracle user privilege
- Foreign Key Indexes
- Latch Hit Ratios
- How Long SQL will take
-
▼
Aug 09
(15)
-
▼
August
(30)
No comments:
Post a Comment