Hot answers tagged

6 votes

How to replace and overwrite all existing objects in Oracle with impdp for full database import?

I don't think there is a "Replace" option for non-table objects like procedures, packages etc. The best option would be to drop the schema entirely before the datapump import. This way, datapump ...
user avatar
  • 559
5 votes
Accepted

Oracle 12c: installing expdp and impdp on Linux

Data Pump utilities are installed automatically during the installation of Oracle Database. It should be working without any issues as long as the environment variables(PATH, ORACLE_HOME) are set ...
user avatar
  • 8,406
5 votes

How to replace and overwrite all existing objects in Oracle with impdp for full database import?

There is one trap - if you drop schema - you lost all grants to other schema objects that schema had - and some of vievs/procedures/functions which depends on objects in other schemas - will not ...
user avatar
4 votes
Accepted

Sequence number shows a lower value after dump file import

If you run export command against live database without parameters like flashback_scn or flashback_time which ensures consistency during the export, then this can be an expected behavior because these ...
user avatar
  • 8,406
3 votes
Accepted

Is there a way to Oracle expdp not inherit char type nls semantics on DDL?

I made a quick and dirty solution which worked for me today (Oracle 12.1), no reason to wait for the next 11 years. create or replace trigger T_mutate_len_semantics after create on database when (SYS....
user avatar
  • 46
3 votes

How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

Are you sure about the target and source version? I did here and worked right. My source DB version was 12.1.0.2.0 and the target was 12.2.0.1.0. Please, see my example below: $ cat myfile.par ...
user avatar
3 votes
Accepted

EXPDP snapshot too old live monitoring

You need to fully understand how undo works to troubleshoot this error. I recommend this blog post: http://blog.oracle48.nl/wordpress/oracle-database-undo-space-explained/ You can use this query to ...
user avatar
3 votes
Accepted

IMPDP from Oracle 10g TO 11g

A full export/import contains tablespace definitions with datafile paths as well, so impdp tries to create the datafiles with the original path. Just create the tablespaces manually before running ...
user avatar
  • 38.2k
3 votes

Are expdp/impdp commands appropriate for daily schema sync?

No, that is not a possible way: Most of the non-table objects will already exist in your schema. You already get such error messages. The import will not overwrite it. You can configure how ...
user avatar
  • 7,441
3 votes
Accepted

Oracle Full Client / Database Client package locations

When it comes to software downloads for production use, the Oracle sites, thankfully, are a little less of a maze - provided that you are familiar with the entrance. You can find the latest media ...
user avatar
  • 1,060
3 votes

How to use / as sysdba in pluggable database?

First of all, to export from a PDB you shouldn't use SYS for that. It is way over-privileged. You should create a user in the PDB and use that user to connect to the database. You can use the ...
user avatar
3 votes

Issue With EXPDP Full Tablespace

Tracking Progress Within a Job While the data and metadata are being transferred, a master table is used to track the progress within a job. ... The master table is created in the schema of the ...
user avatar
  • 38.2k
3 votes
Accepted

ORA-39014 import in Oracle 19c from DMP file of Oracle12c

It looks like your Oracle instance is failing you. But let's roll it up from the beginning. IMPDP in Parallel To execute the IMPDP command with the PARALLEL parameter you have to meet the following ...
user avatar
2 votes

Oracle Full Client / Database Client package locations

If anyone is looking for 11.2.0.4, go to this link https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=13390677 and download Oracle client (11.2.0.4) 64-bit which is ...
user avatar
  • 21
2 votes

How to transfer data using expdp and impdp commands?

For importing the truncated tables, i.e you only want the data to be imported back to the table: impdp user/pass TABLES=dept DIRECTORY=TEST_dir dumpfile=TEST.dmp logfile=expdpTEST.log ...
user avatar
2 votes

Export pump all schemas except Sytem related schemas

As @a_horse_with_no_name mentioned, you can use the exclude parameter to limit the objects to be exported. It's very simple to use and it supports in/ like... operators. Usage example: expdp ...
user avatar
2 votes

Impdp 12c takes table original size

Typically caused by the segment attributes of the tables. You can use TRANSFORM=SEGMENT_ATTRIBUTES:N, so the tables will be created with default options. You can use TRANSFORM=SEGMENT_CREATION:N as ...
user avatar
  • 38.2k
2 votes

Oracle Data Pump Export

It is enough to specify the schemas parameter. expdp \'/ as sysdba\' schemas=siddhu If you want to export only the tables, you can also add include=table: expdp \'/ as sysdba\' schemas=siddhu ...
user avatar
  • 38.2k
2 votes
Accepted

expdp Hangs at DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM in oracle 12c

So in the end it's just a time issue you have. You would have to open up a ticket with Oracle if you want to speed things up. This is probably not an Oracle issue, but possibly an issue with your ...
user avatar
2 votes

Does the target database need to have the same structure of the source database before to use IMPDP?

The new database can have a different directory structure and even storage system, but you'd need some preparation before running the import. Especially, you'd need to create the tablespaces in ...
user avatar
  • 468
2 votes

How to use / as sysdba in pluggable database?

First of all, \"/ as sysdba@mypdb_oracle_net\" will not work. SYSDBA OS authentication will not work through the listener. Second, \"/ as sysdba\" is flawed in 19c (compared to earlier versions), ...
user avatar
  • 38.2k
2 votes
Accepted

EXPDP - why is block estimate significantly larger than segment size?

Because the table may have LOB segments that have different names. select column_name, segment_name from user_lobs where table_name = 'TABLENAME'; And you should check the size of those segments as ...
user avatar
  • 38.2k
1 vote

How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] (Doc ID 553337.1) Data Pump client compatibility. =============================== ...
user avatar
  • 38.2k
1 vote
Accepted

ORA-39001: ORA-39000: ORA-31640: ORA-27041: and Linux-x86_64 Error: 13: Permission denied

The main issue is Linux-x86_64 Error: 13: Permission denied cd /u01/app/oracle/admin/XE/dpdump/ chmod 777 -R gsw Then login as oracle user and run the same command again
user avatar
  • 158
1 vote

Can expdp be piped directly into impdp, without intermediate files?

As @mutap stated in the comments, you can expdp/impdp over the network. Technically, you will be doing the DataPump export and import over a database link without the need for an intermediate file. ...
user avatar
  • 4,706
1 vote
Accepted

Using Oracle Directories names as same of path

Just because you are able to do something, it does not necessarily mean it is a good idea. [oracle@o73 ~]$ sqlplus / as sysdba SQL*Plus: Release 18.0.0.0.0 Production on Thu Apr 5 16:20:15 2018 ...
user avatar
  • 38.2k
1 vote

Pass .csv file as parameter file for expdp Oracle 11.2.0.4.0

No you cannot. I'm not entirely sure what you are after here - I'm assuming that you want to unload a table in CSV format ? If that's the case, then an easy way would be to download sqcl http://www....
user avatar
1 vote

Is there a way to Oracle expdp not inherit char type nls semantics on DDL?

This feature was requested 11 years ago, and it is still not implemented: Bug 5034478 : ENH: ADD PARAMETER TO IMPDP TO OVERWRITE NLS_LENGTH_SEMANTICS At the moment there is no way of moving all ...
user avatar
  • 38.2k
1 vote

ORA-01555:Snapshot too old error during execution of expdp command

If you can't change your "retention policy" (aka UNDO_RETENTION), you should search for other ways to improve your expdp performance. You can try one (or more) of the following: Try to higher your ...
user avatar
1 vote

Oracle file permissions expdp vs dbms_datapump

I assume that the database runs on a Linux OS. I assume that you connect with a tool like sqldeveloper to the database using a tns-connection string (maybe from a machine different) I assume that you ...
user avatar
  • 7,441

Only top scored, non community-wiki answers of a minimum length are eligible