Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, DEEPAK.

Asked: June 12, 2008 - 10:55 am UTC

Last updated: June 12, 2008 - 2:46 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi Tom,

It is really a privilege for me to start a discussion thread on your great site.

In our database we had recyclebin enabled at database level. After few months we decided to turn off recyclebin at database level. However we did not purge the recyclebin after turning off recyclebin.

After few days (of turning off recyclebin) I got the following exception.

ORA-01654: Unable to extend index %s.%s by %s in tablespace %s

I queried the dba_segments view and tried to figure out which indexes/objects are sitting on my index tablespace because of which the above exception happened. I found that almost 90% of the space is occupied by recyclebin objects (BIN$...).

As per Oracle documentation when a object needs more space or a new object is created and no free space is available then the space occupied by recyclebin would be reused.

Had that been the case I would not have got the above exception.

After I purged the recyclebin everything ran fine without any issues.

Request you to explain this behaviour of Oracle. Please correct me if my understandings are not correct.

Many thanks for all your help to the Oracle community.

-- Deepak

and Tom said...

Did a little testing, in 10g, setting recyclebin = off entirely disables the recyclebin processing - including the freeing / reclamation of space.

In 11g, setting recyclebin = off disables future objects from going into the recyclebin - but enables existing objects in the recyclebin to have their space reused.

So, by setting recyclebin = off in 10g, that disabled the entire set of recyclebin features - including space reclamation.

In 11g, this behavior was changed (I could not find the bug# for the issue that caused the change...)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Space reclaim happening

DEEPAK, June 12, 2008 - 1:07 pm UTC

Hi Tom,

Thanks a lot for your response.

Sorry Tom here am not trying to contradict you but just now I tested and found that in 10g that the recyclebin space is getting reused (when it is turned off) and when there is not enough space to be allocated(inline with the document). Not sure why it threw that exception on the other day.

The only difference between the two environments is the DB version.

The exception occured on a 10.2.0.1 database and
now I did the testing on 10.2.0.3

I did not find bug fix in 10.2.0.3 related to this behavior as well.


Tom Kyte
June 12, 2008 - 2:46 pm UTC

more specifically - the fix went in for 10.2.0.3 and did not exist in 10.2.0.2 :)

ops$tkyte%ORA10GR2> alter system set recyclebin = on ;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for Linux: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

ops$tkyte%ORA10GR2> create or replace view fs
  2  as
  3  select allocated_k, free_k, recycle_able_k
  4    from
  5  ( select nvl(sum(bytes)/1024,0) free_k from dba_free_space
  6     where tablespace_name = 'DEMO'),
  7  ( select sum(bytes)/1024 allocated_k from dba_data_files
  8     where tablespace_name = 'DEMO'),
  9  ( select nvl(sum(space) * 8,0) recycle_able_k from dba_recyclebin
 10     where ts_name = 'DEMO')
 11  /

View created.

ops$tkyte%ORA10GR2> create tablespace demo datafile size 640k;

Tablespace created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from fs;

ALLOCATED_K     FREE_K RECYCLE_ABLE_K
----------- ---------- --------------
        640        576              0

ops$tkyte%ORA10GR2> create table t ( x int ) storage (initial 576k) tablespace demo;

Table created.

ops$tkyte%ORA10GR2> select * from fs;

ALLOCATED_K     FREE_K RECYCLE_ABLE_K
----------- ---------- --------------
        640          0              0

ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> select * from fs;

ALLOCATED_K     FREE_K RECYCLE_ABLE_K
----------- ---------- --------------
        640        576            576

ops$tkyte%ORA10GR2> alter system set recyclebin = off ;

System altered.

ops$tkyte%ORA10GR2> select * from fs;

ALLOCATED_K     FREE_K RECYCLE_ABLE_K
----------- ---------- --------------
        640        576            576

ops$tkyte%ORA10GR2> create table t ( x int ) storage (initial 576k) tablespace demo;
create table t ( x int ) storage (initial 576k) tablespace demo
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace DEMO


ops$tkyte%ORA10GR2> alter system set recyclebin = on ;

System altered.

ops$tkyte%ORA10GR2> create table t ( x int ) storage (initial 576k) tablespace demo;

Table created.

ops$tkyte%ORA10GR2> alter system set recyclebin = on ;

System altered.


note how the create table fails, no space - until the recyclebin is turned back on.

Thanks a lot Tom

DEEPAK, June 12, 2008 - 2:58 pm UTC

Thanks a lot Tom for your clarification. Am a fan of yours Tom... :)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.