You can only shrink it to the top of high water mark...
try these scripts to see hor far you can go to resize your datafiles....
Code:
set verify off
appdev@NICK817>column file_name format a50 word_wrapped
appdev@NICK817>column smallest format 999,990 appdev@NICK817>heading "Smallest|Size|Poss."
appdev@NICK817>column currsize format 999,990 heading "Current|Size"
appdev@NICK817>column savings format 999,990 heading "Poss.|Savings"
appdev@NICK817>break on report
appdev@NICK817>compute sum of savings on report
column value new_val blksize
appdev@NICK817> select file_name,
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
3 ceil( blocks*&&blksize/1024/1024) currsize,
4 ceil( blocks*&&blksize/1024/1024) -
5 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
6 from dba_data_files a,
7 ( select file_id, max(block_id+blocks-1) hwm
8 from dba_extents
9 group by file_id ) b
10 where a.file_id = b.file_id(+)
11 /
Smallest
Size Current Poss.
FILE_NAME Poss. Size Savings
-------------------------------------------------- -------- -------- --------
E:\ORACLE\ORADATA\NICK817\SYSTEM01.DBF 55 264 209
H:\ORACLE\ORADATA\NICK817\RBS01.DBF 30 200 170
F:\ORACLE\ORADATA\NICK817\TEMP01.DBF 1 72 71
E:\ORACLE\ORADATA\NICK817\INDX01.DBF 1 58 57
F:\ORACLE\ORADATA\NICK817\TOOLS01.DBF 1 12 11
G:\ORACLE\ORADATA\NICK817\USERS01.DBF 1 108 107
--------
sum 625
6 rows selected.
Then run this to resize them..
Code:
appdev@NICK817> column cmd format a75 word_wrapped
appdev@NICK817>
appdev@NICK817> select 'alter database datafile '''||file_name||''' resize ' ||
2 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
3 from dba_data_files a,
4 ( select file_id, max(block_id+blocks-1) hwm
5 from dba_extents
6 group by file_id ) b
7 where a.file_id = b.file_id(+)
8 and ceil( blocks*&&blksize/1024/1024) -
9 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
10 /
CMD
---------------------------------------------------------------------------
alter database datafile 'E:\ORACLE\ORADATA\NICK817\SYSTEM01.DBF' resize
55m;
alter database datafile 'H:\ORACLE\ORADATA\NICK817\RBS01.DBF' resize 30m;
alter database datafile 'F:\ORACLE\ORADATA\NICK817\TEMP01.DBF' resize 1m;
alter database datafile 'E:\ORACLE\ORADATA\NICK817\INDX01.DBF' resize 1m;
alter database datafile 'F:\ORACLE\ORADATA\NICK817\TOOLS01.DBF' resize 1m;
alter database datafile 'G:\ORACLE\ORADATA\NICK817\USERS01.DBF' resize 1m;
6 rows selected.
HTH