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.

0
votes
1answer
23 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
20 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....
2
votes
0answers
53 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
0answers
32 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 ...
1
vote
1answer
146 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
25 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 ...
0
votes
1answer
640 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
54 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
2answers
138 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
60 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 ...
1
vote
3answers
548 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
10k 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
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); --...
0
votes
1answer
660 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
0answers
2k views

slow datapump exports on 12.1 for one database but not the other

I've got two databases running on the same 12.1.0.1.0 oracle home on the same machine, on the same disks. Each database has multiple schemas of approximately the same size. On database A, the ...
1
vote
1answer
57 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: ...
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 ...
-1
votes
1answer
620 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
115 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
1answer
543 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 ...
2
votes
1answer
553 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 ...
0
votes
0answers
356 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
197 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
878 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
1answer
520 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
0answers
869 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 ...
3
votes
1answer
636 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)...
1
vote
0answers
336 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 ...
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=...
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 ...
-3
votes
1answer
77 views

importing 50gb+ dmp using imdp

Attempting to import 50GB+ dumpfile using impdp. Hitting the 11GB datafile limit. Never used oracle before. I created bigfile tablespace with autoextend on. Perhaps I should use different approach?
0
votes
1answer
268 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 ...
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 ...
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 ...
1
vote
1answer
551 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 ...
0
votes
1answer
6k 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 ...
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.)...
0
votes
0answers
1k views

Extremely slow datapump imports from 10.2.0.1.0 -> 11.2.0.1.0

All imports/exports talked about here are using datapump, the 10.1.5 system is running on Windows Server 2003 and the 11.2 system is running on Linux. I have an 11gig datapump dumpfile from a 10.1.5 ...
0
votes
2answers
2k 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
231 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 ...
0
votes
1answer
103 views

Oracle 11.2 restore DB to a specific point

I should execute following tasks: 1. test system and during test change Oracle data and Create New/Remove Old datafiles and tablespace. 2. at the end of test rollback to behviour before of point 1. I'...
0
votes
1answer
36 views

Is it possible to drop export some shema without data and the other with data

I have oracle 11G R2 under RHEL 6.3 test is the name of the database, the database contain 12 custom schema, sch1,sch2,...,sch12. is it possible to create database backup using datapump method that ...
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 ...
3
votes
1answer
782 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 ...
0
votes
2answers
2k views

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

Why is datapump export faster than exp?
3
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 ...
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 ...
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 ...