table or view does not exist

[Former Member]

Dera all

when i chek the dulicate key for the table SAPUSER

following error comes out.

Details are the followings

Microsoft Windows [Version 5.2.3790]

(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\prdadm>cd\

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Oct 6 12:08:34 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

SQL> conn /as sysdba

Connected.

SQL> select alldata.rowid, alldata.*

2 from sapuser alldata,

3 (SELECT K_COL1, K_COL2

4 FROM sapuser GROUP BY K_COL1, K_COL1

5 HAVING COUNT(*) > 1) keydata

6 where alldata.K_COL1=keydata.K_COL1

7 and alldata.K_COL2=keydata.K_COL2;

FROM sapuser GROUP BY K_COL1, K_COL1

*

ERROR at line 4:

ORA-00942: table or view does not exist

SQL>

please advise whre we are wrong.

thanks

with best reagrds

jainendra prajapati

[debasissahoo · Accepted solution]

Venkat,

As Lars has already explained the access to the table SAPUSER is not granted from SYS, as it is not owned by sys but by OPS$ user. sqlplus / is the easiest way to do this.

But however if you're logged in <sid>adm and through connect / as sysdba, then you can also use the below statement to access SAPUSER.

SQL> select * from "OPS$<host>\<sid>ADM".SAPUSER;

hope this helps.

Debasis.

[lbreddemann]

>

> SQL> conn /as sysdba

> Connected.

> FROM sapuser GROUP BY K_COL1, K_COL1

> *

> ERROR at line 4:

> ORA-00942: table or view does not exist

>

> please advise whre we are wrong.

HI there,

this is pretty easy.

You connected as "SYSDBA" (btw: what for?) and when you do that you always end up as the user "SYS".

Now, "SYS" does not own the SAPUSER table, but your OPS$-User does.

So the easiest way to avoid this issue is to loging via:

  sqlplus / 
or 
  conn /

instead of

  sqlplus "/as sysdba" 
or 
  conn /as sysdba

regards,

Lars