Dear Ask Tom Team,
I hope you had wonderful vacations and happy new year!!!
I have question related to Table's Space Reclaiming using "shrink" command.
There are some fragmented tables in our DB. I found their current size and wasted space using below command:
select table_name,blocks,num_rows,avg_row_len,round(((blocks*32/1024)),2) "TOTAL_SIZE_MB",
round((num_rows*avg_row_len/1024/1024),2) "ACTUAL_SIZE_MB",
round(((blocks*32/1024)-(num_rows*avg_row_len/1024/1024)),2) "FRAGMENTED_SPACE_MB"
from dba_tables
where partitioned='NO'
and owner not like 'SYS%'
and round(((blocks*32/1024)-(num_rows*avg_row_len/1024/1024)),2) > 100;
It gives me list of tables having fragmented space greater than 100 MB. for example:
TABLE_NAME BLOCKS NUM_ROWS AVG_ROW_LEN TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
------------------------------ ---------- ---------- ----------- ------------- -------------- -------------------
XXX_LOG 115486 24292600 134 3608.94 3104.41 504.53
XXX_25M 25497 807020 819 796.78 630.33 166.45
When I ran below command it works as per expectation (i.e. by defragmenting tables it free up some space) but for above 2 tables it's not behaving as per my expectation :
alter table username.XXX_LOG shrink space;
Also, second question is for tables who's size is reduced, i cant see size reduction in their tablespace's datafiles. so what should i do to free up datafile's size and if i didnt do anything then can that reclaimed size is still available in datafile for new tables/indexes?
Could you please answer my questions and also please clear my concept if i'm wrong (that above command should free up some space if there's fragmentation).
Thank You,
Amol V. Palav
Be aware that those fragmentation estimates are only as good as the last time you gathered statistics on those tables, and how you gathered the statistics. So its quite possible that the two tables in question are actually not fragmented at all.
In terms of freeing up space in datafiles, is not the space consumption that is the issue, but *where* that space is being consumed. For example, if I have three tables in a datafile, like this:
Then even if I drop table T2, which makes the file almost empty, the fact that table T3 is positioned at the tail of file, means that I cannot reduce the file size at all. I would have to perform an "alter table T3 move" to shuffle it closer to the start of the file.
You can use this script to see what kind of datafile reduction is possible
set verify off
undefine tablespace
select '&&tablespace' from dual;
column file_name format a64 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
compute sum of currsize on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
drop table ext purge;
create table ext pctfree 0 as select file_id,block_id,blocks from dba_extents;
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 ext -- dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and a.tablespace_name = nvl(upper('&&tablespace'),a.tablespace_name)
order by 1
/
column cmd format a75 word_wrapped
select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,8)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from ext -- dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 10
and a.tablespace_name = nvl(upper('&&tablespace'),a.tablespace_name)
/
--drop table ext purge;
undefine tablespace