Learn IT

Tablespace Usage, Space Usage

set pagesize 3000

-- ALTER TABLESPACE PDB_TEMP AUTOEXTEND ON MAXSIZE 100G;

SELECT df.tablespace_name tablespace_name,

max(df.autoextensible) auto_ext,

to_char(round(df.maxbytes / (1024 * 1024), 0),'999,999,999') max_ts_size,

round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 0) max_ts_pct_used,

to_char(round(df.bytes / (1024 * 1024), 0),'999,999,999') curr_ts_size,

to_char(round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 0),'999,999,999') used_ts_size

FROM dba_free_space fs,

(select tablespace_name,

sum(bytes) bytes,

sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,

max(autoextensible) autoextensible

from dba_data_files

group by tablespace_name) df

WHERE fs.tablespace_name (+) = df.tablespace_name

GROUP BY df.tablespace_name, df.bytes, df.maxbytes

UNION ALL

SELECT df.tablespace_name tablespace_name,

max(df.autoextensible) auto_ext,

to_char(round(df.maxbytes / (1024 * 1024), 2),'999,999,999') max_ts_size,

round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 0) max_ts_pct_used,

to_char(round(df.bytes / (1024 * 1024), 2),'999,999,999') curr_ts_size,

to_char(round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2),'999,999,999') used_ts_size

FROM (select tablespace_name, bytes_used bytes

from V$temp_space_header

group by tablespace_name, bytes_free, bytes_used) fs,