Oracle RENAME Table Statement By Examples

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

Introduction to the Oracle RENAME table statement

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 from the old table to the new one. In addition, it invalidates all objects that depend on the renamed table such as views, stored procedures, functions, 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 the 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)

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)

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?