Explain how to use impdp and expdp in oracle Database, impdpexpdp
Create a test table and insert Test Data
Create table test_tab (id number not null primary key, name varchar2 (20); insert into test_tab values (1, 'Data 1 ');
Create directory object
create or replace directory DMP_DIR as '/oradata/ebankbak/';
Export the specified table
expdp mia/mia tables=test_tab dumpfile=test.dmp directory=DMP_DIR
Import the specified table
Table_exists_action pa
Impdp gets stuck, DW waits for library cache lock, impdplibraryMy colleagues reported that impdp was stuck in the SCHEMA_REPORT/TYPE/TYPE_SPEC step and did not respond after more than one hour,Check the v $ session:Select program, sid, event, blocking_session from gv $ session where program like '% DW % ';Result:DW01, 98, library cache lock, 213DW03, 13, library cache lock, 213DW02, 36, library cache lock,
The difference of function between Exp/imp and EXPDP/IMPDP: 1, the user UserA to import the object into the UserBEmp/imp usage:Formuser=usera Touser=userb;EMPDP/IMPDP usage:Remap_schema= ' UserA ': ' UserB 'For example:Imp system/password fromuser=usera touser=userb file=back.dmp log=backlog.log;IMPDP system/password directory=expdp dumpfile=back.dmp remap_schem
Tags: shel database name rar root LIS nec Service font LinuImmediately following the previous article, the Oracle database schema has been created, and my requirement is to migrate the databases on the old server to the new database . This will use the IMPDP (import) operation. To implement IMPDP (import) work on a new database, First you need to do EXPDP (export) operation from the old database, the comman
. Sometimes parallel and filesize This parameter conflict, if found that the statement written correctly, but there are unknown reasons for the error, you can try to remove the FileSize parameter test.filesize=5g--size of each dump file--Export the whole library structureAdd parameter: full=yContent=metadata_only--Export All-Library data non-conductive structureAdd parameter: full=yContent=data_only--Export schemasAdd parameter: schemas=xxx--Export tableAdd parameter: tables=xxx,xxx,xxxImport:VI
ORA-39002: invalid operation.ORA-39070: Unable to open the log file.ORA-29283: invalid file operationORA-06512: at "SYS. UTL_FILE", line 475ORA-29283: invalid file operation
Solution 1:
Today, when I used IMPDP to import data, I encountered "ORA-39002, ORA-39070 ......" Continuous Error Reporting.The cause of the problem is very simple, but the content of the error message is "weird". To avoid detours for friends, simply record the problem handling pr
According to the prompt of the ORA-29283, the directory is found to have a problem, select the correct directory, impdp successful!
According to the prompt of the ORA-29283, the directory is found to have a problem, select the correct directory, impdp successful!
ORA-29283 solution (impdp)
The impdp error message
This article is the original article, reprint please indicate the source: http://blog.csdn.net/msdnchina/article/details/38782657 Impdp import Materialized View (MV) error: ORA-31685: Object Type materialized_view DB version 10.2.0.4The impdp command isImpdp sys/As dumpfile = Scott. dmp logfile = scott_imp_no_remap.logImport error:Processing object type schema_export/MATERIALIZED_VIEWORA-31685: Object Type
increasing number of "..." to change the blinking cursor.
New:
Exp/imp is very easy to use, but the only confirmation is that the speed is too slow. If the data in a table has hundreds of millions of records, it is often a long time to stop importing and exporting the data in this table, however, the new tool expdp/impdp is provided from Oracle 10 Gb, which provides high-speed parallel and big data migration for Oracle data.
Imp/exp can be called on
Colleagues reflect IMPDP when the Schema_report/type/type_spec step stuck, 1 hours later also did not respond,Check the v$session:Select Program,sid, event,blocking_session from gv$session where program like '%dw% ';The result is:Dw01,98,library Cache lock,213Dw03,13,library Cache lock,213Dw02,36,library Cache lock,213Dw00,213,library Cache lock,213All DW processes are waiting for the library cache lock to see the previous
Methods to terminate Imp/exp and EXPDP/IMPDP processesLone Wind A sword Released: 2013-08-22 17:37 Column: Database development click: 2,613 times Grab SofaFirst, stop Exp/imp optimization speedCan kill the process directly, but first to kill the parent process, and then kill the child process, only kill the child process, Exp/imp will also be executed in the backgroundExample: Ps-ef |grep imp query to pid,kill-9 PID, kill-9 ppid, you canSecond, stop
Grant user permission to manipulate the Dump_dir directory Export data using the EXPDP command (you can export by table, export by user mode, export by table space and full-scale), import data using the IMPDP command (you can import by table, import by user mode, export by table space, and full-Library import). Import and Export databases using command-line-based data pump technology. Sql>col Directory_name for A20 Sql>col Director_path for A60 Sql
Tags: Oracle database Migration Impdp+dblinkLab Environment:SOURCE Library: 192.168.2.200 Sid=testdbTarget Library: 192.168.2.100 Sid=testdbExperimental Purpose:Use Impdp+dblink to import all data from a jtrms user on a 192.168.2.200 database to the target database 192.168.2.100Note: Using Impdp+dblink, this saves the data export (EXPDP) and then the import proce
There is a need to back up the data from the production library to the backup machine at 23 o'clock every night, the database of the backup machine can be used directly the next day, the data is the data of the production library yesterday. (production data is not much, all data is not more than 3000w).With no exposure to advanced Oracle knowledge, only simple IMPDP and EXPDP tools are available, so it is intended to be done with this self-brought com
The skip_constraint_errors option of Oracle impdp skips the unique constraint error.
In the past, impdp imported to an existing table has a unique index, and the data to be imported has a unique conflict with the current data. the general solution is to first Delete the unique index, import the duplicate data, and then re-create the index, or import the table as another table name, and then import the joine
platform database version of the environment
---------------------------------------------------Dividing line------------------------------------------------------------
Below is an import export of data using Oracle's EXPDP/IMPDP data pump in the 10g and 11g versions, the following Oracle-related commands are executed in the user Oracle environment:
(PS:EXPDP/IMPDP is a server-side tool that runs only on
Use the EXPDP\IMPDP and EXP\IMP commands provided by Oracle to implement export \ Import. The last exported table is incomplete because the Exp\imp command automatically ignores the row_num=0 table. So we chose to use the EXPDP\IMPDP command.
The tasks we want to accomplish:
1, export the. dmp file format from the Airtest user (optionally, the format is. sql, but the SQL table cannot have large characters (
Oracle IMPDP's table_exists_action detailedCategory: [oracle]--[Backup and Recovery]2012-01-06 22:44 9105 People read Comments (0) favorite reports Tableactionoracleschemaobjectimport1 table_exists_action parameter DescriptionWhen importing data using IMP, if the table already exists, drop the table before importing.When using IMPDP to complete the database import, if the table already exists, there are four kinds of processing methods:1) Skip: Def
, 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
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.