Oracle Applications HOWTO: modify LOB storage

Go to Michael Dvorkin's user page

Prerequisites
E-Business Suite 11.5.9, 11.5.10

Steps to modify LOB storage
1. Find the LOB owner according to the error from init.ora:

ORA-1693: max # extents 505 reached in lobsegment APPLSYS.SYS_LOBXXXXXXXXXXXXXXXX$$

sqlplus system/ select owner, table_name, column_name from dba_lobs where segment_name = 'SYS_LOBXXXXXXXXXXXXXXXX$$'; exit;

2. Modify LOB maxextents

sqlplus system/ alter table . modify lob () (STORAGE (MAXEXTENTS UNLIMITED)); exit;

3. Example:

ORA-1693: max # extents 505 reached in lobsegment APPLSYS.SYS_LOB0000255319C00040$$

sqlplus system select owner, table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000255319C00040$$';

OWNER                         TABLE_NAME           COLUMN_NAME --    APPLSYS                        WF_NOTIFICATION_OUT  "USER_DATA"."TEXT_LOB"

sqlplus system alter table APPLSYS.WF_NOTIFICATION_OUT modify lob ("USER_DATA"."TEXT_LOB") (STORAGE (MAXEXTENTS UNLIMITED));

Known Issues
Go to Michael Dvorkin's user page