Month: May 2017

If you are performing and Export/Import from a source database that contains types, you may encounter the error:

ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE ""."object_type_name"   OID '8FD43617684FA1F6E033A625D854A1F6' AS OBJECT
(
   parm_01             varchar2(18),
   parm_02             varchar2(50)
);

Each type has an OID. If you create an object type and do not specify an OID, Oracle generates an OID and assigns it to the type.

To resolve this error, you need to transform the OID by adding the below option to you IMPDP command:

transform=OID:n:type

The type object will now get a new OID in the target database.

Today we had users complaining that a query would run fast the first time and then fail to complete the second time. The SQL used bind parameters and we (DBAs) suspected that the issue was caused by the user changing the bind parameters. However, when we ran the SQL ourselves with hard coded values we also experienced the same behavior. The first execution completed in < 5 seconds and the second execution was killed at > 400 seconds of elapsed time. After some searching on the web I ran across an excellent explanation at the web site:

http://orcasoracle.squarespace.com/oracle-rdbms/2012/12/18/when-a-query-runs-slower-on-second-execution-a-possible-side.html

We added

/*+ OPT_PARAM('_OPTIMIZER_USE_FEEDBACK' 'FALSE') */

and the SQL began to perform consistently every time.

This was experienced on Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production.