Shulou(Shulou.com)05/31 Report--

How is dblink used in Oracle? Many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can gain something.

Business requirements: original database (10.2.0.4.0), new database (11.2.0.3)

Due to the requirements of the program, it is now necessary to map a user on the new library directly to the old library user:

1. Users who back up the original library

Nohup exp scott/scott OWNER=scott BUFFER=10240000 STATISTICS=none RESUMABLE=y FILE=scott_all_exp.dmp LOG=scott_all_exp.log &

two。 Delete the table under the user of the original library

Set linesize 180 pagesize 1000 SELECT 'DROP TABLE' | | table_name | | 'CASCADE CONSTRAINTS PURGE;' FROM user_tables

The command to get SQL is saved to droptable.sql by vi.

Then SQL > @ droptable

3. Create a dblink to the new library on the original library

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 is available:

Select * from dual@link167

4. Create synonyms on the original library

Create synonym MDRT_12E92 $for MDRT_12E92 $@ link167

Query user_tables in the new library to get a list of table_name

Set pagesize 1500 select table_name from user_tables

The command is edited with UE column, and vi is saved to createsynonym.sql.

Then SQL > @ createsynonym

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.