Hi,
I have datafile with following free sizes(After the Advanced Compression) used following query to find out used/free spaces.
Select ds.file_name,round(ds.total_size - fs.free_space) USED_SIZE, (round(ds.total_size - fs.free_space)/DS.TOTAL_SIZE) "USED_%"
,FS.FREE_SPACE, (FS.FREE_SPACE/DS.TOTAL_SIZE) * 100 "FREE_%"
from (select sum(bytes/1024/1024) free_space,file_id from dba_free_space where tablespace_name='&TBS' group by file_id) fs,
(select file_id,file_name,bytes/1024/1024 total_size from dba_data_files where tablespace_name='&TBS')ds
where fs.file_id=ds.file_id order by FS.file_id
/
FILE_NAME USED_SIZE USED_% FREE_SPACE FREE_%
-------------------------------------------------- ---------- ---------- ---------- ----------
/oracle/RWP/sapdata1/sr3_101/sr3.data101 4263 .1421 25736.5625 85.7885417
/oracle/RWP/sapdata1/sr3_102/sr3.data102 4185 .1395 25815.0625 86.0502083
so, I am leaving/retaining Double the amount of space to the datafile for future use and trying to resize and release the space to OS level.
But I am getting following errors.
13:11:48 29-SEP-2014 at rwp> alter database datafile '/oracle/RWP/sapdata1/sr3_101/sr3.data101' resize 25000M;
alter database datafile '/oracle/RWP/sapdata1/sr3_101/sr3.data101' resize 25000M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
Above error causes due if there is any issues in the OS level ???
will it be any other issue ???
If the above issue is due to High water mark in the datafile is not in right place, then what could be the easiast/fastest way to get the high water mark with defragment the objects in the datafiles.
If we want to resize the datafile did we always need to do re-organizations ?????
Pls. explore your past experience for the above.
Thanks in Advance,
Siva