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....
Then run this to resize them..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.
HTHCode:
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.
