Created
January 2, 2016 22:10
-
-
Save Dani3lSun/c2e0f8bb249ec2ae7bd2 to your computer and use it in GitHub Desktop.
Returns the percentage usage of disk space of an Oracle XE 11g database that has 11GB max space available
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION gen_xe_space_percent RETURN NUMBER IS | |
-- | |
l_return NUMBER := 0; | |
l_xe_max_size NUMBER := 11811160064; | |
l_used_size NUMBER := 0; | |
-- | |
CURSOR l_cur_dba_ts_size IS | |
SELECT a.tablespace_name ts, | |
a.file_id, | |
SUM(b.bytes) / COUNT(*) - SUM(a.bytes) used | |
FROM dba_free_space a, | |
dba_data_files b | |
WHERE a.tablespace_name = b.tablespace_name | |
AND a.file_id = b.file_id | |
AND a.tablespace_name NOT IN ('SYSTEM', | |
'SYSAUX', | |
'UNDOTBS1') | |
GROUP BY a.tablespace_name, | |
a.file_id | |
ORDER BY 1; | |
-- | |
BEGIN | |
-- | |
FOR l_rec IN l_cur_dba_ts_size LOOP | |
l_used_size := l_used_size + l_rec.used; | |
END LOOP; | |
-- | |
l_return := round(l_used_size / l_xe_max_size * 100, | |
2); | |
-- | |
RETURN l_return; | |
-- | |
END gen_xe_space_percent; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment