Oracle 11g database import and export:
① : traditional way – exp (export) and (IMP) import:
② : data pump mode — expdp export and（impdp）Import;
③ : the third party tool PL / SQL develpoer;
1、 What is database import and export?
The import / export of Oracle 11g database is what we usually call the restore / backup of Oracle data.
Database import: import. DMP format file from local to database server (local Oracle test database);
Database export: export the data in the database server (the data in the local Oracle test database) to the local generated. DMP format file.
. DMP format file: it is the file format of Oracle data (for example, video is. MP4 format, music is. MP3 format);
2、 Their advantages and disadvantages are described as follows
Advantages: code writing is simple and easy to understand, can be imported directly from the local, do not operate in the server, reduce the difficulty, reduce the operation on the server, also ensure the security of data files on the server.
Disadvantages: this kind of import and export speed is relatively slow, suitable for less database data. If the file is more than a few gigabytes, it will take at least 4-5 hours for a Volkswagen performance computer.
Advantages: the import and export speed is relatively fast, several G data files are generally about 1 ~ 2 hours.
Disadvantages: the code is relatively difficult to understand. In order to implement the import and export operation, you must create a logical directory (not a real directory) on the server. We all know the importance of database server, so we must be careful in the above operation. Therefore, this method is generally completed by professional programmers (not necessarily DBAs, small and medium-sized companies may not have DBAs).
Advantages: encapsulates the import and export commands, so there is no need to manually input commands every time. Convenient and efficient.
Disadvantages: long time application will rely on it, reducing the understanding of the principle of code execution.
3、 Special emphasis on:
Target database: the database to be imported (usually the formal database on the project);
Source database: the database of data export (generally the test database on the project);
1. The target database should have the same table space as the source database.
2. When importing the target data, the user name should be the same as far as possible (so that the user’s permission level is the same).
3. Before importing data into the target database, data backup should be done to prevent data loss.
4. When using the data pump, it is necessary to establish the available logical directory on the server and check whether it is available.
5. Find out whether to import or export to the same version or different versions (Oracle10g version and Oracle11g version).
6. Before importing the target data, make sure whether it is data coverage (replacement), or only insert new data or replace part of the data table.
7. Determine whether the disk space of the target database is enough to hold new data and whether the table space needs to be expanded.
8. Pay attention to whether the character set is the same when importing or exporting. Generally, there is only one character set in Oracle database, which is fixed and does not change.
9. Introduction to export format:
DMP format: DMP is a binary file, which can be cross platform and contain permissions, so it is efficient;
SQL format: files in. SQL format can be viewed with a text editor. They are more general and less efficient than the first one,
Suitable for small amount of data import and export. In particular, you should not have large fields (blob, CLOB, long) in the table. If there are, an error will be reported;
PDE format: file in. PDE format. PDE is a file format owned by PL / SQL developer and can only be used by PL / SQL developer tools
Import and export cannot be viewed with a text editor;
10. Confirm the operator’s account authority.
Forgive me for going
4、 The import and export methods are as follows
1. Traditional method:
Export of DMP file
First, let’s understand the export of DMP file
There are three kinds of DMP file export: export all the data of the whole database instance, export all the tables of the specified user and export the specified table
Let’s take our own database as an example and assume that the instance of our database is “oracle”
You can view your own database instance through “task manager —– > service”
The general information is enabled Automatic local system
Open the CMD command line :
1: Export the database Oracle completely, and export the user name system and password manager to C: / / daochu.dmp
exp system/[email protected] file=c:\daochu.dmp full=y
2: Export the tables of RFD users and jyzgcx users in the database
exp system/[email protected] file=d:\daochu.dmp owner=(RFD,JYZGCX)
3: Set the table t in the database_ USER,T_ Role export
ystemIs the user name,managerIs the password,ORACLEFor the database instance name, in fact, it does not have to be usedsystemUsers, as long as the user has administrator rights can
exp JYZGCX/[email protected] file = d:\data\newsmgnt.dmp tables = (T_USER,T_ROLE)
Import of DMP file
The steps are as follows:
The machine environment is Windows 7. It doesn’t matter. Isn’t the command line the same
1. Open “start” —- enter CMD, open the CMD command window, enter: sqlplus / as SYSDBA; Then use conn / as SYSDBA; In this way, you can log in with the highest authority of super administrator, which depends on the database initialization parameter in init.ora file
2. After the above SYSDBA logs in, the table space and user can be created
(open “start” – > Enter CMD — > sqlplus / nolog; Enter conn / as SYSDBA administrator account to login;)
Since we already have a DMP file, we can use Notepad + + to open the DMP file, press Ctrl + F to find the table space corresponding to the DMP file, and then create the table space according to the table space information, so as to import the DMP file
Then create a table space. The command is as follows:
create tablespace USERS logging datefile 'D:\oracle\product.2.0\oradata\orcl\USERS.dbf' size 32m autoxtend on next 32m maxsize 2048m extend management local;
Create a test user, and the password is also test222. Use the table space created above
create user test identifiles by test222 default tablespace USERS
Assign permissions to the created test users. For convenience, you can directly assign DBA permissions
grant dba to test;
In this way, our preliminary preparation work is completed, and then we can close the command window just now
Open start — enter CMD (it is a CMD window, not a sqlplus window)
Since the test user was created in the above steps, we import data to the test user
Enter the following statement directly:
imp test/[email protected]/orcl file="C:\Users\xiejiachen\Desktop\test20190630.DMP" full =y;
Let’s explain the above sentence
Test is the user name of the login database created above
Test222 is the password for the login database above
Localhost: represents whether your database is imported locally or remotely. If necessary, you can replace the IP address at any time
Orcl: is the name of the instance
File: the following is the file path of your DMP
full=y : Import all
These are the two methods of Oracle database export and import DMP file
2. Data pump method:
expdp(impdp) username/[email protected]:1521 schemas=username dumpfile=file1.dmp logfile=file1.log directory=testdata1 remap_schema=test:test;
Database export example:
expdp xinxiaoyong/[email protected]:1521 schemas=xinxiaoyong dumpfile=test.dmp logfile=test.log directory=testdata1;
Exp: export command. It must be written when exporting.
Imp: Import command. It must be written when importing. Only one of them can be selected for each operation.
User name: the user name of the exported data; required;
Password: the password of the exported data, which must be written;
@: address symbol, required;
Servicename: Oracle service name, required;
1521: port number, 1521 is the default, can not be written, non default to write;
Schemas: the user name of the export operation;
Dumpfile: exported file;
Logfile: the exported log file, which can not be written;
Directory: the name of the created folder;
remap_ Schema = user name of source database: user name of target database. They are not required to be written at the same time, but the same can be omitted;
1. View the table space:
select ＊ from dba_tablespaces;
2. Check the manager directory (at the same time, check whether the operating system exists, because Oracle does not care whether the directory exists, if it does not exist, an error will occur).
select ＊ from dba_directories;
3. Create a logical directory. This command will not create a real directory in the operating system. It is better to create a logical directory by system administrator.
create directory testdata1 as 'd:\test\dump';
4. To give xinxiaoyong users the operation permission in the specified directory, it is better to give it to system and other administrators.
//Xinxiaoyong is the user name (123456 is the user password)
grant read,write on directory testdata1 to xinxiaoyong;
5. Export data
1) Guide by user
expdp xinxiaoyong/[email protected] schemas=xinxiaoyong dumpfile=expdp.dmp directory=testdata1;
2) Parallel process
parallel expdp xinxiaoyong/[email protected] directory=testdata1 dumpfile=xinxiaoyong3.dmp parallel=40 job_name=xinxiaoyong3
3) Guide by table name
expdp xinxiaoyong/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=testdata1;
4) Export by query condition
expdp xinxiaoyong/[email protected] directory=testdata1 dumpfile=expdp.dmp tables=emp query='WHERE deptno=20';
5) Export by table space
expdp system/manager directory=testdata1 dumpfile=tablespace.dmp tablespaces=temp,example;
6) Guide the whole database
expdp system/manager directory=testdata1 dumpfile=full.dmp FULL=y;
6. Restore data
1) Import to the specified user
impdp xinxiaoyong/123456 directory=testdata1 dumpfile=expdp.dmp schemas=xinxiaoyong;
2) Change the table
owner impdp system/manager directory=testdata1 dumpfile=expdp.dmp tables=xinxiaoyong.dept remap_schema =xinxiaoyong:system;
3) Import table space
impdp system/manager directory=testdata1 dumpfile=tablespace.dmp tablespaces=example;
4) Import database
impdb system/manager directory=dump_dir dumpfile=full.dmp FULL=y;
5) Additional data
impdp system/manager directory=testdata1 dumpfile=expdp.dmp schemas=system table_exists_action;
3. PLSQL method:
Log in to PLSQL tool, and the user is the source database with export permission (Exp_ full_ Database, DBA, etc.).
1. Export table building statement (including storage structure)
Export step Tools > export user object, select the object to export, export the. SQL format file and wait for the export to complete, as shown in the following figure:
Export data file;
2. Export steps Tools > export tables, select the table to export and the format to export.
Export to DMP format, as shown in the following figure:
Export to SQL format, as shown in the following figure:
Export to PDE format, as shown in the following figure:
Note: using a third-party tool to export and import the entire database will take a long time, and it must be sufficient
It takes a lot of time to operate.
3. Import table building statements
import steps Tools > Import tables > sql inserts import. SQL file
4. Import data;
Tools > Import talbes, and then choose to import DMP file or SQL file according to the exported data format,
Or PDE file.
note: it’s better to delete the previous table before importing, except for importing another database.
If there is no response, do not think that the program is stuck. The import and export is slow, as long as there is no error,
Or do not stop the program when the import is complete.