Steps to Clean Nonexistent Nodes or IP Addresses from FND_NODES

Here we will discuss how to clean fnd_nodes table in oracle apps.

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

After complete clone in E-Business suite, if FND_NODE system tables will show incorrect values.

If a clone was done, the OAM Dashboard reflects the target machine name and the source machine name although the source machine is no longer being used.

In some cases, there are IP addresses from nonexistent machines in FND_NODES.

The Nodes in the OAM Dashboard are retrieved from the FND_NODES table.

Further investigation showed that the FND_NODES table had both node names and node_modes 'O' as activated.

To check fnd_nodes table value.

SQL> select node_name, node_mode, support_cp, support_web, support_admin,support_forms from FND_NODES;

NODE_NAME              N S S S S
-----------------------------------------------
TARGET                     O Y Y Y Y
SOURCE                     O Y Y Y Y

How to Clean fnd_nodes table :
------------------

1) Always apply the latest cloning patches, to avoid all the bugs and fixes

2) SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;

    COMMIT;

    EXIT;

3) Run AutoConfig on all tiers, firstly on the dbTier and then the appsTier,to repopulate the required system tables.

Once we will execute the EXEC FND_CONC_CLONE.SETUP_CLEAN, it will delete all data from system tables such as FND_NODES, FND_OAM_CONTEXT_FILES, etc.

The correct information for the current system will be repopulated once AutoConfig will successfully completed.

Steps to Recover Apps Password in EBS R12

Here we will discuss How to recover APPS and SYSADMIN password .

Suppose someone forgot to remember the APPS and SYSADMIN password,follow the below steps to recover the same password.

To recover APPS password:-

create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME

'oracle.apps.fnd.security.WebSessionMa nagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/

set linesize 200 long 300

select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

Below will show the apps password.

SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG59262C41BF17CF61B68941A4A4AD707B61C407D54D52615AA1344981200AA4089E1BD5FB811FB1E1517D61D0EDCD008CDE') from dual;

Step to Find SYSADMIN PASSWORD :

Step# 1:

SQL> conn apps/APPS123;
Connected.

Step#2:

SQL> CREATE OR REPLACE PACKAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/
Package created.

Step# 3:

SQL>
CREATE OR REPLACE PACKAGE BODY get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; END get_pwd;
/
Package body created.

Step# 4: In this step we will see the sysadmin password.

SQL> SELECT usr.user_name,get_pwd.decrypt((SELECT (SELECT get_pwd.decrypt(fnd_web_sec.get_guest_username_pwd,usertable.encrypted_foundation_password)
FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name =(SELECT SUBSTR(fnd_web_sec.get_guest_username_pwd,1,INSTR
(fnd_web_sec.get_guest_username_p '/' )- 1)FROM DUAL)),usr.encrypted_user_password) PASSWORD FROM fnd_user usr WHERE usr.user_name = '&USER_NAME';

Enter value for user_name: SYSADMIN
old 23: WHERE usr.user_name = '&USER_NAME'
new 23: WHERE usr.user_name = 'SYSADMIN'

USER_NAME PASSWORD
------------------------ -----------------
SYSADMIN SYSADMIN123

Step#5 : Bellow sql will help you to validate SYSADMIN Password

SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN123) from dual;

FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN123')
--------------------------------------------------------------------------------
Y

steps to bounce apache in r12

Automate Apache server restart (bounce) through script.

In this post we will know how to automate Apache server restart through shell script.

If you are thinking to automating Apache server restart which can be done through a crontab entry.

Pls check carefully below steps:-

Here is Details :

Create the below entry in crontab (Please modify the timing in the cronjob as per your Apache restart requirement):

10 8 * * * su -c applmgr "sh /oracle/prodappl/11.5.0/bin/ApacheBounce.sh"

Note: ApacheBounce is the script to be created which will do the Apache restart. Mineserver is the servername where Apache services are running.

This example is for the services running on a single node and same concept can be followed while implementing on multiple servers.

Location of the bounce script: /oracle/prodappl/11.5.0/bin/ApacheBounce.sh

The below mentioned are the contents of the Apache restart script (ApacheBounce.sh) :

#################################################################################

#Source the ENV file

. /oracle/prodappl/APPSPROD_XXX.env

Sleep 5

# Notify all users before the restart.

cat $MINE_TOP/bin/ Apachedown.txt | mailx -s "PRODUCTION - Apache Bounce will happen in 3 mins " -c “oracledba@gmail.com”

sleep 180

#Stops the Apache services

sh $ADMIN_SCRIPTS_HOME/adapcctl.sh stop

sleep 10

# Kill leftover Apache processes

kill_processes=`ps -ef | grep "/iAS/Apache/Apache/bin/" | awk '{print $2}'`

echo $kill_processes

kill -9 $kill_processes

sleep 5

sh $ADMIN_SCRIPTS_HOME/adapcctl.sh start # starts the Apache services

sleep 5

# Notify all users after restart

cat $CUST_TOP/bin/Apacheup.txt | mailx -s "PRODUCTION Apache Bounce Complete" -c “oracledba@gmail.com”

#################################################################################

End of file.

Note: Save the content of the email to be sent in a text file: Apachedown.txt and Apacheup.txt

For example:

$ cat $CUST_TOP/bin/Apachedown.txt

----------------------------------------------------------------------------------------------------------------------------

Hi All,

PRODUCTION Apache services are scheduled to be bounced in 2 minutes.

Please ignore any alerts while it is being bounced.

Thanks,

Oracle Apps DBA Team

----------------------------------------------------------------------------------------------------------------------------

$ cat $CUST_TOP/bin/Apacheup.txt

-----------------------------------------------------------------------------------------------------------------------------

Hi All,

PRODUCTION Apache services have been restarted.

Please report if there are any issues.

Thanks,

Oracle Apps DBA Team

R12 : Autoconfig failed on jtfictx.sh INSTE8_PRF 1 - ORA-29874: warning in the execution of ODCIINDEXALTER routine

R12 : Autoconfig failed on jtfictx.sh INSTE8_PRF 1 - ORA-29874: warning in the execution of ODCIINDEXALTER routine

In this post we will know how to resolve jtfictx.sh INSTE8_PRF 1 - ORA-29874.

During EBS clone I faced the following error while try to run adcfgclone.pl on appsTier then autoconfig failed at appsTier.

Autoconfig completed with errors.

/apps/inst/apps/TEST_test/admin/install/jtfictx.sh

script returned:

****************************************************

jtfictx.sh started at Thu Mar 30 15:28:49 IST 2017

SQL*Plus: Release 10.1.0.5.0 - Production on Thu Mar 30 15:28:49 2017

Enter value for 1: Enter value for 2: Enter value for 3: Connected.

DECLARE

ERROR at line 1:

ORA-29874: warning in the execution of ODCIINDEXALTER routine

ORA-29960: line 1,

DRG-10595: ALTER INDEX JTF_AMV_ITEMS_URL_CTX failed

DRG-11000: invalid keyword SYNC

ORA-06512: at "SYS.DBMS_SQL", line 1120

ORA-06512: at line 96

CAUSE

The JTF_AMV_ITEMS_URL_CTX index is invalid for some reason.

SOLUTION

The simple fix for this is to drop and recreate the index.

1:- Back-up your database
2:- Rebuild the JTF_AMV_ITEMS_URL_CTX index.

a. DROP INDEX JTF_AMV_ITEMS_URL_CTX FORCE
b. sqlplus uname/pwd @jtfiaibu.sql JTF JTF APPS

3:- Re-Run Autoconfig

4:- If the above command does not compile the index correctly then please retry the process again running step 2b as:

sqlplus uname/pwd @jtfiaibu.sql JTF JTF APPS

5:- Migrate the solution as appropriate to other environments.

References:

"Jtfictx.sh" Failed During Autoconfig On Application Tier, ORA-29874, DRG-10595 (Doc ID 1271186.1)
Oracle Text may not include Theme Functionality after Installation (Doc ID 262701.1)

ORA-ERROR which every DBA facing

ORA- 12514: TNS:listener does not currently know of service requested in connect descriptor.

Cause:- The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action:

- Wait a moment and try to connect a second time.

- Check which services are currently known by the listener by executing: lsnrctl services <listener name>

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.

ORA-12537: TNS: connection closed error

Question: I'm trying to connect to Oracle and I get the ORA-12537: TNS: connection closed error. How do I fix an ORA-12537?

Answer: The ORA-12537 is an informational error, and the "real" error is the secondary error message.

Error:-

Tnsping Net Service Name fails with TNS-12537: TNS:connection closed or Sqlplus connection errors out with Ora-12537

Cause:

* In the Sqlnet.ora file the parameter TCP.VALIDNODE_CHECKING is enabled and TCP.INVITEDNODES is set to some specific IP's of the Client Machine.

Error:-

If trying to connect to the Apps database, by default, with Oracle Apps Installation the parameter TCP.VALIDNODE_CHECKING is enabled and TCP.INVITEDNODES is set to Server Machines IP or Hostname.

Solution

* You can disable these parameters in the Sqlnet.ora

* Or add the client machines ip address in the TCP.INVITEDNODES list.

ORA-00600/ORA-07445

Clause: Most likely it is related to bug

Solution: go to metalink and apply patch set.

ORA-04031

Clause: Mis-configuration of SGA, insufficient memory

Solution: Sametime need to add memory on system, or sametime need to reduce SGA memory

ORA-12154

Clause: Wrong TNS_ENTRY is used.

Solution: Using NETCA tools configure tns entry or check tnsnames.ora file for correct entry.

ORA-12368

Clause: It is generic error lots of reason

Solution: Edit SQLNET.ORA and change NONE from NTS "SQLNET.AUTHENTICATION_SERVICES=NONE"

ORA-12500

Clause: It is generic error lots of reason

Solution: increase "processes" parameter, increase physical memory

ORA-12528

Clause: It is BUG in 10g (if you face this error in 10g)

Solution: Don't use TNS_ENTRY with connect string or edit listener configuration from STATIC to DYNAMIC.(search on my blog for more)

ORA Error ! Oracle database symptoms (ORA Error)

Oracle database symptoms (ORA Error):-

oracle error codes list with description

1. Symptoms

Memory heap error ORA-7445[kssrem] and ORA-7445[kggchk] is reported in the alert.log and PMON terminates.

ORA-07445: exception encountered: core dump [kssrem()+176] [SIGSEGV] [Address

not mapped to object] [0x000000008] [] []

ORA-07445: exception encountered: core dump [kggchk()+52] [SIGSEGV] [Address

not mapped to object] [0x44000000020] [] [] MMAN: terminating instance due to error 472

ORA-00472: PMON process terminated with error

Cause:-

This is bug where various dumps / ORA-600 errors and ultimately an instance

crash can occur when a session performs a rollback to a savepoint type operation.

Action: Contact your customer support representative

2. Symptoms:-

ORA-00600 [kokcup:01] and ORA-00600 [kssadpm: null parent]

ORA-00600: internal error code, arguments: [LibraryCacheNotEmptyOnClose], [], [], [], [], [], [], []

Cause:

This is the generic internal error number for Oracle program exceptions. This indicates that a process has encountered an exceptional condition.

Action: Report as a bug - the first argument is the internal error number

3. Symptoms.

ORA-00060: Deadlock detected.

00060, 00000, "deadlock detected while waiting for resource"

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

4. Symptoms.

ORA-1652: unable to extend temp segment by 256 in tablespace TEMP

Cause: Failed to allocate an extent of the required number of blocks for 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

Below are some ORA-ERROR which every DBA face:

1.ORA 12514:	TNS:listener does not currently know of service requested in connect descriptor
*Cause:*	The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
*Action:*	- Wait a moment and try to connect a second time. - Check which services are currently known by the listener by executing: lsnrctl services <listener name> - Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener. - If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener. - Check for an event in the listener.log file.

2. ORA-12537: TNS: connection closed error

Question: I'm trying to connect to Oracle and I get the ORA-12537: TNS: connection closed error. How do I fix an ORA-12537?
Answer: The ORA-12537 is an informational error, and the "real" error is the secondary error message.
Tnsping Net Service Name fails with TNS-12537: TNS:connection closed or Sqlplus connection errors out with Ora-12537
Cause:

* In the Sqlnet.ora file the parameter TCP.VALIDNODE_CHECKING is enabled and TCP.INVITEDNODES is set to some specific IP's of the Client Machine.

* If trying to connect to the Apps database, by default, with Oracle Apps Installation the parameter TCP.VALIDNODE_CHECKING is enabled and TCP.INVITEDNODES is set to Server Machines IP or Hostname.

Solution:-

/* You can disable these parameters in the Sqlnet.ora

* Or add the client machines ip address in the TCP.INVITEDNODES list.

3 ORA-00600/ORA-07445

Clause: Most likely it is related to bug

Solution: go to metalink and apply patch set.

4
ORA-04031

Clause: Mis-configuration of SGA, insufficient memory

Solution: Sametime need to add memory on system, or sametime need to reduce SGA memory

ORA-12154

Clause: Wrong TNS_ENTRY is used.

Solution: Using NETCA tools configure tns entry or check tnsnames.ora file for correct entry.

ORA-12368

Clause: It is generic error lots of reason

Solution: Edit SQLNET.ORA and change NONE from NTS "SQLNET.AUTHENTICATION_SERVICES=NONE"

ORA-12500

Clause: It is generic error lots of reason

Solution: increase "processes" parameter, increase physical memory

ORA-12528

Clause: It is BUG in 10g (if you face this error in 10g)

Solution: Don't use TNS_ENTRY with connect string or edit listener configuration from
STATIC to DYNAMIC.(search on my blog for more)

ORA-1691: unable to extend lobsegment APPLSYS.SYS_LOB in tablespace APPLSYSD

Recently I have faced the ORA-1691 error in our production environment.

Error:

ORA-1691: unable to extend lobsegment APPLSYS.SYS_LOB0000255030C00040$$ by 1574805 in tablespace APPLSYSD

Analysis and Solution:

Step1: Verify Free space in tablespace & Check data file size

SQL> select dt.tablespace_name,round(sum(df.bytes)/1024/1024/1024,0) "Free_GB"
from dba_free_space df,dba_tablespaces dt where df.tablespace_name=dt.tablespace_name(+)
and df.tablespace_name not in (select tablespace_name from dba_temp_files)
group by dt.tablespace_name order by 1;

TABLESPACE_NAME          Free_GB
------------------------------ ----------
APD                                     0
APPLSYSD                         114
APPLSYSX                         139
APPS_UNDOTBS01                 0
APPS_UNDOTBS02                 6
.
.
ASFD                                    0

SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='APPLSYSD';

FILE_NAME                                                    BYTES/1024/1024
------------------------------------------------------------ ---------------
/data01/oracle/crmprdata/applsysd01.dbf                                 9216
/data01/oracle/crmprdata/applsysd02.dbf                                 8192
/data02/oracle/crmprdata/applsysd03.dbf                                 8192
/data02/oracle/crmprdata/applsysd04.dbf                                 9216
/data04/oracle/crmprdata/applsysd05.dbf                                 9692
/data04/oracle/crmprdata/applsysd06.dbf                                 9216
/data02/oracle/crmprdata/applsysd07.dbf                                 8196
/data01/oracle/crmprdata/applsysd08.dbf                                 8696
/data01/oracle/crmprdata/applsysd13.dbf                                 4000
/data04/oracle/crmprdata/applsysd09.dbf                                 8192

27 rows selected.

Free space in tablespace found to be over 100 GB , So it is not space issue but some storage parameter issue

Step 2: If enough free space is available then verify NEXT_EXTENT & MAX_EXTENTS value of the segment highlighted in error

SQL> select * from dba_segments where segment_name='SYS_LOB0000255030C00040$$';

OWNER                          SEGMENT_NAME                  PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_
------------------------------ ----------------------------- ------------------------------ ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -------
APPLSYS                        SYS_LOB0000255030C00040$$                                    LOBSEGMENT         APPLSYSD                               289        84109 25815859200    3149650         32          40960 12900777984           1 2147483645           50          1               1          289 DEFAULT

SQL> select * from dba_tablespaces where tablespace_name='APPLSYSD';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ --------
APPLSYSD                             8192          40960       40960           1 2147483645           50          0 ONLINE    PERMANENT LOGGING   NO LOCAL      USER      NO MANUAL DISABLED

Here, NEXT_EXTENT found to be very huge (12 GB) which is the root cause of this issue.MAX_EXTENTS found OK & marked as unlimited

Step 3: Modify table lob storage clause to reduce the NEXT_EXTENT size from 12 GB to 1 MB

SQL> alter table APPLSYS.WF_NOTIFICATION_OUT modify lob ("USER_DATA"."TEXT_LOB") (STORAGE (NEXT 1048576));

Table altered.

Step 4: Verify the modifications made in previous step & check if new extent is allocated

SQL> select * from dba_segments where segment_name='SYS_LOB0000255030C00040$$';

OWNER                          SEGMENT_NAME                 PARTITION_NAME                 SEGMENT_TYPE       TABLESPACE_NAME                HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS RELATIVE_FNO BUFFER_
------------------------------ ----------------------------------------------------------- ------------------ ------------------------------ ----------- ------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- ------------ -------
APPLSYS                        SYS_LOB0000255030C00040$$                                   LOBSEGMENT         APPLSYSD                               289        84109 25815859200    3149650         32          40960     1048576           1 2147483645           50          1               1          289 DEFAULT

Error disappeared & size of the segment started growing

SQL> select bytes/1024/1024 from dba_segments where segment_name='SYS_LOB0000255030C00040$$';
BYTES/1024/1024
---------------
     24606.6406

SQL> /

BYTES/1024/1024
---------------
     24607.6563

SQL> select count(1) from dba_extents where segment_name='SYS_LOB0000255030C00040$$';

COUNT(1)
----------
        33

SQL> select * from dba_extents where segment_name='SYS_LOB0000255030C00040$$';

OWNER        SEGMENT_NAME                   PARTITION_NA SEGMENT_TYPE       TABLESPACE_NAME       EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ------------------------------ ------------ ------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                      0        289      84109      40960          5          289
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                      1        406     387084      40960          5          406
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                      2        410     449239      81920         10          410
.
.
.
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     26        429     304259 1132584960     138255          429
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     27        509          9 1698897920     207385          509
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     28        509     726494 2548326400     311075          509
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     29        522          9 3822469120     466610          522
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     30        445     414584 5733703680     699915          445
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     31        526          9 8600535040    1049870          526
APPLSYS      SYS_LOB0000255030C00040$$                   LOBSEGMENT         APPLSYSD                     32        527    1221914    1064960        130          527

33 rows selected.

33rd extend created with 1 MB & No more ORA-1691 error in alert log

To avoid this issue in future proactively you may use below sql or schedule in script..

#Output shows segments with NEXT_EXTENT over 1 GB & relative data file contiguos free space less than 2 GB

set line 900
set pages 200
col SEGMENT_NAME for a25
col FILE_NAME for a50
col TABLESPACE_NAME for a12
col OWNER for a12
select ds.owner,ds.segment_name,ds.segment_type,round(ds.NEXT_EXTENT/1024/1024,0) NEXT_EXTENT_MB ,ds.MAX_EXTENTS,ds.PCT_INCREASE,ds.tablespace_name,dd.FILE_NAME,dd.FILE_ID ,round(dd.bytes/1024/1024,0) SIZE_MB , round(sum(df.bytes)/1024/1024,0) FREE_MB
from (select segment_name,next_extent,header_file,tablespace_name,segment_type,owner,max_extents,PCT_INCREASE from dba_segments where round(NEXT_EXTENT/1024/1024,0) > 1024 ) ds, dba_data_files dd , dba_free_space df
where ds.header_file=dd.file_id
and dd.file_id=df.file_id
having sum(df.bytes)/1024/1024 - 2048 < round(ds.NEXT_EXTENT/1024/1024,0)
group by ds.owner,ds.segment_name,ds.segment_type,round(ds.NEXT_EXTENT/1024/1024,0) , ds.MAX_EXTENTS ,ds.PCT_INCREASE, ds.tablespace_name,dd.FILE_NAME,dd.FILE_ID ,round(dd.bytes/1024/1024,0) ;

OWNER SEGMENT_NAME SEGMENT_TYPE NEXT_EXTENT_MB MAX_EXTENTS PCT_INCREASE TABLESPACE_N FILE_NAME FILE_ID SIZE_MB FREE_MB

------------ ------------------------- ------------------ -------------- ----------- ------------ ------------ -------------------------------------------------- ---------- ---------- ----------

APPLSYS WF_NOTIFICATION_OUT TABLE 1080 2147483645 50 APPLSYSD /u02/oracle/uatcrmdata/applsysd02.dbf 289 8192 2626

APPLSYS SYS_LOB0000255030C00040$$ LOBSEGMENT 1620 505 50 APPLSYSD /u02/oracle/uatcrmdata/applsysd02.dbf 289 8192 2626

# Output shows datafile free space per datafile to verify if space need to be increase or new datafile to be added or NEXT_EXTENT to be reduced

select dd.tablespace_name,dd.FILE_NAME,df.FILE_ID ,round((dd.bytes/1024/1024),0) SIZE_MB , round(sum(df.bytes)/1024/1024,0) FREE_MB
from dba_data_files dd , dba_free_space df , (select distinct tablespace_name,next_extent from dba_segments where round(NEXT_EXTENT/1024/1024,0) > 1024) ds
where ds.tablespace_name=dd.tablespace_name
and dd.file_id=df.file_id
having sum(df.bytes)/1024/1024 - 2048 < round(ds.NEXT_EXTENT/1024/1024,0)
group by dd.tablespace_name,dd.FILE_NAME,df.FILE_ID ,round((dd.bytes/1024/1024),0) ,round(ds.NEXT_EXTENT/1024/1024,0)
order by 1,5;

TABLESPACE_N FILE_NAME FILE_ID SIZE_MB FREE_MB

------------ -------------------------------------------------- ---------- ---------- ----------

APPLSYSD /u02/oracle/uatcrmdata/applsysd04.dbf 420 7144 2001

APPLSYSD /u02/oracle/uatcrmdata/applsysd02.dbf 289 8192 2626

APPLSYSD /u01/oracle/uatcrmdata/applsysd03.dbf 406 8992 3222

APPLSYSD /u02/oracle/uatcrmdata/applsysd01.dbf 21 8196 3613

NEXT_EXTENT is greater than 1 GB & Contiguous free space of the datafile in which segment is located may not accommodate next few extents which will lead to allocation of next extent in new datafile .Please verify if other datafiles in the same tablespace has sufficient contiguous space available . If not then add a datafile with size equal to or greater than NEXT_EXTENT else reduce NEXT_EXTENT size using .. alter table <owner>.<table_name> <modify lob> <(lob column_name)> (STORAGE (NEXT <size>));

Run below to fix issue.

alter table APPLSYS.FND_LOBS modify lob (FILE_DATA) (storage (maxextents 1000 next 100M));

Hope this post will help to resolve ORA-1691: unable to extend lobsegment issue....

Sajid's Oracle DBA Blog