| Rename or switching the table name [message #411068] |
Wed, 01 July 2009 15:27  |
ctbalamurali
Messages: 11
Registered: June 2009
|
Junior Member |
|
|
Hi we have two source and destination with exact structure. I need to transfer data from source to destination and truncate the source table for doing it I need to do following steps
1. Switch the table name (I need to know how to handle index and need to know sample code)
a. Rename Dest to Dest_Old
b. Rename Source to Dest
c. Rename Dest_old to Source
Here I'm facing problem in renaming the foreign key contraints.
2. truncate the source table.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Rename or switching the table name [message #411214 is a reply to message #411211] |
Thu, 02 July 2009 08:39  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here's something I had kicking about to rename all the constraints.
Modifying this to do indexes instead shouldn't be too taxing
BEGIN
for rec in (select table_name
,constraint_name
from user_constraints
where table_name = '<old table name>') loop
execute immediate 'ALTER TABLE '|| rec.table_name||' RENAME CONSTRAINT '||rec.constraint_name||
' TO '||<function to generate new constraint name>;
end loop;
END;
/ |
|
|
|
| Re: Rename or switching the table name [message #411787 is a reply to message #411101] |
Mon, 06 July 2009 14:13 |
ctbalamurali
Messages: 11
Registered: June 2009
|
Junior Member |
|
|
I put my question is different way in the PL/SQL procedure while switching the table name just foreign key is renamed along with table rename is that is enough or should I need to modify the foreign key to reference to different table. Eg., While renaming the original table to old right now we are renaming the foreign key alone. Here should I need to alter the foreign key to point to the old table also along with renaming the foreign key?
execute immediate 'alter table original rename to OLD';
execute immediate 'alter table EOB_REPORT_HEADER_OLD rename constraint original_PK to OLD_PK';
execute immediate 'alter index original_PK rename to OLD_PK';
execute immediate 'alter index original_FK rename to OLD_FK';
|
|
|
|
| Re: Rename or switching the table name [message #412028 is a reply to message #411787] |
Tue, 07 July 2009 08:06 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think you've got a bit of a misconception here - the NAME of an index or constraint is utterly irrelevant to its function.
Using a naming convention where the table name is part of the index/constraint is sensible, but not vital.
When you rename a table, the constraints that are on, or point to the table, and the indexes on the table will all be altered to point to the new table as part of the rename command - as you'd know if you'd tried and looked at user_constraints / user_indexes
|
|
|
|
| Re: Rename or switching the table name [message #412079 is a reply to message #412028] |
Tue, 07 July 2009 12:01 |
ctbalamurali
Messages: 11
Registered: June 2009
|
Junior Member |
|
|
Now I'm just trying to rename instead of index rename suggest as earlier. ButI'm facing problem while renaming the table loaded with data. Here I'm trying to swith the table names but while renaming the table data is not getting loaded. Any suggestion please
Initialy Bakup table wil have data, I'm trying to rename the Backup table to Original and Original table to Backup.
begin
DBMS_OUTPUT.put_line (
'Inside EOB_temp_header');
execute immediate 'alter table Original rename to OLD';
execute immediate 'alter table BAKUP rename to Original'; --Now Original table not having any data
execute immediate 'alter table OLD'rename to BAKUP';
--BAKUP table again have the data!!. But expectation is Original table to be loaded with data
END;
|
|
|
|
|
|
|
|
| Re: Rename or switching the table name [message #412093 is a reply to message #411068] |
Tue, 07 July 2009 14:49 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
In that case one of following must be true:
1) At the end the table now called original contains data.
2) At the start it is actually original that has data.
3) There are more steps to this process than you have listed.
How about posting a full copy and paste of a sqlplus session where you execute these steps along with count(*)'s from each table before and after.
|
|
|
|
| Re: Rename or switching the table name [message #412224 is a reply to message #412085] |
Wed, 08 July 2009 04:57 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
What you have posted does what you are looking for:create table original (col_1 number, col_2 number);
create table back_up (col_1 number, col_2 number);
insert into back_up values (1,2);
insert into back_up values (1,3);
select count(*) original_rows from original;
select count(*) back_up_rows from back_up;
alter table Original rename to OLD;
alter table back_up rename to Original;
alter table OLD rename to back_up;
select count(*) original_rows from original;
select count(*) back_up_rows from back_up; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
