Oracle RENAME TABLE

RENAME table statement is used to rename an existing table in Oracle database.

Syntax:

RENAME existing_table_name TO new_table_name

Here,

existing_table_name – It is the name of table that already exists in the Oracle database.

new_table_name – It is new name of table that we want to give which must meet naming standards. It should also not be name that’s already taken by a table existing in the Oracle database. A table once renamed can’t be rolled back to previous name using ROLLBACK command.

Once a table is renamed – indexes, grants and constraints are transferred to the new table while the objects like stored procedures, views, functions and synonyms become invalid.

Oracle RENAME table Examples

Let’s create a new sample table for demonstration.

CREATE TABLE students(
    student_id NUMBER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    joining_date DATE NOT NULL,
    subject_name VARCHAR(30) NOT NULL,
    teacher_id NUMBER NOT NULL,

    CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) 
    REFERENCES teachers(teacher_id) ON DELETE CASCADE
);

Now, let’s rename this table to students_new:

RENAME students TO students_new;

In the background Oracle will transfer grants, indexes and constraints to the newly renamed table. At the same time, all the objects like views and stored procedures which were using the old name of table will become invalid.

If our old table has any such objects associated with it that are now invalid, we can view them by querying data from the all_objects view. Let’s have a look:

SELECT
    owner,
    object_type,
    object_name
FROM
    all_objects
WHERE
    status = 'INVALID'
ORDER BY
    object_type,
    object_name;

If we know the name of object or function, we can check if its invalid or not.

In this article, we understood how Oracle RENAME TABLE statement works.