Results 1 to 5 of 5

Thread: invalid trigger

  1. #1
    Join Date
    Jul 2003
    Posts
    129

    Unanswered: invalid trigger

    I have migrate a user (containing all the database, including scripts like triggers) to my 9.2.0.1 database server (from 7.3.3)

    The problem is that some trigger are still invalid (the error is that the table or view v$session cannot be found eventhough there is such a table under the SYS schema.

    Have you ever faced anything similar?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    GRANT SELECT ON V$SESSION TO <username>;

  3. #3
    Join Date
    Jul 2003
    Posts
    129
    here is my attempt
    Code:
    SQL> GRANT SELECT ON V$SESSION TO user;
    GRANT SELECT ON V$SESSION TO user
                    *
    ERROR at line 1:
    ORA-02030: can only select from fixed tables/views
    
    
    SQL>
    Where is my fault?

    PS. i logged as SYSTEM AS SYSDBA

  4. #4
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by dominant
    here is my attempt

    Code:
    SQL> GRANT SELECT ON V$SESSION TO user;
    GRANT SELECT ON V$SESSION TO user
                    *
    ERROR at line 1:
    ORA-02030: can only select from fixed tables/views
    
    
    SQL>
    Where is my fault?

    PS. i logged as SYSTEM AS SYSDBA
    sys.v$session is a fixed view (a false table that is actually a C array or linked list in the kernel), so it is "special", it is not a regular object; e.g. you cannot drop, alter it, and you cannot grant anything on it, even if you are the owner.

    But, sys.v_$session is a view "select * from sys.v$session", and you can grant anything on it (if you are the owner).
    The v$session you access from the trigger is a public synonym for the view sys.v_$session.

    So, you need to grant the select on the view:

    grant select on v_$session to user;

    while connected as SYS (the owner of the view)
    and all works fine.

    You could also try
    grant select_catalog_role to user;

    depending on
    1) if you want your user to access other v$ views
    2) if it works in triggers - never remember if roles are enabled or not in trigger ;-)

    HTH
    Al

    PS In reality there's also gv$session involved in the picture, but essentially, it's the same as above.

  5. #5
    Join Date
    Jul 2003
    Posts
    129
    thank you for your reply i fixed!!

    Now only one trigger remains with some errors which inserts values into
    TXSYS.DRQ_PENDING
    what can i do for that? the tables under this schema does not exist.
    Obviously it does exist in oracle server 7.3.3.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •