Shulou(Shulou.com)06/01 Report--

Database link in oracle is an object that defines the path from one database to another. Database link allows you to query remote tables and execute remote programs. In any distributed environment, dblink is necessary, and note that database link is an one-way connection. When creating database link, oracle stores the relevant database link information in the data dictionary. When using database link, oracle accesses the corresponding remote database through the connection information pre-defined by oracle net users to complete the corresponding work.

1. Before establishing database link, you should pay attention to:

(1) confirm that the network connection from local database to remote database is normal, and tnsping must be successful.

(2) confirm that you have the appropriate access rights on remote database.

2. Oracle database link can be divided into the following three categories:

(1) private: a user-level dblink is created. Only the user who created the dblink can use this dblink to access the remote database, and only the user can delete the dblink.

(2) public: create a database-level dblink, which can be used by all users in the local database who have access to the database or pl/sql programs.

(3) global: create a network-level dblink, which is for oracle network.

3. The permissions required to create a dblink:

If you create a new user, you must grant the following permissions to it to create dblink:create database link, create public database link, and create session.

4. Create a dblink:

Here) folding or opening expdp scott/tiger@172.17.146.3/orcl here the user of my dblink is the remote database, and the corresponding user is lyznetwork_link=dl_OCPLYZ1. Because the users of my local database and the remote database are different, we need to add a prefix: lyz.xxt, otherwise the database will not recognize where you come from.

TABLES=lyz.TESTA then gets the following error: scott.testa not found

In other words, if you don't add the user source: lyz.testa table, the database should be yours by default.

TABLES=TESTA is from the scott table, and the scott table does not have this table, of course, it is wrong. That's why I kept reporting this mistake because I didn't write this before.

Of course, if your target database (local database) has this lyz user, we can also write:

Then we can pour it into the library: the database to be poured here is: oracl

Since the above is table-based export, we can use the following command:

Impdp scott/tiger@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=exptable.dmp TABLES=lyz.TESTA REMAP_SCHEMA=lyz:scott table_exists_action=REPLACE

What it means here is: if impdp is poured into the user under the oracl library, the path is DATA_PUMP_DIR, the dmp file is exptable.dmp, the table is TABLES=lyz.TESTA, REMAP_SCHEMA=lyz:scott: this means from lyz user to scott user, table_exists_action=REPLACE: if the table exists, replace it directly.

Or based on the user's input: remove the TABLES=lyz.TESTA, but also from lyz users to scott users

Impdp scott/tiger@orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=exptable.dmp REMAP_SCHEMA=lyz:scott table_exists_action=REPLACE

OK, this is the use of db_link 's expdp, impdp's export and import.

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.