DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Am confused....thinking of going back to MS SQL Server.

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Am confused....thinking of going back to MS SQL Server.

    PS. Am confused.

    Version :
    Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
    PL/SQL Release 9.2.0.3.0 - Production
    CORE 9.2.0.3.0 Production
    TNS for 32-bit Windows: Version 9.2.0.3.0 - Production
    NLSRTL Version 9.2.0.3.0 - Production

    Code:
    SQL> conn sys as sysdba
    Enter password: 
    Connected.
    SQL> grant dba, resource, connect to abhay;
    
    Grant succeeded.
    
    SQL> conn abhay
    Enter password: *****
    Connected.
    SQL> create trigger test_v_session after alter on schema
      2  begin
      3  select * from v$session;
      4  end;
      5  .
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace trigger test_v_session after alter on schema
      2  begin
      3  select * from v$session; -- Oracle says Table/View does not exist
      4* end;
    SQL> /
    
    Warning: Trigger created with compilation errors.
    
    SQL> show errors
    Errors for TRIGGER TEST_V_SESSION:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/1      PL/SQL: SQL Statement ignored
    2/15     PL/SQL: ORA-00942: table or view does not exist
    
    
    SQL> select * from v$session; -- Ahh now oracle feels table/view is there.
    
    SADDR                     SID              SERIAL#               AUDSID PADDR                   USER# USERNAME          
    -------- -------------------- -------------------- -------------------- -------- -------------------- -------------
    7ADB3708                    1                    1                    0 7AD90C3C                    0                   
    7ADB4020                    2                    1                    0 7AD90F90                    0                   
    7ADB4938                    3                    1                    0 7AD912E4                    0                   
    7ADB5250                    4                    1                    0 7AD91638                    0                   
    7ADB5B68                    5                    1                    0 7AD9198C                    0                   
    7ADB6480                    6                    1                    0 7AD91CE0                    0                   
    7ADB6D98                    7                 1176                  521 7AD92034                   25 ABHAY             
    7ADB76B0                    8                  319                  518 7AD926DC                   18 SCOTT             
    7ADB88E0                   10                  355                  519 7AD92388                   18 SCOTT             
    
    9 rows selected.
    
    -- We upgraded our ACP DB to 9i today and here oracle behaves like the senario as above ( Above test case is on my local DB 9i-> simulated the problem of a DB event trigger in ACP )
    
    -- I dont understand Oracle now a days.
    Some times i get feeling, Oracle is becoming like MS SQL Server.


    Any thoughts or is it bug on 9i?

    Thanks
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    You will need to grant select access to v_$session to your user to access that view in a procedure/trigger.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jovery
    You will need to grant select access to v_$session to your user to access that view in a procedure/trigger.

    Regards
    I thought this would have taken care of that

    Code:
    SQL> ed
    Wrote file afiedt.buf
    
      1* select * from role_sys_privs where pRIVILEGE like 'SELECT ANY TABLE'
    SQL> /
    
    ROLE                           PRIVILEGE                                ADM
    ------------------------------ ---------------------------------------- ---
    DBA                           SELECT ANY TABLE                         YES 
    EXP_FULL_DATABASE              SELECT ANY TABLE                         NO
    IMP_FULL_DATABASE              SELECT ANY TABLE                         NO
    Any specific reason to explicitly grant a table/view that is being used by user in PROC/Trigger?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Privileges granted via the roles are disabled inside the procedure, take a look at the following doc for more info:

    http://support.oracle.co.uk/metalink...&p_id=168168.1

    HTH
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jovery
    Privileges granted via the roles are disabled inside the procedure, take a look at the following doc for more info:

    http://support.oracle.co.uk/metalink...&p_id=168168.1

    HTH
    Thanks.

    But now 1 more question.

    Before upgrade to 9i, all the required permissions were ther.
    How come after upgrade to 9i these permissions have gone?

    I think Oracle ( During install of new version ) would sense existing DBs and ask if the DBs need to be upgraded to new versions.

    Then it should take care what was ther before should be retained.
    why has'nt it been able to do.

    I see in our development DB which was upgraded 2 days ago, had all permissions or watever before and now.

    Is it possible that during upgrade somthings might change?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by abhaysk
    Thanks.

    But now 1 more question.

    Before upgrade to 9i, all the required permissions were ther.
    How come after upgrade to 9i these permissions have gone?

    I have upgraded so many oracle databases (may be more than 50) and between all versions from 7.3.4 till 9i and no, never, not even a single grant was missing. Here i would say you are speculating things up.

    Do it properly and there wont be any err.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    I hope you realise that after you sort out your problems related to grants, that trigger of yours will never compile (unless you correct it syntacticaly, ofcourse)....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by adewri
    Do it properly and there wont be any err.
    Its our PRO DBAs who do upgrades...not me.

    Well wat i told is exactly correct(infact wat i saw) that

    Dev DB

    Upgraded 2 days ago - No issues.

    Only triggers(2 logon triggers) went invalid due to some incompatibility or i could say wrong coding.
    I corrected those in Dev today and they had V$session object using in triggers. They got compiled correctly and are working fine.

    ACP DB

    Upgraded today.

    When i went to correct the triggers ( same two triggers as in DEV )
    i got the errors of Ora - 00942.

    Now when this trigger was working properly when it where 8i, it obiviously had permission on v$session. Why not after upgrade.

    And to my notice no errors were reported during upgrade.

    Thanks
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Before you upgraded to 9i, did you read its new features? No? What do you expect?

    SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    O7_DICTIONARY_ACCESSIBILITY boolean FALSE
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

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


Click Here to Expand Forum to Full Width