SCRIPT FOR CHECKING ARCHIVE LOG GENERATION ON ORACLE DATABASE

/////////////////////////On daily basis:

set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
-- As Table
SELECT TRUNC(completion_time) AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time) || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time) ORDER BY 1 DESC;



//////////////////////////On hourly basis:



set pagesize 100
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24';
-- As Table
SELECT TRUNC(completion_time,'HH24') AS time, ROUND(SUM(blocks * block_size)/1024/1024,2) AS size_mb, ROUND(SUM(blocks * block_size)/1024/1024/1024,2) AS size_gb
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;
-- As csv-File
SELECT TRUNC(completion_time,'HH24') || ';' || ROUND(SUM(blocks * block_size)/1024/1024,2) || ';' || ROUND(SUM(blocks * block_size)/1024/1024/1024,2)
FROM v$archived_log GROUP BY TRUNC(completion_time,'HH24') ORDER BY 1 DESC;

SCRIPT TO CALCULATE UNDO SIZE AND RETENTION TIME IN ORACLE DATABASE FOR EXP-00008: ORACLE error 1555 encountered ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small error

//////////////////////maximum time take to execute query in sec


SQL>select max(maxquerylen) from v$undostat;


//////////////////////////Needed UNDO Size in MB


SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((TO_NUMBER(e.value)*TO_NUMBER(f.value)*g.undo_block_per_sec)/(1024*1024),2) "NEEDED UNDO SIZE [MByte]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.TS# = b.TS#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';


///////////////////////Optimal UNDO Retention in seconds





SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size/(TO_NUMBER(f.value)*g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.TS# = b.TS#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';


/////////////////////for change undo retention time



SQL>ALTER SYSTEM SET UNDO_RETENTION = [values based on your calculation];


/////////////////////////////number of error ora-01555



SQL>select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;




/////////////////////Actual undo tablespace size


SQL>SELECT SUM(a.bytes/1024/1024) undo_size
  FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

SCRIPT TO DATAPUMP/NORMAL EXPORT/IMPORT STATUS MONITORING

/////////////import job  status


SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),50) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

///////////export job status

select

   round(sofar/totalwork*100,2)  percent_completed, 

   v$session_longops.* 

from 

   v$session_longops 

where

   sofar <> totalwork 

order by

   target, sid;