ora-00942 even i create table in my schema

Question

I have created new user and granted (database administration) privilege to him. Then I connected to this new user.

When I create a new table in this user then try to select from this table it gives me

oracle-00942: table/view does not exist

When I try to find it through toad from schema browser I cannot find it. I searched for it and found this table in (sys) user.

When I create table with schema name I found it in schema browser but also I cannot select from it.

So what is the wrong with this?

Answer 1

It would help if you would show the sequence of steps you used to create the table. My guess is somehow you're creating the table under the the wrong schema (owner). Do this to find out where it is:

select owner, table_name from all_tables where table_name = 'MYTABLE'

If connected as SYS, you can use dba_tables instead of all_tables.

Answer 2

It would be better if you actually showed us what you did and how Oracle responded (i.e. copy/pasted the whole SQL*Plus session).

As you can create users, you probably can connect as SYS. Do so, and then run such a statement:

SQL> select owner, object_type
  2  from dba_objects
  3  where object_name = 'EMP';

OWNER                          OBJECT_TYPE
------------------------------ -------------------
SCOTT                          TABLE

SQL>

It will show who that table really belongs to.

---

I'm going to simulate what you did (actually, what I understood you did). You'll see that - if you do it right - everything is OK.

Connect as SYS and create new user:

SQL> show user
USER is "SYS"
SQL> create user utest identified by utest
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant dba to utest;

Grant succeeded.

Connect as newly created user, create a table:

SQL> connect utest/utest
Connected.
SQL> create table test (id number);

Table created.

SQL> select * from test;

no rows selected

OK; the table is empty, but - no ORA-00942 error.

Back to SYS, to check who owns the TEMP table:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select owner, object_type
  2  from dba_objects
  3  where object_name = 'TEST'
  4  order by owner;

OWNER                          OBJECT_TYPE
------------------------------ -------------------
SCOTT                          TABLE
UTEST                          TABLE

SQL>

Now, it is your turn.

Answer 3

I searched for it and found this table in (sys) user.

It sounds like you have connected to the database using SYSDBA privilege, something like this:

connect your_user/password as sysdba

When we do this Oracle ignores the passed username and connects us as SYS. Consequently, any actions we take are executed as SYS. Which means any tables we create are created in the SYS schema, unless we prefix them with a specific schema name.