Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [datapump]

Oracle Data Pump is a set of command line utilities and PL/SQL packages for moving data and metadata between Oracle databases.

8
votes
1answer
884 views

How can a datapump export have its resource usage limited on linux?

Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
7
votes
1answer
6k views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
7
votes
1answer
16k views

How to find the schema name of a data pump dmp file?

I have been given a DMP data pump export file to import into my local Oracle instance. I've tried running this command line: impdp full=Y directory=DATA_PUMP_DIR dumpfile=MyDumpFile.dmp logfile=...
6
votes
3answers
2k views

How can a datapump export have its resource usage limited on windows?

Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
4
votes
3answers
14k views

Permissions needed to call DBMS_DATAPUMP from within package

I am attempting to write a procedure (within a package) that calls DBMS_DATAPUMP to copy an existing schema into a different schema. The code works when executed from an anonymous block, but from a ...
4
votes
1answer
2k views

How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file?

How do I import data into a new installation of oracle 11g r2 using a .dmp file and a .par file? Here is what I have done so far, on a Windows 8.1 devbox: 1.) Open command line as administrator 2.)...
3
votes
1answer
664 views

How to export a single schema from Oracle EE to Oracle XE?

I would like to export a schema (DDL or DDL+data) from Oracle EE (Enterprise Edition) to Oracle XE (Express Edition) for development purpose (in our case: for use as test databases for Java developers)...
3
votes
1answer
2k views

Oracle Datapump: modify fully qualified objects when cloning a schema

I am confronted with a problem for which I have not found an elegant solution: I need to clone a schema within the same database, so I use datapump's remap_schema import parameter. The problem is ...
3
votes
1answer
791 views

Oracle Data Pump Import with running advanced replication

In our system we have a running advanced replication between two databases(11g release 2) and want to use pump import to import a backup on one site. However the imported data is not replicated to the ...
3
votes
3answers
3k views

Oracle Data Pump Import imports no records

When we do an Oracle Data Pump import of our database, it runs without significant errors. However, on one table Oracle reports that 0 records have been imported. Processing object type ...
2
votes
1answer
574 views

Loading External Table from dumpfile

I have created a dumpfile using EXPDP process. I am trying to create an external table using the above said dumpfile. I am using the following statement to create the external table: CREATE TABLE ...
2
votes
2answers
5k views

expdp full export not exporting all schemas

On Oracle 12c, fairly new database with APEX installed. I do a expdp \"/ as sysdba\" directory=dp_dir full=y dumpfile=full.dmp logfile=full.log And I am getting just a few tables from SYS and ...
2
votes
1answer
12k views

Oracle 10g dump file into Oracle 11g database

I went through this link for my answer to the same question. I was not sure if i could continue in the same link, so created a new question. Oracle 10g dump file push into Oracle 11g database Sorry ...
2
votes
3answers
19k views

ORA-39165 Schema was not found in export

I'm getting the following error on a data pump export: ORA-39165: Schema OFDAT was not found. First I verified that I had full export permission: GRANT EXPORT FULL DATABASE TO OFDAT; Then I tried ...
2
votes
1answer
95 views

Oracle impdp error when importing data

I get this error ORA-02289: sequence does not exist when I try to import a schema to my database, could you give me some recommendation? Import Command: impdp '"sys/mmm as sysdba"' directory=...
1
vote
3answers
571 views

Oracle DB size decreased after import

My database size was 1TB and its full export size is 300GB. After imported db to new environment, db size reduced to 700GB. What could be reason for this ?
1
vote
1answer
1k views

Oracle expdp puts INDEXES to LOGGING mode

I've created a table in schema LARRY create global temporary table TT1 (N number); create index TT_IX1 on TT1 (N); then export it, and try to import in schema LARRY2: expdp LARRY/LARRY directory=...
1
vote
1answer
2k views

impdp expects wrong file path

Summary: I'm using expdp / impdp to export a database from a test / demo database (Oracle 11.2) and import it into an instance of Oracle Express (11g) on my own machine. The import fails because ...
1
vote
1answer
233 views

Can we reuse a .dmp file(expdp) to perform import using data pump utility

I'm an accidental DBA. We use Oracle 11g database. I had to do a database refresh in the test environment. I created a .dmp file using expdp command and performed an import using impdp command a week ...
1
vote
1answer
203 views

expdp Hangs at DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM in oracle 12c

Expdp Hangs at Below Stage for long time even in structure backup also . Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type ...
1
vote
1answer
26 views

Oracle Data Pump Export

If I want to export all the tables in a schema, can I just specify the schema parameter in the parfile or do I also need to list out all the tables individually in the tables parameters? I tried ...
1
vote
1answer
699 views

Oracle out-of-place upgrade on same host: impdp issues

I am testing an out-of-place Oracle upgrade from 11.2.0.2 to 11.2.0.3 on the same host. From what I've read, expdp and impdp is the way to go. My problem is that since I am importing to another ...
1
vote
1answer
11k views

How to replace and overwrite all existing objects in Oracle with impdp for full database import?

I want to replace all the existing objects in my database, not just tables, into my current database. I ran expdp with the parameter full=yes as the system user, who I granted ...
1
vote
1answer
60 views

How to restrict data pump logon to localhost

I have a user in Oracle 12c database that will be used to run full data pump export and import. I would like to restrict logons from that user to localhost. I have tried to create logon trigger: ...
1
vote
1answer
608 views

Oracle 12c Datapump import error

I am importing a database (.dmp) file using the 12c Data Pump import wizard. I created a user for the database and remapped the schema and tablespace. I get the following errors: ORA-39071: Value ...
1
vote
1answer
560 views

Selective data extract at Schema level

I am attempting to achieve the following within a single data pump export: SCHEMA1 = Structures only SCHEMA2 = Structures only SCHEMA3 = Structures and data The answer in this question only works ...
1
vote
0answers
2k views

Import Dump: Cannot Locate Master Table

I'm trying to import a dump file with the command below: impdp system/password dumpfile=mw0sd1.dmp keep_master=Y But it returns following error: ORA-39002: invalid operation ORA-39059: dump file ...
1
vote
1answer
3k views

How do I kill Oracle DataPump expdp job stuck in DEFINING state?

My job failed because dump file already existed. Now it's stuck in DEFINING state. How do I stop/kill it? declare h1 number; BEGIN h1 := DBMS_DATAPUMP.ATTACH('EXPORT_TABLE_2017_1205_1402',user); --...
1
vote
0answers
120 views

Get current-processed column information in Oracle datapump

In order to mask data I am using REMAP_DATA parameter in Oracle datapump as follows: expdp \'/ as sysdba\' DIRECTORY=DUMP_DIR DUMPFILE=dumpfile.dmp LOGFILE=logfile.log SCHEMAS=user0 REMAP_DATA=user0....
1
vote
0answers
368 views

Function can't drop synonym

I am debugging an application that is working correctly on many PCs but not on one particular one. The part of the application that is having an issue is an automated restore using dpimp from another ...
0
votes
1answer
2k views

How to export all tables with user in Oracle 10g Enterprise edition?

I want to take backup of all my tables and the user too in Oracle 10g Enterprise edition and then I want to import it to some other computer. The user is not system I have created another user and all ...
0
votes
1answer
285 views

Is this package missing and if so, how do I fix?

I am following the given link. https://oracle-base.com/articles/10g/oracle-data-pump-10g I'm an Oracle newbie. Oracle 12c is the target version. I have a Development database running in a docker ...
0
votes
1answer
3k views

Using Oracle Datapump when target table has additional column

Table 1 has 5 columns. Table 2 has the same 5 columns, plus an additional non-nullable column. I would like to use datapump to copy the data from Table 1 to Table 2. In Sql Server, I can use bcp and ...
0
votes
1answer
7k views

How do I kill a non-running impdp job?

I am trying to run an import operation into an Oracle 11G R2 database from a .dmp file using SQL Developer 4.1's Data Pump Import Wizard. I set everything up with IMMEDIATE start, but then the job ...
0
votes
2answers
3k views

Do I need to create tablespace and also add all datafiles before importing a schema using datapump?

Do I need to create tablespace and also add all datafiles before importing a schema using datapump? Or does impdp add all data files itself first and then unloads data into it?
0
votes
2answers
2k views

How Datapump is faster than export/import backup? [closed]

Why is datapump export faster than exp?
0
votes
1answer
26 views

Oracle Data Pump imported table, some columns not selectable

Imported a table into Oracle 12g using data pump. Some of the columns, though present in the table as shown by describe, cannot be selected but they show up in select *. Cannot select them ...
0
votes
1answer
846 views

Database Hangs while impdp “DATABASE_EXPORT/STATISTICS/MARKER” at this stage

I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production i am facing issue while impdp on other server. it hangs at the last line message below. it takes min. over 2-3 ...
0
votes
2answers
153 views

Oracle Data Pump for extracting DDL as a set of files – one per object

I use Oracle Data Pump to extract the DDL for all schema objects – impdp with sqlfile parameter. It generates one huge SQL file. I want to split it up into several files – one file per one schema ...
0
votes
1answer
62 views

importing oracle schema

I need to import a saved .dmp made with exp in Oracle9i in another server which is a copy of the server where the save was made, the save is from a specific scheme (PERCON) that is the one I want to ...
0
votes
1answer
702 views

Import dump file schema not found

I want to import a dump file but I have this error: Import: Release 11.2.0.4.0 - Production on Mar. Nov. 28 10:12:56 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved....
0
votes
1answer
540 views

Import data to existing schema from dmp file

ORA-39083: Object type ALTER_PACKAGE_SPEC failed to create with error: ORA-01775: looping chain of synonyms Failing sql is: ALTER PACKAGE "XZB"."P_IATA" COMPILE SPECIFICATION ...
0
votes
1answer
4k views

Oracle EXPDP using QUERY parameter with results from SELECT

Oracle 11.2g XE I'm trying to write a initial deployment script for my application. There are several tables with data that I want to include in datapump export with a QUERY. I have a table which ...
0
votes
1answer
2k views

Oracle dbms_datapump removes dumpfile after succesful import

I'm working on a PLSQL script to import a Oracle datapump dumpfile using the dbms_datapump package. Each time the script has succesfully imported the contents of the dumpfile I notice that the ...
0
votes
1answer
26 views

Using JOINS in QUERY clause in EXPDP

I have a par file that is using JOINS in QUERY clause. But it is not working. directory=expdp DUMPFILE=Test_SIT_Query_1table_%U.dmp encryption_password=oracle parallel=8 logfile=TEST_SIT_Query_table....
0
votes
0answers
87 views

Oracle impdp only imports one schema from a dump into which expdp exported many schemas

I have the following problem and doubt in Oracle. I am trying to import with impdp a dump from my database into which I exported several schemes. The problem comes when I try to import into the same ...
0
votes
1answer
61 views

Oracle Datapump between different database releases

I need to transfer schemas from a Oracle 12.1.0.2 Database to a Oracle 11.2 XE Database. Accodring to Oracle Docs: On a Data Pump export, if you specify a database version that is older than ...
0
votes
1answer
1k views

Oracle 11 IMPDP Table Exists but gets Does Not Exist Error

I'm running in to a weird problem with IMPDP and Oracle DB version 11.2.0.4.0 where I have a table in the destination schema that does indeed exist, but IMPDP thinks it doesn't. See this entry from ...
0
votes
0answers
367 views

Datapump Import Parallel execution

I have noticed differences in executions between data pump imports lately, and I am trying to determine what is causing the differences. I am using impdp to move data between databases, and remap the ...
0
votes
0answers
911 views

Issue COMMIT in between impdp process

I am using Oracle DBMS_DATAPUMP API to import a dumpfile containing some table data. Is it possible to stop the job and issue COMMIT; in the same session to maybe cleanup UNDO tablespace and then ...