Zara,

It looks like the procedures can't recompile because the GLOGOWNER user
doesn't have rights to access the v$ views such as v$parameter (10/32),
v$session (100,21), as well as some of the DBA tables DBA_OBJECTS,
DBA_CONSTRAINTS, etc. I suspect that the role APP_OWNER does not have the
"SELECT ANY TABLE" privilege. You can check to see what pivileges APP_OWNER
has with the following SQL (run as SYSTEM)

SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='APP_OWNER';

And this should return the following

PRIVILEGE
----------------------------------------------------------------------------
CREATE PUBLIC SYNONYM
COMMENT ANY TABLE
ALTER SESSION
CREATE SESSION
CREATE DATABASE LINK
CREATE VIEW
DROP PUBLIC SYNONYM
CREATE TABLE
CREATE TYPE
CREATE ANY CONTEXT
CREATE SEQUENCE
CREATE TRIGGER
CREATE PROCEDURE
CREATE ANY SYNONYM
SELECT ANY TABLE

15 rows selected.

If as I suspect you don't have "SELECT ANY TABLE" you need to revisit
"create_glog_roles.sql", did you run this script & did you run it as SYSTEM
(not SYS)? You can rerun this script as system (which will drop and recreate
the roles) then manually grant the roles & any missing privileges back to
the users by following along with the grant statements in the
create_glog_users.sql script. This should help you avoid having to drop the
users & data and recreate everything. For example the glogowner grants in
create_glog_users.sql are

grant app_owner to glogowner;
grant app_user to glogowner;
grant app_user_select to glogowner with admin option;
grant create any table, alter any trigger, alter any table, select any
table, create any sequence, create any trigger, analyze any,
create user, drop user, create procedure,
create public synonym, drop public synonym, CREATE MATERIALIZED VIEW to
glogowner;
grant execute on dbms_rls to glogowner;
GRANT aq_administrator_role TO glogowner;
GRANT EXECUTE ON DBMS_AQ TO glogowner;
GRANT EXECUTE ON DBMS_AQADM TO glogowner;
GRANT EXECUTE ON DBMS_AQIN TO glogowner;
GRANT execute on dbms_pipe TO glogowner;

And so on for each user...

Then go back and try running recompile_invalid_objects.sql

-Alan

On 12/14/06 1:00 AM, "idqpbi" <idqpbi (AT) gmail (DOT) com> wrote:

> Hi,
>
> Anybody experienced this before? This is the last SQL script to run
> before I could actually start to verify my imported data. All of the
> previous scripts were OK, even the import after I took Stephen's advice.
> Sigh...... :-(
> Maybe Alan or Stephen could help again. :-)
>
> Here is what I have in the terminal window: (sorry it's kind of long
> since I ran the same scripts twice hoping to fix it)
> ------------------------------------------------------------------------------
> ---------------------------------------------------
>
> _*1st Run*_
>
> SQL> @recompile_invalid_objects.sql
>
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
>
>
> List of first 50 Invalid objects after recompile.
> ================================================
> DBHEALTH PACKAGE BODY
> CREATE_TABLE PACKAGE BODY
> PKG_PURGE PACKAGE BODY
>
> Errors for PACKAGE BODY CREATE_TABLE:
> 55/23 PL/SQL: ORA-00942: table or view does not exist
>
> 55/7 PL/SQL: SQL Statement ignored
>
>
> Errors for PACKAGE BODY DBHEALTH:
> 10/14 PL/SQL: SQL Statement ignored
>
> 10/32 PL/SQL: ORA-00942: table or view does not exist
>
> 100/21 PL/SQL: ORA-00942: table or view does not exist
>
> 106/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 120/20 PL/SQL: SQL Statement ignored
>
> 120/77 PL/SQL: ORA-00942: table or view does not exist
>
> 53/14 PL/SQL: SQL Statement ignored
>
> 53/84 PL/SQL: ORA-00942: table or view does not exist
>
> 57/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 64/14 PL/SQL: SQL Statement ignored
>
> 64/56 PL/SQL: ORA-00942: table or view does not exist
>
> 68/40 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 75/14 PL/SQL: SQL Statement ignored
>
> 75/48 PL/SQL: ORA-00942: table or view does not exist
>
> 79/44 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 98/14 PL/SQL: SQL Statement ignored
>
>
> Errors for PACKAGE BODY PKG_PURGE:
> 1339/4 PL/SQL: SQL Statement ignored
>
> 1339/43 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/29 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/5 PL/SQL: SQL Statement ignored
>
> 1913/5 PL/SQL: SQL Statement ignored
>
> 1913/61 PL/SQL: ORA-00942: table or view does not exist
>
> 1921/8 PLS-00364: loop index variable 'JOB_PARAM_REC' use is
> invalid
>
> 1938/7 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
> 1950/15 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
>
>
> Invalid objects before Recompile....
>
> 45
>
> *Invalid objects after Recompile....
>
> 3 *
>
>
>
> Some objects have compiled successfully.
>
>
> Please re-run recompile_invalid_objects to compile the rest of invalid
> objects.
>
> _*2nd Run*_
>
> SQL> @recompile_invalid_objects.sql
>
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
> ORA-24344: success with compilation error
>
>
> List of first 50 Invalid objects after recompile.
> ================================================
> DBHEALTH PACKAGE BODY
> CREATE_TABLE PACKAGE BODY
> PKG_PURGE PACKAGE BODY
>
> *Errors for PACKAGE BODY CREATE_TABLE: *
> 55/23 PL/SQL: ORA-00942: table or view does not exist
>
> 55/7 PL/SQL: SQL Statement ignored
>
>
> *Errors for PACKAGE BODY DBHEALTH: *
> 10/14 PL/SQL: SQL Statement ignored
>
> 10/32 PL/SQL: ORA-00942: table or view does not exist
>
> 100/21 PL/SQL: ORA-00942: table or view does not exist
>
> 106/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 120/20 PL/SQL: SQL Statement ignored
>
> 120/77 PL/SQL: ORA-00942: table or view does not exist
>
> 53/14 PL/SQL: SQL Statement ignored
>
> 53/84 PL/SQL: ORA-00942: table or view does not exist
>
> 57/39 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 64/14 PL/SQL: SQL Statement ignored
>
> 64/56 PL/SQL: ORA-00942: table or view does not exist
>
> 68/40 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 75/14 PL/SQL: SQL Statement ignored
>
> 75/48 PL/SQL: ORA-00942: table or view does not exist
>
> 79/44 PLS-00364: loop index variable 'C1REC' use is invalid
>
> 98/14 PL/SQL: SQL Statement ignored
>
>
> *Errors for PACKAGE BODY PKG_PURGE: *
> 1339/4 PL/SQL: SQL Statement ignored
>
> 1339/43 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/29 PL/SQL: ORA-00942: table or view does not exist
>
> 1910/5 PL/SQL: SQL Statement ignored
>
> 1913/5 PL/SQL: SQL Statement ignored
>
> 1913/61 PL/SQL: ORA-00942: table or view does not exist
>
> 1921/8 PLS-00364: loop index variable 'JOB_PARAM_REC' use is
> invalid
>
> 1938/7 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
> 1950/15 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>
>
>
> Invalid objects before Recompile....
>
> 3
>
> *Invalid objects after Recompile....
>
> 3 *
>
>
>
> The number of invalid objects is still the same after recompile.
>
>
> Please analyze the existing invalid objects.
>
> SQL>
> ------------------------------------------------------------------------------
> ------------------------------------------------------------------------------
> ---
>
> The only thing I could think of was I failed to grant execute permission
> on "dbms_rls", "dbms_aqin" and "dbms_pipe" when running the
> "create_glog_users.sql" script. So I ended up connecting as SYS user
> instead and manually ran those four statements. However it does not look
> like that would cause these invalid objects...........My head is going
> to explode, I think.
>
> Thanks in advance for any help!!
>
> Zara
>
> You are receiving this mail as a subscriber of the Oracle Transportation
> Management SIG List Server. This list is provided by Solution Beacon,
> LLC for the Oracle Transportation Management SIG to facilitate distribution
> of issues and topics related to Oracle Transportation Management. If you
> do not want to receive future mail from the list, send email to
> majordomo (AT) solutionbeacon (DOT) net with the words "unsubscribe otmsig" (without
> quotes) in the body of the email.
> If you need help, please contact mweiss (AT) solutionbeacon (DOT) com.
> Thank you!



You are receiving this mail as a subscriber of the Oracle Transportation
Management SIG List Server. This list is provided by Solution Beacon,
LLC for the Oracle Transportation Management SIG to facilitate distribution
of issues and topics related to Oracle Transportation Management. If you
do not want to receive future mail from the list, send email to
majordomo (AT) solutionbeacon (DOT) net with the words "unsubscribe otmsig" (without
quotes) in the body of the email.
If you need help, please contact mweiss (AT) solutionbeacon (DOT) com.
Thank you!

Alan,

Sorry for the late reply. I was occupied with some other stuff the whole
day and only able to try this again just now.
I did remember I was using *SYS* to run the "create_glog_roles.sql"
because no where in the doc. seems to mention I need to use the SYSTEM
user. It worked fine and I just confirmed by running your "select
privilege from dba_sys_privs where grantee='APP_OWNER';", it did return
*15 rows incl. 'Select any table'*.

However I decided to re-run the 'create_glog_roles.sql' again, this time
using the *SYSTEM *user. And guess what, it failed with 4 "ORA-01031:
insufficient privileges" errors:

grant SELECT ON DBA_SYNONYMS to app_owner
grant execute on dbms_rls to app_owner
grant select on sys.v_$context to app_owner
grant execut on dbms_rls to rep_owner

I don't know why I could not use SYSTEM user to run the script. Was this
caused by my previous run of the script as SYS user? The script should
take care of dropping those old ones first I assume.

Now I also remembered that previously when I tried to run
*"create_glog_users.sql"*, I was forced to use "SYSTEM" user because the
script itself prompted for its password. At that time, I also
encountered 4 "ORA-01031: insufficient privileges" errors:

" grant execute on dbms_aqin to glogdba"
"grant execute on dbms_rls to glogowner"
"grant execute on dbms_aqin to glogowner"
"grant execute on dbms_pipe to glogowner"

Eventually I had to use SYS user to run the above manually one by one. I
guessed I have the following problems:

1) I mixed up the SYS user and SYSTEM user. I don't know which user OTM
requires, I guess most of the time SYSTEM.
2) I am not sure how I can clear up the mess I created so far. Eg. why
using SYSTEM user, I got all those "insufficient privileges" and had to
use SYS, which is abnoraml, i guess for other OTM users?

Again thanks everyone who responded to my questions so far!! Without you
guys, I really want to call it quits.

Zara



Alan Cuartero wrote:
> Zara,
>
> It looks like the procedures can't recompile because the GLOGOWNER user
> doesn't have rights to access the v$ views such as v$parameter (10/32),
> v$session (100,21), as well as some of the DBA tables DBA_OBJECTS,
> DBA_CONSTRAINTS, etc. I suspect that the role APP_OWNER does not have the
> "SELECT ANY TABLE" privilege. You can check to see what pivileges APP_OWNER
> has with the following SQL (run as SYSTEM)
>
> SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE='APP_OWNER';
>
> And this should return the following
>
> PRIVILEGE
> ----------------------------------------------------------------------------
> CREATE PUBLIC SYNONYM
> COMMENT ANY TABLE
> ALTER SESSION
> CREATE SESSION
> CREATE DATABASE LINK
> CREATE VIEW
> DROP PUBLIC SYNONYM
> CREATE TABLE
> CREATE TYPE
> CREATE ANY CONTEXT
> CREATE SEQUENCE
> CREATE TRIGGER
> CREATE PROCEDURE
> CREATE ANY SYNONYM
> SELECT ANY TABLE
>
> 15 rows selected.
>
> If as I suspect you don't have "SELECT ANY TABLE" you need to revisit
> "create_glog_roles.sql", did you run this script & did you run it as SYSTEM
> (not SYS)? You can rerun this script as system (which will drop and recreate
> the roles) then manually grant the roles & any missing privileges back to
> the users by following along with the grant statements in the
> create_glog_users.sql script. This should help you avoid having to drop the
> users & data and recreate everything. For example the glogowner grants in
> create_glog_users.sql are
>
> grant app_owner to glogowner;
> grant app_user to glogowner;
> grant app_user_select to glogowner with admin option;
> grant create any table, alter any trigger, alter any table, select any
> table, create any sequence, create any trigger, analyze any,
> create user, drop user, create procedure,
> create public synonym, drop public synonym, CREATE MATERIALIZED VIEW to
> glogowner;
> grant execute on dbms_rls to glogowner;
> GRANT aq_administrator_role TO glogowner;
> GRANT EXECUTE ON DBMS_AQ TO glogowner;
> GRANT EXECUTE ON DBMS_AQADM TO glogowner;
> GRANT EXECUTE ON DBMS_AQIN TO glogowner;
> GRANT execute on dbms_pipe TO glogowner;
>
> And so on for each user...
>
> Then go back and try running recompile_invalid_objects.sql
>
> -Alan
>
> On 12/14/06 1:00 AM, "idqpbi" <idqpbi (AT) gmail (DOT) com> wrote:
>
>
>> Hi,
>>
>> Anybody experienced this before? This is the last SQL script to run
>> before I could actually start to verify my imported data. All of the
>> previous scripts were OK, even the import after I took Stephen's advice.
>> Sigh...... :-(
>> Maybe Alan or Stephen could help again. :-)
>>
>> Here is what I have in the terminal window: (sorry it's kind of long
>> since I ran the same scripts twice hoping to fix it)
>> ------------------------------------------------------------------------------
>> ---------------------------------------------------
>>
>> _*1st Run*_
>>
>> SQL> @recompile_invalid_objects.sql
>>
>> ORA-24344: success with compilation error
>> ORA-24344: success with compilation error
>> ORA-24344: success with compilation error
>>
>>
>> List of first 50 Invalid objects after recompile.
>> ================================================
>> DBHEALTH PACKAGE BODY
>> CREATE_TABLE PACKAGE BODY
>> PKG_PURGE PACKAGE BODY
>>
>> Errors for PACKAGE BODY CREATE_TABLE:
>> 55/23 PL/SQL: ORA-00942: table or view does not exist
>>
>> 55/7 PL/SQL: SQL Statement ignored
>>
>>
>> Errors for PACKAGE BODY DBHEALTH:
>> 10/14 PL/SQL: SQL Statement ignored
>>
>> 10/32 PL/SQL: ORA-00942: table or view does not exist
>>
>> 100/21 PL/SQL: ORA-00942: table or view does not exist
>>
>> 106/39 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 120/20 PL/SQL: SQL Statement ignored
>>
>> 120/77 PL/SQL: ORA-00942: table or view does not exist
>>
>> 53/14 PL/SQL: SQL Statement ignored
>>
>> 53/84 PL/SQL: ORA-00942: table or view does not exist
>>
>> 57/39 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 64/14 PL/SQL: SQL Statement ignored
>>
>> 64/56 PL/SQL: ORA-00942: table or view does not exist
>>
>> 68/40 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 75/14 PL/SQL: SQL Statement ignored
>>
>> 75/48 PL/SQL: ORA-00942: table or view does not exist
>>
>> 79/44 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 98/14 PL/SQL: SQL Statement ignored
>>
>>
>> Errors for PACKAGE BODY PKG_PURGE:
>> 1339/4 PL/SQL: SQL Statement ignored
>>
>> 1339/43 PL/SQL: ORA-00942: table or view does not exist
>>
>> 1910/29 PL/SQL: ORA-00942: table or view does not exist
>>
>> 1910/5 PL/SQL: SQL Statement ignored
>>
>> 1913/5 PL/SQL: SQL Statement ignored
>>
>> 1913/61 PL/SQL: ORA-00942: table or view does not exist
>>
>> 1921/8 PLS-00364: loop index variable 'JOB_PARAM_REC' use is
>> invalid
>>
>> 1938/7 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>>
>> 1950/15 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>>
>>
>>
>> Invalid objects before Recompile....
>>
>> 45
>>
>> *Invalid objects after Recompile....
>>
>> 3 *
>>
>>
>>
>> Some objects have compiled successfully.
>>
>>
>> Please re-run recompile_invalid_objects to compile the rest of invalid
>> objects.
>>
>> _*2nd Run*_
>>
>> SQL> @recompile_invalid_objects.sql
>>
>> ORA-24344: success with compilation error
>> ORA-24344: success with compilation error
>> ORA-24344: success with compilation error
>>
>>
>> List of first 50 Invalid objects after recompile.
>> ================================================
>> DBHEALTH PACKAGE BODY
>> CREATE_TABLE PACKAGE BODY
>> PKG_PURGE PACKAGE BODY
>>
>> *Errors for PACKAGE BODY CREATE_TABLE: *
>> 55/23 PL/SQL: ORA-00942: table or view does not exist
>>
>> 55/7 PL/SQL: SQL Statement ignored
>>
>>
>> *Errors for PACKAGE BODY DBHEALTH: *
>> 10/14 PL/SQL: SQL Statement ignored
>>
>> 10/32 PL/SQL: ORA-00942: table or view does not exist
>>
>> 100/21 PL/SQL: ORA-00942: table or view does not exist
>>
>> 106/39 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 120/20 PL/SQL: SQL Statement ignored
>>
>> 120/77 PL/SQL: ORA-00942: table or view does not exist
>>
>> 53/14 PL/SQL: SQL Statement ignored
>>
>> 53/84 PL/SQL: ORA-00942: table or view does not exist
>>
>> 57/39 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 64/14 PL/SQL: SQL Statement ignored
>>
>> 64/56 PL/SQL: ORA-00942: table or view does not exist
>>
>> 68/40 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 75/14 PL/SQL: SQL Statement ignored
>>
>> 75/48 PL/SQL: ORA-00942: table or view does not exist
>>
>> 79/44 PLS-00364: loop index variable 'C1REC' use is invalid
>>
>> 98/14 PL/SQL: SQL Statement ignored
>>
>>
>> *Errors for PACKAGE BODY PKG_PURGE: *
>> 1339/4 PL/SQL: SQL Statement ignored
>>
>> 1339/43 PL/SQL: ORA-00942: table or view does not exist
>>
>> 1910/29 PL/SQL: ORA-00942: table or view does not exist
>>
>> 1910/5 PL/SQL: SQL Statement ignored
>>
>> 1913/5 PL/SQL: SQL Statement ignored
>>
>> 1913/61 PL/SQL: ORA-00942: table or view does not exist
>>
>> 1921/8 PLS-00364: loop index variable 'JOB_PARAM_REC' use is
>> invalid
>>
>> 1938/7 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>>
>> 1950/15 PLS-00364: loop index variable 'SO_JOB_REC' use is invalid
>>
>>
>>
>> Invalid objects before Recompile....
>>
>> 3
>>
>> *Invalid objects after Recompile....
>>
>> 3 *
>>
>>
>>
>> The number of invalid objects is still the same after recompile.
>>
>>
>> Please analyze the existing invalid objects.
>>
>> SQL>
>> ------------------------------------------------------------------------------
>> ------------------------------------------------------------------------------
>> ---
>>
>> The only thing I could think of was I failed to grant execute permission
>> on "dbms_rls", "dbms_aqin" and "dbms_pipe" when running the
>> "create_glog_users.sql" script. So I ended up connecting as SYS user
>> instead and manually ran those four statements. However it does not look
>> like that would cause these invalid objects...........My head is going
>> to explode, I think.
>>
>> Thanks in advance for any help!!
>>
>> Zara
>>
>> You are receiving this mail as a subscriber of the Oracle Transportation
>> Management SIG List Server. This list is provided by Solution Beacon,
>> LLC for the Oracle Transportation Management SIG to facilitate distribution
>> of issues and topics related to Oracle Transportation Management. If you
>> do not want to receive future mail from the list, send email to
>> majordomo (AT) solutionbeacon (DOT) net with the words "unsubscribe otmsig" (without
>> quotes) in the body of the email.
>> If you need help, please contact mweiss (AT) solutionbeacon (DOT) com.
>> Thank you!
>>
>
>
>
> You are receiving this mail as a subscriber of the Oracle Transportation
> Management SIG List Server. This list is provided by Solution Beacon,
> LLC for the Oracle Transportation Management SIG to facilitate distribution
> of issues and topics related to Oracle Transportation Management. If you
> do not want to receive future mail from the list, send email to
> majordomo (AT) solutionbeacon (DOT) net with the words "unsubscribe otmsig" (without
> quotes) in the body of the email.
> If you need help, please contact mweiss (AT) solutionbeacon (DOT) com.
> Thank you!
>
>


You are receiving this mail as a subscriber of the Oracle Transportation
Management SIG List Server. This list is provided by Solution Beacon,
LLC for the Oracle Transportation Management SIG to facilitate distribution
of issues and topics related to Oracle Transportation Management. If you
do not want to receive future mail from the list, send email to
majordomo (AT) solutionbeacon (DOT) net with the words "unsubscribe otmsig" (without
quotes) in the body of the email.
If you need help, please contact mweiss (AT) solutionbeacon (DOT) com.
Thank you!