DB stats fail - ORA-20000: this index object is being online built or rebuilt

[Former Member]

Hi ,

We executed rebuild index for a big table, Index size was 32 GB.

Rebuild index was running almost 10 hrs but did not complete and was showing row lock in in ST04 So we canceled rebuild index job in SM37.

Now every time we are running update stats on that table, It is failing with below error -

BR0301E SQL error -20000 at location stats_tab_collect-69, SQL statement:

'EXECUTE stmt_1d'

'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPR3"', TABNAME => '"DBTABLOG"', ESTIMATE_PERCENT => 0.001, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', DEGREE => NULL, CASCADE => TRUE, NO_INVALIDATE => FALSE); END;'

ORA-20000: this index object "SAPR3"."DBTABLOG~0"  is being online built or rebuilt

ORA-06512: at "SYS.DBMS_STATS", line 24301

ORA-06512: at "SYS.DBMS_STATS", line 24352

ORA-06512: at line 1

BR0886E Collecting statistics failed for table SAPR3.DBTABLOG

BR0280I BRCONNECT time stamp: 2015-03-21 13.06.18

BR0879I Statistics checked for 0 tables

BR0878I Number of tables selected to collect statistics after check: 0

BR0880I Statistics collected for 0/0 tables/indexes

BR1308E Collection of statistics failed for 1/0 tables/indexes

BR0806I End of BRCONNECT processing: ceqcqkvr.sta 2015-03-21 13.06.18

BR0280I BRCONNECT time stamp: 2015-03-21 13.06.18

BR0804I BRCONNECT terminated with errors

We checked there is no process or job running for index rebuild but it is still showing index is under rebuild.

Please suggest what could be reason of this and how can we solve it.

Regards,

Shivam

[former_member182967]

Hello Shivam,

Please check that if note 682926 - Composite SAP note: Problems with "create/rebuild index" can help you or not.

Regards,

Ning Tong

[JamesZ]

Hi Shivam,

Please try solution 1 in SAP note 682926, please try multiple times of the clean script.

Best regards,
James

[Former Member]

Hi James,

Checked note, Can you let me know how to execute these OSS notes command -

1. set serveroutput on

begin

  if dbms_repair.online_index_clean() then

    dbms_output.put_line('All fragments cleaned');

  else

    dbms_output.put_line('DML Locks on some tables.');

    dbms_output.put_line('Not all fragments cleaned. ');

    dbms_output.put_line('Try again later');

  end if;

end;

Should we put these lines in text file then save as .SQL and execute or what is the process.

And Will this process be fast or it will execute long time and can impact running system.

Some more information -

SQL> select NAME, OBJ# from obj$ where NAME = 'DBTABLOG~0';

NAME                                 OBJ#

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

DBTABLOG~0                        1622362

SQL> select flags from ind$ where obj#='1622362';

     FLAGS

----------

      2594

Please suggest.

Regards,

Shivam

[Former Member]

Can we also execute this script only for our table on place of clearing all flags.

[JamesZ]

Hi Shivam,

You can copy to sqlplus line by line.

Or you can use .sql script and in sqlpus using @script to execute it.

Best regards,
James

[Former Member]

Hi James,

You think, this script will take time to complete.

Can we modify it to mention specific index name to execute it quickly.

Regards,

Shivam

[JamesZ]

Hi Shivam,

As per link below:

DBMS_REPAIR

So we can specify an index to do that.q

Best regards,
James

[Former Member]

Hi,

Executed Procedure from note 682926 but still have same issue.

Regards,

Shivam

[JamesZ]

Hi Shivam,

In that case, you'd better try

1. dbms_repair.online_index_clean()

without any parameters, so all the related will be cleaned by default.

Best regards,
James

[Former Member]

Thanks James,

Tried this but did not work, Will restart database to see if this resolves issue.

Regards,

Shivam

[JamesZ]

Hi Shivam,

Restart database may help. The shutting down db may take time, so please do not interrupt the
shutting down process. Do let us know the result afterwards.

Best regards,
James

[Former Member]

Hi James,

DB restart helped and resolved the problem.

Thanks.

[stefan_koehler]

Hi Shivam,

unfortunately you have not provided the object state, but you can cross-check this on your own.

SQL> select flags from ind$ where obj#= <OBJECT_ID>;

Failed index rebuilds are usually cleared up by SMON every 60 minutes, but it only works if there are no on-going transaction against the corresponding base table / objects. In your case table DBTABLOG is affected, which can be written permanently (depends on detailed SAP settings).

However you can also clear up this stuff manually with help of PL/SQL procedure DBMS_REPAIR.ONLINE_INDEX_CLEAN.

Regards

Stefan

[Former Member]

Hi Stefan,

Thank you for your reply, Could not mention object state as was not sure on how to find it.

I am running Update stats on table DBSTATLOG and its failing saying index under built.

We have row deletion activity going on this table, So this might be reason of not clearing index by SMON.

Can you let me how can we execute this SQL Procedure and if these is any impact of running this procedure on running system.

Also please suggest how can I find object it for index.

SQL> select flags from ind$ where obj#='DBTABLOG~0';

select flags from ind$ where obj#='DBTABLOG~0'

                                  *

ERROR at line 1:

ORA-01722: invalid number

Regards,

Shivam

[Former Member]

Hello Shivam,

To get the obj# you are after:

select NAME, OBJ# from obj$ where NAME = 'DBTABLOG~0' ;

Equally, if you had tried "alter index SAPSR3."DBTABLOG~0" rebuild online parallel 4 ;" you would have got an error that would also have shown you the obj#.

Kind Regards,

Amerjit