Tuesday, April 15, 2014

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>

No comments:

Post a Comment