UTL_RECOMP.RECOMP_SERIAL must be declared

Question

First off I'd like to specify that I am trying to do this upgrade remotely. I am receiving this error:

2015-04-24:11:59:42 [Error] myDB - Failed to Recompile 'DBTYPE' Database Objects: ExcSQL Failed: Oracle Error Encountered: ORA-06550: line 1, column 7: PLS-00201: identifier 'UTL_RECOMP.RECOMP_SERIAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored (6550) SQL Text: begin utl_recomp.recomp_serial('DBTYPE'); end;

2015-04-24:11:59:42 [Error] WorksObjects Execution Failed: DBTYPE - Process JAR Files Failed: Failed to Recompile 'DBTYPE' Database Objects:ExcSQL Failed: Oracle Error Encountered: ORA-06550: line 1, column 7: PLS-00201: identifier 'UTL_RECOMP.RECOMP_SERIAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored (6550) SQL Text: begin utl_recomp.recomp_serial('DBTYPE'); end;

2015-04-24:11:59:42 [Error] UPGD FAILED! : myDB - Upgrade Error - Worker Thread Process Error - DBTYPE - Process JAR Files Failed: Failed to Recompile 'DBTYPE' Database Objects:ExcSQL Failed: Oracle Error Encountered: ORA-06550: line 1, column 7: PLS-00201: identifier 'UTL_RECOMP.RECOMP_SERIAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored (6550) SQL Text: begin utl_recomp.recomp_serial('DBTYPE'); end;

when I try to do a recompile on invalid objects. The only thing I can find on the internet is that when recompiling, that I should be logged in as a sysdba, which I am currently doing.

"Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = " + MyHostName + ")(PORT = 1521))(CONNECT_DATA =" +
"(SERVICE_NAME = " + mySID + ")));DBA Privilege = SYSDBA;" +
"User Id = " + MyID + ";Password = " + MyPW;

My connection is working correctly cause I can run 3/4 of the upgrade process till I hit this point where I try and recompile this specific schema.

Conn = GetConn( MyID, MyPW );
if (DevelopSrvr)
{
   ExcSQL(Conn, "alter session set plsql_debug=true");
}
ExcSQL(Conn, "begin utl_recomp.recomp_serial('" + schema + "'); end;");

This program runs fine locally and I have checked the stack trace and all the variables are returning the expected values. Is there something I'm missing? Thanks in advance

Answer 1

Here are operational notes related to UTL_RECOMP package:

This package must be run using SQL*PLUS.

This package uses the job queue for parallel recompilation.

You must be connected AS SYSDBA to run this script.

This package expects the following packages to have been created with VALID status:

STANDARD (standard.sql)

DBMS_STANDARD (dbmsstdx.sql)

DBMS_JOB (dbmsjob.sql)

DBMS_RANDOM (dbmsrand.sql)

Answer 2

According to your output:

"UTL_RECOMP.RECOMP_SERIAL' must be declared ORA-06550: line 1, column 7: "

This message states that you are logged in but without sysdba privilege.

In your target database validate that:

1.) Remote login password file is set EXCLUSIVE

SQL> show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

2.) The username you are using to login is listed in v$pwfile_users catalog view (assume that you are connecting using "Z_TEST" database account as sysdba):

SQL> select username,sysdba from v$pwfile_users ;

USERNAME                       SYSDBA
------------------------------ -----
SYS                            TRUE
Z_TEST                         TRUE

The following example assumes "z_test/welcome1" account credentials and "db99" as target service name:

sqlplus z_test/welcome1@db99 as sysdba

SQL> set serveroutput on
SQL> begin
 2     utl_recomp.recomp_serial('Z_TEST');
 3  end;
 4  /

PL/SQL procedure successfully completed.

TIP: when connected as sysdba your username becomes "SYS". Verify that in your code. As example:

SQL> show user
USER is "SYS"