37 lines
1.3 KiB
SQL
37 lines
1.3 KiB
SQL
/*
|
|
gives the total_amount of mb free space and the 10 biggest extents
|
|
per tablespace of type PERMANENT (with the number of times this extent size extists)
|
|
|
|
This query can be used to verify the fragmentation of a tablespace
|
|
*/
|
|
|
|
set linesize 120
|
|
set pagesize 9999
|
|
column tablespace_name format a30
|
|
column total_free_mb format 9G999G990D999
|
|
column extent_mb format 9G999G990D999
|
|
column counted format 9G999G990
|
|
break on tablespace_name skip 1 on total_free_mb
|
|
|
|
select A.tablespace_name, total_free/1024/1024 total_free_mb, bytes/1024/1024 extent_MB,
|
|
counted
|
|
from ( select tablespace_name, bytes, counted,
|
|
row_number()
|
|
over ( partition by tablespace_name
|
|
order by bytes desc
|
|
) rn
|
|
from ( select A.tablespace_name, A.bytes , count(*) counted
|
|
from dba_free_space A, dba_tablespaces B
|
|
where A.tablespace_name = B.tablespace_name
|
|
and B.contents = 'PERMANENT'
|
|
group by A.tablespace_name, bytes
|
|
)
|
|
) A,
|
|
( select tablespace_name, sum(bytes) total_free
|
|
from dba_free_space
|
|
group by tablespace_name
|
|
) B
|
|
where A.tablespace_name = B.tablespace_name
|
|
and A.rn <= 10
|
|
order by tablespace_name, rn;
|