Scripts - Tablespace Related

Tablespace Size

select sysdate,(select decode(autoextensible,'YES','+',' ') ||
decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc/1024 kbytes,
(kbytes_alloc/1024)-nvl(kbytes_free,0)/1024 used,
round(nvl(kbytes_free,0)/1024,2) free,
round(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,2) pct_used,
round((nvl(kbytes_free,0)/kbytes_alloc)*100,2)pct_free,
round(kbytes_max/1024,2) Max_Size,
round((kbytes_max/1024) - ((kbytes_alloc/1024)-nvl(kbytes_free,0)/1024),2) MaxMb_GW
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(case autoextensible
when 'YES'
then
(greatest(maxbytes-bytes,0)/1024)
end) +sum(bytes/1024) Kbytes_max, --sum(maxbytes)/1024 Kbytes_max,
tablespace_name, max(autoextensible) autoextensible
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(case autoextensible
when 'YES'
then
(greatest(maxbytes-bytes,0)/1024)
end) +sum(bytes/1024) kbytes_max, --sum(maxbytes)/1024 Kbytes_max,
tablespace_name, max(autoextensible) autoextensible
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
/

or

SELECT sysdate, Total.tablespace_name Tablespace_Name,
Dt.EXTENT_MANAGEMENT,
total_space total_Size_MB,
round(nvl(total_space-free_space,0),2) used_MB,
round(nvl(free_space,0),2) free_MB,
round(nvl(total_space-free_space,0)/total_space*100,2) pct_used,
round(nvl(free_space,0)/total_space*100,2) pct_free,
round(Maxsize,2) Maxsize_MB
FROM
(select tablespace_name,
sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select TABLESPACE_NAME, sum(MAXBYTES)/1024/1024 Maxsize,
sum(bytes)/1024/1024 Total_space
From dba_data_files
group by tablespace_name ) Total,
(select TABLESPACE_NAME,
EXTENT_MANAGEMENT
from dba_tablespaces
order by TABLESPACE_NAME) dt
WHERE Free.Tablespace_name(+) = Total.tablespace_name
and total.Tablespace_name = dt. TABLESPACE_NAME
ORDER BY Total.tablespace_name,dt.tablespace_name
/

Upto how much such we can resize the Datafile:-

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column cmd format a75 word_wrapped
column value new_val blksize

select value from v$parameter where name = 'db_block_size'
/
column cmd format a75 word_wrapped
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
Ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/





Tablespace - Free space Fragmented

select
tablespace_name, count(*) fragments,
decode(round((sum(bytes) / 1024000),2),null,0,round((sum(bytes) / 1024000),2)) total,
decode(round((max(bytes) / 1024000),2),null,0,round((max(bytes) / 1024000),2)) largest,
nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))),2),0) fragmentation_index
from
sys.dba_free_space
group by
tablespace_name
order by
2 desc, 1
/

More about Free space Fragmentation:-

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:733126913855



Script for finding out actual free space and usage considering the autoenxtend on.

select to_char(sysdate,'YYYYMMDD HH24MI') "YYYYMMDD-HrMi", (select name from v$database) DB_NAME,
a."Extent_Mngt Tablespace",
a.MB_alloc,a.MB_used,a.MB_MaxFree,round((a.MB_MaxFree*100)/a.MB_alloc) PCT_Free
from (select substr((select decode(autoextensible,'YES','A','N') ||
decode(extent_management,'LOCAL','*L','*D') ||
decode(segment_space_management,'AUTO','*Seg-A -> ','*Seg-M -> ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')),1,35) "Extent_Mngt Tablespace",
round(kbytes_alloc/1024) MB_alloc,
round((kbytes_alloc/1024)-nvl(kbytes_free,0)/1024) MB_used,
(CASE WHEN round(nvl(kbytes_free,0)/1024)-round((kbytes_max/1024) - ((kbytes_alloc/1024)-nvl(kbytes_free,0)/1024)) LIKE '-%' THEN round((kbytes_max/1024) - ((kbytes_alloc/1024)-nvl(kbytes_free,0)/1024))
ELSE round(nvl(kbytes_free,0)/1024)
END) MB_MaxFree,
round(kbytes_alloc/1024*0.05) Threshold_MB
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(case autoextensible
when 'YES'
then
(greatest(maxbytes-bytes,0)/1024)
end) +sum(bytes/1024) Kbytes_max, --sum(maxbytes)/1024 Kbytes_max,
tablespace_name, max(autoextensible) autoextensible
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(case autoextensible
when 'YES'
then
(greatest(maxbytes-bytes,0)/1024)
end) +sum(bytes/1024) kbytes_max, tablespace_name, max(autoextensible) autoextensible
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by b.tablespace_name) a;



SELECT sysdate, Total.tablespace_name Tablespace_Name,
Dt.EXTENT_MANAGEMENT,
Mbytes_alloc Allocated_Size_MB, 
nvl(Mbytes_max,Mbytes_alloc) total_Size_MB,
round(nvl(used_spaces,0),2) Used_MB, 
round(nvl(Mbytes_max,Mbytes_alloc)-nvl(Used_spaces,0),2) Free_MB,
round(nvl(Used_spaces,0)/nvl(Mbytes_max,Mbytes_alloc)*100,2) pct_used,
round((1-(nvl(Used_spaces,0)/nvl(Mbytes_max,Mbytes_alloc)))*100,2) pct_free
FROM
(select tablespace_name,
sum(bytes/1024/1024) Used_spaces
from sys.dba_segments
group by tablespace_name
) Free,
(
select sum(bytes)/1024/1024 Mbytes_alloc,
sum(
case 
when autoextensible = 'YES' then (greatest(maxbytes-bytes,0)/1024/1024)
end) 
+
sum(bytes/1024/1024) Mbytes_max, 
tablespace_name
from sys.dba_data_files
group by tablespace_name ) Total,
(select TABLESPACE_NAME,
EXTENT_MANAGEMENT
from dba_tablespaces
order by TABLESPACE_NAME) dt
WHERE Free.Tablespace_name(+) = Total.tablespace_name
and total.Tablespace_name = dt. TABLESPACE_NAME
ORDER BY pct_free
/