Oracle synonym + dblink Application
Oracle synonym + dblink Application
Business Requirements: original database (10.2.0.4.0), new database (11.2.0.3)
Due to program requirements, you need to map a user in the new database to the user in the old database:
1. Users who back up the original database
Nohup exp scott/scott OWNER = scott BUFFER = 10240000 STATISTICS = none RESUMABLE = y FILE = scott_all_exp.dmp LOG = scott_all_exp.log &
2. Delete the table under the user of the original database
Set linesize 180 pagesize 1000
SELECT 'drop table' | table_name | 'cascade constraints purge; 'FROM user_tables;
Run the SQL command vi to save it to droptable. SQL.
Then SQL> @ droptable
3. Create the dblink to the new database on the original database.
Create public database link link167 connect to scott identified by scott using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.167) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = NEWDB)
)
)';
Test dblink availability:
Select * from dual @ link167;
4. Create a synonym for the original database
Create synonym MDRT_12E92 $ for MDRT_12E92 $ @ link167;
Query user_tables in the new database to obtain the table_name list,
Set pagesize 1500
Select table_name from user_tables;
The command is edited using the UE column, and vi is saved to createsynonym. SQL.
Then SQL> @ createsynonym