0 Replies Latest reply: Oct 11, 2012 10:53 AM by 963625 RSS

    Oracle Tablespace issue

    963625
      Hi,
      I am having problem with one of our tablespace. I receive a error message saying unable to extent tablespace very often, even though we have enough space in tablespace, And i also observerd that max(bytes) in dba_free_Space is decreasing everyday. We have oracle 11g and tablespaces are locally manged with ASSM.
      Space in our tablespace is as follows:
      TSName Tot_Size Tot_free PCT_free Max_Free Chunk_Free
      User 44,746,932,224 3,656,908,800 8 1,966,080 4,668

      I get the above result when i execute the following query:

      select a.tablespace_name,sum(a.tots) Tot_Size,
      sum(a.sumb) Tot_Free,
      sum(a.sumb)*100/sum(a.tots) Pct_Free,
      sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free
      from
      (
      select tablespace_name,0 tots,sum(bytes) sumb,
      max(bytes) largest,count(*) chunks
      from dba_free_space a
      group by tablespace_name
      union
      select tablespace_name,sum(bytes) tots,0,0,0 from
      dba_data_files
      group by tablespace_name) a
      group by a.tablespace_name;

      Why is the Max_free very less and it keep decreasing daily. I tried to shrink tables and rebuild indexes to gain some space , nothing helped. Can anyone help me if i am missing anything here ? And also please let me know if you need more information

      Thanks for your time.