This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Nov 16, 2012 7:29 AM by j_DBA_sourav Go to original post RSS
  • 15. Re: Not able to reduce the size of Datafile
    sb92075 Guru
    Currently Being Moderated
    j_DBA_saha wrote:

    SQL> SELECT owner,segment_name FROM dba_extents WHERE tablespace_name = TABLESPACE_NAME' AND block_id (SELECT MAX(block_id) FROM dba_extents WHERE tablespace_name = 'TABLESPACE_NAME');

    ------------------------------ ---------------------------------------------------------------------------------

    if you move object above into different tablespace, then you'll be able to lower High Water Mark
  • 16. Re: Not able to reduce the size of Datafile
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    j_DBA_saha wrote:
    Hello sb92075 ,

    The select * from dba_free_space where tablespace_name='MY TABLESPACE' is retruning almost 5.5K records.. as given below.

    select count(*) from dba_free_space where tablespace_name='TABLESPACE_NAME'
    There are many little details to consider when you run into "simple" problems with space allocation - and sometimes the problem is in the history, in particular problems relating to minimum space requirements for the new object compared to the largest free extent size in the tablespace; alternatively the available space reported by one view compared with the available space after allowing for effects of the recycle bin.

    A few steps to consider
    a) select * from dba_recyclebin where ts_name = '.... your tablespace ....';
    b) purge (dba_)recyclebin;
    c) query dba_free_space and dba_extents together to get a full map of the tablespace.

    For an example of investigating what's in a tablespace here's an article I wrote some time ago that may help:

    Jonathan Lewis
    Author: <b><em>Oracle Core</em></b>
  • 17. Re: Not able to reduce the size of Datafile
    j_DBA_sourav Newbie
    Currently Being Moderated
    requirement partly met.

1 2 Previous Next


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