Difference between revisions of "Check the percentage free space on autoextend tabelspaces"
From Peter Pap's Technowiki
(Created page with " select df.TABLESPACE_NAME, round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct, round(decode(df.MAXBYTES, 34359721984, 0, (df.BYTES - fs.BYTES) / df.M...") |
|||
Line 34: | Line 34: | ||
USERS 1 0 YES | USERS 1 0 YES | ||
− | where USAGE_PCT is the current usage of unextended data files, and MAX_PCT is | + | where USAGE_PCT is the current % usage of unextended data files, and MAX_PCT is the percentage of the extendable space that has been used, i.e. the actual percenteage free space. |
Revision as of 23:06, 3 January 2017
select df.TABLESPACE_NAME, round(((df.BYTES - fs.BYTES) / df.BYTES) * 100) usage_pct, round(decode(df.MAXBYTES, 34359721984, 0, (df.BYTES - fs.BYTES) / df.MAXBYTES * 100)) max_pct, df.AUTOEXTENSIBLE from ( select TABLESPACE_NAME, sum(BYTES) BYTES, AUTOEXTENSIBLE, decode(AUTOEXTENSIBLE, 'YES', sum(MAXBYTES), sum(BYTES)) MAXBYTES from dba_data_files group by TABLESPACE_NAME, AUTOEXTENSIBLE ) df, ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_free_space group by TABLESPACE_NAME ) fs where df.TABLESPACE_NAME=fs.TABLESPACE_NAME order by df.TABLESPACE_NAME asc;
This will generate output like this:
TABLESPACE_NAME USAGE_PCT MAX_PCT AUT ------------------------------ ---------- ---------- --- MYDATA 99 18 YES SYSAUX 97 28 YES SYSTEM 98 18 YES UNDOTBS1 2 0 YES USERS 1 0 YES
where USAGE_PCT is the current % usage of unextended data files, and MAX_PCT is the percentage of the extendable space that has been used, i.e. the actual percenteage free space.