84 lines
2.8 KiB
SQL
84 lines
2.8 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display RMAN sessions
|
|
* Parameters :
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 07-Feb-14 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE days
|
|
UNDEFINE operation
|
|
UNDEFINE object_type
|
|
UNDEFINE whereclause
|
|
|
|
DEFINE days="&&1"
|
|
DEFINE operation="&&2"
|
|
DEFINE object_type="&&3"
|
|
DEFINE whereclause="&&4"
|
|
|
|
set term off
|
|
COLUMN _days NEW_VALUE days NOPRINT
|
|
COLUMN _operation NEW_VALUE operation NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&days','','1','&&days')) "_days"
|
|
, UPPER(DECODE('&&operation','','%','&&operation')) "_operation"
|
|
, UPPER(DECODE('&&object_type','','%','&&object_type')) "_object_type"
|
|
FROM DUAL
|
|
;
|
|
set term on
|
|
|
|
|
|
PROMPT *************************************************************************
|
|
PROMPT * RMAN Sessions
|
|
PROMPT *
|
|
PROMPT * Input Parameter
|
|
PROMPT * - Days = '&&days'
|
|
PROMPT * - Operation = '&&operation'
|
|
PROMPT * - Object Type = '&&object_type'
|
|
PROMPT * - Whereclause = '&&whereclause'
|
|
PROMPT *************************************************************************
|
|
|
|
COLUMN session_recid HEADING "SESSION|RECID" FORMAT 9999999
|
|
COLUMN session_stamp HEADING "SESSION|STAMP" FORMAT 9999999999
|
|
COLUMN start_time HEADING "Start Time" FORMAT a20
|
|
COLUMN end_time HEADING "End Time" FORMAT a20
|
|
COLUMN output_device_type HEADING "Output|Device|Type" FORMAT a10
|
|
COLUMN operation HEADING "Operation" FORMAT a30
|
|
COLUMN object_type HEADING "Object Type" FORMAT a15
|
|
COLUMN input_gb HEADING "Input|(GB)" FORMAT 999,999
|
|
COLUMN output_gb HEADING "Output|(GB)" FORMAT 999,999
|
|
COLUMN optimized HEADING "Optimized" FORMAT a10
|
|
|
|
SELECT /*+ RULE */
|
|
s.session_recid
|
|
, s.session_stamp
|
|
, TO_CHAR(s.start_time,'DD-MON-YY HH24:MI:SS') start_time
|
|
, TO_CHAR(s.end_time,'DD-MON-YY HH24:MI:SS') end_time
|
|
, s.output_device_type
|
|
, s.operation
|
|
, s.object_type
|
|
, ROUND(s.input_bytes/power(1024,3)) input_gb
|
|
, ROUND(s.output_bytes/power(1024,3)) output_gb
|
|
, s.optimized
|
|
FROM v$rman_status s
|
|
WHERE s.start_time > sysdate - &&days
|
|
AND s.operation LIKE '&&operation'
|
|
AND s.object_type LIKE '&&object_type'
|
|
&&whereclause
|
|
ORDER BY s.start_time
|
|
;
|
|
|
|
|
|
@@footer |