Troubleshooting

Another let-us-help-Google post ;). While running impdp import in 11g, you hit: [sql]ORA-39083: Object type INDEX failed to create with error: ORA-14102: only one LOGGING or NOLOGGING clause may be specified[/sql] It is related to bug 9015411 where DBMS_METADATA.GET_DDL creates incorrect create index statement by dumping both LOGGING and NO LOGGING clauses. Due to this the CREATE INDEX statement, while running impdp fails with the above error. It applies to 11.2.0.1 (Metalink doc id 1066635.1) ...

Yesterday, a friend of mine asked me about an error he was getting while running a schema level export in Oracle 8i: [sql]exp system/manager@DB owner=ABC file=ABC.dmp log =ABC.log Export: Release 8.1.7.1.0 - Production on Fri Nov 12 04:21:05 2010 (c) Copyright 2000 Oracle Corporation. All rights reserved. EXP-00056: ORACLE error 28002 encountered ORA-28002: the password will expire within 11 days EXP-00056: ORACLE error 24309 encountered ORA-24309: already connected to a server EXP-00000: Export terminated unsuccessfully[/sql] ...

Today i was refreshing a MVIEW (Oracle 9.2.0.1.0 on Windows 2000) and instead of writing [sql]exec dbms_mview.refresh(‘SCHEMA1.MVIEW1’,‘C’); [/sql] i wrote [sql]exec dbms_mview.refresh(‘SCHEMA1’,‘MVIEW1’,‘C’);[/sql] And it gave me: ERROR at line 1: ORA-30019: Illegal rollback Segment operation in Automatic Undo mode ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 794 ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 851 ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 832 ORA-06512: at line 1 [sql]ERROR at line 1: ORA-30019: Illegal rollback Segment operation in Automatic Undo mode ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 794 ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 851 ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 832 ORA-06512: at line 1[/sql] ...

Today i was gathering stats on one schema (10.2.0.3 on AIX 5.3, 64 bit) and it said: [sql]ERROR at line 1: ORA-03001: unimplemented feature ORA-06512: at “SYS.DBMS_STATS”, line 13336 ORA-06512: at “SYS.DBMS_STATS”, line 13682 ORA-06512: at “SYS.DBMS_STATS”, line 13760 ORA-06512: at “SYS.DBMS_STATS”, line 13719 ORA-06512: at line 1[/sql] Little bit of searching on Metalink revealed that i had hit Bug no 6011068 which points to the base Bug 576661 which is related to function based indexes. There were 2 function based indexes in the schema. Before talking about the workaround let us re-produce the test case. Here i am doing it on my laptop (10.2.0.1 on Windows XP 32 bit) ...

Today one of my colleague was working on development of a screen in Oracle Forms to give the end user an option to schedule a job using dbms_scheduler. With the hope that i would be able to explain it properly, the whole scenario is like this: User will log in to the application with his username (Lets say USER01) and password (basically every application user is a database user). He is provided with a screen where he can enter details about the job and the code behind the button calls a PL/SQL procedure in the main application schema (lets say APP1) which in turn uses DBMS_SCHEDULER.CREATE_JOB to schedule the new job. The ultimate task of the job is to move data from one table in the first database to a table in the second database using a DB Link. There is a VPD policy applied on all the application users to restrict the view of data. Policy function uses SYS_CONTEXT to fetch some information about the logged in user. The main application user APP1 is exempted from policy and can see the whole data. Things seem to work fine till the schedule part. But when the job runs it hits ORA-02070: database does not support operator SYS_CONTEXT in this context as SYS_CONTEXT and DB link doesn’t go together. ...

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): ...

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: [sourcecode language=‘css’]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.[/sourcecode] ...

Today I was running export of an Oracle 9.2.0.1 database. The export completed but with an ORA-600 error: [sourcecode language=‘css’] 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[/sourcecode] 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. ...

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. ...

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. ...