8
votes
Accepted
Oracle expdp puts INDEXES to LOGGING mode
This is caused by transform=DISABLE_ARCHIVE_LOGGING:Y. Because of that parameter, the database creates indexes with NOLOGGING property to avoid generating redo, and at the end, it changes the index to ...
- 41.1k
7
votes
impdp stuck on index import
It's not stuck. It just does not print every rebuilded index.
You can view status of the process using DBA_RESUMABLE.
select * from dba_resumable;
7
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 ...
- 71
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 Data Pump, pause a job
Well you can stop the job and make changes in the table and later continue the job either skipping the current object or not.
Demo:
[oracle@localhost]$ impdp jay/password directory=dp_dir tables=...
- 8,650
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,650
4
votes
Accepted
impdp stuck on index import
During the impdp process, it doesn't import indexes, in fact it runs number of CREATE INDEX statements.
There could be various reasons of slow index creations such as inefficient PGA memory ...
- 8,650
4
votes
Accepted
How to Import an Oracle dump version 5.1 into 12c?
Of course Oracle did not make this information public, but if you can access MOS, then go ahead:
Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle ...
- 41.1k
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,650
3
votes
restore database using impdp full back
Data Pump uses directory objects. Read this:
Default Locations for Dump, Log, and SQL Files
First create the directory object as:
create directory dump_dir as '/home/oracle/rmanscripts/dumbbackup';
...
- 41.1k
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 ...
- 41.1k
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 ...
- 18.3k
2
votes
Import datadump in schema mode
One way to find the cause of ORA errors is to use oerr tool by Oracle. In the error stack the top most is the application error which is followed by other secondary errors.
For example, the ORA-...
- 8,650
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 ...
- 41.1k
2
votes
Oracle db impdp command syntax error
Fromuser and touser are not valid using datapump. If you are trying to change the schema, you can use remap_schema:
REMAP_SCHEMA=source_schema:target_schema
https://docs.oracle.com/database/121/...
- 126
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
ORA-39097: Data Pump job encountered unexpected error -942
Known issue:
https://community.oracle.com/thread/4125750
ORA-39006, ORA-39065 and ORA-00942: table or view does not exist When Starting DataPump in 12.2 (Doc ID 2341607.1)
Rebuild the missing part ...
- 41.1k
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 ...
- 498
2
votes
Accepted
Oracle - impdp - import a partition
According to this example in the Oracle Docs:
TABLES=XY_RESPONSE:SYS_P25129
should be sufficient.
Unless you have a very strange collation in your database, no quoting is required for any of ...
- 9,480
2
votes
Accepted
Import Tables without Constraints from Schema Export
You are right when stating I thought that by using INCLUDE=TABLE only the tables would be included, but it includes all related objects too. This means that constraints and triggers will be created ...
- 18.3k
2
votes
Get tablespace name from dump file
https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm
impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
SQLFILE=dpump_dir2:expfull.sql
- 672
2
votes
Why does user default tablespace not change from USER to new tablespace when import transport tablespace in oracle using IMPDP command?
A user can write to several tablespaces (he just needs to have the corresponding quota in the given tablespace, or the UNLIMITED TABLESPACE privilege).
A tablespace can be written by several users.
...
1
vote
Migrated DB from 12c to 19c with IMPDP failed
2. Import excluding statistics in new system - Oracle 19c:
$ impdp user/password@vcas exclude=statistics dumpfile=dump_file_name.dmp logfile=log_file_name.log schemas=SCHEMA01, SCHEMA02, SCHEMA03, ...
- 11
1
vote
ORA-39002, ORA-39168 on IMPDP, Object path GRANT was not found
This happened because you specified EXCLUDE=GRANT on export as well.
This is expected behavior - according to Oracle.
The workaround is to not specify the same EXCLUDE in expdp and impdp.
See the ...
- 41.1k
1
vote
Database Hangs while impdp "DATABASE_EXPORT/STATISTICS/MARKER" at this stage
Known bug, still under investigation.
Workaround is to use exclude=statistics.
- 41.1k
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
Avoid already existing objects when importing into Oracle
Exclude the schemas with the exclude parameter. E.g:
EXCLUDE=SCHEMA:\"IN\(\'SYS\',\'SYSTEM\',\'OUTLN\',\'ORDDATA\',\'OLAPSYS\',\'MDDATA\',\'SPATIAL_WFS_ADMIN_USR\',\'ANONYMOUS\',\'...
- 41.1k
1
vote
Accepted
DBMS_DATAPUMP error: The current API cannot be executed because of inconsistencies between the API and the current definition of the job
After many tries I found this way:
Running expdp with version=11.2 parameter set.
Copy with DBMS_FILE_TRANSFER.PUT_FILE the dmp to the RDS Instance
Running impdp with version=11.2 parameter set ...
- 337
1
vote
DBMS_DATAPUMP error: The current API cannot be executed because of inconsistencies between the API and the current definition of the job
Your versions differ between source and destination.
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null, version=>'COMPATIBLE');
Oh, the bad news: ...
- 3,255
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,839
Only top scored, non community-wiki answers of a minimum length are eligible