getdbspace.sql

/*
SET TERMOUT OFF

DEFINE tbs='%'
DEFINE detalhar='NAO'

COL PRINT_DETALHE NEW_VALUE PRINT_DETALHE
COL BREAK_DETALHE NEW_VALUE BREAK_DETALHE

SELECT
  DECODE( UPPER('&DETALHAR.'), 'SIM', 'PRINT', 'NOPRINT' ) PRINT_DETALHE
 ,DECODE( UPPER('&DETALHAR.'), 'SIM', 'ON TABLESPACE_NAME SKIP PAGE', 'ON REPORT SKIP PAGE' ) BREAK_DETALHE
FROM DUAL
/
*/

SET TERMOUT ON 

--BREAK &BREAK_DETALHE.
--COMPUTE SUM LABEL "TOTAIS" OF MAX_MB SIZE_MB FREE_MB ON REPORT
--COL DETALHE FORMAT A21 &PRINT_DETALHE.
SET VERIFY OFF DEFINE ON LINES 200 NUMWIDTH 7 FEED OFF PAGES 200
COL DISK_GROUP FORMAT A30
COL TABLESPACE_NAME FORMAT A30
COL FILE_NAME FORMAT A80
COL TIPO FORMAT A45
COL STATUS FORMAT A9
COL FILE_ID FORMAT 9999 HEAD "F#"
COL PCT_USED_MAX FORMAT A12
COL PCT_USED FORMAT A8
COL MAXMEGAS FORMAT 9G999G999G999
COL USERMEGAS FORMAT 9G999G999G999
COL MEGAS FORMAT 9G999G999G999

WITH DG_TS AS
(
  SELECT /*+MATERIALIZE */ DISTINCT 
    CASE WHEN SUBSTR( NAME, 1, 1 ) = '+'
       THEN SUBSTR( NAME, 2, INSTR( NAME, '/', 1, 1 )-2 )
       ELSE '' 
    END DISK_GROUP
   ,CASE WHEN SUBSTR( NAME, 1, 1 ) = '+'
       THEN UPPER(SUBSTR( NAME, INSTR( NAME, '/', -1, 1 )+1, INSTR( NAME, '.', 1, 1 )-INSTR( NAME, '/', -1, 1 )-1 ))
       ELSE '' 
    END TABLESPACE_NAME
  FROM V$DATAFILE
), 
DB AS 
(
	SELECT /*+MATERIALIZE */ DBS.NAME, T.TABLESPACE_NAME, COUNT(*) 	QTD, row_number() over ( partition by TABLESPACE_NAME order by COUNT(*) desc ) as row_num 
	FROM DBA_SEGMENTS T
	JOIN 
	(
		SELECT USERNAME NAME 
		FROM ALL_USERS 
		WHERE 
		( 
				 SUBSTR(USERNAME, 1, 2) IN ( 'MV' ) 
			OR SUBSTR(USERNAME, 1, 3) IN ( 'DBA', 'BIB' )  
			OR SUBSTR(USERNAME, 1, 4) IN ( 'EXT_', 'USR_' )
		)	
		AND USER_ID BETWEEN 55 AND 1E+5
	) DBs ON ( DBS.NAME = T.OWNER )
	GROUP BY DBS.NAME, T.TABLESPACE_NAME
),
FILETS AS
(
  SELECT tablespace_name, file_id, FILE_NAME, increment_by, USER_BYTES, bytes, maxbytes FROM DBA_DATA_FILES F
  UNION ALL
  SELECT tablespace_name, file_id, FILE_NAME, increment_by, USER_BYTES, bytes, maxbytes FROM DBA_TEMP_FILES T 
),
V AS
(
  SELECT 
    TBS.TABLESPACE_NAME
  , DECODE(BIGFILE, 'YES', 'BIGFILE ', '' )  || DECODE( EXTENT_MANAGEMENT, 'DICTIONARY', EXTENT_MANAGEMENT, EXTENT_MANAGEMENT || ' ' || 
      DECODE( ALLOCATION_TYPE, 'SYSTEM', 'AUTOALLOCATE', 'UNIFORM' ) ) || ' ' || SUBSTR( CONTENTS, 1, 4 ) || 
        DECODE( LOGGING, 'LOGGING', ' LOGG', ' NOLOG' ) || ' ' ||
          DECODE( SEGMENT_SPACE_MANAGEMENT, 'AUTO', 'ASSM' ) TIPO
  , STATUS
  , BLOCK_SIZE
  , ( SELECT SUM(ROUND(DECODE(I.INCREMENT_BY, 0, I.BYTES, I.MAXBYTES)/1048576, 0))
      FROM FILETS I
      WHERE I.TABLESPACE_NAME = TBS.TABLESPACE_NAME ) "MAX_MB"
  , ( SELECT SUM(ROUND(I.BYTES/1048576,0))
      FROM FILETS I
      WHERE I.TABLESPACE_NAME = TBS.TABLESPACE_NAME ) "SIZE_MB"
  , CASE TBS.CONTENTS
       WHEN 'TEMPORARY' THEN
         ( SELECT 
            TRUNC(
              ((SELECT SUM(T.BYTES) FROM FILETS T WHERE T.TABLESPACE_NAME = TBS.TABLESPACE_NAME) -
                 (SELECT SUM(I.BLOCKS*TBS.BLOCK_SIZE) FROM V$SORT_USAGE I WHERE I.TABLESPACE = TBS.TABLESPACE_NAME))/1048576
             )
           FROM DUAL
         )
       ELSE
         ( SELECT SUM(ROUND(I.BYTES/1048576,0))
           FROM DBA_FREE_SPACE I
           WHERE I.TABLESPACE_NAME = TBS.TABLESPACE_NAME
         )
    END "FREE_MB"
  FROM DBA_TABLESPACES TBS
)
SELECT
  DG.DISK_GROUP
 ,DECODE( GROUPING( DB.NAME), 1, 'OUTROS', DB.NAME ) NAME
 ,TRUNC(SYSDATE) DT_COLETA
 ,AVG(COLD_USED_MB) DG_ALLOC_MB
 ,DECODE( GROUPING( DB.NAME), 1, AVG(COLD_USED_MB) - SUM(V.SIZE_MB), SUM(V.SIZE_MB) ) SIZE_MB
 ,DECODE( GROUPING( DB.NAME), 1, 0, SUM( NVL( V.FREE_MB, 0 ) ) ) FREE_MB
FROM V
JOIN DB ON (V.TABLESPACE_NAME = DB.TABLESPACE_NAME AND DB.ROW_NUM=1)
LEFT JOIN DG_TS DG ON (V.TABLESPACE_NAME = DG.TABLESPACE_NAME)
LEFT JOIN V$ASM_DISKGROUP AD ON (DG.DISK_GROUP = AD.NAME )
GROUP BY GROUPING SETS((DG.DISK_GROUP, DB.NAME),())
ORDER BY SIZE_MB
/
PROMPT

UNDEFINE 1 2 3 PRINT_DETALHE BREAK_DETALHE
SET VERIFY ON NUMWIDTH 10 FEED 6 PAGES 66

CLEAR COMPUTE
CLEAR BREAK

--COL DETALHE CLEAR
COL TABLESPACE_NAME CLEAR
COL TIPO CLEAR
COL STATUS CLEAR
COL MAXMEGAS CLEAR
COL USERMEGAS CLEAR
COL MEGAS CLEAR