This discussion is archived
8 Replies Latest reply: Feb 6, 2013 1:33 AM by 987709 RSS

tablespace not released space by purging recyclebin

987709 Newbie
Currently Being Moderated
Hi,

We have purged entire Recyclebin and now there is no object available.
before purging recyclebin we have same tablespace 89.5% full and after purge recycle bin we suppose to get 20 GB free more space.

but we didnt get 20 gb free space.

We are using Oracle database 11.2.0.3.0.

Let me know how to reclaim unused space from tablespace?

Please do needful.

Thank you,
Nitin
  • 1. Re: tablespace not released space by purging recyclebin
    Rob_J Journeyer
    Currently Being Moderated
    Hi,

    I think this is because other segments will have created extents further along in the datafiles, so removing that data might not show the tablespe as having more free space because the high water mark will remain increased. But the database should be able to use up the freed space from within the datafiles as more extents are created. If you are using uniform extent sizes and locally managed, automatic segment space management tablespaces it should take care of it automatically.

    If you want to reduce the high water mark you will need to rebuild/move the tables/indexes so that they get rebuilt at the lower end of the datafiles.

    Rob
  • 2. Re: tablespace not released space by purging recyclebin
    moreajays Pro
    Currently Being Moderated
    Hi,

    Free Space was reflected in dba_free_space view when you dropped the object.
    Post deletion of objects oracle renames the object to hash value & update the free bytes , when space pressure appears hexa valued object automatically gets deleted
     select OWNER,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP,CAN_PURGE from dba_recyclebin;
    OWNER                          OBJECT_NAME                    ORIGINAL_NAME                    DROPTIME            CAN_UNDROP CAN_PURGE
    ------------------------------ ------------------------------ -------------------------------- ------------------- --- ---
    BOREP                          BIN$XSEhIoYR0UDgQzuiIZTRQA==$0 TEST                             2008-12-03:13:51:23 YES YES
    here TEST tables was dropped on DROPTIME & CAN_UNDROP is yes means objects still retained in recyclebin to be flashback.

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 3. Re: tablespace not released space by purging recyclebin
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    984706 wrote:
    Hi,

    We have purged entire Recyclebin and now there is no object available.
    before purging recyclebin we have same tablespace 89.5% full and after purge recycle bin we suppose to get 20 GB free more space.

    but we didnt get 20 gb free space.
    What SQL did you run to come to show the 89.5% and the 20 GB freeable space ?
    If we can see what you've done (and the results) we may be able to tell you what your results mean.

    Regards
    Jonathan Lewis

    P.S. For fixed font output, put the word "code" in curly brackets "{" and "}" on the line before and line after your output.
  • 4. Re: tablespace not released space by purging recyclebin
    987709 Newbie
    Currently Being Moderated
    Hi Jonathan,

    I am using following query get free tablespace size.

    select tablespace_name "Tablespace Name",
    round(decode(Maxsize,0, CurrentSize,Maxsize)/1024/1024,1) "Max
    (Extendable)",
    round(CurrentSize/1024/1024,0) "Current Size",
    round(decode(free,null,0,free)/1024/1024,2) "Free Space",
    round((CurrentSize-decode(free,null,0,free))/1024/1024,2)"Used Space",
    round(100*(CurrentSize-decode(free,null,0,free))/1024/1024/(decode(Maxsize,0
    , CurrentSize,Maxsize)/1024/1024),1) "Percent Used"
    from ( select tablespace_name, sum(maxbytes) Maxsize, sum(bytes) CurrentSize
    ,
    (select sum(bytes) from dba_free_space b
    where b.TABLESPACE_NAME=a.TABLESPACE_NAME) free
    from dba_data_files a group by tablespace_name
    union all (Select d.tablespace_name, maxbytes, (f.bytes_free +
    f.bytes_used)TotalK ,(f.Bytes_used)
    from SYS.V_$TEMP_SPACE_HEADER f , DBA_TEMP_FILES d
    where f.tablespace_name(+) = d.tablespace_name and f.file_id(+) =
    d.file_id ) )
    order by "Percent Used" desc;


    i dont have any query or any way to check freeable size in tablespace after purge recyle bin.

    procedure i followed is

    1. first i checked object size in recyclebin.
    2. then i dropped it. before dropped object from recyclebin, i took tablespace size is 89% used.
    3. still tablepsace size is increasing.

    its should be decreased tablespace used space.

    Thank you,
    Nitin
  • 5. Re: tablespace not released space by purging recyclebin
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    984706 wrote:

    i dont have any query or any way to check freeable size in tablespace after purge recyle bin.

    procedure i followed is

    1. first i checked object size in recyclebin.
    2. then i dropped it. before dropped object from recyclebin, i took tablespace size is 89% used.
    3. still tablepsace size is increasing.

    its should be decreased tablespace used space.
    Space allocated to objects in the recyclebin is reported in dba_free_space - so purging the recycle bin will not change the output from your query.

    Regards
    Jonathan Lewis
  • 6. Re: tablespace not released space by purging recyclebin
    845641 Newbie
    Currently Being Moderated
    Jonathan Sir then when will the space be reclaimed can you be more clear ????? Do you mean when we purge recyclebin, tables are not gone away ??, HWM should also get washed away when the tables should go , isnt it ? So what does purge recyclebin do thats the new question ??

    Edited by: 842638 on Feb 4, 2013 12:37 PM
  • 7. Re: tablespace not released space by purging recyclebin
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    842638 wrote:
    Jonathan Sir then when will the space be reclaimed can you be more clear ????? Do you mean when we purge recyclebin, tables are not gone away ??, HWM should also get washed away when the tables should go , isnt it ? So what does purge recyclebin do thats the new question ??
    Try this experiment:

    create a table with an initial allocation of 100MB
    Check dba_free_space
    drop the table (but without the purge option)
    Check dba_free_space - do you see any change in the output or not ?
    Purge the recyclebin
    Check dba_free_space - do you see any change in the output or not ?


    Regards
    Jonathan Lewis
  • 8. Re: tablespace not released space by purging recyclebin
    987709 Newbie
    Currently Being Moderated
    Hi,

    Thank you Jonathan,

    I will try your suggest and will get back to you soon.

    Thank you,
    Nitin

Legend

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