Oracle Applications HOWTO: modify LOB storage

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));

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15149581/viewspace-676448/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15149581/viewspace-676448/