Oracle import and export, oracle Import and Export commands
Oracle 10g has introduced the Data Pump (Click Data Dump) technology. What are the advantages of this technology and the previous exp/imp? in simple words, It is very fast to restore and back up;
Before explaining how to use the data pump, let's first understand the differences between the two:
1. Introduction to Database Import and Export:
Currently, oracle provides two Import and Export Methods: imp/exp and impdp/expdp;
2. simple comparison between the two:
Command |
Command Applicability |
Efficiency |
Exported DMP file storage path |
IMP/EXP |
Server/client |
Low |
It is stored on the client when used on the client, and on the server when used on the server. |
IMPDP/EXPDP |
Server/client |
High |
Store only the server |
3. Example usernames and tablespaces are the same:
Imp user/password@127.0.0.1: 1521/orcl file = d: \ qis. dmp exp user/password@127.0.0.1: 1521/orcl file = d: \ qis. dmp full = y ignore = y
Create or replace directory impdp_dir as 'd: \ impdp_dir '; -- note that the physical drive letter also needs to create the corresponding directory create or replace directory expdp_dir as 'd: \ expdp_dir '; -- note that the physical drive letter also needs to create the corresponding directory grant read, write on directory impdp_dir, expdp_dir to user; impdp user/password@127.0.0.1: 1521/orcl directory = impdp_dir dumpfile = qis. dmp expdp user/password@127.0.0.1: 1521/orcl directory = expdp_dir dumpfile = qis. dmp
Different user names and tablespaces:
Impdp user/password@127.0.0.1: 1521/orcl directory = impdp_dir dumpfile = qis. dmp remap_schema = qis: cvqm remap_tablespace = qis: cvqm
Description: qis is the schema and tablespace of the dmp file to be imported.
4. Create Database tablespace
Create tablespace tablespace_name logging datafile 'd: \ app \ hwm \ oradata \ orcl \ QIS. DBF 'size 2048 m autoextend on next 100 m maxsize 10G extent-Limit the maximum growth of tablespaces to 10G management local;
5. User Creation and authorization
Create user user_name identified by password default tablespace tablespace_name; grant connect, dba, resource to user_name with admin option;
6. Batch Processing (. bat) it is very painful to repeat the command every time you back up the database. Fortunately, batch processing solves this problem for us, the following code is an example of using batch backup: store the following code in the txt text and change the text suffix to bat
@ Echo: the pause set Data_FileName = CVQM-% DATE :~ 0, 4% % DATE :~ 5, 2% % DATE :~ 8, 2%-% time :~ 0, 2% % time :~ 3,2% % time :~ 6, 2%. DMP set Log_FileName = CVQM-% DATE :~ 0, 4% % DATE :~ 5, 2% % DATE :~ 8, 2%-% time :~ 0, 2% % time :~ 3,2% % time :~ 6, 2%. LOG expdp cvqm/cvqm@127.0.0.1: 1521/orcl directory = expdp_dir schemas = cvqm dumpfile = % Data_FileName % logfile = % Log_FileName % @ echo Database Backup succeeded pause