You need to import data from the table GIS_WEICHAI_DATA_1S in a database to the table GIS_WEICHAI_DATA_1S in another database. Next, you can explain how to query and insert data between different databases. For more information, see
You need to import data from the table GIS_WEICHAI_DATA_1S in a database to the table GIS_WEICHAI_DATA_1S in another database. Next, you can explain how to query and insert data between different databases. For more information, see
During work, you need to import data from the GIS_WEICHAI_DATA_1S table in a database to the GIS_WEICHAI_DATA_1S table in another database. The database servers are all remote ! My implementation method is to use PL/SQL locally to operate two remote servers. The implementation method is as follows:
1.
Create a local service name for the remote database server you want to operate on:
In the local database installation file, find the $ ORACLE_HOME/network/admin/tnsnames. ora file,
Add at the end
The Code is as follows:
-- Service name of the first remote server: MYORACLE1
MYORACLE1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 221.131.228.256) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
-- Service name of the first remote server: MYORACLE2
MYORACLE2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 211.161.192.46) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
-- If more remote databases need to be operated at the same time, you can add them in sequence!
-- If you are using a Windows operating system, you can use the Net Manager tool that comes with Oracle to create a service name in a graphical way!
2.
Log on to the local database using the sysdba role on the local machine and create the database link:
Run the following SQL statement:
The Code is as follows:
-- Corresponding database link of Remote Server 1
Create public database link MYDBLINK1 -- you can name it at will. Of course, it cannot be a keyword or a reserved word.
Connect to dbUserName1 identified by dbpwd1
Using 'myoracle1 ';
-- Database link of Remote Server 2
Create public database link MYDBLINK2 -- can be named at will. Of course, it cannot be a keyword or a reserved word.
Connect to dbUserName2 identified by dbpwd2
Using 'myoracle2 ';
-- Enter the Database Service name, dbUserName1, and dbpwd1 corresponding to the Database Service name and password after using.
-- Delete database link
Drop database link MYDBLINK1; -- in this example, MYDBLINK1 and MYDBLINK2
3.
To operate a table on the remote server, add @ linkName (corresponding database link name) to the corresponding table, which is similar to operating the table in the local database, data can be extracted from different database servers! Very convenient!
Insert into GIS_WEICHAI_DATA_1S @ MYDBLINK1 select * from GIS_WEICHAI_DATA_1S @ MYDBLINK2 where rownum <= 10000;
4.
If you need to frequently use a table on the remote server, the above statement is a bit annoying. At this time, you can consider creating a synonym for this table.
Create synonym syName for GIS_WEICHAI_DATA_1S @ MYDBLINK1;
You can use syName to use GIS_WEICHAI_DATA_1S @ MYDBLINK1 in the future!
Syntax for deleting Synonyms:
Drop synonym syName;
5.
View the DB Link of the current database;
The Code is as follows:
Select * from user_db_links; -- User DB Link
Select * from dba_db_links; -- dba DB Link
Select * from v $ dblink; -- current DB Link
