Re: ORA-01000: maximum open cursors exceeded

DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1098160208.601210_at_yasure>...
> vinnie washington wrote:
>
> > I'm wondering if anyone can help me figure out why I'm receiving this
> > problem. I am running Oracle 8.1.7 on Win2K server and have been
> > receiving this message in my application. When I query 'select
> > sql_text from v$open_cursor; ' to see what is holding things up, I
> > see:
> >
> > SELECT NULL AS table_cat, t.owner AS table_schem,
> >
> > with 300 entries in the table. Any thoughts?
>
> Not using bind variables?

This will return the full text of the SQL statements reported in v$open_cursor:

select s.sql_text
from v$open_cursor o, v$sqltext s
where o.address = s.address
and o.hash_value = s.hash_value
order by s.address, s.hash_value, s.piece;

You can see what is being executed, and possibly run explain plan on these statements to determine what the optimizer is doing with them:

SQL> desc plan_table

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(80)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(255)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 DISTRIBUTION                                       VARCHAR2(30)
 CPU_COST                                           NUMBER(38)
 IO_COST                                            NUMBER(38)
 TEMP_SPACE                                         NUMBER(38)
 ACCESS_PREDICATES                                  VARCHAR2(4000)
 FILTER_PREDICATES                                  VARCHAR2(4000)

If the table does not exist, have it created using %ORACLE_HOME%\rdbms\admin
utlxplan.sql script.

SQL> explain plan
  2> set statement_id = 'sometext' for
  3> <your query here>;

Explained.

SQL> Since you're running 8.1.7 you have no access to dbms_xplan, therefore you must use a script like I posted earlier and for which I was chastised:

SQL> set pagesize 0
SQL> select decode(id,0,'',
  2> lpad(' ', 2*(level -1))||level||'.'||position)||' '||
  3>   operation||' '||options||' '||object_name||' '||
  4>   object_type||' '||
  5>   decode(id,0,'Cost = '||cost) Query_plan
  6> from plan_table
  7> connect by prior id = parent_id
  8> and statement_id = 'sometext'
  9> start with id = 0 and statement_id = 'sometext'
 10> /

Hopefully this will assist you.

David Fitzjarrell Received on Tue Oct 19 2004 - 13:35:49 CDT