Different Ways To Check The Tablespace Size
Script 1.
set colsep |
set linesize 100 pages 100 trimspool on numwidth 14
col name format a25
col owner format a15
col "Used (GB)" format a15
col "Free (GB)" format a15
col "(Used) %" format a15
col "Size (M)" format a15
SELECT d.status "Status",
d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024,
0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0),
0)/1024/1024 /1024,'99999999.99') "Used (GB)", TO_CHAR(NVL(f.bytes / 1024 / 1024 /1024,
0),'99,999,990.90') "Free (GB)", TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) /
a.bytes * 100, 0), '990.00') "(Used) %" FROM
sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name)
a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name)
f WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = f.tablespace_name(+) AND NOT (d.extent_management like 'LOCAL' AND
d.contents like 'TEMPORARY') UNION ALL SELECT d.status "Status", d.tablespace_name
"Name", TO_CHAR(NVL(a.bytes / 1024 / 1024 /1024,
0),'99,999,990.90') "Size (GB)", TO_CHAR(NVL(t.bytes,0)/1024/1024
/1024,'99999999.99') "Used (GB)", TO_CHAR(NVL((a.bytes -NVL(t.bytes, 0)) / 1024
/ 1024 /1024, 0),'99,999,990.90') "Free (GB)",TO_CHAR(NVL(t.bytes / a.bytes * 100, 0),
'990.00') "(Used) %" FROM
sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name)
a, (select
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by
tablespace_name) t WHERE
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name =
t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND
d.contents like 'TEMPORARY';
Script 2.
set lines 135
column TABLESPACE_NAME for a20
column FILE_NAME for a93
set lines 220 pages 220
SELECT
df.tablespace_name, df.size_mb, f.free_mb, df.max_size_mb, f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb, df.file_name FROM (SELECT file_id, file_name, tablespace_name, TRUNC(bytes/1024/1024) AS size_mb, TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb FROM dba_data_files) df, (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb, file_id FROM dba_free_space GROUP BY file_id) f WHERE df.file_id = f.file_id (+) ORDER BY
df.tablespace_name, df.file_name;
Script 3.
select fs.tablespace_name "Tablespace", (df.totalspace - fs.freespace) "Used MB", fs.freespace "Free
MB", df.totalspace "Total
MB", round(100
* (fs.freespace / df.totalspace)) "Pct. Free" from (select tablespace_name, round(sum(bytes) / 1048576) TotalSpace from dba_data_files group by tablespace_name ) df, (select tablespace_name, round(sum(bytes) / 1048576) FreeSpace from dba_free_space group by tablespace_name ) fs where df.tablespace_name = fs.tablespace_name order by
1;
Script 4.
set pages 80
set lin 120
set echo off
set feed off
column PCT format 999.99
column tbspce format A30
col container for a30
select substr(f.tablespace_name,1,30) tbspce, round(f.tsbytes/(1024*1024),0) "ALLOCATED(MB)", round(nvl(s.segbytes,0)/(1024*1024),0) "USED(MB)", round((nvl(s.segbytes,0)/f.tsbytes)*100,2) PCT, lower(vc.name) as container from (select
con_id,tablespace_name,sum(bytes) tsbytes from cdb_data_files group by
con_id,tablespace_name) f, (select
con_id,tablespace_name,sum(bytes) segbytes from cdb_segments group by
con_id,tablespace_name) s, v$containers vc where f.con_id=s.con_id(+) and
f.tablespace_name=s.tablespace_name(+) and
f.con_id=vc.con_id order by container, tbspce;
Script 5.
set pages 999
set lines 400
SELECT df.tablespace_name tablespace_name, max(df.autoextensible) auto_ext, round(df.maxbytes / (1024 * 1024), 2)
max_ts_size,round((df.bytes - sum(fs.bytes)) /
(df.maxbytes) * 100, 2) max_ts_pct_used,round(df.bytes / (1024 * 1024), 2)
curr_ts_size,round((df.bytes - sum(fs.bytes)) / (1024 *
1024), 2) used_ts_size,round((df.bytes-sum(fs.bytes)) * 100 /
df.bytes, 2) ts_pct_used,round(sum(fs.bytes) / (1024 * 1024), 2)
free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2)
ts_pct_free 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, round(df.maxbytes / (1024 * 1024), 2)
max_ts_size, round((df.bytes - sum(fs.bytes)) /
(df.maxbytes) * 100, 2) max_ts_pct_used, round(df.bytes / (1024 * 1024), 2)
curr_ts_size, round((df.bytes - sum(fs.bytes)) / (1024 *
1024), 2) used_ts_size, round((df.bytes-sum(fs.bytes)) * 100 /
df.bytes, 2) ts_pct_used, round(sum(fs.bytes) / (1024 * 1024), 2)
free_ts_size, nvl(round(sum(fs.bytes) * 100 / df.bytes), 2)
ts_pct_free FROM (select tablespace_name, bytes_used bytes from
V$temp_space_header group by
tablespace_name, bytes_free, bytes_used) fs, (select
tablespace_name, sum(bytes)
bytes, sum(decode(maxbytes, 0, bytes, maxbytes))
maxbytes, max(autoextensible) autoextensible from
dba_temp_files group by
tablespace_name) df WHERE fs.tablespace_name (+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytes, df.maxbytes ORDER BY 4 DESC;
