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_MANAGEMENT format a13
column EXT_MANAGEMENT format a14
column GB_ALLOC format a12

--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.EXTENT_KB, SUB1.ALLOCATION_TYPE, SUB1.SEGMENT_SPACE_MANAGEMENT SS_MANAGEMENT, SUB1.EXTENT_MANAGEMENT EXT_MANAGEMENT, SUB2.GB_USED, SUB1.GB_ALLOC, SUB1.GB_MAX
FROM 
(SELECT F.TABLESPACE_NAME, T.NEXT_EXTENT/1024 EXTENT_KB, T.ALLOCATION_TYPE, T.SEGMENT_SPACE_MANAGEMENT, T.EXTENT_MANAGEMENT,
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, T.SEGMENT_SPACE_MANAGEMENT, T.EXTENT_MANAGEMENT) SUB1,
(SELECT S.TABLESPACE_NAME TABLESPACE_NAME, TO_CHAR(sum(S.BYTES)/1024/1024/1024, '999999.00') GB_USED
FROM DBA_SEGMENTS S
WHERE S.TABLESPACE_NAME LIKE upper('&&1')
GROUP BY S.TABLESPACE_NAME) 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
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;


SET FEEDBACK ON
SET PAGESIZE 9999