Tablespace: Free space management

set pagesize 100

set linesize 200
break on report

compute sum of free_mb on report
compute sum of act_size on report
compute sum of used_mb on report
col %used format 999.99
col %Free format 999.99
col act_size format 99,99,999.99
col free_mb format 99,99,999.99
col used_mb format 99,99,999.99
col largest_extent_MB format 99,99,999.99
col tablespace for a25

select SUBSTR(a.tablespace_name,1,20) "TABLESPACE",act_size,free_mb,
free_mb*100/act_size "%FREE",
act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB
from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,
max(bytes)/1024/1024 largest_extent_MB
FROM DBA_FREE_SPACE group by tablespace_name) a,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM
DBA_DATA_FILES group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
and a.tablespace_name in ('IOB_DATA_2013')
order by 6
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select file_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name='&tablespace_name' group by file_name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set lines 120
col FILE_NAME for a70
select file_name,bytes/1024/1024,autoextensible,MAXBYTES/1024/1024 from dba_data_files
where tablespace_name in ('IOB_DATA_2013');

alter tablespace TDM_INDX01 add datafile '/data/oracle/LNSMISP1/data37/LNSMISP1_TDM_INDX01_50.dbf' size 6G autoextend off;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter database datafile '/export/data/oracle/NYARADP1/data01/NYARADP1_sysaux_01.dbf' resize 9000m;