Is the disk half full or half empty?
Posted by Ceri Davies Fri, 13 Jan 2006 11:54:00 GMT
I continue to learn about Oracle, and that includes finding tasks that should be simple but that seem intolerably difficult. I’m sure that this is a result of my ignorance, so please do correct me if you know better.
So long as you have a basic understanding of Oracle, you’ll know that filling up a tablespace is bad. Here’s a bit of SQL that tells you which tablespaces are getting full:
SELECT t.tablespace_name,
decode(t.status, 'ONLINE', t.status, nls_initcap(t.status)) AS status,
(tsa.bytes / 1024) AS ts_size,
((tsa.bytes - decode(tsf.bytes, null, 0, tsf.bytes)) / 1024) AS used,
(decode(tsf.bytes, null, 0, tsf.bytes) / 1024) AS free,
(round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa, sys.sm$ts_free tsf
WHERE t.tablespace_name = tsa.tablespace_name
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name
/
which gives output somewhat like:
TABLESPACE_NAME STATUS TS_SIZE USED FREE PERCENT
------------------------------ ------ ---------- ---------- ---------- ----------
XDB ONLINE 46080 45824 256 99
CWMLITE ONLINE 20480 15168 5312 74
SYSTEM ONLINE 512000 358656 153344 70
DRSYS ONLINE 20480 4928 15552 24
WHATEVER ONLINE 2097152 120768 1976384 6
TOOLS ONLINE 10240 64 10176 1
INDX ONLINE 25600 64 25536 0
UNDOTBS1 ONLINE 1048576 1856 1046720 0
USERS ONLINE 25600 64 25536 0
9 rows selected.
This gives some pretty good stats, but there is a problem. These statistics only count the blocks that are currently allocated, and tablespaces can be set to autoextend.
This means that the statistics above don’t actually indicate whether you’re about to run out of space or not, since a tablespace that looks %99 full above might be allowed to autoextend another 5GB[1]. Basically, you don’t want to get out of bed on the basis of this script, because by the time you do, the tablespace might have autoextended and fixed the problem all by itself. So let’s try to work around that.
Strictly speaking, tablespaces do not autoextend; the data files that act as a tablespace’s backing store do. Oracle maintains a table called DBA_DATA_FILES which contains file names, internal file ids, and the AUTOEXTENSIBLE flag indicating whether they may autoextend or not. So we can qualify the output of the previous script a little with an AUTOEXTEND column:
SELECT t.tablespace_name,
decode(t.status, 'ONLINE', t.status, nls_initcap(t.status)) AS status,
(tsa.bytes / 1024) AS ts_size,
((tsa.bytes - decode(tsf.bytes, null, 0, tsf.bytes)) / 1024) AS used,
(decode(tsf.bytes, null, 0, tsf.bytes) / 1024) AS free,
(round((1 - decode(tsf.bytes, null, 0,tsf.bytes) / tsa.bytes) * 100)) AS percent,
dbf.autoextensible AS autoextend
FROM sys.dba_tablespaces t, sys.sm$ts_avail tsa,
sys.sm$ts_free tsf, dba_data_files dbf
WHERE t.tablespace_name = tsa.tablespace_name
AND t.tablespace_name = dbf.tablespace_name (+)
AND t.tablespace_name = tsf.tablespace_name (+)
ORDER BY percent DESC, t.status, t.tablespace_name
/
which looks like:
TABLESPACE_NAME STATUS TS_SIZE USED FREE PERCENT AUT
------------------------------ ------ ---------- ---------- ---------- ---------- ---
XDB ONLINE 46080 45824 256 99 YES
CWMLITE ONLINE 20480 15168 5312 74 YES
SYSTEM ONLINE 512000 358656 153344 70 YES
DRSYS ONLINE 20480 4928 15552 24 YES
WHATEVER ONLINE 2097152 120768 1976384 6 NO
TOOLS ONLINE 10240 64 10176 1 YES
INDX ONLINE 25600 64 25536 0 YES
UNDOTBS1 ONLINE 1048576 1856 1046720 0 YES
USERS ONLINE 25600 64 25536 0 YES
9 rows selected.
As you can see, where the XDB tablespace looked in trouble before, we can now safely ignore (assuming that the disk that the data file resides on has space, but that’s another article - see footnote 1).
Unfortunately, this is broken too. A tablespace may be made up of multiple data files, and we haven’t made any consideration for that at all here; in fact the above extract will just give multiple rows if a tablespace has more than one data file. What’s worse, those rows may contain different values in the AUTOEXTEND column, since one data file may be set to autoextend while another is not.
I don’t actually have any production databases that contain tablespaces comprising of multiple data files, so for now, I’ve given up at this point. Someone must be monitoring this properly though, so please let me know how you work out whether to get out of bed or not in this situation.
[1] The effect that having 15 tablespaces all set to autoextend has on the interpretation of the amount of free disk reported by df(1) is an exercise for an entirely different day…
Note: Apologies for the whitespace here. I can’t get markdown to stop fiddling with it for some reason.
Hmm, not simple in oracle to do anything with space issues. Put simply you need to find out all the data files that comprise the tablespace. Check for the autoextensible flag, and then look at maxbytes which will tell you what the maxbytes has been set to for each data file. Do a sum on this and then compare against amount of used extents you have. Becomes rather interesting as you noted when you have to start checking the maxsize against multiple filesystems. Normally the sum of maxbytes on any given filesystem far exeeds the total available in the volume. Some arbitrary code that I use below, returns tablespaces that are breaking the user defined limit passed at runtime. You will have to do your variable passing trickery to set the limit or type it in each time. Group by and having clauses do most of the work. select a.tablespace_name,100-((sum(b.maxbytes/1024/1024) - sum(b.bytes/1024/1024))+sum(a.bytes/1024/1024))/sum(b.maxbytes/1024/1024/100) "used" from dba_free_space a,dba_data_files b where a.tablespace_name = b.tablespace_name and b.autoextensible='YES' group by a.tablespace_name having 100-((sum(b.maxbytes/1024/1024) - sum(b.bytes/1024/1024))+sum(a.bytes/1024/1024))/sum(b.maxbytes/1024/1024/100) = &limit / One other thing to note if the maxbytes is set to smaller than the current file size the whole thing falls over. You can have datafiles that are larger than maxbytes if they have been explicitly grown with resize. Enjoy.