| ABOUT SEQUENCE IN PL/SQL [message #369679] |
Fri, 04 August 2000 04:06  |
KED
Messages: 1
Registered: August 2000
|
Junior Member |
|
|
my table isCREATE TABLE TEST_TABLE (
FUND_RUN_NO1 NUMBER(10) DEFAULT 0 NULL,
COMP_RUN_NO1 NUMBER(12) DEFAULT 0 NULL,
MEM_RUN_NO1 NUMBER(12) DEFAULT 0 NULL;
and my pl/sql is
SQL> CREATE OR REPLACE TRIGGER TEST_SEQ
2 BEFORE INSERT ON TEST_TABLE
3 FOR EACH ROW
4
4 BEGIN
5 INSERT INTO TEST_TABLE
6 ( FUND_RUN_NO1)
7 VALUES
8 (FUND_SEQUENCE.NEXTVAL);
9 COMMIT;
10 END;
11 /
but when I run script in sql plus to insert table test_table like this :
INSERT INTO TEST_TABLE
(COMP_RUN_NO1,MEM_RUN_NO1)
VALUES (3,4);
then return this code:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "GEN.TEST_SEQ", line 2
ORA-04088: error during execution of trigger 'GEN.TEST_SEQ'
how I handle it?
|
|
|
|
| Re: ABOUT SEQUENCE IN PL/SQL [message #369682 is a reply to message #369679] |
Sun, 06 August 2000 16:34  |
Frank Naude
Messages: 4593
Registered: April 1998
|
Senior Member |
|
|
Hi,
Ask your DBA to increase the OPEN_CURSORS init.ora parameter. Also read the following (note 18591.1) from Oracle MetaLink:
Error: ORA-1000
Text: maximum open cursors exceeded
-------------------------------------------------------------------------------
Cause: A host language program attempted to open too many cursors.
The initialization parameter OPEN_CURSORS determines the maximum
number of cursors per user.
Action: Modify the program to use fewer cursors.
If this error occurs often, shut down Oracle, increase the value of
OPEN_CURSORS, and then restart Oracle.
*** Important: The notes below are for experienced users - See [[NOTE:22080.1]]
Explanation:
Cursors are resources explicitly opened by application code, and in
recursive operations by Oracle code. The init.ora <Parameter:OPEN_CURSORS>
controls the maximum number of concurrent cursors a session may have open
at any point in time. An attempt to open an additional cursor results in
the ORA-1000 "maximum open cursors exceeded" error.
Diagnostic Steps:
1. Check the value of the OPEN_CURSORS parameter
2. A DBA can use the <View:V$OPEN_CURSOR> to see what cursors are in use
at any point in time.
3. If it is proving difficult to identify the reason for the ORA-1000
error then it is possible to get the user session to generate a trace
file when the error occurs by adding the following event to the init.ora.
IMPORTANT: See [[NOTE:75713.1]] before adding any event to the init.ora file
event="1000 trace name errorstack level 3"
This will cause a trace file to be written by any session when it hits
an ORA-1000. Provided MAX_DUMP_FILE_SIZE is large enough this trace
should help identify what all cursors in the session are being used for
and hence help identify the cause of the ORA-1000.
WARNING: Although ORA-1000 can often be tackled by increasing OPEN_CURSORS
it is not sensible to just keep increasing this value as open
cursors hold resources. Eg: If you have got OPEN_CURSORS up to
several hundred then it may be better to identify what all these
cursors are for rather than just blindly increasing OPEN_CURSORS
further.
Additional Notes:
Dictionary corruption, especially loops in DEPENDENCY$, can cause
ORA-1000 on ALTER / DROP of an object.
Articles:
HOLD_CURSOR and RELEASE_CURSOR in precompilers [[NOTE:2055810.6]]
Tuning Precompiler applications [[NOTE:73922.1]]
Workarounds for ORA-1000 [[NOTE:2060331.6]]
.
--------------------------------------------------------------------------------
Best regards
Frank Naude
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
