1 2 Previous Next 17 Replies Latest reply: Nov 16, 2012 9:29 AM by j_DBA_sourav Go to original post RSS
      • 15. Re: Not able to reduce the size of Datafile
        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');

        ------------------------------ ---------------------------------------------------------------------------------
        USER1 ODS_JDE_F0902_BKP

        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
          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
            requirement partly met.

            1 2 Previous Next