Skip to main content

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
0 votes
0 answers
160 views

while using expdp getting this error

[oracle@oracle197 ~]$ expdp CITI_MDR_DEV_3/CITI_MDR_DEV_3 DIRECTORY=CITIBKP DUMPFILE=CITI_MDR_DEV_3.dmp LOGFILE=CITI_MDR_DEV_3.log Export: Release 19.0.0.0.0 - Production on Sat Mar 16 15:43:35 2024 ...
Ajay Sharma's user avatar
0 votes
1 answer
155 views

Unable to use data pump utility in Oracle to import data to another server

I am using this command: impdp atif/[email protected]:1521/serv1 DIRECTORY=DATA_PUMP_DIR DUMPFILE=mltpltblbkp.dmp LOGFILE=mltpltblbkp.log to import data from one server to another. this is my ...
dbafix's user avatar
  • 62
0 votes
3 answers
12k views

export multiple tables using expdp with query condition

I have requirement to export multiple tables using datapump job and the ultimate goal is that we are doing a migration from on-Prem . They have something like 6TB of AUD data and we are not required ...
sarat's user avatar
  • 60
0 votes
1 answer
2k views

Oracle 11gR2 - Expdp Full Database With Exclude Some Table Data

We will migrate our database using datapump. It will be full database export with exclude some table's datas. But we want to do a different operation for 2 different tables. We want to export the data ...
jrdba123's user avatar
0 votes
1 answer
6k views

Oracle 12c to 19c Internal Schema Export & Import

I want to exp & imp operations for APEX users from Oracle Database 12cR1 to 19c. Data Pump Utility can't do that. When I try to export by using following parfile and command: DUMPFILE=APEX.dmp ...
jrdba123's user avatar
0 votes
0 answers
525 views

Data pump exclude include

hello im learning about datapump and i trying to use include to filter which table i want to export.when i use the code below the import/export job able to run but an error occur saying that the ...
Silent Boots's user avatar
0 votes
0 answers
626 views

Oracle XE 11.2.0 data pump expdb works on but impdb doesn't on Windows 10

New to Oracle DB administration here. I have a fresh localhost installation of Oracle XE 11.2.0 (x64 installation from here) on my computer using Windows 10 64-bit. I am able to connect to the DB ...
Nikolas Charalambidis's user avatar
1 vote
1 answer
3k 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 ...
Jason's user avatar
  • 113
2 votes
1 answer
699 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 ...
r0tt's user avatar
  • 1,028
0 votes
1 answer
1k views

Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)

I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say ...
user3150146's user avatar
2 votes
2 answers
12k 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 ...
Patrick's user avatar
  • 688
0 votes
1 answer
124 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 ...
miguel ramires's user avatar
-1 votes
1 answer
1k views

Oracle 11gR2 - Export - Import (expdp, impdp) How To Exclude A Table Partiton And This Table's Indexes

I have a table partition (135GB) and index partition on this table (total=150GB) and I want to exclude owner schema of this table and indexes with excluding this table partition and it's indexes. What ...
jrdba's user avatar
  • 55
0 votes
1 answer
747 views

ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3

I am trying to import an oracle database from a dump file. While importing I am getting ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3. I have tried to fetch the ...
user avatar
1 vote
0 answers
6k 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 ...
sgargel's user avatar
  • 337
2 votes
1 answer
527 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 (...
user avatar
2 votes
1 answer
962 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 ...
laurt's user avatar
  • 233
2 votes
1 answer
4k 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 ...
Jakub P's user avatar
  • 157
1 vote
1 answer
189 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 ...
user3102917's user avatar
1 vote
1 answer
359 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 ...
Astora's user avatar
  • 793
0 votes
2 answers
6k views

Errors using expdp with `flashback_time=systimestamp`

Oracle 11.2.0.1.0 on Windows 2012 Std: expdp '/ as sysdba' full=y directory=exp dumpfile=... logfile=... flashback_time=systimestamp Fails with: ORA-39001: invalid argument value ORA-39150: bad ...
user2847643's user avatar
0 votes
0 answers
44 views

ORACLE timed Datapump on Linux? (No crontab)

what is the best way to create a timed Datapump Dump of a PDB on a Linux System? No Crontab. And how do I implement it?
xorben's user avatar
  • 1
3 votes
3 answers
10k 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 ...
KimCM's user avatar
  • 133
0 votes
1 answer
254 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 ...
Ananth N's user avatar
0 votes
1 answer
3k 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....
sabarish jackson's user avatar
2 votes
1 answer
2k 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=...
miguel ramires's user avatar
1 vote
1 answer
3k 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 ...
Ray's user avatar
  • 71
1 vote
1 answer
223 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 ...
siddhu's user avatar
  • 111
0 votes
1 answer
8k 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 ...
Ray's user avatar
  • 71
0 votes
1 answer
363 views

Oracle Datapump between different database releases

I need to transfer schemas from an Oracle 12.1.0.2 Database to an Oracle 11.2 XE Database. According to Oracle Docs: On a Data Pump export, if you specify a database version that is older than the ...
r0tt's user avatar
  • 1,028
0 votes
2 answers
932 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 ...
Misha Kriachkov's user avatar
0 votes
1 answer
346 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 ...
Arnaldo Raxach's user avatar
1 vote
1 answer
1k views

Recreate existing database with a different charset

I want to change the character set of an existing database from none to utf8, it seems that the safest way is to create a new one then pump data to it. I have tried two methods, both gave me the same ...
user2475096's user avatar
1 vote
3 answers
2k 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 ?
john true's user avatar
  • 155
8 votes
3 answers
67k 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 ...
Saad Mahmood's user avatar
1 vote
0 answers
6k 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 ...
Lucas Santana's user avatar
5 votes
2 answers
14k 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); --...
olekb's user avatar
  • 163
0 votes
1 answer
2k 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....
Mercer's user avatar
  • 107
1 vote
1 answer
248 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: ...
sjk's user avatar
  • 751
0 votes
1 answer
3k 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 ...
Mike DeMille's user avatar
-1 votes
1 answer
1k views

Restore a dump file from a different server, with a different name

I Expdp a user with the name user_prd for example. I copied the dump file to another server, and I want to restore it with the name user_hom. I tried remap_schema with no succes ( Im a SQL DBA, so it'...
Racer SQL's user avatar
  • 7,416
1 vote
0 answers
258 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....
Bibhuti Bhusan Padhi's user avatar
1 vote
1 answer
2k 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 ...
John Faig's user avatar
2 votes
1 answer
2k 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 ...
Bibhuti Bhusan Padhi's user avatar
-1 votes
1 answer
464 views

Datapump export

I'm trying to export the full database using the datapump API, I tried to use this following code: DECLARE handle NUMBER; nom_job varchar(25) := to_char(SYSDATE,'YYYYMMDD_HH24MISS'); nom_dump ...
zenami's user avatar
  • 11
-1 votes
1 answer
4k views

ORA-01555: snapshot too old: rollback segment too small

We see this error on a number of tables when doing an expdp on 12c / Windows. A restart of the Oracle database service makes this go away... Can anyone shed some light on what may cause this and the ...
dank's user avatar
  • 101
0 votes
1 answer
1k 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 ...
user112359's user avatar
2 votes
1 answer
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)...
user3078523's user avatar
1 vote
0 answers
753 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 ...
Shawn's user avatar
  • 11
1 vote
1 answer
5k 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 ...
vishal's user avatar
  • 13