Data Pump

How to Resolve impdp STATISTICS Stuck

Posted on
Importing Stuck at STATISTICS When I imported data via a network link, I found it stuck at STATISTICS. … Import: Release 12.1.0.2.0 – Production on Wed Sep 26 20:47:29 2018 … Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** tables=ERPAPP.BANKS content=all table_exists_action=replace network_link=erp_db_link logfile=imp_table_erpapp.log Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 10 […]
Data Pump

How to Interrupt and Kill a Data Pump Job

Posted on
Sometimes, you are unable to stop the import or export job on the spot, and no one knows whether it’s hanging or not. In such case, you can initiate another console and attache the import or export job to take over the control like this: [oracle@test ~]$ impdp system/password attach=SYS_IMPORT_SCHEMA_01 Import: Release 11.2.0.4.0 – Production […]
Data Pump

Network Link May Not Work When Importing Partitioned Tables

Posted on
I found I can’t import partitioned table via NETWORK_LINK.[oracle@test ~]$ impdp system/password tables=sh.sales content=data_only table_exists_action=truncate network_link=system_link_orclImport: Release 11.2.0.4.0 – Production on XCopyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** […]
Data Pump

How to Resolve impdp Error ORA-31693 with ORA-39116

Posted on
ORA-31693 Got ORA-31693 when import data with schema mode. [oracle@test ~]$ impdp system/password schemas=hr,oe,sh content=data_only table_exists_action=truncate network_link=system_link_orcl … ORA-31693: Table data object “OE”.”ORDER_ITEMS” failed to load/unload and is being skipped due to error: ORA-39116: invalid trigger operation on mutating table OE.ORDER_ITEMS … In practice, we disable all related triggers before data pump for avoiding trigger […]
Data Pump

How to Resolve ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Posted on
ORA-02266 Got ORA-02266 when import data in schema mode. [oracle@test ~]$ impdp system/password schemas=hr,oe,sh content=data_only table_exists_action=truncate network_link=system_link_orcl … ORA-39120: Table “OE”.”PRODUCT_INFORMATION” can’t be truncated, data will be skipped. Failing error is: ORA-02266: unique/primary keys in table referenced by enabled foreign keys … You might consider to add data_options=skip_constraint_errors for this import, but this option cannot […]
Data Pump

How to Disable All Referencing Constraints Before Importing Data

Posted on
Disable Constraints Before you can import with table_exists_action=truncate, you have to disable all referencing constraints. Otherwise, you may get the following error: ORA-02266: unique/primary keys in table referenced by enabled foreign keysSuppose you want to import 3 schemas, HR, SH, OE. Compose disabling statement. SQL> set heading off feedback off pagesize 0 linesize 150 echo off SQL> spool /home/oracle/disable_references.sql […]
Data Pump

How to expdp AS SYSDBA

Posted on
expdp AS SYSDBA Normally, we use system to migrate data from one to another, who has pretty enough privileges for all kinds of manipulation at content-level. Whereas SYSDBA is the highest privilege which usually does maintenance and critical jobs like STARTUP. Whomever we are using to connect to the database with SYSDBA privilege, we should […]