ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2



**********************************************************************
Following errors written to the Alert log file. Please verify
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2
ORA-1652: unable to extend temp segment by 128 in tablespace                 TEMP2

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

Solution
you would just add disk to TEMP to avoid the ORA-1652 error, but you can also wait for SMON to clean-up the TEMP segment.

This basically means that free space from other instances is being requested, and typically signifies that there is instance contention.  Instance contention within the temporary space can make the instance take more time to process.

In sever cases, a slowdown may occur, in which you might want try one of the following work-arounds:
  1. Increase size of the temp tablespace
  2. Increase sort_area_size and/or pga_aggregate_target

Identify temporary datafile details :

SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;

FILE_NAME                                     TABLESPACE_NAME

/apps/db/apps_st/data/temp01.dbf    TEMP1
/apps/db/apps_st/data/temp02.dbf         TEMP2

SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM v$sort_segment;

TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS

TEMP2 524160 3456 520704
TEMP 0 0 0
TEMP1 524160 512 523648



Add additional temp file in temporary table-space
SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE ‘/u01/app/oracle/oradata/ iamdb/temp03.dbf’ size 1024m;

Check if there is any space available in temporary table-space (segment)

SELECT   A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total -SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
(
SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM     v$tablespace B, v$tempfile C
WHERE    B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

TABLESPACE   MB_TOTAL MB_USED   MB_FREE

TEMP1             4096                   6.625           4089.375
TEMP2             4096                     43             4053


Monitoring Temporary Tablespaces and Sorting
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from $TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED BYTES_FREE

TEMP1                          4294967296            0
TEMP2                          4294967296            0

Root Cause Analysis

Identify temp segment usages per session


Temp segment usage per session.

SQL > SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, -SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS




Identify temp segment usages per statement

 Temp segment usage per statement.

SQL> SELECT  S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM    v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE   T.session_addr = S.saddr
AND     T.sqladdr = Q.address
AND     T.tablespace = TBS.tablespace_name
ORDER BY mb_used;

SID_SERIAL USERNAME HASH_VALUE SQL_TEXT MB_USED TABLESPACE



SQL > select * from v$sort_segment;

TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS RELATIVE_FNO

TEMP2 0 0 128 38 4095 524160 38 4864 4057 519296 0 4980625 0 0 4095 524160 4095 524160 4059 519552 0
TEMP 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1024
TEMP1 0 0 16 37 32760 524160 37 592 32723 523568 0 26657530 0 0 32760 524160 31266 500256 31191 499056 0


Each tablespace has one or more datafiles that it uses to store data.

The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.

To find out if the actual limit is 32gb, run the following:

sql > select value from v$parameter where name = 'db_block_size';

Compare the result you get with the first column below, and that will indicate what your max datafile size is.

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).


Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.

If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:


SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> DROP TABLESPACE temp;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP
  2  TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
  3  AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.



SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

Create & drop TEMP TABLE-space in 11gR1

While doing this activity, existing temporary tablespace may have existing live sessions, 
due to same oracle won’t let us to drop existing temporary tablespace. Resulting,
 we need to kill existing session before dropping temporary tablespace.

Following query will give you tablespace name and datafile name along with path of that 
data file.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/prod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

Following query will help you to alter database for default temporary tablespace. 
( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old 
temporary tablespace ( i.e. TEMP ) and kill them.

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

Provide above inputs to following query, and kill session’s.

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';

Example :-

SQL> alter system kill session '59,57391';

Now, we can drop old temporary tablespace without any trouble with following:

SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;

Contents and datafiles are deleted successfully.

If you wish to continue with old temporary tablespace name, i.e. 
‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.

ORA-20005: object statistics are locked (stattype = ALL)

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

ERROR:

FNDGSCST module: Gather Schema Statistics
+---------------------------------------------------------------------------+

Current system time is 25-NOV-2017 21:00:22

+---------------------------------------------------------------------------+

**Starts**25-NOV-2017 21:00:22
ORACLE error 20005 in FDPSTP

Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "APPS.FND_STATS", line 780
ORA-06512: at line 1



APPLIES TO:

Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
10g or 11g Database with Apps 11i or R12


SYMPTOMS

Gathering statistics using concurrent jobs "Gather Schema Statistics / Gather Table Statistics" or running FND_STATS fails with "ORA-20005: object statistics are locked".

Example 1:

Error #1: ERROR: While GATHER_TABLE_STATS:
object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***

Example 2:

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP stats on table GAT_REQ_QTBL is locked

CAUSE

This can happen with Advance Queue tables. In 10g, if a queue is created or recreated during the upgrade,
automatic statistics gather is locked (disabled) on these queue.

The following statement can be used to check the tables which have statistics locked:
[oracle@sujeet ~]$ sqlplus / as sysdba


SQL> select owner, table_name, stattype_locked
    from dba_tab_statistics
    where stattype_locked is not null;



SOLUTION

Unlock statistics gathering on those queues running the commands below. To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner');

OR

To unlock individual tables (need to run for all tables individually):

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:

SQL> exec dbms_stats.unlock_schema_stats ('AR');
SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');


SQL> exec dbms_stats.unlock_schema_stats('SYSTEM');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.unlock_schema_stats('APPLSYS');

PL/SQL procedure successfully completed.


SQL> exec dbms_stats.unlock_schema_stats('SYS');

PL/SQL procedure successfully completed.


Internal Server Error

Intermittent Login issue R12.1 - 500 Internal Server Error

Problem Description:

Users are unable to get application login page.
Users are frequently getting below error while accessing the EBS application R12.1.3 URL.

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, ohs_admin@acs.net and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.

Cause:

This issue occurred due to low oacore heap size.

Solution:

Increase the oacore heap size and restart application services.

Change the parameters in $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
of both form nodes as shown below:

From:
-Xmx512M -Xms128M -XX:MaxPermSize=160M

To:
-Xmx1024M -Xms128M -XX:MaxPermSize=160M


Reference:

JVM: Guidelines to setup the Java Virtual Machine in Apps E-Business Suite 11i and R12 (Doc ID 362851.1)


How to run Gather Statistics for a Schema in R12.1.3

How to run Gather Schema Statistics concurrent program

1. Log on to Oracle Applications with
    Responsibility = System Administrator

2. Submit Request Window
    Navigate to: Concurrent > Requests

3. Query for the Gather Schema Statistics

4. Enter the appropriate parameters. This can be run for specific schemas by specifying the schema name or entering  ‘ALL’  to gather statistics for every schema in the database

5. Submit the Gather Schema Statistics program


Parameters :

Schema Name:
  Schema for which statistics are to be gathered. Specify ALL for all Oracle Applications schemas

Percent:
  The sampling percentage. If left blank, the default value of 10 is used. The valid range is from 0 to 100

Degree: 
 The degree of parallelism to be used for gathering statistics. If a Degree is not provided, it defaults to the minimum of parallel_max_servers and cpu_count.

Backup Flag:  NO BACKUP is used,
 then the GATHER_SCHEMA_STATS procedure will not backup the current statistics. This way the GATHER_SCHEMA_STATS procedure will run faster.

Restart Request ID: 
 In the case where the Gather Schema Statistics run fails due to whatever reasons, the concurrent request can be re-submitted and it will pick up where the failed run left off, if you provide the concurrent request_id of the failed run.
History Mode:  Last Run – History records for each object are maintained only for the last gather statistics run. Each subsequent run will overwrite the previous history record for the object. This is the default behavior

Gather Options:  
GATHER: All tables and indexes of the schema schema name are selected for stats gathering. This is the default

Gather Options This parameter specifies how objects are selected for statistics gathering.

GATHER : All tables and indexes of the schema schemaname are selected for stats gathering. This is the default.

GATHER AUTO : Tables of the schema schemaname for which the percentage of modifications has exceeded modpercent are selected for statistics gathering.
Indexes of these tables are selected by default. Table monitoring needs to be enabled before using this option.

GATHER EMPTY : Statistics are gathered only for tables and indexes that are missing statistics.

LIST AUTO : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistic gathering,
if the GATHER AUTO option is used.

LIST EMPTY : This option does not gather statistics. It only provides a listing of all the tables that will be selected for statistics gathering,
if the GATHER EMPTY option is used.

Modifications Threshold:  Applicable only to GATHER AUTO and LIST AUTO Options

Invalidate Dependent Cursors:  This flag indicates whether cursors dependent on the table being analyzed should be invalidated or not. By default, dependent cursors are invalidated.

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:- IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1) SYMPTOMS:- Yo...