dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66

    Question Create VIEW problem

    Any explanation for the following would be much appreciated.

    I have two users A and B, both with their own schemas.

    if I execute this sql (as UserA)

    create table test_table
    as select account_no,
    period
    from userB.accounts
    where period = last_day(to_date('31-OCT-2002')-170)

    No problem, Table created.

    But if I execute this sql

    create view test_view
    as select account_no,
    period
    from userB.accounts
    where period = last_day(to_date('31-OCT-2002')-170)

    Then I get an error

    from userB.accounts
    *
    ERROR at line 6:
    ORA-00942: table or view does not exist

    I can overcome this by granting SELECT ANY TABLE to UserA, but I realy don't want to give this privilege to this user.
    Why will oracle let me create a table in userA schema based on a select from a table in userB schema, but wont let me create a view based on the same select statement?





  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Preplexing!!

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    btw Oracle version is 8.0.6

    I have tried the above statement while granting CREATE VIEW and CREATE ANY VIEW to userA but still get the same error. Only granting SELECT ANY TABLE seems to work.

  4. #4
    Join Date
    Jun 2001
    Location
    Dublin, Ireland
    Posts
    66
    Sorry guys,
    It seems to be working now with just CREATE VIEW grant. I dont know how it didnt work earlier.

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




×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.