[oracle@rh2 ~]$ oerr ora 165201652, 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 PlatformsSymptoms:Related To:    Error May Occur    Storage Space Usage Affected    ORA-1652    Recycle BinDescription    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-59Abstract: 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-1652Abstract: ORA-1652  LMT SPACE NOT REALLOCATED CORRECTLY AFTER DROP TABLE*** 04/16/08 12:57 pm ***TAR:----6880393.992PROBLEM:--------ORA-12801: error signaled in parallel query server P038ORA-1652: unable to extend temp segment by 320 in tablespace ERROR_TSAfter dropping a table in a LMT the space is not properly returned to thetablespace 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.logWORKAROUND:-----------noneRELATED BUGS:-------------REPRODUCIBILITY:----------------TEST CASE:----------See attached test case. test_output.logSTACK 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 spacewhen they were created, dba_free_space shows one datafile to contain all thefree 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 againstthis patch.Notice the query immedatly following the ORA_1652 error.  The temporarysegments 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_tbnologging tablespace error_tsparallel (degree 6)asselect * 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

create view dba_free_space_pre10g asselect 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 = 0union allselect /*+ 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 asselect /*+ 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$ = 0union allselect 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.

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277664