Oracle RENAME Table

Summary: in this tutorial, you will learn how to use the Oracle RENAME table statement to rename an existing table in the database.

To rename a table, you use the following Oracle RENAME table statement as follows:

RENAME table_name TO new_name;
Code language: SQL (Structured Query Language) (sql)

In the RENAME table statement:

  • First, specify the name of the existing table which you want to rename.
  • Second, specify the new table name. The new name must not be the same as another table in the same schema.

Note that you cannot roll back a RENAME statement once you executed it.

When you rename a table, Oracle automatically transfers indexes, constraints, and grants on the old table to the new one. In addition, it invalidates all objects that depend on the renamed table such as views, stored procedures, function, and synonyms.

Oracle RENAME table example

Let’s create a table named promotions for the demonstration.

CREATE TABLE promotions( promotion_id NUMBER GENERATED BY DEFAULT AS IDENTITY, promotion_name varchar2(255), start_date DATE NOT NULL, end_date DATE NOT NULL, PRIMARY KEY(promotion_id), CHECK (end_date > start_date) );
Code language: SQL (Structured Query Language) (sql)

The following PL/SQL function returns the number of promotions by querying data from the promotions table:

CREATE OR REPLACE FUNCTION count_promotions RETURN NUMBER IS v_count NUMBER; BEGIN SELECT COUNT( * ) INTO v_count FROM promotions; RETURN v_count; END;
Code language: SQL (Structured Query Language) (sql)

To rename the promotions table to campaigns table, you use the following statement:

RENAME promotions TO campaigns;
Code language: SQL (Structured Query Language) (sql)

As mentioned earlier, Oracle transferred all indexes, constraints, and grants from the promotions table to the campaigns table. The following statement shows constraints of the new campaigns table transferred from the promotions table:

SELECT constraint_name, search_condition FROM all_constraints WHERE table_name = 'CAMPAIGNS' AND constraint_type = 'C';
Code language: SQL (Structured Query Language) (sql)
Oracle RENAME table - invalid objects

Because the COUNT_PROMOTIONS function references to promotions table, when you renamed the promotions table, the COUNT_PROMOTIONS function became invalid.

To find the invalid objects in the current schema, you query data from the all_objects view as follows:

SELECT owner, object_type, object_name FROM all_objects WHERE status = 'INVALID' ORDER BY object_type, object_name;
Code language: SQL (Structured Query Language) (sql)
Oracle RENAME table - constraints

As you can see, the statement returns COUNT_PROMOTIONS function as an invalid object.

In this tutorial, you have learned how to use the Oracle RENAME table statement to rename an existing table in the database.

Was this tutorial helpful?