Skip to content

Instantly share code, notes, and snippets.

@bbuchalter
Last active September 29, 2020 13:05
Show Gist options
  • Save bbuchalter/cf9a9df37e8f99af594e36aead0f8db1 to your computer and use it in GitHub Desktop.
Save bbuchalter/cf9a9df37e8f99af594e36aead0f8db1 to your computer and use it in GitHub Desktop.
SET pagesize 30
set linesize 300
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
column "Tablespace path" format a50
ttitle center 'Application tablespace' skip 2
SELECT fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
ROUND(100 * (fs.freespace / df.totalspace)) "Pct. Free",
(SELECT DISTINCT SUBSTR(file_name, 0, INSTR(file_name, '\', -1)) tablespace_file_path
FROM dba_data_files WHERE dba_data_files.tablespace_name = fs.tablespace_name) "Tablespace path"
FROM
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM dba_free_space
GROUP BY tablespace_name
) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY fs.tablespace_name;
SET pagesize 30
set linesize 300
column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999
column "Tablespace path" format a50
ttitle center 'System tablespace available' skip 2
SELECT A.tablespace_name Tablespace,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 "Used MB",
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 "Free MB",
D.mb_total "Total MB"
FROM v$sort_segment A,
(SELECT B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B,
v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name,
C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP BY A.tablespace_name,
D.mb_total;
SET pagesize 30
set linesize 300
column "Tablespace" format a13
column "Tablespace path" format a50
ttitle center 'System tablespace path' skip 2
SELECT v$tablespace.name as Tablespace, tablespace_file_path "Tablespace path" FROM
(SELECT DISTINCT TS#, SUBSTR(name, 0, INSTR(name, '\', -1)) tablespace_file_path
FROM v$tempfile) tempfile
JOIN v$tablespace ON v$tablespace.TS# = tempfile.TS#;
EXIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment