SET PAGESIZE     9999
SET VERIFY       OFF
SET SERVEROUTPUT ON  SIZE 500000 FORMAT WORD_WRAPPED
SET FEEDBACK     OFF
SET TRIMSPOOL    ON
SET LINESIZE 3500
SET PAGESIZE 35000

COLUMN FILE_NAME FORMAT A70

ALTER SESSION set nls_numeric_characters = ',.';

-- Disect the input argument, and get the owner filter and table filter into two seperate 
-- substitution variables. The owner filter defaults to the current user.

DEFINE InputTablespaceFilter = ' '
COLUMN Tablespace NOPRINT NEW_VALUE InputTablespaceFilter

column GB_USED format a12
column GB_MAX format a12
column ALLOCATION_TYPE format a15
column SS_MGMT format a10
column EXT_MGMT format a10
column GB_ALLOC format a12
column TABLESPACE_NAME format a30
column BLOCK format 999


--SELECT TABLESPACE_NAME, TO_CHAR(sum(BYTES)/1024/1024/1024, '999999.00') GB_USED, TO_CHAR(sum(MAXBYTES)/1024/1024/1024, '999999.00') GB_MAX
--FROM DBA_DATA_FILES
--WHERE TABLESPACE_NAME LIKE upper('&&1')
--GROUP BY TABLESPACE_NAME
--ORDER BY TABLESPACE_NAME;

--SELECT F.TABLESPACE_NAME, T.NEXT_EXTENT/1024 EXTENT_KB, T.ALLOCATION_TYPE, TO_CHAR(sum(F.BYTES)/1024/1024/1024, '999999.00') GB_ALLOC, TO_CHAR(sum(F.MAXBYTES)/1024/1024/1024, '999999.00') GB_MAX
--FROM DBA_DATA_FILES F, DBA_TABLESPACES T
--WHERE F.TABLESPACE_NAME LIKE upper('&&1')
--AND F.TABLESPACE_NAME = T.TABLESPACE_NAME
--GROUP BY F.TABLESPACE_NAME, T.NEXT_EXTENT, T.ALLOCATION_TYPE
--ORDER BY F.TABLESPACE_NAME;

SELECT SUB1.TABLESPACE_NAME, SUB1.BLOCK_SIZE/1024 BLOCK, SUB1.EXTENT_KB, SUB1.ALLOCATION_TYPE, SUB1.SEGMENT_SPACE_MANAGEMENT SS_MGMT, SUB1.EXTENT_MANAGEMENT EXT_MGMT,
TO_CHAR(SUB2.USED_SPACE*SUB1.BLOCK_SIZE/1024/1024/1024, '999999.00') GB_USED, SUB1.GB_ALLOC, SUB1.GB_MAX, sub1.bigfile,sub1.status
FROM 
(SELECT F.TABLESPACE_NAME, T.NEXT_EXTENT/1024 EXTENT_KB, T.ALLOCATION_TYPE, T.SEGMENT_SPACE_MANAGEMENT, T.EXTENT_MANAGEMENT, T.BLOCK_SIZE,
TO_CHAR(sum(F.BYTES)/1024/1024/1024, '999999.00') GB_ALLOC, 
TO_CHAR(sum(F.MAXBYTES)/1024/1024/1024, '999999.00') GB_MAX,
min(t.bigfile) bigfile, T.status
FROM DBA_DATA_FILES F, DBA_TABLESPACES T
WHERE F.TABLESPACE_NAME LIKE upper('&&1')
AND F.TABLESPACE_NAME = T.TABLESPACE_NAME
GROUP BY F.TABLESPACE_NAME, T.NEXT_EXTENT, T.ALLOCATION_TYPE, T.SEGMENT_SPACE_MANAGEMENT, T.EXTENT_MANAGEMENT, T.BLOCK_SIZE,  T.status) SUB1,
(SELECT S.TABLESPACE_NAME TABLESPACE_NAME, USED_SPACE
FROM DBA_TABLESPACE_USAGE_METRICS S
WHERE S.TABLESPACE_NAME LIKE upper('&&1')) SUB2
WHERE SUB1.TABLESPACE_NAME = SUB2.TABLESPACE_NAME (+)
ORDER BY TABLESPACE_NAME;

--SELECT TABLESPACE_NAME, TO_CHAR(sum(BYTES)/1024/1024/1024, '999999.00') GB_USED, TO_CHAR(sum(MAXBYTES)/1024/1024/1024, '999999.00') GB_MAX
--FROM DBA_TEMP_FILES
--WHERE TABLESPACE_NAME LIKE upper('&&1')
--GROUP BY TABLESPACE_NAME
--ORDER BY TABLESPACE_NAME;

SELECT F.TABLESPACE_NAME, T.NEXT_EXTENT/1024 EXTENT_KB, T.ALLOCATION_TYPE, TO_CHAR(sum(F.BYTES)/1024/1024/1024, '999999.00') GB_USED, TO_CHAR(sum(F.MAXBYTES)/1024/1024/1024, '999999.00') GB_MAX, min(t.bigfile) bigfile
FROM DBA_TEMP_FILES F, DBA_TABLESPACES T
WHERE F.TABLESPACE_NAME LIKE upper('&&1')
AND F.TABLESPACE_NAME = T.TABLESPACE_NAME
GROUP BY F.TABLESPACE_NAME, T.NEXT_EXTENT, T.ALLOCATION_TYPE
ORDER BY F.TABLESPACE_NAME;

SELECT * FROM DBA_TABLESPACE_GROUPS;

SET FEEDBACK ON
SET PAGESIZE 9999