ORA-01790 when trying to create a stored outline

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Fri, 20 Mar 2009 10:22:34 -0600
Message-ID: <49C3C2CA.5050200_at_optimaldba.com>

Oracle EE 10.2.0.4 on AIX
Using Automatic SGA (500m) and the shared pool tends to be pretty small (less than 125m)
This database has used stored outlines in the past (10.1.0.3). The person who did this has passed on her instructions, but did not get this error.

I get the error when the sql is in the shared pool, but not when it ages out. Of course, when it ages out then the outline is never created.

The steps...
In another session as the application user, run the query of interest. It completes and uses the 'good' plan (bind variable peeking strikes again!)

In a sysdba session
alter session set used_stored_outlines=true; wait until the query finishes and verify that the query is still in the shared pool
SYSTEM_at_dev> select hash_value, child_number from v$sql where hash_value
= 2267168828;

HASH_VALUE CHILD_NUMBER
---------- ------------
2267168828 0

SYSTEM_at_dev> exec dbms_outln.create_outline (2267168828,0,'PROD'); BEGIN dbms_outln.create_outline (2267168828,0,'PROD'); END;

*
ERROR at line 1:

ORA-01790: expression must have same datatype as corresponding expression
ORA-06512: at "SYS.OUTLN_PKG", line 323
ORA-06512: at line 1

SYSTEM_at_dev> select hash_value, child_number from v$sql where hash_value
= 2267168828;

-- No rows returned
SYSTEM_at_dev> exec dbms_outln.create_outline (2267168828,0,'PROD');

If I query dba_outlines, the sql of interest does not show up. The dba_outlines view does return rows.

Regards,
Daniel Fink

-- 
Daniel Fink

OptimalDBA.com - Oracle Performance, Diagnosis, Data Recovery and Training

OptimalDBA    http://www.optimaldba.com
Oracle Blog   http://optimaldba.blogspot.com

Lost Data?    http://www.ora600.be/

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 20 2009 - 11:22:34 CDT