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. Be sure to include the Oracle version tag such as [oracle-12c].

Filter by
Sorted by
Tagged with
8
votes
1answer
8k views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
8
votes
1answer
19k 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=...
8
votes
1answer
986 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 ...
6
votes
3answers
35k 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 ...
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
18k 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
3k 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.)...
4
votes
2answers
7k 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); --...
3
votes
2answers
7k 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 ...
3
votes
3answers
3k views

Oracle Database 19.3 on Windows: Howto impdp or expdp from a UNC path in Windows?

With oracle database release 18.3 it is possible to use a unc path as a directory in windows and pump a database pump from this directory with impdp. Example: C:\>sqlplus system/passwd@exampledb ...
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
918 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
4k 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
2k 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=...
2
votes
1answer
1k 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
1answer
3k 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 ...
2
votes
1answer
13k 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
2answers
527 views

Import Tables without Constraints from Schema Export

I am trying to do an import using data pump, but I am struggling to come up with the right options. The export was taken as a schema export. I am trying to import just the tables, with no other ...
2
votes
1answer
1k 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)...
2
votes
1answer
4k views

Using environment variables in data pump query

I have been trying to use data pump (expdp and impdp) in order to retrieve some data from a database to import it into another. I am using the expdp using the table and the query parameters in order ...
2
votes
3answers
25k 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
316 views

Nulling a BLOB column in Oracle 11 impdp

I need to import a 50 GB dump file containing three schemas. Over 90% of the file is taken up by a single BLOB column. How could I import the schemas, including the table containing the blobs, but ...
2
votes
1answer
801 views

Oracle - impdp - import a partition

I truncated a partition, and would like to import it from backup. How can I import a partition from the dump file? Export log: . . exported "XY_PROD"."XY_LOG_PLSQL" 69.16 MB 320459 ...
2
votes
0answers
151 views

Whether or Not to Use DataPump with Oracle GoldenGate

We are setting up GoldenGate to keep 3 of our Oracle databases in sync. Regarding configuration:   We plan to have an instance of GoldenGate running on all 3 nodes. Each node will run an extract on (...
2
votes
1answer
1k 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
900 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
2answers
7k 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?
1
vote
1answer
277 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
99 views

ambiguous “invalid operation” while importing during Oracle datapump

Let me summarize the problem first and I'll give details of the SQL I used to get where I'm at after the summary. I'm exporting a schema from a production AWS RDS Oracle instance, using a database ...
1
vote
1answer
54 views

Does the target database need to have the same structure of the source database before to use IMPDP?

When I do a import from another database have I need the same estruture of the source database in the target database? For example, if my source database use ASM and my target database no, what have I ...
1
vote
1answer
1k 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
78 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
1k 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
4k 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 ...
1
vote
1answer
727 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
43 views

Choosing the right blocksize for Oracle Database tablespaces with lobs

We are currently switching from an older character set to Unicode using impdp/expdp on Oracle Database 19c. We had to changed several data types. We changed tables with LOBs from basicfile to ...
1
vote
1answer
29 views

Are LOBs also backed up if I take dump of all partitions of table individually?

I am taking export of a large table in oracle through datapump. I am taking export of all partitions one by one. The table has a LOB column as well. Are LOBs also backed up with partitions? Because ...
1
vote
1answer
106 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
776 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

19c Impdp hangs after SCHEMA_EXPORT/VIEW/VIEW with cpu 100%

I've a impdp job on Oracle 19c always hanging with 100% CPU (ora_dw00_orclcd process) after the message below: Elaborazione dell'object type SCHEMA_EXPORT/FUNCTION/FUNCTION Elaborazione dell'object ...
1
vote
0answers
4k 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
0answers
203 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
587 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
732 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
4k 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
2answers
2k views

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

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

Logical Backup strategy in large Oracle Databases

in Oracle databases, I would like to know if it is advisable to make logical backups with exp or expdp in large databases with large volumes of data, greater than terabyte, I have seen several systems ...
0
votes
1answer
10k 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
1answer
75 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
4k 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 ...