How to Rename a Table

I tested this example in Oracle 12.1. First I created a table:

SQL> create table fred

  2  as select * from user_synonyms

  3  where 1 = 2

  4  /

 

Table created.

SQL>

Then I checked its object_id for later:

SQL> select object_id

  2  from user_objects

  3  where object_name = 'FRED'

  4  /

 

OBJECT_ID

----------

     92212

SQL>

... and described it:

SQL> desc fred

Name                       Null?    Type

-------------------------- -------- ------------------

SYNONYM_NAME               NOT NULL VARCHAR2(128)

TABLE_OWNER                         VARCHAR2(128)

TABLE_NAME                 NOT NULL VARCHAR2(128)

DB_LINK                             VARCHAR2(128)

ORIGIN_CON_ID                       NUMBER

SQL>

Then I changed its name:

SQL> rename fred to joe

  2  /

 

Table renamed.

SQL>

... and finally, to prove I was still looking at the same object, I used the new name to look up the object_id and description and confirmed that they had not changed:

SQL> select object_id

  2  from user_objects

  3  where object_name = 'JOE'

  4  /

 

OBJECT_ID

----------

     92212

 

SQL> desc joe

Name                       Null?    Type

-------------------------- -------- ------------------

SYNONYM_NAME               NOT NULL VARCHAR2(128)

TABLE_OWNER                         VARCHAR2(128)

TABLE_NAME                 NOT NULL VARCHAR2(128)

DB_LINK                             VARCHAR2(128)

ORIGIN_CON_ID                       NUMBER

 

SQL>

Create Force View

You cannot create a view of a table which does not exist:

SQL> create or replace view blah
  2  as select * from missing_table
  3  /
as select * from missing_table
                 *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL>

... but, if you have no choice, you can use the force option:

SQL> create or replace force view blah
  2  as select * from missing_table
  3  /

Warning: View created with compilation errors.

SQL>

Of course, such a view will not work:

SQL> select * from blah
  2  /
select * from blah
              *
ERROR at line 1:
ORA-04063: view "SYS.BLAH" has errors

SQL>

... until you have created the missing table:

SQL> create table missing_table
  2  as select 'Dial 999' how_to_call_the_police
  3  from dual
  4  /

Table created.

SQL>

Even then, the view is still invalid:

SQL> select status from user_objects
  2  where object_name = 'BLAH'
  3  /

STATUS
-------
INVALID

SQL>

... but, if you try to use it, Oracle recompiles it and it works:

SQL> select * from blah
  2  /

HOW_TO_CALL_THE_POLICE
----------------------
Dial 999

SQL>

... and the next time you check its status, it is valid:

SQL> select status from user_objects
  2  where object_name = 'BLAH'
  3  /

STATUS
-------
VALID

SQL>