Check free tablespace

select b.tablespace_name, tbs_size SizeMb, to_char(a.free_space/tbs_size * 100,’99.99′) || ‘%’ FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;
column owner format A7
column bytes format 99999999999
break ON owner  ON report
compute SUM  OF bytes ON owner report
 
SELECT owner, table_name, NVL(num_rows*avg_row_len,0) bytes
FROM dba_tables
WHERE tablespace_name=’OWB_DATA’  and NVL(num_rows*avg_row_len,0) >10000
ORDER BY NVL(num_rows*avg_row_len,0) desc
/
select distinct(a.TABLESPACE_NAME) TABLESPACE_NAME,NVL(a.AUTOEXTENSIBLE,’NO’) AUTOEXTD,
(a.INCREMENT_BY*(select value from v$parameter where lower(name)=’db_block_size’))/1024/1024 NEXT_SIZE,
a.bytes/1024/1024 SIZE_MB,
nvl(a.MAXBYTES/1024/1024,’0′) MAXSIZE,a.file_id,
REPLACE(
REPLACE(a.file_name,’/etrade/pkgs/linux/intel/oracle’,’${ORACLE_BASE}’)
,’/etrade/pkgs/oracle’,’${ORACLE_BASE}’)
as DATA_FILE_NAME
from dba_data_files a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name and a.tablespace_name like ‘SPOTFIRE_DATA_500′
order by NVL(a.AUTOEXTENSIBLE,’NO’);

for the shrink (which has datafile number listed) :

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/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(+)  and a.tablespace_name=’&tsname’
/

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/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(+)  and a.tablespace_name=’&tsname’
/

see if this helps:
alter table <tbl name> enable row movement;
alter table <tbl name> shrink space;

Leave a Reply

Your email address will not be published. Required fields are marked *