ORA-01435: user does not exist

oracle

I am trying to run the SQL queries coded on my friend's laptop onto mine. I have imported the .sql file into SQL developer and I'm trying to run it.However, I keep getting the error:

01435. 00000 -  "user does not exist"

I have listed all the users:

select * from all_users;

and I get:

OUTLN                                   9 30-MAR-10 
SYSTEM                                  5 30-MAR-10 
SYS                                     0 30-MAR-10

among other users.
My SQL query is :

  CREATE OR REPLACE TYPE "HOMEWORK1"."INNERTABLE" IS TABLE OF VARCHAR2(128);

I am using Oracle 11g on Win8. Please tell me if I need to post anything else to help you.
Thanks a lot

Best Answer

DazzaL has highlighted the nub of the problem but to give a bit more background, on the assumption you're fairly new to Oracle...

The create table syntax shows that your command is trying to create a table called INNERTABLE in the schema HOMEWORK1. Schema and user are essentially interchangeable in Oracle, so the error saying 'user does not exist' is referring to the HOMEWORK1 schema/user. Unless you've already created that in your database, that won't exist by default.

You can either (a) create the HOMEWORK1 user, (b) update the script to replace HOMEWORK1 with a (non-built-in; see below) user that does exist, or (c) update the script to remove the schema reference which will cause objects to be created as the user you're logged in as.

But who you are logged in as is something to check - it isn't clear from the question, so apologies if you know this and it isn't relevant to you. It's important not to create your own objects under Oracle's built-in schemas, especially SYS and SYSTEM as you could do a lot of damage, and you should not be logged in to a built-in schema routinely - only for specific activities that can't be done otherwise (like shutdown). The only exceptions to that are maybe the sample HR and SCOTT schemas, and even then it'll be better in the long term to use your own account for anything except queries against those.

If this is a fresh install and you're logging in as SYS because that's all you had available, please create a new user, assign it the relevant privileges, and do all further work under that.

Related Solutions

Oracle crash with ORA-00942: table or view does not exist

The error you're getting indicates that a query is trying to reference an object that doesn't exist or that the user doesn't have access to. If you started getting it a week ago and weren't getting it before that, something changed a week ago so that an object was dropped, a privilege was revoked, or a SQL statement was added that references an invalid object. It is unlikely that upgrading the database would resolve the error.

Can you determine the SQL statement that your application is issuing when it receives this error? If not, can you add additional logging so that you can determine what statement is generating the error? If your application doesn't log the SQL statements it generates, you could create a SERVERERROR trigger (based on this trigger code). Obviously, you would probably want to log more than just the SQL statement.

create table stmt_err_log (
  sql_stmt varchar2(1000)
);

 CREATE OR REPLACE TRIGGER after_error
  AFTER SERVERERROR ON DATABASE
  DECLARE
  pragma autonomous_transaction;
  id NUMBER;
  sql_text ORA_NAME_LIST_T;
  v_stmt CLOB;
  n NUMBER;
 BEGIN
  n := ora_sql_txt(sql_text);
  --
  IF n >= 1
  THEN
    FOR i IN 1..n LOOP
      v_stmt := v_stmt || sql_text(i);
    END LOOP;
  END IF;
  --
  FOR n IN 1..ora_server_error_depth LOOP
   IF ora_server_error(n) = '942'
   THEN
     insert into scott.stmt_err_log( sql_stmt )
       values( v_stmt );
     commit;
   END IF;
 END LOOP;
  --
 END after_error;

Getting “ORA-00942: table or view does not exist” while table does exist

You created the table in the SYS schema (which you should never, ever do. Really, never).

When you log in as teacher1 any statement looks for objects in that schema. But there is no TEACHER1.INSTRUCTORS table, because the real name is SYS.INSTRUCTORS (did I mention what a bad idea it is to create objects in the SYS schema?).

You need to run select * from sys.instructors to get access to that table. If you don't want to prefix the table name with the schema, create a synonym in the teacher1 schema:

create synonym teacher1.instructors for sys.instructors;

Then teacher1 can access the table from the SYS schema without fully qualifying it.

Again: stop using the SYS or SYSTEM account for anything that is not DBA stuff. Use a regular account for that.

Best Answer

Related Solutions

Oracle crash with ORA-00942: table or view does not exist

Getting “ORA-00942: table or view does not exist” while table does exist

Related Question