ORA-30009错误，如何解决？

alonedba

创建表时使用connect连接遇到如下错误：
SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;
CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000
                                                                                                                           *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation

数据库版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Maclean Liu(刘相兵

ODM TEST:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production



SQL> set linesize 200 pagesize 1400

SQL> show parameter work

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
fileio_network_adapters              string
listener_networks                    string
workarea_size_policy                 string                 MANUAL


SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;
CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000
                                                                     *
第 1 行出现错误:
ORA-30009: CONNECT BY 操作内存不足


SQL> alter system set workarea_size_policy=AUTO;

系统已更改。

SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;

表已创建。

SQL> show parameter sort

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer                0
sort_area_size                       integer                65536

Maclean Liu(刘相兵

Action plan:

select name,value from v$parameter where isdefault='FALSE';

select * from v$pgastat;

alonedba

19:05:55 SQL> select name,value from v$parameter where isdefault='FALSE';

NAME                           VALUE
------------------------------ --------------------------------------------------
processes                      150
sga_max_size                   419430400
nls_date_format                yyyy-mm-dd hh24:mi:ss
nls_length_semantics           BYTE
resource_manager_plan
sga_target                     327155712
control_files                  /u01/oracle/oradata/orcl/control01.ctl, /u01/orac
                               le/oradata/orcl/control02.ctl, /u01/oracle/oradat
                               a/orcl/control03.ctl

db_block_size                  8192
compatible                     10.2.0.5.0
db_file_multiblock_read_count  16
db_recovery_file_dest          /u01/oracle/flash_recovery_area
db_recovery_file_dest_size     2147483648
undo_management                AUTO
undo_tablespace                UNDOTBS1
undo_retention                 900
remote_login_passwordfile      EXCLUSIVE
db_domain
dispatchers                    (PROTOCOL=TCP) (SERVICE=orclXDB)
job_queue_processes            10
background_dump_dest           /u01/oracle/admin/orcl/bdump
user_dump_dest                 /u01/oracle/admin/orcl/udump
core_dump_dest                 /u01/oracle/admin/orcl/cdump
audit_file_dest                /u01/oracle/admin/orcl/adump
db_name                        orcl
open_cursors                   300
pga_aggregate_target           25165824
statistics_level               ALL
aq_tm_processes                0



19:10:51 SQL> select * from v$pgastat;

NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                     25165824 bytes
aggregate PGA auto target                                           8294400 bytes
global memory bound                                                 5032960 bytes
total PGA inuse                                                    16006144 bytes
total PGA allocated                                                23291904 bytes
maximum PGA allocated                                              29898752 bytes
total freeable PGA memory                                           4980736 bytes
process count                                                            16
max processes count                                                      20
PGA memory freed back to OS                                         4390912 bytes
total PGA used for auto workareas                                         0 bytes
maximum PGA used for auto workareas                                 1378304 bytes
total PGA used for manual workareas                                       0 bytes
maximum PGA used for manual workareas                                536576 bytes
over allocation count                                                     0
bytes processed                                                    40310784 bytes
extra bytes read/written                                                  0 bytes
cache hit percentage                                                    100 percent
recompute count (total)                                                 606

19 rows selected.

19:10:56 SQL> show parameter sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer     0
sort_area_size                       integer     65536

可能当时更改修改了参数：
ALTER SESSION SET workarea_size_policy = manual;
现在记不起来了，昨晚做了很多测试，搞忘了。

现在想问的，workarea_size_policy 这个参数怎么与建表有关系呢？

Maclean Liu(刘相兵

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> alter session set events '30009 trace name errorstack level 4';

Session altered.




SQL>  alter session set workarea_size_policy=MANUAL;

Session altered.

SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;
CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000
                                                                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation


SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name
/s01/orabase/diag/rdbms/g11r23/G11R23/trace/G11R23_ora_24919.trc

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=4, mask=0x0)
----- Error Stack Dump -----
ORA-30009: Not enough memory for CONNECT BY operation
----- Current SQL Statement for this session (sql_id=263cyr72uw1q4) -----
CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFA9EF7C98 ? 000000001 ?
                                                   000000001 ? 000000002 ?
ksedst1()+98         call     skdstdst()           000000000 ? 000000000 ?
                                                   7FFFA9EF7C98 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedst()+34          call     ksedst1()            000000000 ? 000000001 ?
                                                   7FFFA9EF7C98 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbkedDefDump()+2741  call     ksedst()             000000000 ? 000000001 ?
                                                   7FFFA9EF7C98 ? 000000001 ?
                                                   000000000 ? 000000002 ?
ksedmp()+36          call     dbkedDefDump()       000000004 ? 000000000 ?
                                                   7FFFA9EF7C98 ? 000000001 ?
                                                   000000000 ? 000000002 ?
                                                   000000000 ? 000000002 ?
dbkdaKsdActDriver()  call     ksedmp()             000000004 ? 000000000 ?
+1960                                              7FFFA9EF7C98 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgdaExecuteAction(  call     dbkdaKsdActDriver()  2B3F6A7CC710 ? 7FFFA9EFE920 ?
)+1065                                             7FFFA9EF7C98 ? 000000001 ?
                                                   000000000 ? 000000002 ?
dbgdaRunAction()+81  call     dbgdaExecuteAction(  2B3F6A7CC710 ? 00A0A8360 ?
0                             )                    0020C0003 ? 7FFFA9EFE920 ?
                                                   000000001 ? 000000002 ?
dbgdRunActions()+59  call     dbgdaRunAction()     2B3F6A7CC710 ? 000000005 ?
                                                   0020C0003 ? 7FFFA9EFE920 ?
                                                   000000001 ? 000000002 ?
dbgdProcessEventAct  call     dbgdRunActions()     2B3F6A7CC710 ? 000000005 ?
ions()+651                                         0020C0003 ? 7FFFA9EFE920 ?
                                                   000000001 ? 000000002 ?
dbgdChkEventKgErr()  call     dbgdProcessEventAct  2B3F6A7CC710 ? 00BAF3FA0 ?
+1653                         ions()               2B3F6AD357D0 ? 7FFFA9EFE920 ?
                                                   000000001 ? 000000002 ?
dbkdChkEventRdbmsEr  call     dbgdChkEventKgErr()  2B3F6A7CC710 ? 00BAF3FA0 ?
r()+56                                             7FFFA9EF44D0 ? 7FFFA9EFE920 ?
                                                   000000001 ? 000000002 ?
ksfpec()+61          call     dbkdChkEventRdbmsEr  7FFFA9EF44D0 ? 00BAF3FA0 ?
                              r()                  7FFFA9EF44D0 ? 7FFFA9EFE920 ?
                                                   000000001 ? 000000002 ?
dbgePostErrorKGE()+  call     ksfpec()             7FFFA9EF44D0 ? 7FFFA9EF44D0 ?
1129                                               7FFFA9EF44D0 ? 7FFFA9EFE920 ?
                                                   000000001 ? 000000002 ?
dbkePostKGE_kgsf()+  call     dbgePostErrorKGE()   00BAF3FA0 ? 2B3F6AD00040 ?
kgesev()+280         call     dbkePostKGE_kgsf()   00BAF3FA0 ? 2B3F6AD00040 ?
                                                   000007539 ? 000000000 ?
                                                   100000000 ? 000000002 ?
ksesec1()+165        call     kgesev()             00BAF3FA0 ? 2B3F6AD00040 ?
                                                   000007539 ? 000000001 ?
                                                   7FFFA9EFF6B0 ? 000000002 ?
qercbiPushState()+5  call     ksesec1()            00BAF3FA0 ? 000000001 ?
18                                                 00000000A ? 009B49418 ?
                                                   2B3F6AD336BC ? 2B3F6AD336C0 ?
qercbiFetch()+273    call     qercbiPushState()    089369CA0 ? 2B3F6AD330D0 ?
                                                   00000000A ? 009B49418 ?
                                                   2B3F6AD336BC ? 2B3F6AD336C0 ?
qercoFetch()+205     call     qercbiFetch()        089369CA0 ? 2B3F6AD330D0 ?
                                                   0024CB996 ? 7FFFA9EFF9D0 ?
                                                   2B3F00007FFF ? 2B3F6AD336C0 ?
rwsfcd()+103         call     qercoFetch()         089369940 ? 2B3F6AD33418 ?
                                                   0024CB996 ? 7FFFA9EFF9D0 ?
                                                   2B3F00007FFF ? 2B3F6AD336C0 ?
qerltFetch()+599     call     rwsfcd()             089369848 ? 2B3F6AD33478 ?
                                                   0024D0378 ? 7FFFA9EFF9D0 ?
                                                   2B3F00007FFF ? 2B3F6AD336C0 ?
ctcdrv()+13581       call     qerltFetch()         0893695E8 ? 2B3F6AD33600 ?
                                                   001335A12 ? 7FFFA9F00970 ?
                                                   2B3F00000001 ? 2B3F6AD336A0 ?




stack call                           ctcdrv=>qerltFetch=>rwsfcd=>qercoFetch=>qercbiFetch=>qercbiPushState=> 30009 raised



Dumping Work Area Table (level=1)
=====================================

  Global SGA Info
  ---------------

    global target:      312 MB
    auto target:        242 MB
    max pga:            200 MB
    pga limit:            0 MB
    pga limit known:  0
    pga limit errors:     0

    pga inuse:           42 MB
    pga alloc:           64 MB
    pga freeable:        15 MB
    pga freed:          500 MB
    pga to free:          0 %
    broker request:       0

    pga auto:             0 MB
    pga manual:           0 MB

    pga alloc  (max):   149 MB
    pga auto   (max):     8 MB
    pga manual (max):     0 MB

    # workareas     :     2
    # workareas(max):     9


       
                                                                                                  
Plan Table
============
--------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | CREATE TABLE STATEMENT          |         |       |       |     3 |           |
| 1   |  LOAD AS SELECT                 |         |       |       |       |           |
| 2   |   COUNT                         |         |       |       |       |           |
| 3   |    CONNECT BY WITHOUT FILTERING |         |       |       |       |           |
| 4   |     FAST DUAL                   |         |     1 |       |     2 |  00:00:01 |
--------------------------------------------------+-----------------------------------+


DYNAMICALLY MODIFIED PARAMETERS:
  workarea_size_policy     = MANUAL
  _smm_auto_cost_enabled   = FALSE


CONNECT BY         Clause 会消耗一定量的PGA 内存

connect by 操作时   
        pga inuse:           42 MB
    pga alloc:           64 MB                                                       

  没有connect by 时
SQL> set linesize 200 pagesize 1400
SQL> select name,value/1024/1024 from v$pgastat where name in ('total PGA inuse','total PGA allocated');

NAME                                                             VALUE/1024/1024
---------------------------------------------------------------- ---------------
total PGA inuse                                                         36.78125
total PGA allocated                                                   56.3720703

Maclean Liu(刘相兵

KST TRACE:

2012-04-23 12:13:29.749757 :807956ED:db_trace:kst.c@675:kstipg(): [10280:20:0] kst: process state object created on 04-23 12:13:29.749
2012-04-23 12:13:29.749758 :807956EE:db_trace:kst.c@681:kstipg(): [10280:20:0] kst: process info: ospid=24919 pso_num=20 pso_serial#=90
2012-04-23 12:13:29.749854 :807956EF:db_trace:kso.c@4268:ksonfy(): [10420:20:0] kso: new process: pid=24919 (legacy spawn)
2012-04-23 12:14:04.929489 :807963D0:db_trace:ktc.c@9270:ktcTxnTrace(): [10441:20:14] atxpus:0x2b3f6ad81d38:psdpgi.c:2084
2012-04-23 12:14:04.938179 :807963D1:db_trace:ktc.c@9270:ktcTxnTrace(): [10441:20:14] atxpop:0x2b3f6ad81d38:psdpgi.c:2103
2012-04-23 12:14:04.943693 :807963D2:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=11 num=1 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 i
d2=0 name=   type=0
2012-04-23 12:14:04.949277 :807963DA:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=13 num=2 loc='ktm.h LINE:614 ID:ktmpsm' id1=0 id
2=0 name=   type=0
2012-04-23 12:14:04.950886 :807963E7:db_trace:ksl2.c@16003:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=21 num=3 loc='ksl2.h LINE:2362 ID:kslpsr' id1=25
8 id2=0 name=EV type=0
2012-04-23 12:14:04.950890 :807963E8:db_trace:ksl2.c@16003:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=19 num=4 loc='ksl2.h LINE:2362 ID:kslpsr' id1=25
8 id2=0 name=EV type=0
2012-04-23 12:14:04.953131 :807963E9:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=11 num=5 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 i
d2=0 name=   type=0
2012-04-23 12:14:04.953803 :807963EC:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=11 num=6 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 i
d2=0 name=   type=0
2012-04-23 12:14:04.958923 :807963ED:db_trace:ktur.c@3198:ktuabt(): [10444:20:14] ABORT TRANSACTION - xid: 0x0008.014.0000071d
2012-04-23 12:14:04.959048 :807963EE:KFNC:kfn.c@2622:kfnPrepareASM(): kfnPrepareASM CF ro=1 force=0 state_kfnsg=0x7
2012-04-23 12:14:04.959881 :807963F0:KFNC:kfn.c@2622:kfnPrepareASM(): kfnPrepareASM CF ro=1 force=0 state_kfnsg=0x7
2012-04-23 12:14:05.148622 :8079644C:KFNC:kfn.c@2622:kfnPrepareASM(): kfnPrepareASM CF ro=0 force=0 state_kfnsg=0x7
2012-04-23 12:14:05.151697 :8079644D:db_trace:kcc.c@15070:kccwbp(): [10021:20:14:1] 0115000000000000 0115000000000000 3b4e000000000000
2012-04-23 12:14:05.151724 :8079644E:db_trace:ksb.c@8043:ksbcic_int(): [10254:20:14] KSBCIC: (USER) starting cic on opcode KSBCISYN [3] with args (1 110080 1)
2012-04-23 12:14:05.151743 :8079644F:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=10 num=7 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 i
d2=0 name=   type=0
2012-04-23 12:14:05.152204 :80796459:db_trace:ksl2.c@14563:ksliwat(): [10005:20:14] KSL POST RCVD poster=10 num=116 loc='ksa2.h LINE:289 ID:ksasnr' id1=0 id2=0 name=
type=0 fac#=3 posted=0x3 may_be_posted=1
2012-04-23 12:14:05.152229 :8079645D:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=11 num=8 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 i
d2=0 name=   type=0
2012-04-23 12:14:05.153806 :80796460:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=11 num=9 loc='ksa2.h LINE:285 ID:ksasnd' id1=0 i
d2=0 name=   type=0
2012-04-23 12:14:06.683926 :807964DB:db_trace:ksl2.c@16009:ksl_update_post_stats(): [10005:20:14] KSL POST SENT postee=11 num=10 loc='ksa2.h LINE:285 ID:ksasnd' id1=0
id2=0 name=   type=0
2012-04-23 12:14:06.684280 :807964E4:db_trace:ksl2.c@14563:ksliwat(): [10005:20:14] KSL POST RCVD poster=11 num=317 loc='ksl2.h LINE:2362 ID:kslpsr' id1=137 id2=0 name
=EV type=0 fac#=3 posted=0x3 may_be_posted=1
2012-04-23 12:14:06.875393*:807964F2:sql_mon_query:keswx.c@6154:keswxCurPushMonitoring(begin): xsc=0x2b3f6ad33bb8 monCtx=(nil) estart=1335197644 eid=16777216 ctx=0x893
85c88 cwh=0x2b3f6ad30500 cpu=1092835 buffg=538 interb=58949632 r=103 rb=47054848 w=23 wb=11894784
2012-04-23 12:14:06.875393*:807964F3:sql_mon_query:keswx.c@2845:keswxCurPrepare(): Allocate monitor context: xsc=0x2b3f6ad33bb8 curCtx=0x2b3f6ad64e40 pubTabIdxCnt=0
2012-04-23 12:14:06.875393*:807964F4:sql_mon_query:keswx.c@6200:keswxCurPushMonitoring(): clear monitoring flag for xsc=0x2b3f6ad33bb8
2012-04-23 12:14:06.875393*:807964F5:sql_mon_query:keswx.c@6332:keswxCurPushMonitoring(end): full monitoring not yet started for xsc=0x2b3f6ad33bb8
-------------------------------------------------------------------------------
Trace Bucket Dump End: default bucket for process 20 (osid: 24919)

alonedba

由于设置 该参数为 workarea_size_policy     = MANUAL 手动管理pga，消耗的PGA内存不会自动回收
在做connect连接时，又消耗一定内存，而pga没有足够的内存可分配，所以导致ORA-30009错误。
是这样的吧？

Maclean Liu(刘相兵

workarea_size_policy     = MANUAL  ==> 手动管理PGA 并非说是“消耗的PGA内存不会自动回收”

手动PGA管理时 内存排序空间受到sort_area_size 参数的影响， 单个operation 使用的排序空间不能超过 sort_area_size

SQL> show parameter sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string
sort_area_retained_size              integer     0
sort_area_size                       integer     65536

SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;
CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000
                                                                     *
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation


SQL> alter session set sort_area_size=6553600;

Session altered.

SQL> CREATE TABLE t AS SELECT rownum AS n, lpad('*',1000,'*') AS pad FROM dual CONNECT BY level <= 100000;

Table created.


自动PGA管理下 sort_area_size是废弃参数， 单个 operation 使用的排序内存空间不超过  pga_aggregate_target的 5% 或者 隐藏参数  _pga_max_size

No RAM sort may use more than 5% of pga_aggregate_target or _pga_max_size, whichever is smaller.

alonedba

明白了，分析的很好，很容易明白，非常感谢。