How will you rename a table ?

I have created new table say testTable

create table testTable

(

    id number

);

Renamed that to RenameTestTable

rename testTable to RenamedTestTable

When u try to select data from original table, it gives error as table does not exists.

select *

from TestTable

When you select data from renamed table, command gets succeeded.

select *

from RenamedTestTable

Renaming works even if table has the data

drop table RenamedTestTable

create table testTable

(

    id number

);

insert into testTable values(1);

rename testTable to RenamedTestTable

select *

from RenamedTestTable

Now the question is, what happen if table has

1. Index defined on it

2. Table has synonym defined in another schema

3. Table is getting used in Function or Procedure

4. Table is defined in view query.

We will test these scenarios one by one.

1. If table has an index defined on it.....

drop table RenamedTestTable;

create table testTable

(

    id number

);

create unique index IdxTestTable

on testTable(id) ;

select *

from user_ind_columns

where index_name = upper('IdxTestTable');

insert into testTable values(1);

rename testTable to RenamedTestTable;

select *

from user_ind_columns

where index_name = upper('IdxTestTable');

select *

from RenamedTestTable;

So, if index is defined on a table and you are renaming that particular table, nothing will happen to index. At metedata view of index, table name entry  will get change simply.

2. If table has a synonym defined ...

I logged in as sys user/schema and created synonym as below

select *

from hr.testTable

create synonym testTable for hr.testTable

select *

from testTable

select *

from all_synonyms

where synonym_name = upper('testTable')

When I re-named a table at hr schema, and fired select statement at sys; got an error as "ORA-00980: synonym translation is no longer valid".

As synonym is created in sys schema, there is no changed in synonym entry.

3. If table is getting used in a procedure/function ....

I created table and procedure as below

Table Creation :

drop table RenamedTestTable

create table testTable

(

    id number

);

create unique index IdxTestTable

on testTable(id) ;

Procedure Creation :

create procedure TableTest

as

    vCount number;

begin

    select count(*) into vCount

    from testTable;

   

    dbms_output.put_line('Record Count Is ==> ' || vCount);

end;

Note: if ur using toad, just select whole code and press F5, procedure will get compile

Also, note here, procedure and table names are same. It allows because both are different db objects. I might not able to create table or proedure with same name

Running a procedure:

begin

TableTest;

end;

make your dbms_output on at table. If you are using sql *, then type "setserveroutput on".

 You will get 0 as a result.

select *

from all_procedures

where  owner = 'HR'

If you check that proc at all_procedures, amazingly proc name is null and object name it displays as proc name. Not sure how.

select *

from all_objects

where owner = 'HR'

and object_name like 'T%'

If you check that in all_objects, it shows correctly - object type as table different and object type as procedure different. and status of table and procedure is valid.

Now I will rename the table.

rename testTable to RenamedTestTable

fired the below query

select *

from all_objects

where owner = 'HR'

and object_name like 'T%'

Tables vanished from result set and procedures status became InValid.

Just try to run the proc...

begin

TableTest;

end;

Note: to run the proc at toad, just select the above code and do Ctr+Enter

Got an error as below

ORA-06550: line 3, column 1:

PLS-00905: object HR.TABLETEST is invalid

ORA-06550: line 3, column 1:

PL/SQL: Statement ignored

Here, object tabletest is reffered as procedure and not a table, because tabletest table object is no longer exists.

4. If table is getting used in a view ...

drop table RenamedTestTable

create table testTable

(

    id number

);

create unique index IdxTestTable

on testTable(id)

create view testTableView

as

select * from testTable

select *

from all_views

where view_name = upper('testTableView')

select *

from all_objects

where object_name = upper('testTableView')

View as an object is with valid status.

Now, I will rename the table

rename testTable to RenamedTestTable

Now, view became invalid

select *

from all_objects

where object_name = upper('testTableView')

I will try to select from view now

select *

from testTableView;

It gave error as [ORA-04063: view "HR.TESTTABLEVIEW" has errors]