Category Archives: Troubleshooting

Take care of a slash in a SQL script

1 Reply

Since long time i have almost been writing useless posts only. Now, i guess my blog doesn’t even look like an Oracle blog. So thought about posting something related to Oracle 😉

Day before yesterday a colleague at my workplace asked that she was running an SQL script (which contained a simple DBMS_MVIEW.REFRESH() statement to refresh an MVIEW), it ran successfully but after completion re-ran the last command run in the session. I was also puzzled and checked the SQL script but it contained simple DBMS_MVIEW.REFRESH() statement. Next try revealed that the script actually had a / (slash) in the second line (with no semi-colon at the end of the first line). Something like this (I used dbms_stats instead of dbms_mview):

exec dbms_stats.gather_table_stats(user,'EMP')
/

Now this thing, when run in SQL* Plus session can be confusing:

SCOTT@TESTING >
SCOTT@TESTING >delete emp1;
delete emp1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SCOTT@TESTING >@c:\test

PL/SQL procedure successfully completed.

delete emp1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SCOTT@TESTING >

There is no semicolon at the end of the first statement but it executes without that also. So the slash in the 2nd line simply re-executes the last SQL, as expected 🙂 . But it does get confusing !

Upgrade 10gR1 to 10gR2 – DBUA error

2 Replies

Today I was upgrading 10gR1 to 10gR2 (10.2.0.1) on Linux x86. The upgrade went almost fine (except that I had to install one package and change few kernel parameters) but while running DBUA to upgrade databases, it gave an error:

Could not get database version from the Oracle Server component. The CEP file rdbmsup.sql does not provide the version directive

and

Start of root element expected. Upgrade Configuration file
'C:\Oracle10g2\cfgtoollogs\dbua\test\upgrade5\upgrade.xml' is not a valid XML file.

I searched in the metalink and found that this all happens due to customized glogin.sql file which was there in my case also. And removing that customization made DBUA rock 🙂

You might want to check here, here and here.

EXP-00008: ORACLE error 600 encountered

Leave a reply

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

2 Replies

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

Leave a reply

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