34 lines
1.1 KiB
SQL
34 lines
1.1 KiB
SQL
@@header
|
|
|
|
COLUMN tablespace_name FORMAT a20
|
|
COLUMN total_blocks format 999,999,999
|
|
COLUMN used_blocks format 999,999,999
|
|
COLUMN free_blocks format 999,999,999
|
|
COLUMN total_size HEADING "Total|(MB)" format 9,999,999
|
|
COLUMN used_size HEADING "Used|(MB)" format 9,999,999
|
|
COLUMN free_size HEADING "Free|(MB)" format 9,999,999
|
|
COLUMN percent_free HEADING "%Free" format 999.00
|
|
|
|
BREAK ON TABLESPACE_NAME
|
|
--COMPUTE SUM LABEL 'Total' OF total_size FORMAT 99,999,999 ON TABLESPACE_NAME
|
|
|
|
SELECT s.tablespace_name
|
|
, s.inst_id
|
|
--, s.extent_size
|
|
--, s.total_blocks
|
|
--, s.used_blocks
|
|
--, s.free_blocks
|
|
, (s.total_blocks * p.value)/1024/1024 total_size
|
|
, (s.used_blocks * p.value)/1024/1024 used_size
|
|
, (s.free_blocks * p.value)/1024/1024 free_size
|
|
, ROUND( (s.free_blocks/s.total_blocks) * 100 , 2) percent_free
|
|
FROM gv$sort_segment s
|
|
, v$parameter p
|
|
WHERE p.name = 'db_block_size'
|
|
AND s.total_blocks <> 0
|
|
ORDER BY s.tablespace_name
|
|
, s.inst_id
|
|
/
|
|
|
|
|
|
@@footer |