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
1answer
2k 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
1answer
935 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
11 views

Oracle 11gR2 => Oracle Data Pump (expdp, impdp) => can backup be safely taken during runtime

until now I create a daily Logical backup backup of my Oracle 11gR2 Database at midnight while the database is running but the client application is in an idle state so that no queries are executed on ...
0
votes
0answers
35 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 ...
1
vote
1answer
93 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
42 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 ...
0
votes
1answer
167 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
193 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 ...
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); --...
-1
votes
1answer
271 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 ...
2
votes
2answers
513 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 ...
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 ...
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 ...
2
votes
1answer
314 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 ...
0
votes
1answer
86 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 ...
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 ...
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 ...
2
votes
0answers
150 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 (...
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 ...
2
votes
1answer
791 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 ...
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 ...
0
votes
2answers
2k 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 ...
0
votes
0answers
34 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?
0
votes
1answer
839 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 ...
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 ...
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=...
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 ...
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 ...
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
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?
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 ...
0
votes
1answer
109 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
2answers
438 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
0answers
1k views

Partition table data into dump files in a single command

I have a fairly large table of spatial data. Because it is spatial data, I have discovered that data pump uses a very large amount of space to import it. (I have seen it use approximately 40 GB of ...
1
vote
3answers
899 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 ?
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 ...
-1
votes
1answer
975 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'...
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 ...
8
votes
1answer
8k views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
0
votes
1answer
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....
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
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....
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 ...
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 ...
0
votes
0answers
503 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 ...
-1
votes
1answer
304 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 ...
-1
votes
1answer
2k 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 ...
0
votes
0answers
2k 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 ...
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)...