Dear Team,
My table space has 28 data files each with size of 30g(approx).
Below the query I used to find out each data file size and free space in that data file.
select t1.file_id,total,free from
(select file_id,trunc(bytes/1024/1024/1024) as total
from dba_data_files where tablespace_name='DATA')t1,
(select file_id,trunc(sum(bytes)/1024/1024/1024) as free
from dba_free_space
group by file_id)t2
where t1.file_id=t2.file_id;
FILE_ID TOTAL FREE
22 31 22
25 31 23
30 20 12
34 15 6
6 31 23
11 31 23
13 31 25
14 31 26
20 31 26
21 31 22
26 31 24
31 20 11
24 31 23
32 20 12
5 20 11
8 31 22
17 31 23
23 31 22
33 20 12
7 31 23
18 31 24
27 31 23
9 31 23
10 31 23
12 31 23
15 31 25
16 31 25
19 31 23
As you can see ,each data file has more than 60% free space.
What I want is,to move data from one data file to another and make half of the datafile 100% free and drop them.So that I can use space for another tablespace/database/backup etc.;
One solution I got is use expdp/impdp to delete and recreate tablespace.
I tried to re size the data file,but free extents are scattered around data file ,not at the end of data file.
Is there another possible solution?
I have the luxury of taking the downtime of 3 hours.
First of all, I wouldn't worry too much about the *number* of datafiles. If all you want to do is reclaim some space, then there is a good chance you have some free space at the end of each file.
The script below will ask for a tablespace and then give you a shrink datafile script to reclaim unused space
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