To create a logical directory, the command does not create a real directory in the operating system, preferably created by an administrator such as system.
Create directory Dpdata1 as ' D:\test\dump ';
Second, view the Management Manager directory (and see if the operating system exists, because Oracle does not care if the directory exists, and if it does not exist, an error occurs)
select * from Dba_directories;
third, give the Scott user permissions to operate in the specified directory, preferably by a system administrator.
Grant Read,write on directory dpdata1 to Scott;
Iv. export of data
1)
by user Guide
EXPDP scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;
2)
Parallel Processes
Parallel
EXPDP scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3)
Guided by table name
EXPDP scott/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=dpdata1;
4)
guided by query criteria
EXPDP Scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp tables=emp query= ' WHERE deptno=20 ';
5)
Guided by table space
EXPDP system/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=temp,example;
6)
Guide the entire database
EXPDP system/manager directory=dpdata1 dumpfile=full.dmp full=y;
Five, restore data
1)
leads to the specified user
IMPDP Scott/tiger directory=dpdata1 dumpfile=expdp.dmp Schemas=scott;
2)
Change the table
Owner
IMPDP system/manager directory=dpdata1 dumpfile=expdp.dmp tables=scott.dept remap_schema=scott:system;
3)
Import Table Space
IMPDP system/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=example;
4)
Import Database
impdb system/manager directory=dump_dir dumpfile=full.dmp full=y;
5)
Append Data
IMPDP system/manager directory=dpdata1 dumpfile=expdp.dmp schemas=system table_exists_action=append;----------------------------related parameters of EXPDP/IMPDP----------------------------
EXPDP
command-line Options
1. ATTACH
This option is used to establish an association between a client session and an existing export. The syntax is as follows
Attach=[schema_name.] Job_name
Schema_nameused to specify the scheme name, job_name is used to specify the export job name. Note that if you use the Attach option, you cannot specify any other options except the connection string and the Attach option in the command line, such as the following:
EXPDP Scott/tiger Attach=scott.export_job
2. CONTENT
This option is used to specify what to export. The default value isAll
Content={all | data_only | Metadata_only}
When content is set to all, the object definition and all its data are exported. When data_only, only the object data is exported, and only the object definition is exported as Metadata_only
EXPDP Scott/tiger directory=dump dumpfile=a.dump
Content=metadata_only
3. DIRECTORY
Specify the directory where the dump files and log files reside
Directory=directory_object
Directory_objectused to specify the directory object name. Note that directory objects are objects created using the Create DIRECTORY statement, not the OS directory
EXPDP Scott/tiger Directory=dump Dumpfile=a.dump
Create a directory:
CREATE DIRECTORY dump as ' d:dump ';
The query created those subdirectories:
SELECT * from Dba_directories;
4. DumpFile
used to specify the name of the dump file, the default name isexpdat.dmp
Dumpfile=[directory_object:]file_name [,....]
The directory_object is used to specify the directory object name, and file_name is used to specify the dump file name. Note that if you do not specify Directory_object, the export tool automatically uses the directory object specified by the directory option
EXPDP Scott/tiger directory=dump1 dumpfile=dump2:a.dmp
5. ESTIMATE
Specifies the method by which to estimate the disk space consumed by the exported table. The default value isBLOCKS
Extimate={blocks | STATISTICS}
When set to blocks, Oracle calculates the object footprint based on recent statistics by multiplying the number of data blocks occupied by the target object by the size of the data block to estimate the amount of space the object occupies, when set to statistics
EXPDP Scott/tiger tables=emp estimate=statistics
Directory=dump Dumpfile=a.dump
6. extimate_only
Specifies whether to estimate only the disk space occupied by the export job, the default value isN
Extimate_only={y | N
When set to Y, the export only estimates the disk space occupied by the object, not the export job, which, when N, not only estimates the disk space occupied by the object, but also performs an export operation.
EXPDP Scott/tiger estimate_only=y Nologfile=y
7. EXCLUDE
This option is used to specify whether to dispose of object types or related objects when an operation is performed
Exclude=object_type[:name_clause] [,....]
Object_typeused to specify the type of object to exclude, Name_clause is used to specify the specific object to exclude. Exclude and include cannot be used at the same time
EXPDP scott/tiger directory=dump dumpfile=a.dup Exclude=view
8. FILESIZE
Specifies the maximum size of the exported file, which defaults to 0, which means there is no limit on file size)
9. Flashback_scn
Specify table data to export specific SCN moments
Flashback_scn=scn_value
Scn_valueused to identify the SCN value. FLASHBACK_SCN and Flashback_time cannot be used simultaneously
EXPDP Scott/tiger directory=dump dumpfile=a.dmp
flashback_scn=358523
Ten. Flashback_time
Specify the export of table data at a specific point in time
flashback_time= "To_timestamp (time_value)"
EXPDP Scott/tiger directory=dump dumpfile=a.dmp flashback_time=
"To_timestamp (' 25-08-2004 14:35:00 ', ' dd-mm-yyyy HH24:MI:SS ')"One
. Full
Specifies the database schema export, which defaults toN
Full={y | N
Is Y, the identity performs a database export.
Help
Specifies whether to display help information for the EXPDP command-line option, which defaults toN
When set to Y, the Help information for export options is displayed.
EXPDP help=y
. INCLUDE
Specify the type of objects to include in the export and related objects
INCLUDE = Object_type[:name_clause] [,...]
job_name.
Specifies the name of the action to export, which defaults tosys_xxx
Job_name=jobname_string
LOGFILE.
Specifies the name of the exported log file file, the default name isExport.log
Logfile=[directory_object:]file_name
The directory_object is used to specify the directory object name, and file_name is used to specify the export log file name. If you do not specify Directory_object. Export function automatically uses the appropriate option values for your directory.
EXPDP Scott/tiger directory=dump dumpfile=a.dmp logfile=a.log
Network_link.
Specifies the database chain name, which must be set if you want to export a remote database object to a dump file of a local routine.
Nologfile.
This option specifies that the export log file is prohibited from being generated, and the default value isN.
PARALLEL.
Specifies the number of parallel processes that perform the export operation, with the default value1
Parfile.
Specify the name of the export parameter file
Parfile=[directory_path] file_name
. QUERY
used to specify where conditions to filter the exported data
Query=[schema.] [TABLE_NAME:] Query_clause
SchemaUsed to specify the scheme name, table_name is used to specify the table name, and Query_clause is used to specify a conditional restriction clause. The query option cannot be used in conjunction with options such as Connect=metadata_only,extimate_only,transport_tablespaces.
EXPDP Scott/tiger Directory=dump dumpfiel=a.dmp
Tables=emp query= ' WHERE deptno=20 '
SCHEMAS.
This scenario is used to specify the execution scheme pattern export, which defaults to the current user scenario.
STATUS
Specifies the detailed status of the export action process, with the default value of0
TABLES.
Specify table schema export
Tables=[schema_name.] table_name[:p artition_name][,...]
Schema_nameUsed to specify the schema name, table_name used to specify the name of the exported table, partition_name to specify the name of the partition to export.
tablespaces.
Specify the table space list to export
Transport_full_check.
This option is used to specify the check method for the associated relationship between the moved table space and the unchanged tablespace, which defaults toN.
When set to Y, the export checks the full association of the table space directly, and an error message is displayed if only one tablespace is moved in the table space where the tablespace or its index resides. When set to N, the export only checks for single-ended dependencies, if the table space of the index is removed, but the table space is not moved. An error message will be displayed, and if the table space for the move table is not moved, the table space for the index is not displayed..
transport_tablespaces.
Specify to perform table space mode export
VERSION
Specifies the database version of the object being exported, the default value isCOMPATIBLE.
version={compatible | LATEST | Version_string}
When compatible, the object metadata is generated based on the initialization parameter compatible, and the object metadata is generated based on the actual version of the database when latest. Version_string is used to specify the database version string. CallEXPDP
When using the EXPDP tool, its dump file can only be stored in the OS directory of the directory object, not directly specifying the OS directory where the dump file resides. So,
When you use the EXPDP tool, you must first establish a directory object. And you need to grant database users permission to use directory objects .-------------------------------------Application-------------------------------------Data Pump reflects a complete overhaul of the export/import process. Instead of using common SQL commands, the application-specific API (direct path API etc) is used to load and unload data at much faster speeds.
1.Data PumpExportEXPDP
Example:
sql>create directory dpdata1 as '/u02/dpdata1 ';
Sql>grant read, write on directory dpdata1 to Ananda;
$EXPDP ananda/abc123 tables=cases directory=dpdata1 dumpfile=expcases.dmp job_name=cases_export
$EXPDP ananda/abc123 tables=cases directory=dpdata1
dumpfile=expcases_%u.dmp parallel=4 Job_name=cases_export
Include/exclude Example:
include=table: "In (' DB ', ' TS ')"
orInclude=table: "Like '%e% '"
orinclude=function,package,procedure,table: "= ' EMP '"
orexclude=sequence,table: "In (' EMP ', ' DEPT ')"
2.Data Pump
Import
EXPDP
1)get the data source from EXPDPexp.dmp
2) Copy one schema from one database to another database.
3)Copy all the objects in one schema into another schema in the same database.
Example:
1) IMPDPThe data source is EXPDP-derived DMP file
IMPDP ananda/abc123 directory=dpdata1 dumpfile=expcases.dmp job_name=cases_import
2)copy a schema from one database to another database.
--1.newwork_linkDatabase_link established for the target database,
(user test requiresGrant Exp_full_database to TEST; )
Create Public database link Tolink
Connect to TEST identified by Oracle
Using ' (DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.20.199) (PORT = 1521))
)
(Connect_data =
(service_name = ORCL)
)
)‘;
--2.IMPDP on the target database server only low version to high versionImp
IMPDP Network_link=tolink schemas=test Remap_schema=test:link2
3) Copy all objects from one schema to another schema in the same database.
--1.
Create a connection to your own
Database Link:
Create public database link System_self connect to system identified by "system" using ' ORCL ';
The database link was created.
--2.
copy
HR schema
to
Test Schema:
IMPDP system/system network_link=system_self schemas=hr remap_schema=hr:test
EXPDP IMPDP Database Import and Export command detailed
