140 lines
7.4 KiB
SQL
140 lines
7.4 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Database space usage by Filesystem and FileType
|
|
* Parameters : 1 - Filesystem (% - wildchar, \ - escape char, default is '%')
|
|
* 2 - Depth Level (Default value 1)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ ------------------------------------------------------------------------------
|
|
* 27-Jan-16 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE FILESYSTEM
|
|
UNDEFINE DEPTH_LEVEL
|
|
|
|
DEFINE FILESYSTEM="&&1"
|
|
DEFINE DEPTH_LEVEL="&&2"
|
|
|
|
set term off
|
|
COLUMN _FILESYSTEM NEW_VALUE FILESYSTEM NOPRINT
|
|
COLUMN _DEPTH_LEVEL NEW_VALUE DEPTH_LEVEL NOPRINT
|
|
|
|
SELECT UPPER(DECODE('&&FILESYSTEM','','%',UPPER('&&FILESYSTEM'))) "_FILESYSTEM"
|
|
,DECODE('&&DEPTH_LEVEL','','1','&&DEPTH_LEVEL') "_DEPTH_LEVEL"
|
|
FROM DUAL
|
|
/
|
|
|
|
|
|
set term on
|
|
|
|
|
|
PROMPT
|
|
PROMPT *******************************************************************
|
|
PROMPT * Database Space Usage by Filesystem and FileType *
|
|
PROMPT * *
|
|
PROMPT * Input Parameters *
|
|
PROMPT * - Filesystem = '&&FILESYSTEM'
|
|
PROMPT * - Depth Level = '&&DEPTH_LEVEL'
|
|
PROMPT *******************************************************************
|
|
|
|
DEFINE size_label=MB
|
|
DEFINE size_divider="power(1024,2)"
|
|
--DEFINE size_label=GB
|
|
--DEFINE size_divider="power(1024,3)"
|
|
--DEFINE size_label=TB
|
|
--DEFINE size_divider="power(1024,4)"
|
|
|
|
|
|
DEFINE size_small_label=KB
|
|
DEFINE size_small_divider="1024"
|
|
--DEFINE size_small_label=MB
|
|
--DEFINE size_small_divider="1024/1024"
|
|
|
|
|
|
COLUMN Filesystem HEADING "Filesystem" FORMAT A80
|
|
COLUMN Filesystem_Size HEADING "Total|(&&size_label)" FORMAT 999,999
|
|
COLUMN DATAFILE_Size HEADING "Data|File|(&&size_label)" FORMAT 999,999
|
|
COLUMN TEMPFILE_Size HEADING "Temp|File|(&&size_label)" FORMAT 999,999
|
|
COLUMN ONLINELOG_Size HEADING "Redo|Log|(&&size_label)" FORMAT 99,999
|
|
COLUMN STANDBYLOG_Size HEADING "Standby|Log|(&&size_label)" FORMAT 99,999
|
|
COLUMN ARCHIVELOG_Size HEADING "Arch|Log|(&&size_label)" FORMAT 999,999
|
|
COLUMN CONTROLFILE_Size HEADING "Ctrl|File|(&&size_small_label)" FORMAT 999,999
|
|
COLUMN BACKUPSET_Size HEADING "Backup|Set|(&&size_label)" FORMAT 999,999
|
|
COLUMN DATAFILECOPY_Size HEADING "Data|File|Copy|(&&size_label)" FORMAT 999,999
|
|
COLUMN FLASHBACK_Size HEADING "Flash|Back|Log|(&&size_label)" FORMAT 999,999
|
|
COLUMN OCRFILE_Size HEADING "OCR|(&&size_small_label)" FORMAT 999
|
|
COLUMN AUTOBACKUP_Size HEADING "Auto|Backup|(&&size_small_label)" FORMAT 999,999
|
|
COLUMN CONTROLFILECOPY_Size HEADING "Ctrl|File|Copy|(&&size_small_label)" FORMAT 999,999
|
|
COLUMN PARAMFILE_Size HEADING "Param|File|(&&size_small_label)" FORMAT 999
|
|
|
|
|
|
BREAK ON REPORT ON Filesystem SKIP 0
|
|
|
|
COMPUTE SUM LABEL 'Total' OF bytes FORMAT 9,999,999,999 ON REPORT
|
|
|
|
COMPUTE SUM LABEL 'Total' OF Filesystem_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF DATAFILE_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF TEMPFILE_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF ONLINELOG_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF STANDBYLOG_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF CONTROLFILE_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF ARCHIVELOG_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF BACKUPSET_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF DATAFILECOPY_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF CONTROLFILECOPY_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF FLASHBACK_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF OCRFILE_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF AUTOBACKUP_Size FORMAT 99,999,999 ON REPORT
|
|
COMPUTE SUM LABEL 'Total' OF PARAMFILE_Size FORMAT 99,999,999 ON REPORT
|
|
|
|
SELECT ROUND(SUM(f.bytes)/&&size_divider) Filesystem_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'DATAFILE',f.bytes,0))/&&size_divider) DATAFILE_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'TEMPFILE',f.bytes,0))/&&size_divider) TEMPFILE_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'ONLINELOG',f.bytes,0))/&&size_divider) ONLINELOG_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'STANDBYLOG',f.bytes,0))/&&size_divider) STANDBYLOG_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'CONTROLFILE',f.bytes,0))/&&size_small_divider) CONTROLFILE_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'ARCHIVELOG',f.bytes,0))/&&size_divider) ARCHIVELOG_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'BACKUPSET',f.bytes,0))/&&size_divider) BACKUPSET_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'DATAFILECOPY',f.bytes,0))/&&size_divider) DATAFILECOPY_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'CONTROLFILECOPY',f.bytes,0))/&&size_small_divider) CONTROLFILECOPY_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'FLASHBACK',f.bytes,0))/&&size_divider) FLASHBACK_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'OCRFILE',f.bytes,0))/&&size_small_divider) OCRFILE_Size
|
|
, ROUND(SUM(DECODE(f.filetype, 'AUTOBACKUP',f.bytes,0))/&&size_small_divider) AUTOBACKUP_Size
|
|
, substr(f.file_name,1,DECODE(instr(f.file_name,'/',2,&&DEPTH_LEVEL),0,instr(f.file_name,'/',-1,1),instr(f.file_name,'/',2,&&DEPTH_LEVEL)-1) ) Filesystem
|
|
FROM (
|
|
select 'DATAFILE' filetype, name file_name, bytes from v$datafile
|
|
UNION ALL
|
|
select 'TEMPFILE' filetype, name file_name, bytes from v$tempfile
|
|
UNION ALL
|
|
select 'CONTROLFILE' filetype, name file_name, bytes from v$tempfile
|
|
UNION ALL
|
|
select 'ONLINELOG', lf.member file_name, l.bytes from v$log l , v$logfile lf where l.group# = lf.group#
|
|
UNION ALL
|
|
select 'STANDBYLOG', lf.member file_name, l.bytes from v$standby_log l , v$logfile lf where l.group# = lf.group#
|
|
UNION ALL
|
|
select 'ARCHIVELOG', al.name file_name, al.blocks * al.block_size bytes from v$archived_log al where al.status = 'A' and al.deleted = 'NO' and al.STANDBY_DEST = 'NO'
|
|
UNION ALL
|
|
select 'DATAFILECOPY' filetype, name file_name, blocks*block_size bytes from v$datafile_copy where status = 'A' and deleted = 'NO' and file# > 0
|
|
UNION ALL
|
|
select 'CONTROLFILECOPY' filetype, name file_name, blocks*block_size bytes from v$datafile_copy where status = 'A' and deleted = 'NO' and file# = 0
|
|
UNION ALL
|
|
select 'BACKUPSET' filetype, handle file_name, bytes from v$backup_piece where status = 'A' and deleted ='NO'
|
|
) f
|
|
WHERE upper(f.file_name) like UPPER('&&FILESYSTEM')
|
|
group by substr(f.file_name,1,DECODE(instr(f.file_name,'/',2,&&DEPTH_LEVEL),0,instr(f.file_name,'/',-1,1),instr(f.file_name,'/',2,&&DEPTH_LEVEL)-1) )
|
|
ORDER BY Filesystem
|
|
;
|
|
|
|
|
|
@@footer
|