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].
80
questions
0
votes
2
answers
3k
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 ...
- 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 ...
- 29
0
votes
1
answer
4k
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
...
- 29
0
votes
0
answers
307
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 ...
- 13
0
votes
0
answers
407
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
1
answer
1k
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 ...
- 113
2
votes
1
answer
297
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 ...
- 938
0
votes
1
answer
592
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 ...
2
votes
2
answers
6k
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 ...
- 676
0
votes
1
answer
104
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 ...
- 137
-1
votes
1
answer
868
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 ...
- 55
0
votes
1
answer
383
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 ...
Muhammad Asim
1
vote
0
answers
4k
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 ...
- 337
2
votes
0
answers
326
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 (...
Lance Allison
2
votes
1
answer
669
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 ...
- 233
2
votes
1
answer
3k
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 ...
- 155
1
vote
1
answer
103
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 ...
- 23
1
vote
1
answer
137
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 ...
- 590
0
votes
2
answers
4k
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 ...
- 101
0
votes
0
answers
39
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?
- 1
3
votes
3
answers
7k
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
...
- 133
0
votes
1
answer
170
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
0
votes
1
answer
2k
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....
- 133
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=...
- 137
1
vote
1
answer
2k
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 ...
- 71
1
vote
1
answer
165
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 ...
- 111
0
votes
1
answer
6k
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 ...
- 71
0
votes
1
answer
248
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 ...
- 938
0
votes
2
answers
705
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
1
answer
211
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 ...
- 125
1
vote
1
answer
705
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 ...
- 145
1
vote
3
answers
1k
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 ?
- 155
8
votes
3
answers
53k
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 ...
- 81
1
vote
0
answers
5k
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 ...
5
votes
2
answers
11k
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);
--...
- 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....
- 107
1
vote
1
answer
184
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:
...
- 751
0
votes
1
answer
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 ...
-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'...
- 7,248
1
vote
0
answers
246
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
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 ...
- 11
2
votes
1
answer
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 ...
0
votes
0
answers
615
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 ...
- 676
-1
votes
1
answer
389
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 ...
- 11
-1
votes
1
answer
3k
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 ...
- 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 ...
- 73
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)...
- 123
1
vote
0
answers
708
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 ...
- 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 ...
- 13
2
votes
1
answer
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=...
- 155