Please let me know what are the stored procedures will need to add a linked server. Thank you.
asked
Which stored procedure will we be running to add a linked server?
There are no stored procedures to do what you're asking - this would be done with regular DDL (Data Definition Language) as follows:
CREATE DATABASE LINK my_link_name
CONNECT TO myusername IDENTIFIED BY mypassword
USING 'local';
where "local" is the tnsentry in your tnsnames.ora file used to connect to the remote database. You can find the complete syntax here: http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_5005.htm
Of course there's nothing stopping you from performing those DDL statements from within a stored procedure using the EXECUTE IMMEDIATE syntax described in the online docs (sorry the message board is preventing me from posting > 1 hyperlink)
Once your link is created you can run queries like:
select count(*) from mytable@my_link_name
