This content has been marked as final. Show 11 replies
I do not believe this is the right forum for your question. It does not deal with either SQL or PL/SQL - which are the subjects of this forum.
You also neglected to provide any additional information such as Oracle version and whether or not you're using LMTs, auto extending datafiles, and so on.
The very basic reason for a datafile not to shrink is that there are allocated datablocks at the end of the datafile (e.g. 10GB of unused space followed by 1 8KB data block marked in used).
However, the correct answer could just as well be that you will never see that "freed space" on the o/s filesystem as you are mistaken in how Oracle space management works and what Oracle freelists are.
can you tell the following ?
1. What tablespaces do you use ? Local or dictionary managed
2. What version of Oracle do you use ?
If you are on 10g, you could use
Alter table table_name shrink or compact based on the load on the database. But unless you furnish complete information, it would be very difficult to answer.
here is an example.
SQL> set linesize 150
SQL> column file_name format a50
SQL> column tablespace_name format a10
SQL> column free_space format 9999.9999
SQL> select ddf.file_name
3 ,sum(dfs.bytes)/1024/1024 free_space
4 from dba_data_files ddf, dba_free_space dfs
5 where ddf.file_id = dfs.file_id
6 and ddf.tablespace_name like 'USERS'
7 group by ddf.file_name,ddf.tablespace_name
FILE_NAME TABLESPACE FREE_SPACE
-------------------------------------------------- ---------- ----------
D:\DEV9IDB\ORADATA\DEV9I\USERS01.DBF USERS 23.4375
Thanks to all
I have resized my datafiles. I found OEMs GUI the best way to find free space.
Some file get resize very gently from following statement
DATAFILE 'disk1:db1.dat' RESIZE 10 M;
Others are not ready to loss a single M space. (Give error like got more data . Sorry dont remember the exact wordings)
I got some ambiguity but I personal feel that some files have free space at end and got resize easily other have free spaces in middle and not got resize.
If this is the case tell me how to deal with it. If this is not the case can any expert tell me the reasons why file not got resized?