Questions tagged [expdp]
For questions about the Oracle Data Pump export (invoked with the expdp command). Use together with the [oracle] tag, and include the appropriate version tag, such as [oracle-12c]. If the question applies to both the impdp and expdp commands, do not use this tag; use [datapump] tag instead.
63
questions
29
votes
3
answers
175k
views
How to transfer data using expdp and impdp commands?
I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp and impdp commands. I have ...
- 5,283
8
votes
3
answers
55k
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
4
votes
2
answers
19k
views
How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0
I am running following command to dump my database
./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY
and ...
- 141
3
votes
2
answers
5k
views
Sequence number shows a lower value after dump file import
I'm using Oracle 11g database in which I have more than 12 schema's consisting of many tables. I've created sequences for primary keys to these tables. I've also created synonyms to refer my ...
- 152
3
votes
3
answers
26k
views
Oracle 12c: installing expdp and impdp on Linux
The default Oracle installation for Linux didn't come with the expdp and impdp command line tools. I have trouble finding them on Oracle's website. How could I install them?
- 233
3
votes
1
answer
3k
views
ORA-39014 import in Oracle 19c from DMP file of Oracle12c
I have been testing out expdp/impdp of databases from 12c to 19c( newly created database) but one db have game me this error:
nohup impdp system/****@CKLPROD CONTENT=METADATA_ONLY PARALLEL=4 DIRECTORY=...
- 43
2
votes
1
answer
8k
views
Datapump export taking too much time to complete
I've scheduled to take a backup of my oracle11g database, daily at 11pm via Windows Task Scheduler. At this time, I'm not letting my database to go down. Also, my database is in noarchivelog mode. ...
- 141
2
votes
2
answers
7k
views
Expdp error UDE-22303
I am experiencing the following error, when I am trying to make an export to the database using expdmp utility:
UDE-22303: operation generated ORACLE error 22303
OCI-22303: type "SYS"."KU$...
- 29
2
votes
1
answer
2k
views
Consistent Database Export of Oracle Database
I'm quite confused and I need some clarification!
I do full export of database with Export Pump (for replication/duplication).
What I usually do is:
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP ...
- 357
1
vote
4
answers
11k
views
How to use / as sysdba in pluggable database?
I'm trying to make a export, usually I use userid=\"/ as sysdba\" syntax in Linux command line (I don't know sys password in my production environment.
How can I do this using pluggable ...
- 634
1
vote
2
answers
25k
views
Oracle Full Client / Database Client package locations
I need to install multiple versions of the Oracle client on an app server for an automation project. The platform I'm looking for in specific is Linux, 64 bit.
Oracle's network of websites are a maze ...
1
vote
1
answer
1k
views
IMPDP from Oracle 10g TO 11g
I'm not able to import the full database from oracle 10g to 11g using impdp FULL=Y. It seems that the file path is differing so what shall I do?
The impdp command is looking for oracle 10g path while ...
- 13
1
vote
1
answer
1k
views
EXPDP snapshot too old live monitoring
The likely errors that raises "snapshot too old"s during datapump export are well documented all over the internet such as :
undo_retention parameter being too short
redo logs groups are too small or ...
- 1,324
1
vote
2
answers
2k
views
Are expdp/impdp commands appropriate for daily schema sync?
I've done following comands:
expdp system/password@localdb ^
schemas=SCH directory=dumps_dir dumpfile=SCH_%date%.dmp logfile=expdpSCH_%date%.log
It runs great, without errors. But when I use command:...
- 113
1
vote
2
answers
32k
views
Export pump all schemas except Sytem related schemas
I have around 111 schemas in one database instance and have to export all the schemas except the system related schemas to a new instance. Is there any way as to exclude system related schemas while ...
- 181
1
vote
1
answer
231
views
EXPDP - why is block estimate significantly larger than segment size?
I'm exporting one specific table for a schema which I thought was a relatively small 5.4 GB
SQL> select bytes/1024/1024 MB from user_segments where segment_name='TABLENAME';
MB
----------
...
- 137
1
vote
1
answer
161
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 ...
- 634
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
180
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
1
vote
1
answer
372
views
normal Linux user for oracle database dumps
is it possible to allow normal linux user " neither root nor oracle " to be able to execute expdp and impdp and to have access to dpdump directory with rw access on oracle database 12c ?
i ...
- 11
1
vote
1
answer
258
views
Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server?
I am trying to have my entire remote DB backup made to my local server.
Running the following command from my local server: expdp \"user/user@ip as SYSDBA\" full=Y directory=/backup/backup/ ...
- 13
1
vote
1
answer
518
views
Oracle expdp command executed in a command file not honoring %U instead using _U without a sequence
I have generated a bunch of expdp commands and stored them in a .cmd file. When I execute one of the commands by copy and paste into a command prompt, it works fine. If I try to execute the whole ....
Rita Bowers
1
vote
1
answer
1k
views
Why doesn't Oracle expdp create the dump file?
I'm trying to export a schema from an Oracle 10.2.0.5.0 database. For some reason, when I launch the expdp command, it finishes almost immediately and it only creates the log file, no dump file at all....
- 121
1
vote
1
answer
8k
views
ORA-39001: ORA-39000: ORA-31640: ORA-27041: and Linux-x86_64 Error: 13: Permission denied [closed]
I used this command to export data:
expdp gsw/password DIRECTORY=gsw DUMPFILE=gsw.dmp logfile=gsw.log SCHEMAS=GSW
Now importing this to other server as:
[root@linux gsw]# impdp gsw/password ...
- 13
1
vote
1
answer
1k
views
Can expdp be piped directly into impdp, without intermediate files?
We often need to copy fairly large databases from one server to another. The current method uses expdp on the source, and then impdp on the destination. This gets pretty horrible because:
It takes up ...
- 136
1
vote
1
answer
680
views
Pass .csv file as parameter file for expdp Oracle 11.2.0.4.0
Can we use .csv file as parameter file for expdp ?
Ex: expdp scott/xxx@dev parameter=table_full.csv
Thanks
- 11
1
vote
2
answers
1k
views
Oracle file permissions expdp vs dbms_datapump
We have written our own export function using calls to dbms_datapump. In some systems the export fails due to insufficient file permissions on the chosen directory.
However, using expdp to perform ...
- 121
1
vote
1
answer
353
views
Error in database export
I'm using Oracle 11g databse. For logical backup, we are exporting database on daily basis using below command and dump size is almost 12GB. , but now I'm facing an issue with my expdp.
expdp 'sys/...
- 141
1
vote
1
answer
946
views
impdp syntax for schema that is not available on other database
My intention is to import the data of schema 'SCOTT'. I have exported it from db1 using expdp scott/tiger schemas=scott dumpfile=scottexp.dmp ......
Now When I try to import the same in database db2 ...
1
vote
1
answer
2k
views
Oracle expdp et impdp : order of schemas?
When I try to export and then import many schemas from one Oracle DB to another one, I get errors because grants and synonyms cannot be recreated in the target DB; the reason is probably that the ...
1
vote
1
answer
155
views
Oracle12c - EXPDP hangs when using NFS share
I have a problem with the export via EXPDP for Oracle12 databases.
When the export is local, no problem.
When I use an NFS share, the export starts, creates the log file, then nothing.
VM where the ...
- 11
1
vote
3
answers
674
views
Move expdp part file before job end
I have an Oracle 11gr2 database running on Enterprise Linux 6.6 server.
I use the following command to split the expdp dump file to 10G size chunks:
expdp userid=/ DIRECTORY=dmpdir dumpfile=dump%u....
- 157
1
vote
1
answer
357
views
Need help on expdp and impdp concept [closed]
I have a multiple databases i.e (database1,database2) and i need to export a table from database1 to database2 . Here are the commands i tried
expdp hr/oracle@oracle tables=emp directory = ora ...
- 133
1
vote
1
answer
2k
views
oracle expdp similar method to export datas for SQL Server
On our oracle databases, we regularly run database export through expdp/impdp utility (wrapped for readability):
$ expdp user/[password] SCHEMAS=<nom_schema> DIRECTORY=expDatapump
...
- 303
1
vote
1
answer
1k
views
Oracle migration unable to create indexes
I want to move the contents of a database instance to another using the expdp and impdp utilities. The source instance is version 12.1.0.1.0 and the destination is 11.2.0.1.0 so I modified the expdp ...
- 157
0
votes
3
answers
28k
views
Exporting schema data (tables, permisions) into another schema on same database
I want to take data from one schema schema1 and put it into schema2 this should be quite easy to do but i have searched through the oracle documentation and can't find a way to do it.
- 105
0
votes
2
answers
3k
views
Is there a way to Oracle expdp not inherit char type nls semantics on DDL?
expdp, by default, generated internal DDL to create table observing current NLS_LENGTH_SEMANTICS of each column.
If a column as BYTE (the default and most used) NLS_LENGTH_SEMANTICS, the internal DDL ...
- 140
0
votes
1
answer
178
views
Issue With EXPDP Full Tablespace
I am trying to export schema with below command using expdp in Oracle 18c XE , the schema uses tablespace named "testtablespace"
expdp myschema/*******@//localhost:1521/XEPDB1 schemas=...
- 11
0
votes
1
answer
5k
views
Oracle DB : Export only table data from from all tables of given schemas
Scenario :
I want to export all data(or rows) from all tables from given schemas let say s1, s2, s3 from oracle DB installed on unix server(U1) .
This data has to imported on another oracle DB ...
- 25
0
votes
1
answer
624
views
Impdp 12c takes table original size
I want to import one of my schema "vvs1" to r&d database with content=metdata_only. expdp works perfect but when i am going to import on r&d server it takes tables original size.
for example:-...
- 71
0
votes
2
answers
6k
views
How to export own objects using expdp without special privileges?
As per this post on Oracle forums expdp can be used to export your own schema without any special privileges:
It all depends on what you want that user to be able to export/import.
If all that ...
- 3,107
0
votes
2
answers
70
views
expdp fails with error ORA-31626 without any details on a full export
I am exporting a complete database (full=y) on oracle 11gR2 on Linux, and always get this error. There are no other messages, so the problem is not about timeout or permissions as shown elsewhere. ...
- 4,742
0
votes
1
answer
963
views
What does Oracle impdp do when a name clashes with a materialized view or synonym?
I have a production Oracle database, and I want to export tables, data, views, materialized views, synonyms, etc, to import into a development Oracle database. Actually, this is part of my job, and we ...
- 101
0
votes
1
answer
567
views
Datapump not working in RAC environment
Some minutes ago I've tried to do an export but I get the following error (AIX system):
Additional information: 3
ORA-31693: Table data object "XX"."TABLE_NAME" failed to load/unload and is being ...
- 634
0
votes
1
answer
723
views
EXPDP error ORA-39374 + ORA-13158 Oracle object does not exist
While exporting one of the tables in my db I got the following error :
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
...
- 125
0
votes
1
answer
203
views
Why expdp does not include identified by statement?
I have the following error when I do a impdp (from a pdb in a 12c to 11g. I also used the parameter version=11.2.0)
IMPDP LOG:
ORA-01938: IDENTIFIED BY must be specified for CREATE USER
Failing sql ...
- 634
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
0
votes
1
answer
2k
views
Using Oracle Directories names as same of path
We, at our company, have been using a small hack on Oracle Directories names since some time ago.
Before Oracle Directories we have to explicitly set the directory on pfile, by the utl_file_dir ...
- 140
0
votes
1
answer
1k
views
ROWID and expdp
Can anyone please tell me if ROWID is also exported in expdp process ans stored in dumpfile.
Can I use the following while importing?
QUERY=table_name:\"WHERE rowid = (select min(rowid) from ...
0
votes
3
answers
2k
views
Issue in DB export
I've scheduled to export my database at every midnight via Windows Task Scheduler. This was properly exported for so long. But, recently in some days, export is not happening as required. The logs are ...
- 141