ORA-01422 while compiling objects

There was an interesting issue at one of the customer sites. Few tables in the database were altered and the dependent objects became invalid. But the attempts to compile the objects using utlrp.sql or manually were failing. In all the cases it was giving the same error:

SQL> alter function SCOTT.SOME_FUNCTION compile;
 alter function SCOTT.SOME_FUNCTION compile
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 27

SQL>

 

At first look it sounded like some issue with the dictionary as the error in case of every object (be it a view, function or package) was the same.

Everybody was trying to compile the invalid objects and surprisingly few VIEWs (that were not getting compiled from SQL*Plus) got compiled from Toad ! But that didn’t explain anything. In fact it was more confusing.

Finally I enabled errorstack for event 1422 and tried to compile a view. Here is the relevant content from the trace file

----- Error Stack Dump -----
ORA-01422: exact fetch returns more than requested number of rows
----- Current SQL Statement for this session (sql_id=7kb01v7t6s054) -----
SELECT SQL_TEXT FROM V$OPEN_CURSOR VOC, V$SESSION VS WHERE VOC.SADDR = VS.SADDR AND AUDSID=USERENV('sessionid') AND UPPER(SQL_TEXT) LIKE 'ALTER%'

 

I took it to be some system SQL and started searching in that direction and obviously that was of no use.

In the mean time another guy almost shouted…”oh there is a trigger to capture DDL operations in the database; it must be that”. And indeed it was. Here is the code that was creating the problem:

 select sql_text into vsql_text
           from v$open_cursor voc, v$session vs
           where voc.saddr = vs.saddr
           and audsid=userenv('sessionid')
           and upper(sql_text) like 'ALTER%';
 

As v$open_cursor was returning multiple rows, hence the problem !

Moral is that the errorstack traces do tell a lot (of course if you listen carefully) 😉

EXP-00008: ORACLE error 600 encountered

Today I was running export of an Oracle 9.2.0.1 database. The export completed but with an ORA-600 error:


EXP-00008: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [xsoptloc2], [4], [4], [0], [], [], [], []
ORA-06512: in "SYS.DBMS_AW", line 347
ORA-06512: in "SYS.DBMS_AW", line 470
ORA-06512: in "SYS.DBMS_AW_EXP", line 270
ORA-06512: in line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_AW_EXP.schema_info_exp

I googled a bit and found that the problem is with applying some patchset. Then metalink confirmed the same. Somebody tried applying a patch to upgrade it to 9.2.0.5 but didn’t perform all the steps (missed post installation steps, to be precise). Metalink Note 300849.1 covers the issue and also gives the solution. In nutshell startup the database with startup migrate and run catpatch.sql.

Missing grants

Today one of my colleague was working on a simple PL/SQL procedure. Based on some logic it was returning count(*) from all_tab_columns for few tables. It gave count incorrectly for one table out of around fifty in total. He just hard coded the table name and ran it but again it showed count as zero.

Then he took the code out of procedure and wrote it in DECLARE, BEGIN, END and after running it showed the correct count. But ran as database procedure it always shows incorrectly.

Finally just as hit and trial, he gave SELECT on the TABLE to database user [Table was in different schema], used to run the procedure and everything was ok. Isn’t it bit stupid 🙂

Update: Well, it happens for a reason. Nigel Thomas pointed out in the comment. The reason is that privileges granted to a role are not seen from PL/SQL stored procedures. You need to give direct grant to the user for this or another method is to define the procedure or package with invoker rights.

Thanks Nigel 🙂

ORA-03113 Refresh of a mview in Oracle 10g

At my workplace we were facing a problem with refresh of a mview. Say it was created in schema of user1 but when I tried to refresh it from user2 it would give ORA-03113: end-of-file on communication channel. Then we raised a SR and have been following up with Oracle support for long but it was not getting anywhere. Yesterday that guy seemed to have reached some point. The mviews that we have created and are having problem with refresh are created on top of both local & remote objects and he said that up to 11gr2 there is no possibility of creating mviews on both local and remote objects. I did validate this thing. All the mviews failing to refresh are created on top of both local & remote objects. But again from the owner the refresh is fine but from another user it gives problem. By the way that guy hinted at bug 4084125 and also suggested a work around. I haven’t tried that yet. Will try and update about the results.

Sidhu