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 ...
- 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 ...
- 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 ...
- 51
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 ...
- 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....
- 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
...
- 31
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 ...
- 2,067
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 ...
- 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 ...
- 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 ...
- 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 ...
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 ...
- 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 ...
- 15k
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 ...
- 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 ...
- 19
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 ...
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 ...
- 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 ...
- 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 ...
- 15k
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 ...
- 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), ...
- 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 ...
- 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.
===============================
...
- 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
- 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.
...
- 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
...
- 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....
- 541
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 ...
- 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 ...
- 140
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 ...
- 7,441
Only top scored, non community-wiki answers of a minimum length are eligible