Skip to content
August 22, 2014 / doganay

TABLESPACE FREE SPACE SCRIPT WITH CUSTOM DBA_FREE_SPACE

hi, there is a bug with dba_free_space (Bug 4215578 : VIEW USER_FREE_SPACE VERY SLOW), so you can use this script to find out your free space:

 
set pagesize 1000
col "SEGMENTS in TB(s)" format 999.99
col total_size format 999.99

CREATE OR REPLACE VIEW sys.doganay_free_space (
tablespace_name,
file_id,
block_id,
bytes,
blocks,
relative_fno )
AS
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and u.ktfbuefno = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0
/

SELECT h.tablespace_name,
ROUND (SUM (NVL (p.bytes_used, 0))/SUM (h.bytes_free + h.bytes_used)*100,2) prct,
ROUND (SUM ((h.bytes_free + h.bytes_used)- NVL (p.bytes_used, 0))/1024/1024,2) free__mb,
ROUND (SUM (h.bytes_free + h.bytes_used) / 1024/1024,2) total__mb
FROM SYS.v_$temp_space_header h,
SYS.v_$temp_extent_pool p,
dba_temp_files f
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND f.file_id = h.file_id
AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name
order by 2 desc;

SELECT tablespace_name,
ROUND (100 - (remaining_bytes / total_max_bytes) * 100, 2)
dpercent,
ROUND (remaining_bytes / 1024 / 1024) AS free_megabytes,
ROUND (total_max_bytes / 1024 / 1024)
AS total_max_megabytes
FROM (SELECT tablespace_name,
NVL (free_bytes, 0)
+ CASE
WHEN total_max_bytes - total_current_bytes <
0
THEN
0
ELSE
total_max_bytes - total_current_bytes
END
remaining_bytes,
total_max_bytes
FROM (SELECT a.tablespace_name,
b.free_bytes free_bytes,
a.total_current_bytes,
a.total_max_bytes
FROM ( SELECT tablespace_name,
SUM (bytes) total_current_bytes,
SUM (
CASE
WHEN autoextensible =
'YES'
AND maxbytes - bytes >
0
THEN
maxbytes
ELSE
bytes
END)
total_max_bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
( SELECT tablespace_name,
SUM (bytes) free_bytes
FROM doganay_free_space
GROUP BY tablespace_name) b,
dba_tablespaces c
WHERE a.tablespace_name =
b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
AND c.contents = 'PERMANENT'));

--prompt "SEGMENT:"
--select sum(bytes)/1024/1024/1024/1024 "SEGMENTS in TB(s)" from dba_segments;

prompt "DATA_FILES+TEMP+REDO+CF: (in TB)"
select (a.data_size+b.temp_size+c.redo_size+d.cont_size)/1024/1024/1024/1024 "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$logfile lf, sys.v_$log l
where lf.group# = l.group#) c,
( select sum(block_size*file_size_blks) cont_size
from v$controlfile ) d;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: