Invalid object

[ganimede_dignan]

Hi,

in a BI system just copied I found some invalid object, I post the output of : SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

/BI0/0400027280

/BI0/0400003010

/BI0/0400018987

/BI0/0400002996

/BI0/0400007708

/BI0/0400031992

/BI0/0400027879

/BI0/0400027880

/BI0/0400027128

/BI0/0400027852

/BI0/0400027853

/BI0/0400027906

/BI0/0400027907

/BI0/0400029629

All obj are PROCEDURE and are in ABAP schema... I try with: alter <object_type> schemaname.<object_name> compile;

withour success ... Oracle see: procedure not found.

Any idea ?

Regards.

Ganimede Dignan.

[ganimede_dignan]

Oracle 10.2.0.2 patch 17 Win 32.

Regards.

[stefan_koehler]

Hello Ganimede,

are you sure that the /BI0/ objects are pl/sql procedures?

> SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE STATUS = 'INVALID';

I think that these objects are temporary BW tables.

If these objects are TABLES .. please take a look at sapnote #648203 and run the report SAP_DROP_TMPTABLES to fix/delete the INVALID objects.

Sapnote #449891 is describing the temp objects and its prefixes.

Regards

Stefan

[ganimede_dignan]

I've already run @?/rdbms/admin/utlrp.sql

without success...

Regards.

[ganimede_dignan]

OBJECT_NAME OBJECT_TYPE

-

---

-

---

/BI0/0400027280 PROCEDURE

/BI0/0400003010 PROCEDURE

/BI0/0400018987 PROCEDURE

/BI0/0400002996 PROCEDURE

/BI0/0400007708 PROCEDURE

/BI0/0400031992 PROCEDURE

/BI0/0400027879 PROCEDURE

/BI0/0400027880 PROCEDURE

/BI0/0400027128 PROCEDURE

/BI0/0400027852 PROCEDURE

/BI0/0400027853 PROCEDURE

OBJECT_NAME OBJECT_TYPE

-

---

-

---

/BI0/0400027906 PROCEDURE

/BI0/0400027907 PROCEDURE

/BI0/0400029629 PROCEDURE

14 rows selected.

[stefan_koehler]

Hello Ganimede,

ok then run the report SAP_DROP_TMPTABLES (from sapnote #449891) and select only the "/BI0/04" component.

After that run your query on ALL_OBJECTS again, and take a look if these objects still exist.

Regards

Stefan

[Former Member]

Try

sqlplus '/as sysdba'

startup or startup upgrade

run catalog.sql and catproc.sql and utlrp.sql

[stefan_koehler]

Hello Shaji,

do you know what you are suggesting?

Regards

Stefan

[Former Member]

Stefan, i had a diff assumption.

Pls check Note 648203 - Database objects with INVALID status

Before dropping them, find out what type of objects they are, and see if you can scrutinize them to determine why they are not valid.

if they are views, they can be recreated; if such attempts fail, the views may be referencing non-existent rows in the tables they refer to

if they are procedures, they can be recompiled. If they fail to recompile, the code for the procedures should be reviewed.

One way to change the status from invalid to valid is to use the ‘ALTER VIEW’ statement with the COMPILE option.

The view should be compiled if its status is invalid.

[ganimede_dignan]

>run catalog.sql and catproc.sql and utlrp.sql

already use, no success.

[ganimede_dignan]

>are you sure that the /BI0/ objects are pl/sql procedures?

As described in 449891 note /BIO/04é are sp.

Regards.

[Former Member]

Am not sure of which BW version yours is, but check for the cautions in

Note 1139396 - Temporary database objects in BW 7.X

[stefan_koehler]

Hello Ganimede,

have you run the report SAP_DROP_TMPTABLES with the /BI0/04 checkbox?

Do the objects still persist?

Regards

Stefan

[ganimede_dignan]

Hi,

>have you run the report SAP_DROP_TMPTABLES with the /BI0/04 checkbox?

excuse me but ... with SAP_DROP_TMPTABLES I can't select 04 object.

BI = 7.0

Regards.

[ganimede_dignan]

BI 7.0

I run SAP_DROP_TMPTABLES now (no user, no jobs, no chains running) and delete all temp object.

Is it OK ?

Regards.

[stefan_koehler]

Hello,

if you run the select statement on ALL_OBJECTS again and it doesn't return any invalid objects... then it is o.k.

> SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE STATUS = 'INVALID';

Regards

Stefan

[ganimede_dignan]

>if you run the select statement on ALL_OBJECTS again and it doesn't return any invalid objects... then it is o.k.

No... same objs.

Regards.

[ganimede_dignan]

not yest solved.

[audunlea_hansen]

Try to log in to the database as the database user that owns those invalid objects.

Do an 'alter procedure <procedure_name> compile;'

If it compiles with warnings/errors, type 'show err' to get more information of what's the compilation trouble.

Regards

Audun

[ganimede_dignan]

Already used without success.

regards.

[ganimede_dignan]

Not Solved.

bye.

[ganimede_dignan]

I've just upgrade to 10.2.0.4 with last patches... but this objs still invalid.

Regards.

[ganimede_dignan]

>Try to log in to the database as the database user that owns those invalid objects.

>Do an 'alter procedure <procedure_name> compile;'

>

>If it compiles with warnings/errors, type 'show err' to get more information of what's the compilation trouble.

>

>Regards

>Audun

Hi,

I try with on obecjt:

sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 21 15:14:36 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show error

No errors.

SQL> alter procedure SAPNBU."/BI0/0400018987" compile;

Warning: Procedure altered with compilation errors.

SQL> show err

Errors for PROCEDURE SAPNBU."/BI0/0400018987":

LINE/COL ERROR

----

---

-

---

1/3081 PL/SQL: SQL Statement ignored

1/3093 PL/SQL: ORA-00942: table or view does not exist

1/3241 PL/SQL: SQL Statement ignored

1/4102 PL/SQL: ORA-00942: table or view does not exist

SQL>

Have you got any idea ?

Regards.

[Nik3]

I solved my problem by deleting the old procedures. Now everything is fine.