Search This Blog

Tuesday, 15 August 2017

Archive log generation on an hourly basis:


Archive log generation on an hourly basis ,I got this from
http://www.dbapundits.com/blog/query-script/hourly-daily-archive-generation/




set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;



======================================================================
Gather schema stats in background:I got this from my colleague(Terry) 

----------------------------------------------------------------------
nohup ./gather_stats_RWSH023P.ksh >output.log &

[orcldb@dcplnx22502624 sql]$ cat gather_stats_RWSH023P.ksh
sqlplus / as sysdba << eof
set time on timing on echo on;
spool gather_stats_RWSH023P.log
exec dbms_stats.gather_schema_stats(ownname => 'JOBPRO',cascade => TRUE);
spool off
exit
eof

-----------------------------------------------------------------------


To Monitor stats job (%percentage)

select OPNAME from v$session_longops;

 
COLUMN sid FORMAT A10
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
and opname ='Gather Schema Statistics'; 



=============================================================================

No comments:

Post a Comment