Hi guys.
I'm kinda new on this oracle databases developing carreer path so I'm not sure what's happening (I'll leave my query at the end of this post). On the other hand, I really hope I'm posting this on the correct section since it's my first post.
I'm currently running this query on a daily basis to check on specific characteristics of my table spaces since I'm working with very limited space on my server's hard drive.
So, the problem is that it always ran smoothly and the generated report never had missing information. However a couple days ago I noticed that one of my table spaces was missing in the report and yet it still existed in my database.
A colleage told me that it may have ocurred because all of its datafiles were full and my query filtered it out since it didn't detect any available space but I'm not sure about it since I ran it on another instance that I knew beorehand it had schemas with table spaces that didn't had available space and those schemas did appear in the report. We added a data file and on the next day it reappeared.
Taking this into account, I believe that it migth indeed be a programming issue but it's been avoiding me for a while now.
That's it, guys. I'd really appreciate your help.
Thank you in advance.
EDIT: forgot to mention that this query works over oracle databases 11g r2 and 12c r2 (haven't had the chance to test it out on latest versions).
SELECT
B.USERNAME AS "USER NAME",
A.TABLESPACE_NAME AS "TS NAME",
B.SIZE_KB/1024 AS "TS SIZE (MB)",
((B.SIZE_KB - A.FREE_KB)/1024) AS "USED SPACE (MB)",
A.FREE_KB/1024 AS "FREE SPACE (MB)",
Trunc((A.FREE_KB/B.SIZE_KB) * 100) AS "FREE (%)",
B.AUTOEXTENSIBLE AS "IS IT AUTO EXTENSIBLE?",
CASE
WHEN ((Trunc((A.FREE_KB/B.SIZE_KB) * 100) BETWEEN 0 AND 10)) THEN 'YES'
ELSE 'NO, ACCEPTABLE SIZE'
END AS "CHECK TS SIZE?",
B.MAX_BYTES AS "USERS QUOTA (B)",
ROUND((B.MAX_BYTES /(1024*1024)),2) AS "USERS QUOTA (MB)",
CASE
WHEN B.MAX_BYTES = -1 THEN 'UNLIMITED'
WHEN ((B.MAX_BYTES /(1024*1024)) = ((B.SIZE_KB/1024)*2)) AND B.AUTOEXTENSIBLE LIKE('NO') THEN 'ACCEPTABLE QUOTA SIZE.'
WHEN ((B.MAX_BYTES /(1024*1024)) > ((B.SIZE_KB/1024)*2)) AND B.AUTOEXTENSIBLE LIKE('NO') THEN 'TOO HIGH. SUGGESTED SIZE: '||((B.SIZE_KB/1024) * 2)||' MB'
WHEN ((B.MAX_BYTES /(1024*1024)) < ((B.SIZE_KB/1024)*2)) AND B.AUTOEXTENSIBLE LIKE('NO') THEN 'TOO LOW. SUGGESTED SIZE' || ((B.SIZE_KB/1024) * 2) || ' MB'
ELSE 'SUGGESTION: SET QUOTA TO "-1" (UNLIMITED)'
END AS "USERS QUOTA STATUS"
FROM (SELECT TABLESPACE_NAME,
Trunc(Sum(BYTES)/1024) AS FREE_KB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT DF.TABLESPACE_NAME,
Trunc(Sum(DF.BYTES)/1024) AS SIZE_KB,
Q.MAX_BYTES,
Q.USERNAME,
DF.AUTOEXTENSIBLE
FROM DBA_DATA_FILES DF
JOIN
DBA_TS_QUOTAS Q ON DF.TABLESPACE_NAME = Q.TABLESPACE_NAME
GROUP BY DF.TABLESPACE_NAME, Q.MAX_BYTES, Q.USERNAME, DF.AUTOEXTENSIBLE) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
UNION
SELECT NULL, TABLESPACE_NAME,(BYTES/1024/1024),NULL,null,null,NULL, NULL, NULL, NULL, NULL
FROM DBA_TEMP_FILES
ORDER BY 1 asc;