Amardeep Sidhu's Tech blog

Last week I had a chance to upgrade a 9.2.0.7 database to 10.2.0.5. The size of the database was around 800 GB. The major applications connecting to the database were developed in Pro*C and Oracle Forms. The upgrade itself pretty smooth but there were few glitches around that needed to be handled. Just thought about documenting all the issues: Few users in the database were assigned the CREATE SESSION privilege through a password protected role (That role was the default role for that user). 10.2.0.5 onwards, password protected roles can’t be set as default roles. The alternate is to either disable the password for the role or assign CREATE SESSION directly to the user, not through a role. ...

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

It has been almost an year since i posted something (useful) here. The last post was also a crappy one :) . Well, it all boils down to sheer laziness ;) . Now, i think the time has come to be regular again. Here i am getting a good start talking about Sangam10, i attended last week. It was a great opportunity to meet so many fellow Oracle professionals and most awesomely to meet & see Jonathan Lewis talk about Performance & Tuning. As expected the whole experience was amazing. It was a 2 day event where Jonathan was delivering 2 half day seminars on SQL Tuning and there were other break out sessions as well. We had planned to go a day in advance so me, Aman, Ankit & Neeraj reached Hyderabad on 2nd Sep. ...

2-3 days ago, I came across a code, intended to make delete faster. Just have a look ;) [sql]. . . LOOP SELECT COUNT (1) INTO v_cnt FROM table1 WHERE ROWNUM < 2; IF v_cnt = 0 THEN EXIT; END IF; DELETE FROM table1 WHERE ROWNUM < 1000; COMMIT; v_cnt := 0; END LOOP; . . .[/sql] Comments Comment by Vaibhav on 2009-10-10 07:58:17 +0530 What is the joke here? ...

Yesterday, one of my colleague asked that if he traced a wrap’ed PL/SQL procedure, would the SQL statements show up in the trace ? Very simple thing but at that moment i got, sort of into doubt. So i ran a simple test and yes they do show up ;) [sql]CREATE OR REPLACE PROCEDURE wrap1 AS v_today DATE; BEGIN SELECT SYSDATE INTO v_today FROM DUAL; END; / C:\>wrap iname=wrap1.sql PL/SQL Wrapper: Release 10.2.0.1.0- Production on Fri Sep 18 21:07:49 2009 ...

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