ORA-01652 even though there is sufficient space in RECYCLE BIN

There is a bug 6977045 which may cause ORA-1652 raised even though there is sufficient space in RECYCLE BIN. Version under 11.2 believed to be affected

[oracle@rh2 ~]$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.


Bug 6977045  ORA-1652 even though there is sufficient space in RECYCLE BIN
 This note gives a brief overview bug 6977045.
 The content was last updated on: 06-DEC-2010
 Click here for details of each of the sections below.
Affects:

    Product (Component)	Oracle Server (Rdbms)
    Range of versions believed to be affected 	Versions BELOW 11.2
    Versions confirmed as being affected

        11.1.0.7

    Platforms affected	Generic (all / most platforms affected)

Fixed:

    This issue is fixed in

        11.2.0.1 (Base Release)
        11.1.0.7 Patch 32 on Windows Platforms

Symptoms:

Related To:

    Error May Occur
    Storage Space Usage Affected
    ORA-1652



    Recycle Bin

Description

    Under space pressure an ORA-1652 may be signalled even if there is sufficient
    space in the recyclebin.

    Rediscovery Notes:
     Under space pressure, space allocation fails, even though there
     is sufficient free space in recycle bin.

    Workaround
     Turn off the recycle bin.
     OR
     Purge the recyclebin.

Hdr: 12582291 11.1.0.7 RDBMS 11.1.0.7 SPACE PRODID-5 PORTID-59
Abstract: UPDATING A LOB FAILS WHILE CLEARING RECYCLE BIN EVEN WHEN ENOUGH FREE SPACE IS A

  BUG TYPE CHOSEN
  ===============
  Code

  SubComponent: Recovery
  ======================
  DETAILED PROBLEM DESCRIPTION
  ============================
  An OCI application module tried to update a LOB object, and this operation
  internally & recursively tried to clear off a few segments from the recycle
  bin. As ct. had enabled triggers preventing uncontrolled droppings of
  segments, this apparently prevented the application module from succeeding.
  Further, since this error did not show up on the application module that
  failed, this customer-facing critical application of this large enterprise
  was down for considerable time.

  DIAGNOSTIC ANALYSIS
  ===================
  None. This bug is raised mainly as a Q/A to get clarifications for customer,
  who is demanding an answer and possible action plan so that they can prevent
  such disastrous situation in future.

  WORKAROUND?
  ===========
  Yes

  WORKAROUND INFORMATION
  ======================
  Disable the trigger or not using the recycle bin (Though neither operation
  is acceptable to ct. because of their business reasons).

  TECHNICAL IMPACT
  ================
  Critical application module fails.

  RELATED ISSUES (bugs, forums, RFAs)
  ===================================
  None (MOS Note 978045.1 was referenced by ct.)

Hdr: 6977045 10.2 RDBMS 10.2 RAM DATA PRODID-5 PORTID-23 ORA-1652
Abstract: ORA-1652  LMT SPACE NOT REALLOCATED CORRECTLY AFTER DROP TABLE

*** 04/16/08 12:57 pm ***
TAR:
----
6880393.992

PROBLEM:
--------
ORA-12801: error signaled in parallel query server P038
ORA-1652: unable to extend temp segment by 320 in tablespace ERROR_TS

After dropping a table in a LMT the space is not properly returned to the
tablespace datafiles .

Only after purge tablespace error_ts; do we see the space returned correctly.
 Subsequently the test plan is successful and the table is created.


DIAGNOSTIC ANALYSIS:
--------------------
See attached test case. test_output.log

WORKAROUND:
-----------
none

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------

TEST CASE:
----------
See attached test case. test_output.log

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------

*** 04/16/08 01:29 pm ***
*** 04/16/08 02:04 pm ***
the problem here is that even though the objects are occupying the same space
when they were created, dba_free_space shows one datafile to contain all the
free space reclaimed by the drop table command.
*** 04/16/08 02:35 pm ***
Please confirm this is a duplicate of bug 5083393.
*** 04/17/08 10:56 am ***
*** 04/17/08 05:09 pm ***
*** 04/17/08 05:14 pm *** (CHG: Sta->10)
*** 04/17/08 05:14 pm ***
*** 04/21/08 11:06 am *** (CHG: Sta->16)
*** 04/21/08 11:06 am ***
please review uploaded file ora_test1.log.

Patch 5083393 has been applied to this instance and the test was ran against
this patch.
Notice the query immedatly following the ORA_1652 error.  The temporary
segments seem to be causing the failure and specifically segment 1199.88012  .
*** 04/22/08 01:55 pm ***
Current SQL statement for this session:
create table seckle.my_test2_tb
nologging tablespace error_ts
parallel (degree 6)
as
select * from ecm.E08401AH_GEMINI_CMF_WIDE_TB
        ERROR parallelizer slave or internal
        qbas:54482
        pgakid:2 pgadep:0
        qerpx: error stack: OER(12805)
        qbas_qerpxs: 54482
        dfo_qerpxs: 0x4b7ba89e0 dfo1_qerpxs: 0x4b7ba9178
        ntq_qerpxs: 1 ntqi_qerpxs: 0
        nbfs_qerpxs: 0
        nobj_qerpxs: 2  ngdef_qerpxs: 1
        mflg_qerpxs: 0x2c
        slave set 1 DFO dump:
        kkfdo: (0x4b7ba9178)
        kkfdo->kkfdochi: (0x0)
        kkfdo->kkfdopar: (0x0)
        kkfdo->kkfdonxt: (0x0)
        kkfdo->kkfdotqi: 0
        kkfdo->kkfdontbl: 2
        kkfdo->kkfdongra: 1
        kkfdo->kkfdofigra: 0
        kkfdo->kkfdoflg: 0x2818
        kkfdo->kkfdooct: 1
        kkfdo->kkfdonumeopn: 0
        Output table queue: (0x4b7fab1b8)
          kxfqd     : 0x4b7fa5728
          kxfqdtqi  : 0            TQ id
          kxfqdcc   : 0x14         TQ: from slave set 1 to QC
          kxfqdpty  : 4
          kxfqdsmp  : 0            number of samples
          kxfqdflg  : 0x4
          kxfqdfmt  :              TQ format

          kxfqfnco  : 5            number of TQ columns
          kxfqfnky  : 0            number of key columns
          TQ column        kxfqcbfl   kxfqcdty   kxfqcflg   kxfqcplen
          kxfqfcol[   0]:  4          23         0x0          4
          kxfqfcol[   1]:  32720      23         0x80         32720
          kxfqfcol[   2]:  1          23         0x0          1
          kxfqfcol[   3]:  76         23         0x0          76
          kxfqfcol[   4]:  32720      23         0x0          32720
        slave set 2 DFO dump:
        np_qerpxm: 6 mflg_qerpxm: 0xa7
        cdfo_qerpxm: 0x4b7ba9178 (tqid 0) sdfo_qerpxm: 0x0 (tqid -1)
        ctqh_qerpxm: 0xffffffff79378ac8 dump:
        kxfqh     : 0xffffffff79378ac8
        kxfqhflg  : 0x15         TQ handle open
        kxfqhmkr  : 0x4          QC
        kxfqhpc   : 2            1:producer 2:consumer 3:ranger
        kxfqepty  : 4
        kxfqhnsam : 6
        kxfqhnth  : 6
        kxfqhdsc  :              TQ descriptor

        kxfqd     : 0x4b7fa5728
        kxfqdtqi  : 0            TQ id
        kxfqdcc   : 0x14         TQ: from slave set 1 to QC
        kxfqdpty  : 4
        kxfqdsmp  : 0            number of samples
        kxfqdflg  : 0x4
        kxfqdfmt  :              TQ format

        kxfqfnco  : 5            number of TQ columns
        kxfqfnky  : 0            number of key columns
        TQ column        kxfqcbfl   kxfqcdty   kxfqcflg   kxfqcplen
        kxfqfcol[   0]:  4          23         0x0          4
        kxfqfcol[   1]:  32720      23         0x80         32720
        kxfqfcol[   2]:  1          23         0x0          1
        kxfqfcol[   3]:  76         23         0x0          76
        kxfqfcol[   4]:  32720      23         0x0          32720
        dnst_qerpxm[cur,par]: 6,0 dcnt_qerpxm[cur,par]: 0,0
        ppxv_qerpxm[0]: 0xffffffff79377f50 count[np..1]:1 1 1 1 1 1
        pqv1_qerpxm: 0xffffffff79377f38 bits[np..1]: 111111
        pqv2_qerpxm: 0xffffffff79377f40 bits[np..1]: 000000

If you have enabled recyclebin ,then you should check tablespace free space with dba_free_space and recyclebin space also like:

create view dba_free_space_pre10g as
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       f.block# BLOCK_ID,
       f.length * ts.blocksize BYTES,
       f.length BLOCKS,
       f.file# RELATIVE_FNO
  from sys.ts$ ts, sys.fet$ f, sys.file$ fi
 where ts.ts# = f.ts#
   and f.ts# = fi.ts#
   and f.file# = fi.relfile#
   and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 f.ktfbfebno BLOCK_ID,
 f.ktfbfeblks * ts.blocksize BYTES,
 f.ktfbfeblks BLOCKS,
 f.ktfbfefno RELATIVE_FNO
  from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
 where ts.ts# = f.ktfbfetsn
   and f.ktfbfetsn = fi.ts#
   and f.ktfbfefno = fi.relfile#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
 /

create view dba_free_space_recyclebin as
select /*+ ordered use_nl(u) use_nl(fi) */
 ts.name TABLESPACE_NAME,
 fi.file# FILE_ID,
 u.ktfbuebno BLOCK_ID,
 u.ktfbueblks * ts.blocksize BYTES,
 u.ktfbueblks BLOCKS,
 u.ktfbuefno RELATIVE_FNO
  from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
 where ts.ts# = rb.ts#
   and rb.ts# = fi.ts#
   and u.ktfbuefno = fi.relfile#
   and u.ktfbuesegtsn = rb.ts#
   and u.ktfbuesegfno = rb.file#
   and u.ktfbuesegbno = rb.block#
   and ts.bitmapped <> 0
   and ts.online$ in (1, 4)
   and ts.contents$ = 0
union all
select ts.name TABLESPACE_NAME,
       fi.file# FILE_ID,
       u.block# BLOCK_ID,
       u.length * ts.blocksize BYTES,
       u.length BLOCKS,
       u.file# RELATIVE_FNO
  from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
 where ts.ts# = u.ts#
   and u.ts# = fi.ts#
   and u.segfile# = fi.relfile#
   and u.ts# = rb.ts#
   and u.segfile# = rb.file#
   and u.segblock# = rb.block#
   and ts.bitmapped = 0
/

dba_free_space_pre10g which shows the real free space like 9i behavior , dba_free_space_recyclebin shows free space resided in recyclebin.