Question Re: Invalid object in Technology Q&A

Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 09:48:25 GMT

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.

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 09:49:06 GMT

Oracle 10.2.0.2 patch 17 Win 32.

Regards.

Re: Invalid object

stefan_koehler — Sun, 30 Mar 2008 10:02:45 GMT

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

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 10:04:04 GMT

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

without success...

Regards.

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 10:08:11 GMT

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.

Re: Invalid object

stefan_koehler — Sun, 30 Mar 2008 10:14:45 GMT

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

Re: Invalid object

Former Member — Sun, 30 Mar 2008 10:16:46 GMT

Try

sqlplus '/as sysdba'

startup or startup upgrade

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

Re: Invalid object

stefan_koehler — Sun, 30 Mar 2008 10:19:28 GMT

Hello Shaji,

do you know what you are suggesting?

Regards

Stefan

Re: Invalid object

Former Member — Sun, 30 Mar 2008 10:32:17 GMT

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.

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 11:18:37 GMT

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

already use, no success.

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 11:47:48 GMT

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

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

Regards.

Re: Invalid object

Former Member — Sun, 30 Mar 2008 11:59:02 GMT

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

Note 1139396 - Temporary database objects in BW 7.X

Re: Invalid object

stefan_koehler — Sun, 30 Mar 2008 12:04:42 GMT

Hello Ganimede,

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

Do the objects still persist?

Regards

Stefan

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 12:49:29 GMT

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.

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 12:50:31 GMT

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.

Re: Invalid object

stefan_koehler — Sun, 30 Mar 2008 12:53:21 GMT

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

Re: Invalid object

Ganimede-Dignan — Sun, 30 Mar 2008 20:06:13 GMT

>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.

Re: Invalid object

Ganimede-Dignan — Wed, 22 Oct 2008 20:23:42 GMT

not yest solved.

Re: Invalid object

Former Member — Wed, 29 Oct 2008 15:16:32 GMT

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

Re: Invalid object

Ganimede-Dignan — Thu, 30 Oct 2008 08:23:01 GMT

Already used without success.

regards.