Database dictionary corruption ? Maybe not.

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

 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';

----------------------- ----------------------------------------
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 Smile 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

One comment

  1. 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?

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.