Powered By Blogger

Wednesday, June 6, 2012

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]

No comments: