Oracle import and export, oracle Import and Export commands

Source: Internet
Author: User
Tags directory create

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.