Datapump

Stap 1: maak een DIRECTORY testdir aan
Sqlplus / as sysdba
SQL> CREATE DIRECTORY testdir AS 'C:\plekje\testdir';
SQL> GRANT EXP_FULL_DATABASE  to scott;
SQL> GRANT READ, WRITE ON DIRECTORY testdir to scott;

Stap 2: maak de directory 'C:\plekje\testdir' op OS-level

Stap 3: Geef het export commando voor een schema:

expdp scott/tiger    SCHEMAS=SCOTT    DIRECTORY=testdir    DUMPFILE=scott.dmp    LOGFILE=scott.log



voorbeeld van een import
let op de remap_tablespace. Heel handig ivm met blobs

impdp dmodba@dmotd1 remap_schema=topdeskp:topdeskacc remap_tablespace=users:tdacc DIRECTORY=DATA_PUMP_DIR DUMPFILE=TOPDESKp.DMP LOGFILE=IMPTOPDESKP.LOG

Consistent Backups (flashback_time=systimestamp)

expdp maakt geen consistente backups van tabellen. Dit kan dus tot inconsistentie leiden mbt fk etc.

“in all cases, a table is always exported “as of a single point in time”. However, many times you want ALL tables exported as of a single point in time. If you want the equivalent of the old export “consistent=y”, you would use flashback_time=systimestamp to get the data pump export to be “as of the point in time the export began, every table will be as of the same commit point in time” ”
According to the docs:
“The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent as of this SCN.”


Oplossing: gebruik flashback_time=systimestamp


expdp scott dumpfile=expdp.dmp logfile=expdp.log flashback_time=systimestamp