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.
Posted by Labels: , ,

8 comments :

  1. AnonymousAugust 25, 2009 at 9:55 PM

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

    alter table owner1.customer to owner1.customer_backup;

    ReplyDelete
  2. AnonymousOctober 15, 2009 at 2:37 AM

    Does this really work?

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

    ReplyDelete
  3. AnonymousOctober 16, 2009 at 10:56 AM

    Are you sure you are using Oracle above 8i version?

    ReplyDelete
  4. AnonymousNovember 13, 2009 at 2:44 AM

    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. AnonymousDecember 21, 2009 at 9:47 PM

    hi.. wil rename command work in oracle 8

    ReplyDelete
  6. Anantha NarayananDecember 22, 2009 at 4:40 PM

    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. AnonymousOctober 19, 2012 at 10:22 AM

    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. AnonymousOctober 19, 2012 at 10:26 AM

    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

Subscribe to: Post Comments ( Atom )