Posted by At first glance, this looks like a major drama. Some sort of database dictionary corruption, or internal error. But we’ll see, it’s actually fine.
Let’s do a simple describe on an existing table
SQL> desc MY_TABLE
Name Null? Type
----------------------------- -------- --------------------
EMPNO NUMBER(38)
ENAME VARCHAR2(30)
HIREDATE DATE
DEPTNO NUMBER(38)
SAL NUMBER(38)
Now of course, that could a synonym, or a view… so let’s also make sure that we are actually dealing with a real table.
SQL> select object_type, object_name
2 from user_objects
3 where object_name = 'MY_TABLE';
OBJECT_TYPE OBJECT_NAME
----------------------- ----------------------------------------
TABLE MY_TABLE
So then, it’s definitely a table. Let’s find out more of the attributes for that table.
SQL> select table_name
2 from user_tables
3 where table_name = 'MY_TABLE';
no rows selected
Uh oh….. you can feel the adrenalin levels rising…Is my database corrupt? Do I need to recover ? Maybe I’ll check to see if I can query the table.
SQL> select * from MY_TABLE;
EMPNO ENAME HIREDATE DEPTNO SAL
---------- ------------------------------ --------- ---------- ----------
1234 John 16-MAR-16 10 1000
1235 Sue 04-MAR-16 20 2000
1236 Jill 17-DEC-15 30 2200
1237 Mary 08-NOV-15 30 3400
That all seems good. So how did things go so wrong ? Well, it’s actually perfectly normal behaviour. xxx_TABLES is all of the relational tables. And MY_TABLE is not a relational table. I created it like this:
SQL> create or replace
2 type my_obj as object ( empno int, ename varchar2(30), hiredate date, deptno int, sal int);
3 /
Type created.
SQL> create table MY_TABLE of my_obj;
Table created.
And if you want to find out where the object tables are, then naturally 
you look in xxx_OBJECT_TABLES
SQL> select table_name, table_type
2 from user_object_tables
3 where table_name = 'MY_TABLE';
TABLE_NAME TABLE_TYPE
------------------------------ ------------------------------
MY_TABLE MY_OBJ
Learning is not a spectator sport 
Hello, the same case happened to me,i saw some data files but when i queried v$tablespace, dba_data_file it wasnt there..could you please expalin this issue?