When you use impdp, you will find that many Parameter options are different from imp. For example, you cannot find the fromuser and touser parameters, you cannot find the ignore parameters, and parameters such as indexes no longer exist.Of course, these functions are still available and more powerful. impdp has many new substitution parameters.The following three
, preferably by a system administrator.Grant Read,write on directory dpdata1 to Scott;Iv. Export of data1) According to the user guideEXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;2) Parallel Process parallelEXPDP Scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott33) According to the table name guideEXPDP Scott/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=dpdata1;4) Guided by query criteriaEXPDP Scott/[ema
We often use the exp/imp command for import and export, which is indeed convenient, but also limited. For example, the tablespace names to be imported and exported must be the same, and the schema names must be the same.
Recently I encountered such a problem. In this case, the Data Pump Import and Export tools such as expdp and impdp play a role.
Space name of the table and index to be exported:
SQL> sele
Some time ago in a migration of data pump and exp, found that the difference in efficiency is quite large. Here is an example of a simple comparison.
This article describes ways to improve the efficiency of IMPDP import.
The test in the previous article found that IMPDP's import speed is comparable to IMP's import speed. When Oracle introduced the data pump, it was mentioned that IMPDP's import speed was 1
, preferably with system and other administrators to give.
Grant Read,write on directory dpdata1 to Scott;
Iv. Export of data
1) Guided by user
EXPDP SCOTT/TIGER@ORCL Schemas=scott dumpfile=expdp.dmp directory=dpdata1;
2) Parallel Process parallel
EXPDP scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3
3) Guided by the name of the table
EXPDP SCOTT/TIGER@ORCL tables=emp,dept dumpfile=expdp.dmp directory=dpdata1;
4) According to the query conditions guide
EXPDP sc
,nvduser_fmap2:nn2 Transform=oid:n 2. Transform=oid:n must be added, otherwise the type cannot be created because Oralce will not allow the same ID for the type under the same instance 3, the newly created user password is not available, you must change the password of the new user afterwards alter user NN1 identified by Nvduser_flt 4. You can access user objects under different instances on different servers through the databse link, for example: SEL
databases. The utility can be called using the following command:
Example: impdp scott/tiger DIRECTORY = dmpdir DUMPFILE = scott. dmp
You can control how the import runs. The specific method is to enter various parameters after the 'impdp 'command. To specify parameters, use the Keyword:
Format: impdp KEY
PLSQL_ Data Pump datapump Import and Export data IMPDP/EXPDP (concept) (Oracle Data Import Export tool) [GO]I. Summary
In the normal storage and database migration, when encountering a large database when using exp often takes a few hours, time consuming. oracle10g later can use EXPDP to export the database to spend much less time than Exp spent, and the file is much smaller.Ii. difference between Exp/imp and EXPDP/
Import Mode
As with the export mode, the following 5 modes are mutually exclusive.
Full mode
Set Full=yes, if there is no datapump_imp_full_database role, can only be imported into the data under their own schema
Schema schema
Sets the schema parameter, which is the following syntax
Schemas=schema_name [,...]
The following example imports HR data into the HR schema
> IMPDP hr schemas=hr directory=dpu
Exclude and include parameters can filter or filter specific objects or object types when using EXPDP or IMPDP. For example, to export a particular table or not to export a specific
tables, views, and stored procedures, indexes, constraints, authorization statistics, and so on. The following methods and examples are given for EXPDP or IMPDP using exclude and incl
In Oracle11g, exp cannot export empty tables by default. Using traditional exp, imp Migration to heterogeneous platform databases is troublesome. However, you can use expdp and impdp for migration.
In Oracle 11g, exp cannot export empty tables by default. Using traditional exp, imp Migration to heterogeneous platform databases is troublesome. However, you can use expdp and impdp for migration.
In Oracl
/imp and EXPDP/IMPDP difference: (1) The user UserA object to the user UserB, the use of the difference is Fromuser=usera touser=userb, remap_schema= ' UserA ': ' UserA '. For example: Imp system/passwd fromuser=usera touser=userb file=/oracle/exp.dmp log=/oracle/exp.log; IMPDP system/passwd directory=expdp dumpfile=expdp.dmp remap_schema= ' UserA ': ' UserB ' lo
Label:1. First of all, install the Oracle database. 2, use Sqlplus to enter SYSDBA permission, sqlplus "/as sysdba", for example: 3. Create user framework, for example: CREATE USER Framework Profile DEFAULT identified by frameworkDEFAULT tablespace USERS Temporary tablespace temp account UNLOCK;GRANT UNLIMITED tablespace to framework;GRANT Connect,resource to framework;Grant DBA to framework; Note: If you
Use expdp/impdp to import data from one database to another. 1. create DIRECTORY (the DIRECTORY must be created under the Database User) SQL code sqlplus system/manager create directory test_dir as '/home/orauat/zzj'; 2. Authorize SQL code Grant read, write on directory test_dir to cux; -- view the Directory and permission SQL code SELECT PRIVILEGE, DIRECTORY_NAME, DIRECTORY_PATH FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D WHERE T. TABLE_NAME (+) = D. DI
affiliates. All rights reserved.
Connect to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-ProductionWith the partitioning, OLAP, data mining and real application testing optionsORA-31655: data or metadata object not selected for the jobORA-39154: objects in the external scheme have been deleted from the importThe master table "U1". "sys_import_table_01" is successfully loaded/uninstalled"Start "U1". "sys_import_table_01": u1/******** dumpfile = tmp_1.dmp directory = dir tables = Sc
With the data pump expdp, impdp can import and export data on the server, with high efficiency.You do not need to set the environment variable NLS_LANG. By default, it will go to the character set of the database.
Create directory backup as '/backup ';Grant read, write on directory backup to scott;1. Database-based export and importDatabase a full Database ExportExpdp system/root directory = backup dumpfile = full. dmp logfile = full. log full = y
B.
2014-08-31 BaoxinjianI. Summary
In the normal storage and database migration, when encountering a large database when using exp often takes a few hours, time consuming. oracle10g later can use EXPDP to export the database to spend much less time than Exp spent, and the file is much smaller.Ii. difference between Exp/imp and EXPDP/IMPDP
(1) The user UserA the object to the user UserB, the usage difference is Fromuser=usera touser=userb, rem
Check the database load today and find that the cpu consumption is abnormally high. There are many dw processes, but the impdp process does not exist. Check the process of datapump and find that the process is large.
Check the database load today and find that the cpu consumption is abnormally high. There are many dw processes, but the impdp process does not exist. Check the process of datapump and find tha
Monitor the Data Backup recovery progress (EXPDP/IMPDP/RMAN) 1. view the EXPDP/IMPDP progress 1. When you export data, if the data volume is large, some people may be anxious in the middle, but they may want to see how the progress is. You can see it using two views:
Col owner_name for a10col job_name for a20col operation for a10col job_mode for a10col state for a20col degree for a10col ATTACHED_SESSIONS fo
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.