Hi all,
I have just been assigned a task to migrate a set of SQL scripts to a new server while also cleaning and documenting the scripts. Before they had all of the script run being done on the production server and the new move is to a reporting server that reads data from tables on the active standby. As a result, we have three servers in the mix: Production [P], Active Standby [AS], and Reporting [R]. [P] feeds data to [AS], and [R] pulls data from [AS] via dblinks.
My question is this: The code needs to be updated to reflect the new locations, but I want to make sure that I am going to go about it smartly. From what I can tell, I am debating between two approaches:
- reference "
schema.tablename@dblink" everywhere in the code - create synonyms (public or private) "
create synonym tablename for schema.tablename@dblink"
Any recommendations?
Also, a complication that I think I just realized is that the db links are to individual schema (These were set up by the actual DBA, which is not part of my organization. I do not have permission to create or alter these, so I have to request them. Same for synonyms). For example, the code looks like:
create public database link OBJ_OWNER_AS.WORLD
connect to OBJ_OWNER
using 'RQDTCP01_VEGAS';
and another to...
create public database link CLL_OWNER_AS.WORLD
connect to CLL_OWNER
using 'RQDTCP01_VEGAS';
Would it be better/possible to connect to just the database itself so that I can reference tables in different schema (obj_owner.objects@dblink and cll_owner.calls@dblink) instead of having two different links?
Thanks for your advice. I have been putting this off for a little bit because I am unsure what the best way to do this without blowing up my future workload is...
N