Gather Schema Statistics "program error with Locks and Duplicate columns"

Oracle E-Business Suite Release 12 uses cost–based optimization in order to choose the most efficient execution plan for SQL statements.Gather Schema Statistics is  the favourite one among Apps DBA's and developers. Gather Schema Statistics program generates statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

On Oracle Applications 12.1.3,

Gather schema statistics (GSS) is completing with error in our instances. 

When attempting to run Gather Schema Statistics request, the following error occurs:

Error

*******

**Starts**13-APR-2019 23:59:45

**Ends**14-APR-2019 05:30:40

ORA-0000: normal, successful completion

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

Start of log messages from FND_FILE

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

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 24 internal_flag= NOBACKUP

stats on table AQ$_WF_CONTROL_P is locked

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

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

End of log messages from FND_FILE

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

Successfully resubmitted concurrent program FNDGSCST with request ID  4981147 to start at 21-APR-2019 00:00:00 (ROUTINE=AFPSRS)

Solution

***********

1. Please follow Doc ID 375351.1 to unlock the tables:

                AQ$_WF_CONTROL_P

                FND_CP_GSM_IPC_AQTBL

                FND_SOA_JMS_IN

                FND_SOA_JMS_OUT

Do the following select from dba_tab_stats to show all tables with locked statistics on them:

    SQL> select owner, table_name, stattype_locked from dba_tab_statistics 
           where stattype_locked is not null 
           and owner not in ('SYS');          

From DB node run below to unlock tables

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','AQ$_WF_CONTROL_P');

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_CP_GSM_IPC_AQTBL');

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_IN');

        SQL>exec dbms_stats.unlock_table_stats('APPLSYS','FND_SOA_JMS_OUT');

2. Please perform the following action plan from Doc ID 781813.1 :

   Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

   Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

   SQL> create table FND_HISTOGRAM_COLS_BKP as select * from FND_HISTOGRAM_COLS;

 -- Identify duplicate rows

  SQL>select table_name, column_name, count(*) from FND_HISTOGRAM_COLS

             group by table_name, column_name 

             having count(*) > 1;

 -- Use above results on the following SQL to delete duplicates

 SQL>delete from FND_HISTOGRAM_COLS

           where table_name = '&TABLE_NAME'

           and column_name = '&COLUMN_NAME'

           and rownum=1;

 -- Use following SQL to delete obsoleted rows

  SQL>delete from FND_HISTOGRAM_COLS

            where (table_name, column_name) in

                  (

            select hc.table_name, hc.column_name

             from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

            where hc.table_name ='&TABLE_NAME'

            and hc.table_name= tc.table_name (+)

            and hc.column_name = tc.column_name (+)

            and tc.column_name is null

                  );

   SQL>commit;

3. Run "Gather Schema Statistics" program again.

    Fix worked successfully for our env.

[Solved]How to open o123.out file in excel format

An *.out file is a compiled executable file created by various source code compilers in Unix-like operating systems, such as Linux and AIX. *.out files are most commonly seen as a.out files. The name stands for "assembler output". *.out files may also be used to collect data that has been dumped from a program to standard output.

Our goal is to help you understand what a file with a *.out suffix is and how to open it.

We can follow below steps to open *.out files:

Step 1: First of all change the o123.out file extension to o123.xml extension.

Step 2: Now change the First line in o123.xml extension file.

Example: <?xml version="1.0" encoding="UTF-8"?>    replace from

                  <?xml version="1.0" encoding="WINDOWS-1252"?>

Step 3: Load this o123.xml file in RTF Template if getting any Out of Memory error in RTF Template then doing the required changes the Java Heap size in RTF Template( Now change –Xmx256M to -Xmx1500M).

Go To=> Add-Ins=>Tools=>Options

Click on OK.

Step 4: Again load this o123.xml file in RTF Template.

Step 5: View output in as you want.

How to Create a Custom Concurrent Manager

Adding Concurrent Manager is a crucial decision. Lots of things needs to be examined before adding new Concurrent Manager. One of the major area is resources.If you have less number of concurrent managers then overall concurrent processing gets delayed with a lot many requests having the status pending.On the other hand if you have too many Concurrent Managers then there will be a heavy load on your Operating System.So before adding any new Concurrent Managers a proper analysis needs to be done.

General Steps for creating new custom concurrent manager in Oracle App R12

1. Navigate to Concurrent / Manager / Define.

2. Manager Field: Custom Manager.

3. Short Name: CUSTOM.

4. Type: Concurrent Manager.

5. Program Library: FNDLIBR.

6. Enter desired cache.

7. Work Shifts: Standard.

8. Enter number of Processes.

9. Provide Specialization Rules(you can include or exclude program,id,user,types or combination).

10. Save.

11. Navigate to Concurrent / Manager / Administer.

12. Activate the Custom Manager.

Why need to create a custom concurrent manager?

It is avoid to long running concurrent request during business hours.this improves overall performance of the system.create a Specialized Manager to process the specific Long Running requests. assign the Specialized Manager with a work shift with Off peak time.

Assume, the Users submit the Concurrent Requests during peak time, then it will get assigned to the Specialized Manager.But Specialized Manager will not process it during peak time.

You can define as any concurrent managers as you want. For defining Concurrent Manager we need to login to the e-business suite as a 'SYSADMIN' and need to choose the System Administrator responsibility.

Follow below steps for create custom concurrent manager 

1. Navigate to Concurrent==>Manager==>Define

2. Manager Field: XXX Heavy Report Manager (Custom Manager).

3. Short Name: CUSTOM.

4. Type: Concurrent Manager.

5. Program Library: FNDLIBR.

6. Enter desired cache.

Go To Work Shifts: Choose Standard.

7. Enter number of Processes: 5

8. Sleep Seconds: 30

Define Request Type

9. Navigate to Concurrent==>Program==>Type

10. Provide Specialization Rules:

11. Save

12. Exclude the Request Type from Standard Manager’s Specialization Rules.

13. Navigate to Concurrent==>Manager==>Administer.

14. Activate the Custom Manager.