Error ORA-04030 : out of process memory when trying to allocate 4108 bytes when compiling package

Applies to:

Oracle Payables - Version: 12.0.1
This problem can occur on any platform.

Symptoms

In  Release 12 instances , the package XLA_00200_AAD_S_000010_PKG fails to compile by firing the Error ORA-04030 : out of process memory when trying to allocate 4108 bytes.

STEPS
The issue can be reproduced at will with the following steps:
1. Change database initialization parameters.
2. Convert pl/sql code type to native following steps under
"Modifying the Entire Database for PL/SQL Native or Interpreted Compilation (page 11-29)"
of "Oracle Database PL/SQL User's Guide and Reference 10g Release 2 (10.2)".
3. Recompile package body XLA_00200_AAD_S_000010_PKG.

Changes

Changed the database initialization parameter plsql_code_type to 'native' as per Note 396009.1 Database Initialization Parameters for Oracle Applications Release 12 and converted the pl/sql code to native as per Oracle Database PL/SQL User's Guide and Reference.

Cause

The issue is reported in the internal unpublished Bug which interprets the reason of the failure as follows:

Package size is more than 130K lines which make the sql optimization use too much memory.   The system generated packages are not shipped with the product and they depend on the customer setup so packages can vary in size from one situation to another.

Solution

Action Plan:

1.Set the plsql_optimize_level to 0 before compiling the Package by doing the following:

SQL>alter system set plsql_optimize_level =0;

2.Run the alter command to compile the package

3.Set the plsql_optimize_level to 2 after compiling the package as recommended per the following reference Note 396009.1 (Database Initialization Parameters for Oracle Applications Release
12), plsql_optimize_level should be set to '2'

Note :

• Please note that if the plsql_optimize_level is set '0' after the package compilation , this will affect the performance of the application as  it prevents the code from being rearranged at all. 
  
• The Difference between '0' and '2' for plsql_optimize_level.is as follows:
  1. Level 0
     
     Maintains the evaluation order and hence the pattern of side effects, exceptions, and package
     initializations of Oracle9i and earlier releases. Also removes the new semantic identity of
     BINARY_INTEGER and PLS_INTEGER and restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database 10g.
     
  2. Level 2
     
     Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location.
     Generally, setting this parameter to 2 pays off in better execution performance. If, however, the compiler runs slowly on a particular source module or if optimization does not make sense for some reason (for example, during rapid turnaround development), then setting this parameter to 1 will result in almost as good a compilation with less use of compile-time resources.

Unable to extend

Applies to:

Oracle Server - Enterprise Edition - Version: 7.0.16.0 to 11.1.0.7
Information in this document applies to any platform.

Purpose

This document can be used to diagnose and resolve space management errors of the type "unable to extend ... by %s in tablespace <name>"  where the 'by %s' extent size is the number of DB blocks.

EXAMPLE ERRORS

ORA-1650: unable to extend rollback segment %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a rollback segment in the tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1651: unable to extend save undo segment by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for saving undo entries for the indicated offline tablespace.
  Action: Check the storage parameters for the SYSTEM tablespace. The tablespace needs to be brought back online so the undo can be applied.

ORA-1652: unable to extend temp segment by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a temporary segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1653: unable to extend table %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1654: unable to extend index %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for an index segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1655: unable to extend cluster %s.%s by %s for tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for a cluster segment in tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


ORA-1683: unable to extend index %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for index segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1688: unable to extend table %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for table segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1691: unable to extend lob segment %s.%s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-1692: unable to extend lob segment %s.%s partition %s by %s in tablespace %s
  Cause: Failed to allocate an extent of the required number of blocks for LOB segment in the tablespace indicated.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.


ORA-3233: unable to extend table %s.%s subpartition %s by %s in tablespace %s
  Cause: Failed to allocate an extent for table subpartition segment in tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3234: unable to extend index %s.%s subpartition %s by %s in tablespace %s
  Cause: Failed to allocate an extent for index subpartition segment in tablespace.
  Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

ORA-3238: unable to extend LOB segment %s.%s subpartition %s by %s in tablespace %s
   Cause: An attempt was made to allocate an extent for LOB subpartition segment in tablespace, but the extent could not be allocated because there is not enough space in the tablespace indicated.
   Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.

Troubleshooting Details

An "unable to extend" error is raised when there is insufficient contiguous space available to extend a segment.

I. Information needed to resolve UNABLE TO EXTEND errors

In order to address UNABLE TO EXTEND errors the following information is needed:


1. Determine the largest contiguous space available for the tablespace with the error

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = '<tablespace name>';

The above query returns the largest available contiguous chunk of space.

Please note that if the tablespace you are concerned with is of type TEMPORARY, then please refer to Note 188610.1.

If this query is done immediately after the failure, it will show that the largest contiguous space in the tablespace is smaller than the next extent  the object was trying to allocate.


2. Determine NEXT_EXTENT size

SELECT NEXT_EXTENT, PCT_INCREASE FROM DBA_SEGMENTS WHERE SEGMENT_NAME = <segment name> AND SEGMENT_TYPE = <segment type> AND OWNER = <owner>

<segment type> is usually stated in the error message

Possible Segment Types:

   CLUSTER
   INDEX
   INDEX PARTITION
   LOB PARTITION
   LOBINDEX
   LOBSEGMENT
   NESTED TABLE
   ROLLBACK
   TABLE
   TABLE PARTITION
   TYPE2 UNDO
   TYPE2 UNDO (ORA-1651)

<segment name> name is stated in the error message


3. Compute the NEXT EXTENT SIZE if the segment resides in a dictionary managed tablespace and has a PCT_INCREASE >0

SELECT EXTENT_MANAGEMENT FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = '<tablespace name>';

Use the "next_extent" size with "pct_increase" (from #2 above) in the following formula to determine the size of extent that is trying to be allocated.

extent size = next_extent * (1 + (pct_increase/100)

EXAMPLE

next_extent = 512000
pct_increase = 50

next extent size = 512000 * (1 + (50/100)) = 512000 * 1.5 = 768000

NOTES:

ORA-01650 Rollback Segment

     pct_increase is only needed for early versions of Oracle, by  default in later versions pct_increase for a rollback segment is 0.

ORA-01652 Temporary Segment

    Temporary segments take the default storage clause of the tablespace  in which they are created.

     If this error is caused by a query, then try and ensure that the query  is tuned to perform its sorts as efficiently as possible.

    To find the owner of a sort, please refer to Note 1069041.6.


4) Determine which solution best fits the conditions

     If the NEXT EXTENT size (step 2 or 3 above) is larger than the largest contiguous chunk of free space then 
         Manually Coalesce Adjacent Free Extents may be an option
         If after coalescing ... there is still insufficient contiguous space ... then one of the other options must be chosen

     If the volumes on which the datafile(s) for the tablespace have insufficient space then
         Add a Datafile or Defragment the Tablespace are your only options ... and this file must be added to a new volume with sufficient space

II. Possible Solutions

There are several options for resolving UNABLE TO EXTEND errors

A. Manually Coalesce Adjacent Free Extents

ALTER TABLESPACE <tablespace name> COALESCE;

The extents must be adjacent to each other for this to work.

B. Modify one or more datafiles in the tablespace to use AUTOEXTEND

ALTER DATABASE DATAFILE '<full path and name>' AUTOEXTEND ON MAXSIZE <integer> <k | m | g | t | p | e>;

NOTE: It is strongly recommended that MAXSIZE be specified to try to prevent the datafile from consuming all available space on the volume

C. Add a Datafile

ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file name>' SIZE <integer> <k | m | g | t | p | e>;

D. Lower "next_extent" and/or "pct_increase" size if segment is in a Dictionary Managed Tablespace

For non-temporary and non-partitioned segments:

ALTER <SEGMENT TYPE> <segment_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);

For non-temporary and partitioned segments:

ALTER TABLE <table_name> MODIFY PARTITION <partition_name> STORAGE ( next <integer> <k | m | g | t | p | e> pctincrease <integer>);

For a temporary segment problem: 

ALTER TABLESPACE <tablespace name> DEFAULT STORAGE (initial <integer> <k | m | g | t | p | e> next <integer> <k | m | g | t | p | e> pctincrease <integer>);

E. Resize the Datafile

ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k | m | g | t | p | e>;

ORA-07445 opidsa

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 to 10.2.0.3
Information in this document applies to any platform.

Description

After having applied the 10.2.0.3 patch set on any platform, processes from user sessions can core dump (ORA-7445) in opidsa().
The problem is also known to affect 10.2.0.2, but only for Windows platforms when running with 10.2.0.2 Patch 6 upwards.

Likelihood of Occurrence

The problem will typically be seen after the database has been running for some time (weeks). 

Possible Symptoms

The core dump is shown in the alert.log as messages similar to:

ORA-07445: exception encountered: core dump [opidsa()+480] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []

or

ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [_opidsa+360] [PC:0x2080540] [ADDR:0x0] [UNABLE_TO_READ] []

Looking at the call stack in the trace file shows:

...
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
 ksedmp()+744         CALL     ksedst()             000000840 ? 1066C42AC ?
                                                   000000000 ? 1066C0DA0 ?
                                                   1066BFB08 ? 1066C0508 ?
 ssexhd()+1240        CALL     ksedmp()             000106400 ? 10652CE64 ?
                                                   10652C000 ? 00010652C ?
                                                   000106400 ? 10652CE64 ?
 sigacthandler()+44   PTR_CALL 0000000000000000     10652A000 ? 1066C7EF0 ?
                                                   106526E2C ? 00010652A ?
                                                   00000000B ? 000000067 ?
 opidsa()+480         PTR_CALL 0000000000000000     00000000B ? 1066C7EF0 ?
                                                   1066C7C10 ? 000000001 ?
                                                   000000000 ? 000010000 ?
 ...

The offset (480 in the example) may vary.

We may have different code paths leading to the opidsa() call, such as:
... opidsa opiodr ttcpip opitsk opiino opiodr opirip opidrv ...
... opidsa kpodny opikpr opiodr rpidrus skgmstack ...

Note that it is also possible for this issue to be seen as a dump in opidsc().

Workaround or Resolution

No real workaround exists for the problem, but flushing the shared pool after receiving the error may clear the situation to prevent further occurrences for a while. Restarting the instance will also clear the problem for a period. Implementing regular flushes of the shared pool can prevent the problem from occurring (this could eg. be executed via DBMS_JOB outside business hours to minimize the impact).

The problem is fixed in 11g via non-published bug:5648872 and the fix is included in 10.2.0.4.

Viewer application profile is not set

Symptoms

Create document via Launch Journal Wizard, R12, errors with 'Viewer application profile is not set'

The viewer is NULL in the contextual information

Expected Behavior
==============
Document is created without any issue, in R12 ADI can't be used.

Cause

Profile option 'GL : Default Desktop Viewer' is not set.

Solution

To implement the solution, please execute the following steps:

1. The value displayed in the viewer field, which is currently null, is the 'GL : Default Desktop Viewer' profile option value.

2. This is needed if you are launching the Journal wizard using the shortcut link. Please set this profile option value and try to launch the JW.

Value could be as an example Excel 2000

3. Logout of application in order for the profile option to take the effect.

SQLGL: GL_BUD_INTG is an invalid integrator key

Symptoms

The following error occurs while trying to create the budget worksheet to upload amounts using budget wizard:

SQLGL: GL_BUD_INTG is an invalid integrator key .

Changes

None

Cause

These are the functions that are required for the menu that is assigned to the responsibility from which the budget upload is done.

Function User function name

BNE_ADI_CREATE_DOCUMENT Desktop Integration - Create Document
BNE_CREATE_DOCUMENT Create Document .

Solution

1. Go to Sysadmin--Application--Function and check if these two functions are available or not.

In case they are not available please add these:

Function User function name

BNE_ADI_CREATE_DOCUMENT Desktop Integration - Create Document ,
BNE_CREATE_DOCUMENT Create Document .

2. Go to Sysadmin--Application--Menu

Query the menu GL_SUPERUSER (this is usually the default menu that is assigned with General Ledger responsibility) add the following two menu functions that have been defined in step 1 above, under the field "Function" :

Desktop Integration - Create Document
Create Document