Zara,

Oh, yes I remember I saw that you mentioned that in an earlier email. At the
time I had a thought and you just reminded me of what I was going to ask...
I think the problem may be that your init.ora file is not being used.
Starting with Oracle 9i they introduced a concept called the SPFILE. You
need to execute the following command to "pick up" what's in your init.ora
file and create an SPFILE.

First we need to create a plain text PFILE for you to edit. Log in as user
"SYS as SYSDBA" and execute the following.

CREATE PFILE='/some/reasonable/path/backupinit.ora' FROM SPFILE;

Keep this in a safe place for now and copy it to another filename
(newinit.ora) then edit adding or changing as necessary to add the required
parameters for OTM. There are many more than the two you mentioned and their
is a sample init.ora in /opt/otm/glog/oracle/script8 directory for you to
use. WARNING - Don't change your block size, if you didn't create the
database with at least 4K let me know. But don't bump it to 8K as in the
sample init.ora because your DB won't start (it was already created with
whatever you have set). Also if your memory buffer & cache values are
already larger than what's suggested in the init.ora leave them alone. You
may have to revisit them later but that is a tuning exercise. If you have
any concerns about the new parameters let me know before you do the
following.

Next create a new SPFILE with the following command.

CREATE SPFILE FROM PFILE='/some/reasonable/path/newinit.ora';

Then perform a shutdown and startup on the instance. If it doesn't start let
me know.

Then try running the create_glog_roles.sql again as SYSTEM. Btw, I belive
the problem is that its not picking up the O7_DICTIONARY_ACCESSIBILITY=TRUE.
This is what will allow a user with select any table to see v$ views. They
changed the default behavior in 9i & 10g. We like the old Oracle 7 behavior.

Good luck, this should fix the problem.

-Alan


On 12/14/06 10:21 PM, "idqpbi" <idqpbi (AT) gmail (DOT) com> wrote:

> Alan,
>
> Sure thanks!!
>
> Not sure if this has anything to do with it:
>
> /The following initial parameters should be set:
> O7_DICTIONARY_ACCESSIBILITY = true
> Open_cursors = 200 (or greater)/
>
> I definitely set it in the init.ora file, could not remember on the
> server or the client though.
> Anyway have a good night!!
>
> Zara
>
> Alan Cuartero wrote:
>> Zara,
>>
>> This looks correct. I'm not sure what it is. I'm kicking around a couple of
>> ideas. Let me sleep on it, I'll get back to you in the AM.
>>
>> Have a good night.
>>
>> -Alan
>>
>>
>> On 12/14/06 9:01 PM, "idq pbi" <idqpbi (AT) gmail (DOT) com> wrote:
>>
>>
>>> Hi Alan,
>>>
>>> I really appreciate your help!
>>>
>>> Here's what I got, SYSTEM returns 6 rows while SYS returns 159 rows(as
>>> you said):
>>> ================================================== ====================
>>> SQL> select privilege from dba_sys_privs where grantee='SYSTEM';
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> ALTER SYSTEM
>>> GLOBAL QUERY REWRITE
>>> CREATE MATERIALIZED VIEW
>>> CREATE TABLE
>>> UNLIMITED TABLESPACE
>>> SELECT ANY TABLE
>>>
>>> 6 rows selected.
>>> ================================================== ======================
>>>
>>> SQL> select privilege from dba_sys_privs where grantee='SYS';
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> CREATE EXTERNAL JOB
>>> MANAGE FILE GROUP
>>> ADMINISTER SQL TUNING SET
>>> MANAGE SCHEDULER
>>> CREATE ANY RULE
>>> CREATE ANY EVALUATION CONTEXT
>>> CREATE ANY CONTEXT
>>> MANAGE ANY QUEUE
>>> ALTER ANY INDEXTYPE
>>> CREATE ANY MATERIALIZED VIEW
>>> EXECUTE ANY PROCEDURE
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> ALTER ANY ROLE
>>> GRANT ANY ROLE
>>> DROP PUBLIC DATABASE LINK
>>> CREATE DATABASE LINK
>>> CREATE ANY INDEX
>>> ALTER ANY CLUSTER
>>> ALTER ROLLBACK SEGMENT
>>> ALTER SESSION
>>> AUDIT SYSTEM
>>> DROP ANY SQL PROFILE
>>> ALTER ANY RULE
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> IMPORT FULL DATABASE
>>> CREATE RULE SET
>>> EXECUTE ANY EVALUATION CONTEXT
>>> FLASHBACK ANY TABLE
>>> CREATE ANY DIMENSION
>>> EXECUTE ANY INDEXTYPE
>>> DROP ANY INDEXTYPE
>>> CREATE ANY INDEXTYPE
>>> CREATE ANY OPERATOR
>>> CREATE LIBRARY
>>> CREATE ANY DIRECTORY
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> CREATE PROFILE
>>> CREATE VIEW
>>> INSERT ANY TABLE
>>> SELECT ANY TABLE
>>> LOCK ANY TABLE
>>> DROP ANY TABLE
>>> MANAGE ANY FILE GROUP
>>> CREATE ANY SQL PROFILE
>>> EXECUTE ANY CLASS
>>> CREATE ANY JOB
>>> ADVISOR
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> EXPORT FULL DATABASE
>>> RESUMABLE
>>> DROP ANY OUTLINE
>>> DROP ANY DIMENSION
>>> UNDER ANY TABLE
>>> EXECUTE ANY LIBRARY
>>> DROP ANY LIBRARY
>>> CREATE ANY LIBRARY
>>> ALTER ANY TYPE
>>> DROP ANY ROLE
>>> CREATE ROLE
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> DROP ANY SEQUENCE
>>> ALTER ANY SEQUENCE
>>> CREATE SEQUENCE
>>> CREATE PUBLIC SYNONYM
>>> UPDATE ANY TABLE
>>> CREATE ROLLBACK SEGMENT
>>> UNLIMITED TABLESPACE
>>> CREATE TABLESPACE
>>> CHANGE NOTIFICATION
>>> ADMINISTER ANY SQL TUNING SET
>>> DROP ANY RULE SET
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> DEBUG ANY PROCEDURE
>>> ENQUEUE ANY QUEUE
>>> EXECUTE ANY TYPE
>>> CREATE ANY TRIGGER
>>> CREATE ANY PROCEDURE
>>> ALTER DATABASE
>>> DROP ANY VIEW
>>> CREATE ANY VIEW
>>> CREATE SYNONYM
>>> DROP ROLLBACK SEGMENT
>>> DROP USER
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> CREATE USER
>>> ALTER ANY SQL PROFILE
>>> CREATE RULE
>>> ALTER ANY EVALUATION CONTEXT
>>> CREATE EVALUATION CONTEXT
>>> ON COMMIT REFRESH
>>> MERGE ANY VIEW
>>> CREATE ANY OUTLINE
>>> DROP ANY CONTEXT
>>> GLOBAL QUERY REWRITE
>>> QUERY REWRITE
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> UNDER ANY VIEW
>>> CREATE INDEXTYPE
>>> CREATE ANY TYPE
>>> CREATE TYPE
>>> DROP ANY DIRECTORY
>>> DROP ANY MATERIALIZED VIEW
>>> ANALYZE ANY
>>> ALTER PROFILE
>>> FORCE ANY TRANSACTION
>>> AUDIT ANY
>>> DROP ANY INDEX
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> CREATE ANY CLUSTER
>>> ALTER ANY TABLE
>>> CREATE SESSION
>>> ALTER SYSTEM
>>> CREATE JOB
>>> EXECUTE ANY RULE
>>> DROP ANY EVALUATION CONTEXT
>>> GRANT ANY OBJECT PRIVILEGE
>>> ALTER ANY OUTLINE
>>> ALTER ANY LIBRARY
>>> DROP ANY TYPE
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> GRANT ANY PRIVILEGE
>>> ALTER RESOURCE COST
>>> ALTER ANY TRIGGER
>>> CREATE TRIGGER
>>> FORCE TRANSACTION
>>> DROP ANY CLUSTER
>>> CREATE CLUSTER
>>> COMMENT ANY TABLE
>>> CREATE ANY TABLE
>>> RESTRICTED SESSION
>>> SELECT ANY TRANSACTION
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> EXECUTE ANY PROGRAM
>>> EXECUTE ANY RULE SET
>>> ALTER ANY RULE SET
>>> DEQUEUE ANY QUEUE
>>> ALTER ANY DIMENSION
>>> DROP ANY OPERATOR
>>> CREATE MATERIALIZED VIEW
>>> ALTER ANY PROCEDURE
>>> CREATE PROCEDURE
>>> CREATE ANY SEQUENCE
>>> DROP PUBLIC SYNONYM
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> ALTER ANY INDEX
>>> DELETE ANY TABLE
>>> CREATE TABLE
>>> ALTER USER
>>> BECOME USER
>>> DROP TABLESPACE
>>> ALTER TABLESPACE
>>> READ ANY FILE GROUP
>>> DROP ANY RULE
>>> CREATE ANY RULE SET
>>> DEBUG CONNECT SESSION
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> ADMINISTER DATABASE TRIGGER
>>> ADMINISTER RESOURCE MANAGER
>>> CREATE DIMENSION
>>> EXECUTE ANY OPERATOR
>>> CREATE OPERATOR
>>> UNDER ANY TYPE
>>> ALTER ANY MATERIALIZED VIEW
>>> DROP PROFILE
>>> DROP ANY TRIGGER
>>> DROP ANY PROCEDURE
>>> CREATE PUBLIC DATABASE LINK
>>>
>>> PRIVILEGE
>>> ----------------------------------------
>>> SELECT ANY SEQUENCE
>>> DROP ANY SYNONYM
>>> CREATE ANY SYNONYM
>>> BACKUP ANY TABLE
>>> MANAGE TABLESPACE
>>>
>>> 159 rows selected.
>>>
>>> SQL>
>>> ================================================== ==========================
>>>
>>> Please let me know if you need anything else. Thanks!
>>>
>>> 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!