Migrating to 11.2.0.3 ORA-00976

You can have exists even in join condition in addition to what Rob Van Wijk has writen. We have code lines that has such exists together with an connect by query. Patching an 11.2.0.2 database to 11.2.0.3 In 11.2.0.2 one can run such a query. Here is one stupid example of such.

select d2.dummy from dual d1 left outer join dual d2 on exists (select 1 from dual connect by level < 3); 

D
-
X

In 11.2.0.3 you get an error thrown to your face.

ORA-00976: Specified pseudocolumn or operator not allowed here.
Cause: LEVEL, PRIOR, ROWNUM, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF or CONNECT_BY_ISCYCLE was specified at an illegal location.
Action: Remove LEVEL, PRIOR, ROWNUM, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF or CONNECT_BY_ISCYCLE.

The query may be rewritten like 11.2.0.3 parser is satisfied.

with cte as (select 1 from dual connect by level < 3)
select d2.dummy from dual d1 left outer join dual d2 on exists (select 1 from cte);

D
-
X

This is a runtime problem. Run your tests before patching production. Actually this is not just a runtime problem. We had the code inside a pl/sql procedure. The procedure is valid in 11.2.0.2 environment. After patching the procedure is still valid just waiting for the next compile or runtime problem of this code. Well there is most certainly a reason for such problem appearing. Might this be a side affect for a Bug correction 8724314 mentioned in 11.2.0.3 Patch Set - List of Bug Fixes by Problem Type [ID 1348303.1]. Might this usage of common table expression workaround be just a next bug waiting to appear.

Update: Confirmation about 8724314 being the reason for this
Bug 13496250 - Unexpected ORA-976 using LEVEL / PRIOR / ROWNUM in an ANSI "ON" filter predicate [ID 13496250.8]. The mentioned workaround there will not be a valid workaround always. If you start rewriting the query change to the common table expression approach described in this post.

11.2.0.3

Just downloading Patch 10404530: 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER

Patches are correcting bugs and adding new features. I guess that there is a new value to  OPTIMIZER_FEATURES_ENABLED parameter coming.

Time to unlearn. Tom Kyte talked at Oug Harmony 2011 last spring about caching inside a SQL query. He wrote about the same topic in Oracle Magazine september 2011. The thing seems like a candidate to be implemented in Oracle SQL engine in some future release. (I guess not yet in this 11.2.0.3). Just when a wide developer audience has learned to wrap pl/sql funtion calls inside a query to avoid multiple calls.

The thing to be unlearned might be a bug. Causing something not to perform or do wrong behavior in certain version. In some future version the bug might be corrected and the workaround becomes obsolete.  These are currently most often details that influence some specific situation. If you want to know the basics and more. Jonathan Lewis is comig to Finland this autumn. A two day learning session is available. Optimizing Oracle 1.-2.11.2011. And in addition Oracle User Group Finland autumn seminar 3.11.2011 seems to have pleasure to have his presentation. Agenda and registration information available to both at http://www.ougf.fi/ A lot guess working in this post. To avoid that attend.

Avoid temp usage while table reorg

Richard Foote blog is having questioning posts. Thanks to the latest solution there we have a possible alternative while reorganizing a huge table. Just hit an "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP" problem while doing table reorganization. Who and what sql is consuming temp helps identifying the problem statement.

insert /*+append*/ into targettable select * from sourcetable order by sortcolumn;

The source table has an index on sortcolumn. Using that it is possible to avoid sorting and temp usage.

insert /*+append*/ into targettable select /*+index(sourcetable sortcolumn_idx)*/ * from sourcetable order by sortcolumn;

Password expiring

Yet again...
A new 181 days ago created 11g database instance...
A software connection pool user is not allowed to connect to the database...

ORA-28002: the password will expire within 7 days
OR
ORA-28001: the password has expired

select profile from dba_users where username = 'POOLUSER';



DEFAULT



create profile pool_profile limit PASSWORD_LIFE_TIME unlimited;



alter user pooluser profile pool_profile; 



select profile from dba_users where username = 'POOLUSER';



POOL_PROFILE

set role

Trying to get autotrace out of SQL Developer and getting error message.

Failed to access V$MYSTAT.

Please obtain read catalog privilege

from your database administrator:

grant SELECT_CATALOG_ROLE to RAFU

grant SELECT ANY DICTIONARY toRAFU

NOTE: you need to reconnect your current session

in order for the settings change to have an effect

Granted the privileges. I guess less would be enough. Weird note. Why do I need to reconnect the session? There exists SET ROLE command.

SET ROLE ALL;

That does the job. No reconnect needed.

Crabby code

ORA-00020: maximum number of processes 500 exceeded

Connections are using DEDICATED connections. The pooling is done in the middle tier. Several pools. Connection leakage is not a database fault but it becomes to a everybody problem. While figuring out the problem, I managed to produce a following 10046 SQL trace.

ERROR #1:err=12899 tim=1312894876206660

WAIT #1: nam='SQL*Net break/reset to client' ela= 20 driver id=675562835 break?=1 p3=0 obj#=-1 tim=1312894876206740

WAIT #1: nam='SQL*Net break/reset to client' ela= 303 driver id=675562835 break?=0 p3=0 obj#=-1 tim=1312894876207059

WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876207098

WAIT #1: nam='SQL*Net message from client' ela= 247133 driver id=675562835 #bytes=1 p3=0 obj#=-1 tim=1312894876454273

Using the Oracle friendly search.

Talking about SQL*Net break/reset to client events Tanel Poder mentions these breaks are caused by bad application design. This is something I was trying to find out, but he is talking about MERGE statements. My trace included only inserts.

In the Xtrace manual it is mentioned "err" being equal to 12899 (ORA-12899 is "value too large for column"): That makes sense.

Luckily I had taken the trace with binds. And could point out the actual column causing the root problem. Only the first 255 characters of the bind values seems to be populated to the trace. The problem column was larger in this case. Using My Oracle support page Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1] it is possible to read the trace. In the bind part avl Actual value length (array length too).. Compared that to the column size at the position of the bind in the insert statement and the root problem was found.

About bad application design
* The application should know how long values can be inserted to each column.
* If the application receives an error it should have an error handler that releases the reserved resources. This time the unreleased resource was a database connection.

Major

Today I did something Donald Duck would do. A couple years ago I built a house. Here are two doors to leave the house, one in front and also a back door. There are wooden stairs in the front door and a wooden terrace outside the back door. I just oiled the front stairs and just after that continued oiling at the back terrace. Now I am stuck inside for a while. It is time to open a beer and think of something else done today. Karhu has some virtual fishing to do.

Julian Dontcheff mentioned a while ago about 11.2.0.2 that it should actually be called 11R3. I was reading my oracle support. The issue in my spontaneous online demonstration in What is bugging me presentation is noticed. My demonstration was about "No results with function based indexes and OR expansion". Now there is a document in MOS "Things to Consider Before Upgrade to 11.2.0.2 Database Performance [ID 1320966.1]" updated 7.6.2011. On off patch is recommended as Oracle does not want to interfere optimizer with CPU or PSU patching. The fifth number in the version numbering. So are those four letters actually something that should be considered major version. Well 11.2.0.2 has so many other changes than the optimizer ones that it could be a 11R3. Just see the list of new features in 11.2.0.2 list in the documentation. OPTIMIZER_FEATURES_ENABLED It has already been from version 10.1.0.3 and 9.2.0.8 that the fourth number have had a meaning. Actually 11.2.0.2 is missing there. Yet another place to submit a user comment about the documentation.

While writing this post it seems like the 1320966.1 document is vanishing or is it just something about the flashy interface... The patch recommended to install was 9776940. Contact support before installing... Another thing mentioned there was the 11.2.0.3 patchset due out later this year.

Yet another thing. During last week there has been a slight peak in the visitor count of my blog. After Jonathan Lewis has made an Argh! issue about merge ignores check constraint and someone linking that post to his pages. Yes it actually is an Argh issue.

Actually yet another Argh! issue might be visualized as a snip