Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Ora-00604

Posted on 2003-03-11
11
Medium Priority
?
8,995 Views
Last Modified: 2007-12-19
I am Using form (Form6i) and back-end being oracle 8.1.7PO on a P4 machine 256mb RAM Win-98 PC.
                        The form has been tested else where and has no bugs. But here it is showing "Un Handelled Exception (604)" in executing a simple insert inside a loop.
                     Kindly tell me a way to debug. My init.ora has open_cursor=5000 and proper rollback_segments.
                                           Regards....
                                                 Sujit
0
Comment
Question by:sujit_kumar
11 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8114475
My guess is lack of space in a data or index tablespace.  If you cannot provide any more details, it will be very difficult for us to troubleshoot.  

Here is the Oracle documentation for ORA-00604:

ORA-00604 error occurred at recursive SQL level...

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Customer Support.

- - - -

So, did you see other Oracle errors as well?  Can you post them?
0
 
LVL 1

Expert Comment

by:nitinocp
ID: 8117013
would you just post give the output for ...
 show sga      command in backend...i.e. from sql*plus

how many rollback segments you have created and are online...plzzz confirm..
0
 

Expert Comment

by:Avotar
ID: 8117684
a ref-cursor that has not been defined properly will generate this error...
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 11

Author Comment

by:sujit_kumar
ID: 8126383
Nice comments. But i can't see the second error as i am experincing it in Forms.
                Any other suggestions.
                                Sujit
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8136885
Based on the limited amount of information you've give us, I still think the most likely cause of the problem is a space issue.  This could be O/S disk space (if all of the datafiles in the database are set to autoextend) or if the tablespaces are not set up to autoextend, then either the data, index or system tablespace are probably out of room.  Have you confirmed that there is free space available in both the database and the O/S?

Have you checked the Oracle alert.log file to see if it gives you any clues to the underlying problem?
0
 
LVL 11

Author Comment

by:sujit_kumar
ID: 8141666
Hi ,
    Yes i have checked for System resources and also my tablespaces have auto extend ON, I have checked for the rollback segments and added one more to it. set the perticulat transaction (which is showing message) to the newly created roll back segment. But nothing helps. I tried it in diff machines it works perfectly. I just want to know the possible cause. Because ORA-00604 error is based on the second error i know.. But the error is never appearing from the form run time.
                            Regards...
                                Sujit
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8152112
Have you checked the alert.log file on the server?

You can add exception-handling to the procedure/program unit that has the loop and the insert.  That should make it possible to trap the actual error.

Otherwise, can you make that loop and insert procedure a stand-alone procedure that you can run from SQL*Plus?  That would give you another way to trap the actual error.
0
 
LVL 11

Author Comment

by:sujit_kumar
ID: 8178938
Thanks markgeer ,
       But i have tried all of it. I executed the loop in back-end. It is working fine. I tried exception but it is coming to exception on ORA-00604
                        I have also executed the form from anather back-end (of same configuration) it is working .
              Any other suggestions ??
                              Sujit
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 300 total points
ID: 8180627
If the form works fine on a different database (that is the same version of Oracle and the same operating system), then the problem is definitely in the database, not in the form.  

Please run these two scripts and post the results here:

--filesize.sql
set linesize 100;
break on report;
column size format 99,999,999,999
column "Used Bytes" format 99,999,999,999;
column "% full" format 999;
compute sum of Size on report;
compute sum of "Used bytes" on report;
select substr(df.name,1,40) "File name", lpad(to_char(df.file#),4) "File", df.bytes "Size",
sum(sg.bytes) "Used bytes", (sum(sg.bytes) * 100) / df.bytes "% full"
from dba_extents sg, dba_data_files db, v$datafile df
where db.file_id = df.file#
and sg.tablespace_name (+) = db.tablespace_name
and sg.file_id (+) = db.file_id
group by substr(df.name,1,40), df.file#, df.bytes;
-- (end of "filesize.sql")

-- freespac.sql
column file_id format 99999 heading "File#";
column Extents format 9999999;
column Bytes format 99,999,999,999;
column Blocks format 999,999,999;
column Largest format 9,999,999,999;
to "temporary" tablespaces
select substr(tablespace_name,1,16) "Tablespace",
 file_id, count(block_id) "Extents",
sum(blocks) "Blocks", sum(bytes) "Bytes", max(bytes) "Largest"
from user_free_space
group by tablespace_name, file_id
union
select substr(tablespace_name,1,16) "Tablespace",
file_id, 1 "Extents", blocks_free "Blocks", bytes_free "Bytes",
bytes_free "Largest"
from v$temp_space_header
order by 1;
-- (end of "freespac.sql")
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8180637
There is an error in the "freespac.sql" script I posted.  I had removed part of a comment in that script before posting it here, but not the entire comment.  The line:
'to "temporary" tablespaces'
should be removed.
0
 
LVL 11

Author Comment

by:sujit_kumar
ID: 8229857
hi markgeer,
             I have uninstalled that database and re-installed oracle after that thing are working fine. But thanks for the scripts.
                        Sujit
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month14 days, 19 hours left to enroll

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question