Shulou(Shulou.com)06/01 Report--

EXPDP/IMPDP can only be run in the database server segment

Features:

Path upload and direct path loading technology are adopted to improve the execution efficiency.

You can import and export a large amount of data in parallel with multiple threads on the database server.

You can use DBMS_DATAPUMP PL/SQL API to set up, detect, and adjust database export and import tasks

Data pump technology has the ability to restart the job.

Note: the EXPDP utility and the EXP utility are incompatible

Create a directory object

Create a directory object using a CREATE DIRECTORY statement

Create or replace directory dumpdir as' xxxxxx'

Use the Grant statement to grant the user read and write permissions to the directory object.

Grant read,write on directory to hr

You can query the data dictionary view DBA_DIRECTORIES to view all the directory objects in the database

Select * from DBA_DIRECTORIES

Export mode

Full library export mode

Export the entire database as specified by FULL. The user needs to have the DATAPUMP_EXP_FULL_DATABASE role

Schema Export Schema-exports rows of object metadata from one or more schemas to a dump file

Specified by the parameter SCHEMAS, is the default export mode. Export all objects in the specified schema

Have DATAPUMP_EXP_FULL_DATABASE roles-you can specify a list of roles to export, including the definition of the export pattern and the system permissions that the pattern has

There is no DATAPUMP_EXP_FULL_DATABASE role-only the schema corresponding to the user can be exported. SYS schema objects cannot be exported, and referenced schema objects will not be exported if they are not explicitly stated in the export schema

Table export mode

All tables, partitions, and their dependent objects specified in the specified schema are exported through the parameter TABLES.

If the parameter TRANSPORTABLE=ALWAYS is specified, only object metadata is exported.

If the table to be exported does not belong to the current user mode, you need to have the DATAPUMP_EXP_FULL_DATABASE role.

Note: the data type definition of the column in the table will not be exported, so you need to have the data type definition of the column in the target database as well.

Objects in the reference mode are not exported.

Tablespace export schema-exports metadata and row data for all tables and their dependent objects of one or more tablespaces to a dump file

Export metadata and row data for all tables and their dependent objects in the specified tablespace specified by the parameter TABLESPACES

Transport tablespace export schema

Export metadata for all tables and their dependent objects in the specified tablespace specified by the parameter TRANSPORT_TABLESPACES

The data files contained in the tablespace need to be exported separately for replication.

When you import a transport tablespace, you need to import a dump file that contains tablespace metadata and specify the corresponding data file.

Once the export of the tablespace transfer mode is stopped, it cannot be restarted, and the tablespace transfer mode cannot be exported in parallel.

The export mode of the transport tablespace does not support the export of encrypted columns.

You cannot import exported transport tablespaces into earlier versions of data.

Command parameters-can be obtained using expdp help=y, too many do not want to enumerate

Interactive command Ctrl+C key combination enters interactive command mode commonly used add_file exit_client filesize help kill_job parallel start_job status stop_job example 1 Export employees table and departments table in hr mode The dump file is emp_dept.dmp, the log file name is emp_dept.log, the job name is emp_dept_job, and the export operation starts 3 processes (you can use the query parameter to set the export condition Know the qualified row and column data) expdp hr/hrdirectory=dumpdir tables=employees,departments dumpfile=emp_dept.dmp logfile=emp_dept.log jobname=emp_dept_job parallel=3 example 2 export hr.employees table employee information with department number greater than 50 and salary greater than 7000 yuan expdp hr/hrdirectory=dumpdir dumpfile=exp2.dmp tables=employees nologfile=y query='employees: "where department > 50 and salary > 7000" example 3 is exporting hr.employees,hr.departments Estimate the amount of space required before hr.locations data expdp hr/hr directory=dumpdir estimate_only=yes tables=employees,departments,locations logfile=estimate.log example 4 Export the meta-library of all objects in hr mode and row data expdp hr/hr directory=dumpdir dumpfile=hr.dmp logfile=hr.log schemas=hr job_name=exp_hr_schema example 5 Export example Metadata and row data of all tables and their dependent objects in the users tablespace expdp hr/hr directory=dumpdir dumpfile=tsp.dmp tablespaces=example,users example 6 Export all metadata in the example,users tablespace expdp hr/hr directory=dumpdir dumpfile=tts.dmp transport_tablespaces=example,users transport_full_check=y example 7 exports all the current data without writing to the log file expdp hr/hr directory=dumpdir dumpfile=expfull.dmp full=yes nologfile=yes

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.