Home » SQL & PL/SQL » SQL & PL/SQL » Rename or switching the table name (Oracle 10G)
Rename or switching the table name [message #411068] Wed, 01 July 2009 15:27 Go to next message
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 #411079 is a reply to message #411068] Wed, 01 July 2009 18:41 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

>Here I'm facing problem in renaming the foreign key contraints.

I don't know for sure where "here" actually is

No Operating System name or version
No Oracle version number (from v$version).
No error message (not your interpretation of the actual full and complete message).
No DDL.
No DML.
No expected results.
No help is possible at this time.
Re: Rename or switching the table name [message #411101 is a reply to message #411068] Wed, 01 July 2009 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Here I'm facing problem in renaming the foreign key contraints.

Why do you care about these names?

SQL Reference
ALTER TABLE
Renaming Constraints: Example

Regards
Michel

[Updated on: Wed, 01 July 2009 23:13]

Report message to a moderator

Re: Rename or switching the table name [message #411104 is a reply to message #411068] Wed, 01 July 2009 23:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ctbalamurali wrote on Wed, 01 July 2009 22:27

b. Rename Source to Dest
[...]
2. truncate the source table.

Somehow I doubt that step 2 is required... Maybe you should read your requirements again
Re: Rename or switching the table name [message #411211 is a reply to message #411104] Thu, 02 July 2009 08:09 Go to previous messageGo to next message
ctbalamurali
Messages: 11
Registered: June 2009
Junior Member
Thanks for reply,

This data load will happen montly once so I need to truncate the table from 2nd month data load process while switching the table name.
Re: Rename or switching the table name [message #411214 is a reply to message #411211] Thu, 02 July 2009 08:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #412080 is a reply to message #411068] Tue, 07 July 2009 12:05 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
So, at the start, before you do any renaming, which tables have data and which are empty?
Re: Rename or switching the table name [message #412085 is a reply to message #412080] Tue, 07 July 2009 12:54 Go to previous messageGo to next message
ctbalamurali
Messages: 11
Registered: June 2009
Junior Member
Thanks for the response,

At start before renaming - Backup table will holds data and Original will be empty.

Expeceted after Table rename is - backup Table to be empty and Original to be loaded with data
Re: Rename or switching the table name [message #412093 is a reply to message #411068] Tue, 07 July 2009 14:49 Go to previous messageGo to next message
cookiemonster
Messages: 12412
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 Go to previous message
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;
Previous Topic: Linkage problem when summing values in 2 tables.(merged)
Next Topic: Compilation error PLS-00410: duplicate fields in RECORD,TABLE or argument
Goto Forum:
  


Current Time: Wed Dec 07 09:00:02 CST 2016

Total time taken to generate the page: 0.13570 seconds