crate oracle directory by dba:
CREATE OR REPLACE DIRECTORY [oracle_dir] as '[path_to_directory_on_disk]’;
remove oracle directory:
DROP DIRECTORY [oracle_dir];
check oracle directory:
SELECT directory_name, directory_path FROM dba_directories;
grant privileges to user:
GRANT READ, WRITE ON DIRECTORY [katalog oracle] TO [user];
expdp:
EXPDP [user]/[pass] SCHEMAS=[schema] LOGFILE=[logfile] DIRECTORY=[oracle dir] DUMPFILE=[dumpfile] PARALLEL=[parallel write to many files – use witch option %U as sufix in file name]
impdp:
IMPDP [user]/[pass] DUMPFILE=[plik dumpa] PARFILE=imp.par
[imp.par]
REMAP_SCHEMA=[old_user]:[new_user>]
REMAP_TABLESPACE=[old_ts]:[new_ts]
REMAP_TABLESPACE=[old_ts]:[new_ts]
DIRECTORY=[oracle directory]
LOGFILE=[logfile]
PARALLEL=[a number of processes]
STATUS=[time in sek]
JOB_NAME=[job name]
other:
DUMPFILE=dpump_dir1:plik1.dpump, dpump_dir2:plik2.dmp
FILESIZE=2G
chack current state of dump process:
SELECT job_name,state FROM v$datapump_job;
SELECT * FROM v$datapump_session;
estimate dump size:
EXPDP [user]/[pass] FULL=y ESTIMATE_ONLY=y DIRECTORY=[katalog] logfile=[logfile]
!warning
during estimation, dump size will be lower than original db size, because for indexes during dump only their definition is written, and finally indexes are rebuid, you must notice that index is based on ROWID which changing during dump
dump table:
IMPDP [user]/[pass] TABLES=[tabela] REMAP_SCHEMA=[src_schema]:[dst_schema] DIRECTORY=[directory] PARALLEL=[nr] DUMPFILE=[dumpfile]
dpump by NETWORK_LINK:
– on source db:
CREATE DATABASE LINK [link_name] CONNECT TO [src_user] IDENTIFIED BY [src_password] USING [src_tnsname]
– choose mode IMPDP or EXPDP (the difference is that IMPDP connect with source db, get data and write them directly to destination db, to specified schema, in the other side EXPDP write data only to file)
IMPDP [dst_user]/[dst_password] NETWORK_LINK = [link_name] DIRECTORY = [dir_name] SCHEMAS = [schema_name] LOGFILE = [logfile_name]
EXPDP [dst_user]/[dst_password] NETWORK_LINK = [link_name] DIRECTORY = [dir_name] SCHEMAS = [schema_name] LOGFILE = [logfile_name] DUMPFILE = [file_name]
connect to console:
EXPDP|IMPDP user/pass ATTACH=[nazwa_joba]
ctrl C #go to console mode from log mode
console commands:
exit_client #go from console mode to system
continue_client #go from console mode to log mode
help
add_file=plik1,plik2 #add files
kill_job #stop job end exit
pararell=[nr] #change numbers of process
start_job #start job which was stoped
status
stop_job[=IMMEDIATE] – stop job for a while