使用dbms_sql包来模拟cursor的open、parse、bind、execute、fetch、close，结合errorstack level 3，可以看到具体的cursor信息：

1. open

SQL> variable c1 number
SQL> variable c2 number
SQL> variable c3 number
SQL>
SQL> execute :c1 := dbms_sql.open_cursor;

PL/SQL procedure successfully completed.

SQL> execute :c2 := dbms_sql.open_cursor;

PL/SQL procedure successfully completed.

SQL> execute :c3 := dbms_sql.open_cursor;

PL/SQL procedure successfully completed.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump errorstack 3
Statement processed.

相应的cursor信息如下：

******************** Session Cursor Dump **********************
Current cursor: 0, pgadep: 0
Open cursors(pls, sys, hwm, max): 3(0, 3, 50, 50)
 NULL 3 SYNTAX 0 PARSE 0 BOUND 0 FETCH 0 ROW 0
Cached frame. pages(total, free):
 4k(3, 3), 8k(0, 0), 16k(0, 0), 32k(0, 0)
----------------------------------------
Cursor#2(0xb72b0c54) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158
----------------------------------------
Cursor#3(0xb72b0c94) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158
----------------------------------------
Cursor#4(0xb72b0cd4) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158

由于这三个cursor仅仅被open，还处于null的状态，通过dump文件可以清晰的看到当前session的cursor情况。

2. parse：

SQL> variable b1 varchar2 
SQL> execute dbms_sql.parse (:c1 ,'select null from dual where dummy = :b1' ,dbms_sql.native);

PL/SQL procedure successfully completed.

SQL> oradebug dump errorstack 3
Statement processed.

******************** Session Cursor Dump **********************
Current cursor: 0, pgadep: 0                                  
Open cursors(pls, sys, hwm, max): 3(0, 3, 50, 50)             
 NULL 2 SYNTAX 0 PARSE 1 BOUND 0 FETCH 0 ROW 0                
Cached frame. pages(total, free):                              
 4k(3, 3), 8k(0, 0), 16k(0, 0), 32k(0, 0)                     
----------------------------------------                      
Cursor#2(0xb72b0c54) state=PARSE curiob=0xb72b622c            
 curflg=4d fl2=0 par=(nil) ses=0x2d17e158                     
 sqltxt(0x2cd92508)=select null from dual where dummy = :b1   
  hash=30eb8094cbfb99cd277e0d90dea949b4                       
  parent=0x2a5402f8 maxchild=01 plk=0x2af89324 ppn=n          
cursor instantiation=0xb72b622c used=1248424364               
 child#0((nil)) pcs=0x2a5404fc                                
  clk=(nil) ci=(nil) pn=(nil) ctx=(nil)                       
 kgsccflg=0 llk[0xb72b6230,0xb72b6230] idx=0                  
 xscflg=100032 fl2=40000 fl3=62000 fl4=0                      
 No bind info: cannot access child information block          
 and the oacdefs are not stored in the instantiation          
 Frames pfr (nil) siz=0 efr (nil) siz=0                       
 Cursor frame. dump                                            
----------------------------------------                      
Cursor#3(0xb72b0c94) state=NULL curiob=(nil)                  
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158                     
----------------------------------------                      
Cursor#4(0xb72b0cd4) state=NULL curiob=(nil)                  
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158                     
Session cached cursors                                        

从dump文件可以看出，c1的cursor已经处于parse状态。

3. Bind and Parallelize

SQL> execute :b1:='Y'

PL/SQL procedure successfully completed.

SQL> execute dbms_sql.bind_variable(:c1,':b1',:b1)

PL/SQL procedure successfully completed.

SQL> oradebug dump errorstack 3
Statement processed.

******************** Session Cursor Dump **********************
Current cursor: 0, pgadep: 0
Open cursors(pls, sys, hwm, max): 3(0, 3, 50, 50)
 NULL 2 SYNTAX 0 PARSE 0 BOUND 1 FETCH 0 ROW 0
Cached frame. pages(total, free):
 4k(6, 6), 8k(0, 0), 16k(0, 0), 32k(0, 0)
----------------------------------------
Cursor#3(0xb72b0c94) state=BOUND curiob=0xb712cc54
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158
 sqltxt(0x2cd92508)=select null from dual where dummy = :b1
  hash=30eb8094cbfb99cd277e0d90dea949b4
  parent=0x2a5402f8 maxchild=01 plk=0x2af88f7c ppn=n
cursor instantiation=0xb712cc54 used=1248426174
 child#0((nil)) pcs=0x2a5404fc
  clk=(nil) ci=(nil) pn=(nil) ctx=(nil)
 kgsccflg=0 llk[0xb712cc58,0xb712cc58] idx=0
 xscflg=101432 fl2=1141800 fl3=2062140 fl4=0
kxscoacd
 Bind#0
  acdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  acflg=13 fl2=0001 frm=01 csi=01 siz=0 ff=0
  No bind buffers allocated
 Frames pfr (nil) siz=0 efr (nil) siz=0
 Cursor frame. dump
 kxscphp  0xb72d01f0 siz=1000 inu=188 nps=112
----------------------------------------
Cursor#2(0xb72b0c54) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158
----------------------------------------
Cursor#4(0xb72b0cd4) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158
Session cached cursors

4. Execute, Fetch

SQL> variable r number
SQL> execute :r := dbms_sql.execute(:c1);

PL/SQL procedure successfully completed.

SQL> oradebug dump errorstack 3
Statement processed.

******************** Session Cursor Dump **********************
Current cursor: 0, pgadep: 0
Open cursors(pls, sys, hwm, max): 3(0, 3, 50, 50)
 NULL 2 SYNTAX 0 PARSE 0 BOUND 0 FETCH 1 ROW 0
Cached frame. pages(total, free):
 4k(6, 4), 8k(0, 0), 16k(0, 0), 32k(0, 0)
----------------------------------------
Cursor#3(0xb72b0c94) state=FETCH curiob=0xb712cc54
 curflg=4f fl2=0 par=(nil) ses=0x2d17e158
 sqltxt(0x2cd92508)=select null from dual where dummy = :b1
  hash=30eb8094cbfb99cd277e0d90dea949b4
  parent=0x2a5402f8 maxchild=01 plk=0x2af88f7c ppn=n
cursor instantiation=0xb712cc54 used=1248426642
 child#0(0x2cd6aca4) pcs=0x2a5404fc
  clk=0x2af9da8c ci=0x2a65bc08 pn=0x2cdd5e60 ctx=0x2a3caddc
 kgsccflg=0 llk[0xb712cc58,0xb712cc58] idx=0
 xscflg=c0110476 fl2=45040000 fl3=42262108 fl4=100
Cached frame. pages(total, free):
 4k(6, 4), 8k(0, 0), 16k(0, 0), 32k(0, 0)
----------------------------------------
Cursor#3(0xb72b0c94) state=FETCH curiob=0xb712cc54
 curflg=4f fl2=0 par=(nil) ses=0x2d17e158
 sqltxt(0x2cd92508)=select null from dual where dummy = :b1
  hash=30eb8094cbfb99cd277e0d90dea949b4
  parent=0x2a5402f8 maxchild=01 plk=0x2af88f7c ppn=n
cursor instantiation=0xb712cc54 used=1248426642
 child#0(0x2cd6aca4) pcs=0x2a5404fc
  clk=0x2af9da8c ci=0x2a65bc08 pn=0x2cdd5e60 ctx=0x2a3caddc
 kgsccflg=0 llk[0xb712cc58,0xb712cc58] idx=0
 xscflg=c0110476 fl2=45040000 fl3=42262108 fl4=100
 Bind bytecodes
  pcode = 1   Unoptimized
  ffsi = 36, ffsi = 0
kkscoacd
 Bind#0
  acdty=01 mxl=32(01) mxlc=00 mal=00 scl=00 pre=00
  acflg=13 fl2=0001 frm=01 csi=01 siz=32 ff=0
  kxsbbbfp=b711ebf4  bln=32  avl=01  flg=05
  value="Y"
 Frames pfr 0xb7120404 siz=1860 efr 0xb712033c siz=1768
 Cursor frame. dump
  enxt: 3.0x00000008  enxt: 2.0x00000014  enxt: 1.0x000006cc
  pnxt: 2.0x00000004  pnxt: 1.0x00000058
 kxscphp  0xb72d01f0 siz=1000 inu=376 nps=300
 kxscbhp  0xb72d0508 siz=1000 inu=116 nps=44
 kxscwhp  0xb72d02f8 siz=4072 inu=284 nps=0
----------------------------------------
Cursor#2(0xb72b0c54) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158
----------------------------------------
Cursor#4(0xb72b0cd4) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=
 
 5. close
 
 
SQL> execute dbms_sql.close_cursor(:c1);

PL/SQL procedure successfully completed.
 
SQL> oradebug dump errorstack 3

******************** Session Cursor Dump **********************
Current cursor: 0, pgadep: 0
Open cursors(pls, sys, hwm, max): 2(0, 2, 50, 50)
 NULL 2 SYNTAX 0 PARSE 0 BOUND 0 FETCH 0 ROW 0
Cached frame. pages(total, free):
 4k(6, 6), 8k(0, 0), 16k(0, 0), 32k(0, 0)
----------------------------------------
Cursor#2(0xb72b0c54) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158
----------------------------------------
Cursor#4(0xb72b0cd4) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0x2d17e158