Home » SQL & PL/SQL » SQL & PL/SQL » replicate table in the same server (10G R/2)
replicate table in the same server [message #341672] Tue, 19 August 2008 22:24 Go to next message
TJPokala
Messages: 17
Registered: March 2008
Junior Member
Hi all,

I have a table A at schema A which gets updated every week.

I would liek to replicate this table within thesame server so this table A is not touched and that manipulation will be done in the replicated table.

Will trigger help me do this?


create or replace trigger a_replica
before insert or update on a
for each row
begin
DELETE FROM b;
INSERT INTO  b
SELECT * FROM A;
END;



-I do not want a snapshot because the main reason is to dml on the replicated table.
-this trigger should be able to copy tables synchronously in real time

This is what I think the framework should be:


CREATE OR REPLACE TRIGGER A_replica
AFTER INSERT ON A FOR EACH ROW BEGIN
IF ????(--create a sequence here?,how to identify new columns)THEN
INSERT INTO B 
SELECT * FROM A ;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (-20001, sqlcode() || ': ' ||sqlerrm());
END;
/ 


Any advise?
Re: replicate table in the same server [message #341673 is a reply to message #341672] Tue, 19 August 2008 22:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

I suspect that you have limited to NO experience with triggers.
The proposed code will generate the infamous mutating table error

What is the business justification for these requirements?

I suggest that you have either reliable backups & restoration procedures or test in a database where you don't care about the data.
Re: replicate table in the same server [message #341674 is a reply to message #341672] Tue, 19 August 2008 22:45 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Hi,

Need more information about the case,

1) whether this main table and another table would be in the same schema or different schema?

2) You can use trigger, but as you demonstrate, why you are deleting all rows from table and then insterting all the content from main table?
You are using row level triiger, so you can insert just the newly inserted row using :new.columnname clause.

3) If that table will going to be used just as a lookup table, then instade of creating it and filling it each time, you can create a synonym of main table and use it.

4) Other opetions for doing it is streams or materialized view, aur just simple view ( you need to pick correct one according the requirement).

Regards,
Dipali..

[Updated on: Tue, 19 August 2008 22:46]

Report message to a moderator

Re: replicate table in the same server [message #341681 is a reply to message #341674] Tue, 19 August 2008 23:25 Go to previous message
TJPokala
Messages: 17
Registered: March 2008
Junior Member
Hi Dipali thank you for your input.

And apologize if this question to be amateur and no directions. But again I thought this was where you can ask.Sorry about that.
Previous Topic: Need Assistance
Next Topic: Getting ORA-03114: not connected to ORACLE error
Goto Forum:
  


Current Time: Tue Dec 03 08:34:29 CST 2024