ORA-00942: table or view does not exit.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: ORA-00942: table or view does not exit.

  1. #1
    Join Date
    Jul 2003
    Posts
    20

    ORA-00942: table or view does not exit.

    I did a select * from jobs;
    and it returned ORA-00942.

    But when I did select owner, table_name from all_tables where owner
    ='SIMT', and it returned jobs, authors....

    What's going on? I am under the dba group.

    Any advise? Thanks in advance.

  2. #2
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Re: ORA-00942: table or view does not exit.

    Hi ttsim,

    Who are you connected as? Try select * from .jobs; where owner = owner of table jobs;

    Regds,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  3. #3
    Join Date
    Jul 2003
    Posts
    20
    I did a select * from simt.jobs and it still returned the ORA. I exported this table from sql server 2000 to oracle.

  4. #4
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    try:

    select * from SIMT.jobs;

    According to your query against "ALL_TABLES" you were looking for
    the owner of SIMT ....

    Gregg

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    What is the result of

    select owner,table_name from dba_tables where table_name = '' where table_name is the table you want to select from.

    Regds,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Jul 2003
    Posts
    20
    SQL> show user
    USER is "SIMT"
    SQL> select owner, table_name from all_tables where owner = 'SIMT';

    OWNER TABLE_NAME
    ________________________________________________
    SIMT jobs

    SQL> select * from jobs;
    select * from jobs
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist

  7. #7
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    select * from "jobs"

  8. #8
    Join Date
    Jan 2001
    Posts
    191
    When you import table from sqlserver to oracle, table name is case sensitive.


    xyz

  9. #9
    Join Date
    Jul 2003
    Posts
    20
    it works when I:-

    select * from "jobs";
    Why's that? Don't understand?
    Next time when I export more tables from SQL Server 2000 into Oracle, how can I avoid this from happening each time when I do a select ... from "...", instead of select ....from ....;
    Please advise.
    Thanks in advance.

  10. #10
    Join Date
    Jan 2001
    Posts
    191
    In oracle table name is not case sensitive, in SQL server, default, table name is case sensitive. So after import SQL server table, you table anme is case sensitive. You have to get the table name from user_tables, then you have to use " " for your table name, this way, Oracle know your table name is case sensitive. In your case, your table name is "jobs". But in oracle, if you use

    select * from jobs.

    In oracle it means

    select * from "JOBS".

    But your table name is "jobs".

    xyz

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