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>

ROWNUM

Tested on an Oracle 9 database. ROWNUM is a pseudo column which you can include in the output  from a SQL query:

SQL> select rownum, object_id from dba_objects

  2  where rownum < 6

  3  /

    ROWNUM  OBJECT_ID

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

         1         47

         2         19

         3         17

         4         15

         5         45

SQL>

The first rownum is always 1, the second is always 2 and so on. So if you do not have a rownum of 1 at the start, you will not get any output at all. The following query selects the same rows as the one above:

SQL> select rownum, object_id from dba_objects

  2  where rownum between 1 and 5

  3  /

    ROWNUM  OBJECT_ID

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

         1         47

         2         19

         3         17

         4         15

         5         45

SQL>

But the next one does not select rows 2 through 5. It produces no output at all as the first row of output cannot have a rownum of 1:

SQL> select rownum, object_id from dba_objects

  2  where rownum between 2 and 5

  3  /

no rows selected

SQL>