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.