Amardeep Sidhu's Tech blog

It was a 10g (10.2.0.5 on HP-UX 11.23 RISC) database which was recently upgraded from 9.2.0.8. The CPU and memory utilization was going really high. After tuning few of the queries coming in top, CPU usage was coming within accetable limits but the memory usage was still high. There was a total of 16 GB of RAM on the server and the usage was above 90%, constantly. One of the reasons behind high usage was increase in the SGA size. It was increased from 2.5 GB (in 9i) to around 5 GB (in 10g). Another major chunk was being eaten by OS buffer cache. While looking at the memory usage with kmeminfo:[bash]Buffer cache = 1048448 4.0g 25% details with -bufcache[/bash] ...

Very simple issue but took some amount of time in troubleshooting so thought about posting it here. May be it proves to be useful for someone. Scenario was: Oracle is installed from “oracle” user and all runs well. There is a new OS user “test1” that also needs to use sqlplus. So granted the necessary permissions on ORACLE_HOME to test1. Tried to connect sqlplus scott/tiger@DB and yes it works. But while trying sqlplus scott/tiger it throws: ...

Some time back, I was at a client where the customer complained that no one was able to log in to the database. It was Oracle 10.2.0.4 running on HP-Ux. I logged in to the database and checked the wait events: [sql]SQL> @wait EVENT COUNT(*) ---————————————————————- ———- wait for possible quiesce finish 1 Streams AQ: qmn coordinator idle wait 1 Streams AQ: qmn slave idle wait 1 Streams AQ: waiting for time management or cleanup tasks 1 SQL*Net message to client 1 smon timer 1 pmon timer 1 jobq slave wait 4 rdbms ipc message 11 SQL*Net message from client 27 resmgr:become active 322 ...

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