×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Invalid Schemas

Invalid Schemas

Invalid Schemas

(OP)
I have various invalid scemas after applying a security patch .. i've been googling different cmds to re compile them, but not getting anywhere ..

I have tried running utlrp.sql but doesn't fix them and tried recompiling them individually .. but keep getting syntax errors

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
DBMS_REPCAT_AUTH
MGMT_BSLN_INTERNAL
MGMT_BSLN

Please assist to rectify - thanks.

RE: Invalid Schemas

What errros are you getting when recompiling these manually


In order to understand recursion, you must first understand recursion.

RE: Invalid Schemas

(OP)
Well i'm not sure i'm using the correct syntax ..

SQL> ALTER "PACKAGE BODY" SCHEMENAME.DBMS_REPCAT_AUTH COMPILE;
ALTER "PACKAGE BODY" SCHEMENAME.DBMS_REPCAT_AUTH COMPILE
*
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> ALTER PACKAGE BODY SCHEMENAME.DBMS_REPCAT_AUTH COMPILE;
ALTER PACKAGE BODY SCHEMENAME.DBMS_REPCAT_AUTH COMPILE
*
ERROR at line 1:
ORA-00922: missing or invalid option

RE: Invalid Schemas

$ CHAS:JAVA> sqlplus system/my_system_password

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Oct 17 15:08:30 2012

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning and Data Mining options

SQL> alter package DBMS_REPCAT_AUTH COMPILE;

Package altered.

SQL>


In order to understand recursion, you must first understand recursion.

RE: Invalid Schemas

(OP)
Any idea whats happening now ?

SQL> alter package DBMS_REPCAT_AUTH COMPILE;

Package altered.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
---------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN_INTERNAL
MGMT_BSLN

SQL> alter package MGMT_BSLN COMPILE;
alter package MGMT_BSLN COMPILE
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN does not exist


SQL> alter package MGMT_BSLN_INTERNAL COMPILE;
alter package MGMT_BSLN_INTERNAL COMPILE
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN_INTERNAL does not exist


SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
---------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN_INTERNAL
MGMT_BSLN

SQL>

RE: Invalid Schemas

Who "owns" the MGMT* objects . Try connecting as their owner and re-doing the compile command


In order to understand recursion, you must first understand recursion.

RE: Invalid Schemas

(OP)
Thanks btw .. how do i find that out ? everything should be installed by the same user.

There does seem to be a discrepancy .. the original install was owned by a a user in a domain group and look the upgrade was done by a local account ..

-rwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 2560 Sep 25 15:40 PWDXXXX1.ORA
-rwxrwxrwa 1 Administrators SYSTEM 14467072 Oct 12 10:45 SNCFFT.ORA
-rwxrwxrwx 1 Administrators SYSTEM 5632 Sep 25 16:47 SPFILEFT.ORA
drwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 0 Sep 25 15:39 archive
-rwxrwxrwa 1 Administrators SYSTEM 106 Sep 25 17:05 core_ft_pid_3140_tid_x93C_2012_9_25_15_56_46.log
-rwxrwxrwa 1 Administrators SYSTEM 106 Oct 12 11:25 core_ft_pid_696_tid_x3F8_2012_9_25_17_6_43.log
-rwxrwxrwa 1 Administrators SYSTEM 106 Oct 12 11:56 core_ft_pid_872_tid_x9F8_2012_10_12_11_42_3.log
-rwxrwxrwa 1 Administrators SYSTEM 2048 Sep 25 17:05 hc_ft.dat
-rwxrwxrwx 1 Administrators SYSTEM 2048 Sep 25 15:40 hc_xxxx1.dat
-rwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 4524 Sep 25 15:56 initFT.ora
-rwxrwxrwx 1 Administrators PRIMEWATCH\Domain+Users 4524 Sep 25 15:56 initFT.ora.safe

RE: Invalid Schemas

SELECT OBJECT_NAME, owner FROM DBA_OBJECTS WHERE STATUS='INVALID'


In order to understand recursion, you must first understand recursion.

RE: Invalid Schemas

(OP)
SQL> SELECT OBJECT_NAME, owner FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------

OWNER
------------------------------
DBMS_REPCAT_AUTH
PUBLIC

DBMS_REPCAT_AUTH
SYSTEM

MGMT_BSLN
DBSNMP


OBJECT_NAME
--------------------------------------------------------------------------------

OWNER
------------------------------
MGMT_BSLN_INTERNAL
DBSNMP


SQL>

RE: Invalid Schemas

(OP)

hi have you anything else to try ?

Still have the same issue

thanks.

RE: Invalid Schemas

So for each invalid object you tried connecting to the database as the objects owner, then did an alter ... compile on it ?


In order to understand recursion, you must first understand recursion.

RE: Invalid Schemas

Try the following:

CODE

SELECT 'ALTER '||DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '||
        owner||'.'||object_name||' COMPILE'||DECODE(object_type,'PACKAGE BODY',' BODY;',';')
  FROM dba_objects 
 WHERE status = 'INVALID'; 

This should spit out your compilation statements.

RE: Invalid Schemas

(OP)
So i tried this yesterday and although it said they have been altered - still showing as invalid !

D:\oracle\Bundle\Patch17>sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 23 16:48:24 2012

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

SQL> conn sys/ehealth as sysdba;
Connected.
SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE PACKAGE;

Package altered.

SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE BODY;

Package body altered.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN
MGMT_BSLN_INTERNAL

SQL>

RE: Invalid Schemas

What object types are invalid? If they are package bodies and they have interdependencies, you are invalidating them by recompiling the packages. The package bodies are compiled against the other package specs. If you recompile an entire package (as you are doing), any package bodies that reference the package spec will be invalidated. Assuming this is the case, try this:

CODE

ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE;  -- TO RECOMPILE YOUR PACKAGE SPEC
ALTER PACKAGE MGMT_BSLN COMPILE BODY;    -- TO RECOMPILE THE BODY WITHOUT INVALIDATING ANYTHING ELSE
ALTER PACKAGE MGMT_BSLN_INTERNAL COMPILE BODY;  -- TO RECOMPILE THE BODY WITHOUT INVALIDATING ANYTHING ELSE
ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE BODY;  -- TO RECOMPILE THE BODY WITHOUT INVALIDATING ANYTHING ELSE 

Without full information, I cannot guarantee that this will fix your problem, but I am fairly certain it will.
This is one of the advantages of using packages instead of standalone procedures/functions - you can modify the logic without invalidating dependent objects (as long as you just recompile the body and not the spec).

RE: Invalid Schemas

(OP)
Nope afraid not - thanks for your help so far ..

SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE;

Package altered.

SQL> ALTER PACKAGE MGMT_BSLN COMPILE BODY;
ALTER PACKAGE MGMT_BSLN COMPILE BODY
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN does not exist


SQL> ALTER PACKAGE MGMT_BSLN_INTERNAL COMPILE BODY;
ALTER PACKAGE MGMT_BSLN_INTERNAL COMPILE BODY
*
ERROR at line 1:
ORA-04043: object MGMT_BSLN_INTERNAL does not exist


SQL> ALTER PACKAGE DBMS_REPCAT_AUTH COMPILE BODY;

Package body altered.

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS='INVALID';

OBJECT_NAME
--------------------------------------------------------------------------------

DBMS_REPCAT_AUTH
DBMS_REPCAT_AUTH
MGMT_BSLN
MGMT_BSLN_INTERNAL

SQL>

RE: Invalid Schemas

OK, so the two package bodies don't belong to the same schema you are using them. Use the fully-qualified form of schema.object_name and try it again. The objects will obviously stay invalid if you cannot get the compilation command to run successfully. Also, could you PLEASE include the object types with your query? You are giving us partial clues to your problem and it makes it rather difficult to diagnose your problem. Have you tried the original script I posted? That would give us the owner, type, and name - all of which are crucial elements.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close