New package DBMS_XPAN?

Is this humor from error message writer?

SQL> select * from table(dbms_xplan.display_cursor(format=>'allsts'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Error: format 'allsts' not valid for DBMS_XPAN.DISPLAY_CURSOR()

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