This discussion is archived
4 Replies Latest reply: Oct 30, 2013 9:58 AM by jgarry RSS

Tablespace allocation issues

1006594 Newbie
Currently Being Moderated

Oracle 11gR2 (11.2.0.3) Linux 64bit

 

I am having a strange issue with our datafile growth.

 

We have a tablespace that is sitting on a mountpoint that has 30gb left before it is full. The last datafile in the tablespace is currently at 11gb used space and it set to grow to the maxsize (32g).

So the dilemma is partly that it does not have room to grow to the maxsize as the OS space is only 30G. But my question is that why does it allocate all the way to the end of the datafile (and hence fill up the OS space) when it is set to increment 100MB? The growth of this TBS is about 4GB in the entire day. Could this growth have to do with the Space Management Coordinator Process (SMCO)?

If so, how can we resolve this issue? Obviously I am aware that we are running out of space and the best solution is to fix that, but what can be done immediately?

 

Thanks all.

  • 1. Re: Tablespace allocation issues
    sb92075 Guru
    Currently Being Moderated

    is any SQL doing INSERT /*+ APPEND */ into this tablespace

  • 2. Re: Tablespace allocation issues
    fa5c2018-c9fa-4f6a-8ab8-6e09de4d7349 Newbie
    Currently Being Moderated

    If you set maxsize to 32GB, then there is no surprise the datafile to grow up to maxsize. Then increament 100MB you talking about probably the extent size. Datafile grow based on maxsize.

    I would check using dba_segments to find out who the biggest consumer on the tablespace to have some idea what is consuming the tablespace.

    for immediate action I would increase the tablespace size if not possible, I would try to move objects to different tablespace. If you have indexes on this tablespace rebuilt it on different tablespace.

  • 3. Re: Tablespace allocation issues
    Mark D Powell Guru
    Currently Being Moderated

    1006594, I am not sure I understand everything you posted but if you tablespace is defined using extendable data files those files extent in increment_by amounts up to maxbytes/maxblocks size.  The file will only extend if objects in the tablespace are extending.

     

    As SB92075 suggested look for insert append operations as these go into emtpy blocks rather than reuse existing space.  You would also want to check for parallel insert operations as each slave aims for a new extent.  These operations could consume more space than if traditional insert operations were performed.

    In the meantime you should consider moveing a datafile to another file system with more room available.  The instructions on how to do this can be found in the DBA Administrators Guide chapters on managing tablespaces.

     

    You could also look for indexes and tables defined with the tablespace in question that may be consuming significant space above what should be necessary to hold the data.  These would be canidates for rebuild or shrink operations.

     

    You also have the option of altering the maxbytes value down so that the file cannot consume 100% of the existing disk; however you still need to take action to expand the disk file systme, move a file, or reduce consumtion within the tablespace before you run out of space.

     

    HTH -- Mark D Powell --

  • 4. Re: Tablespace allocation issues
    jgarry Guru
    Currently Being Moderated

    What exactly do you mean allocate to the end of the data file?  How are you determining this?  Are you dropping tables without purging them?

     

    Or are you just looking at the data file, seeing that it is full up to the end.  That would be normal, it would add incrementally as needed with autoextend.  If you have many data files with empty space, Oracle may round-robin when adding data, but if you have some full ones and one autoextend, it will add to the autoextend one.

     

    There are obscure bugs, one example is ASSM and non-standard blocksizes or something like that.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points