Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    35

    Question Unanswered: OK, I can SELECT * from a table but I can't create a view of it?!

    This is starting to boggle me --

    I'm using the Oracle 9.2 SQL*Plus Worksheet tool and I have created a few views on some tables on our Oracle server. However when I try to create a view of one particular table it just doesn't work.

    Here's basically what I've done:

    Code:
    >CREATE VIEW MyLogin.MyView1 AS SELECT Field1, Field2, Field3 FROM Schema1.Table1;
    
    View created.
    
    
    >CREATE VIEW MyLogin.MyView2 AS SELECT Field1, Field2, Field3 FROM Schema1.Table2;
    
    View created.
    
    
    >CREATE VIEW MyLogin.MyView3 AS SELECT Field1, Field2, Field3 FROM Schema1.Table3;
    
    CREATE VIEW MyLogin.MyView3 AS SELECT Field1, Field2, Field3 FROM Schema1.Table3;
                                                                              *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    OK, so this would make you think that this so-called "Table3" does not exist correct? Well actually, it does. Furthermore, when I run a...

    Code:
    SELECT Field1, Field2, Field3 FROM Schema1.Table3;
    .. That works just fine and I get all of the results spit back to me. So the table is there, I have select access to it... but for some reason when I try to create the view it is telling me that it doesn't exist!

    Can anyone please help?

    No I don't have access to SYS account or anything.. just normal public user with R/W to some tables and create view for my schema only. (maybe more than this but this is all that I know so far.. I know that I can't grant access to other users on my views and I can't even create new tables even in my own schema...)

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    A common problem.

    can you do this:
    create table MyLogin.MyView3 AS SELECT Field1, Field2, Field3 FROM Schema1.Table3;

    I wonder if that works.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Posts
    35
    Thanks for the response! As I said above, I can't create tables. When I try it, ...

    Code:
    ERROR at line 7:
    ORA-01950: no privileges on tablespace 'USERS'


    Anything else that I can try?

    Thanks again!

  4. #4
    Join Date
    May 2004
    Location
    Pune, India
    Posts
    15
    hi

    If u dont have the privilege CREATE TABLE....may be u also dont have thep privilege CREATE VIEW. Check that out ....

    If that is not there then u can select from any given table for which u have rights but u wont be able to create the objects like tables or view...or even proc and functions tot hat matter.

    regards
    abhivyakti

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by joshg
    Thanks for the response! As I said above, I can't create tables. When I try it, ...

    Code:
    ERROR at line 7:
    ORA-01950: no privileges on tablespace 'USERS'


    Anything else that I can try?

    Thanks again!
    ah! now we get to the bottom of the problem!
    no privs on the tablespace

    problem solved
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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