Oracle Database Import and Export Functions

 

 

The import and export functions of Oracle databases are very powerful, which are summarized as follows:

 

1. Use the exp/IMP tool in PL/SQL to export/import Oracle Data

 

Importing and exporting Oracle Data imp/exp is equivalent to restoring and backing up ORACLE data. The exp command can export data from the remote database server to the local DMP file, and the IMP command can import the DMP file from the local to the distant database server.

This function can be used to build two identical databases, one for testing and the other for formal use.

 

Execution environment: it can be executed in sqlplus. EXE or DoS (command line,

In dos, the installation directory/ora81/bin in Oracle 8i is set to a global path,

The exp. EXE and imp. EXE files in this directory are used for import and export.

Oracle is written in Java. sqlplus. EXE, exp. EXE, and imp. EXE files may be packaged class files.

Sqlplus. EXE calls the classes encapsulated by exp. EXE and imp. EXE to complete the Import and Export function.

 

The following describes the Import and Export instances.

Data export:

(1) completely export the database test, and the username System Password Manager is exported to D:/daochu. dmp.

Must be a DBA to do full Database Export, that is, normal users cannot use the following command

Exp system/manager @ test file = D:/daochu. dmp full = y

(2) export the tables of system users and SYS users in the database

Non-DBAs may not export other users. Normal users can only export data of normal users.

Exp system/manager @ test file = D:/daochu. dmp owner = (system, sys)

(3) export the database tables inner_policy and policy_staff_relat.

Exp aichannel/aichannel @ testdb2 file = D:/data/newsmgnt. dmp tables = (inner_policy, policy_staff_relat)

(4) export the data with the field filed1 in table 1 in the database starting with "00"

Exp system/manager @ test file = D:/daochu. dmp tables = (Table1) query =/"where filed1 like '2016 '/"

 

The above is a commonly used export. For compression, you can use WinZip to compress the DMP file.

You can also add compress = Y to the command above.

Data Import

(1) import data from D:/daochu. dmp to the test database.

IMP system/manager @ test file = D:/daochu. dmp

IMP aichannel/aichannel @ HUST full = y file = D:/data/newsmgnt. dmp ignore = y

The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.

Add ignore = Y to the end.

(2) Import table 1 in D:/daochu. dmp

IMP system/manager @ test file = D:/daochu. dmp tables = (Table1)

 

The preceding import and export operations are sufficient. In many cases, you must first completely delete the table and then import it.

Note:

If the operator has sufficient permissions, a prompt is displayed.

Databases can be connected. You can use tnsping test to obtain whether the database test can be connected.

 

To import data, follow these steps:

First, start SQL * puls

Second, log in with system/Manager

Third, create user username identified by password (this step can be omitted if you have already created a user)

Fourth, grant create user, drop user, alter user, create any view,

Drop any view, exp_full_database, imp_full_database,

DBA, connect, resource, create session to Username

Fifth, run-cmd-to enter the directory where the DMP file is located,

IMP userid = system/manager full = y file = *. dmp

Or imp userid = system/manager full = y file = filename. dmp

 

 

Oracle cannot directly change the table owner. Export/Import can be used to achieve this purpose. The specific implementation method is to be discussed.

 

2. Import the DBF file:

(1) Java operations on DBF Files

Http://liulu.javaeye.com/blog/104452

(2) DBF viewer can open the DBF file, export it to a file in SQL, txt, HTML, XML, CSV, xls, and other formats, and contain the create statement. But I cannot find it online all the time.

Free registration code. Only 50 lines of records can be exported. If you want to find a registration code, please provide me with a copy. Thank you.

(3) DBF to SQL converter this software can export DBF Files into SQL or TXT files, see the address http://download.csdn.net/source/1544335

 

3. SQL File Import:

Run the doscommand to enter the directory of the file to be imported, and run the sqlplus command as follows:

Sqlplus username/pwd @ oraname <daochu. SQL> C:/log. Log

A log. log is generated after the log is successful. You can check the failure records based on this log.

 

4. Export query statements to txt, XML, HTML, and other files

After running the SELECT command using toad, click grid --> Save As to save the file selectively.

 

5. Back up data between two databases:

The links between databases are established on the database link. To create a DB link, you must first set the link string on each database server.

(1) The link string is the service name AAA. First, configure a service name locally. The address points to the remote database address. The service name will be the database chain name you will use in the future:

(2) create a database link

Log on to the system first and check whether the following parameter is true. If it is true, the Link name must be consistent with the remote service name.

Show parameter global_names

Use sqlplus user/pwd to log on to the target machine

Create public database link aaa_link connect to user identified by PWD using 'aaa ';

Command description:

Create public database link database connection name connect to user name identified by password using 'instance name of locally configured Data ';

If the creation is successful, the system will prompt: database link created

SQL> select * from EMP @ Database Link name;

If the data can be queried correctly, the link is successfully created. If a ORA-02085 error occurs, you can resolve it in any of the following ways:

1. Change the target's global_names parameter to false (/Oracle/admin/AAA/pfile/init. ora)

2. Set global_name to the same name as the database connection.

3. Delete the database connection and create a new database connection with the same name as the other instance.

(3) create synonyms. In order to make distributed operations more transparent, the Oracle database has the synonym object synonym.

SQL> Create synonym bjscottemp for EMP @ Database Link name;

Therefore, you can use bjscottemp to replace the Distributed Link operation EMP @ Database Link name with the @ symbol.

(4) view all database links, go to the system administrator SQL> operator, and run the following command:

SQL> select owner, object_name from dba_objects where object_type = 'database link ';

(5) use the following script to synchronize data tables:

#! /Bin/sh

# To sync Table A to BBB database from AAA Database

Sqlplus user/pwd @ BBB <EOF # Here is the database and password on BBB and the Instance name, Please modify according to the actual situation

Truncate Table;

INSERT INTO

Select * From B _schema.a @ aaa_link; # The source table to be synchronized. The table name must be <Table owner>. <Table Name >@< dblink Name>

Commit;