How to rename a table in Oracle?

There are two ways of renaming a table in Oracle:

Method 1: Simple

rename {old_table_name} to {new_table_name}

Example:

rename CUSTOMER to CUSTOMER_BACKUP

Method 2: Not so Complex

alter table {old_table_name} rename to {new_table_name};

Example:

alter table CUSTOMER rename to CUSTOMER_BACKUP;

The minimum version that supports table renaming is Oracle 8i. All the dependencies of the table will automatically updated. No need of updating them after wards.

8 comments :

  1. How do you specify the owner? It doesn't like it when you say:

    alter table owner1.customer to owner1.customer_backup;

    ReplyDelete
  2. Does this really work?

    ERROR at line 1:
    ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations

    ReplyDelete
  3. Are you sure you are using Oracle above 8i version?

    ReplyDelete
  4. As long as you're logged into the correct schema you do not need the owner info. Oracle considers that a seperate operation that's why you got the "ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations"
    error.

    alter table customer rename to customer_backup;

    will work just fine.

    ReplyDelete
  5. hi.. wil rename command work in oracle 8

    ReplyDelete
  6. I do not have an Oracle Version 8 at my disposal. But as far as I remember, it is starting with version 8.1.6 (8i) and not till 8.0.6 (8)

    ReplyDelete
  7. alter table owner1.customer rename to owner1.customer_backup
    >>> RAISES ORA-14047 ERROR

    alter table owner1.customer rename to customer_backup >> CORRECT ONE "

    I tried this in 11g

    ReplyDelete
  8. alter table owner1.customer rename to owner1.customer_backup
    >>> RAISES ORA-14047 ERROR

    alter table owner1.customer rename to customer_backup >> CORRECT ONE "

    I tried this in 11g

    * As you can see I did not add "owner." before "customer_bakup"

    (By AAA)

    ReplyDelete