How will data of schema will reflect in another schema of same network

[sumanta123]

Dear All,
I have two schema in my network.Let say one is AMS and Another is AMSDATA.
Both contains same table.Let say both the schema have employee table.
But the schema of AMSDATA's employee table data will reflect in AMS
schema of Emplyee table.

Means when i execute the procedure the schema of AMSDATA's employee table data will always updated in schema of AMS's employee table data.


Could please guide me for my requiremnts query of simple example so that i can implement in my project.


Thanks in Advance.
Regards
Sumanta Panda

----------------------------------------------------------
AMS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.106.87)(PORT = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = AMS)
)
)

AMSDATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.106.88)(PORT = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = AMSDATA)
)
)

[amitpatel66]

Alright, You need to do the following:

1. If your both the Schemas are in same database, then You need to GRANT SELECT,UPDATE,INSERT on EMPLOYEE table of AMS to AMSDATA schema.

2. Write a Trigger AFTER INSERT ON employee ON AMSDATA,
<Insert into Employee of AMS schema>

OR

If AMS and AMSDATA belongs to different database, then,

1. Create a DATABASE LINK in AMSDATA pointing to AMS schema of another database
2. Create a Trigger in AMSDATA for AFTER INSERT and INSERT into employee of AMS schema any new record inserted in AMSDATA's EMployee table

I hope this helps!!

[sumanta123]

Dear AmitSir,
Thanks for your support.Sir second point i am agree to manage it.

Please sir advise me how can i Create a DATABASE LINK in AMSDATA pointing to AMS schema of another database.

Sir, i dont have enough knowledge in oracle.
Could you please guide me in the sysntax how can i write for it.

Thanks and Regards
Sumanta Panda

[debasisdas]

You can find the details here.

[sumanta123]

Thanks a ton Debasis Sir and Amir Sir for your support and cooperation.