DanBrother的部落格

-- Create Database Directory
create directory mydump_dir as '/home/orale/mydump'

-- Grant Directory Privileges
grant read,write on DIRECTORY mydump_dir to SCOTT;

-- Query Permissions Granted to the Directory
select * from dba_tab_privs where table_name = UPPER('mydump_dir');

-- Revoke Directory Privileges
revoke read,write on DIRECTORY mydump_dir from SCOTT;

-- Drop the Directory
drop DIRECTORY mydump_dir;

---------------------------------------------------------------------------------------------------------------------
read -s MYPWD

-- Datapump Export (Full Database)

expdp system/$MYPWD@testdb full=y directory=mydump_dir dumpfile=testdb_dump_full.dmp logfile=testdb_dump_full.log

-- Datapump Export (Schema with Data)
expdp system/$MYPWD@testdb directory=mydump_dir schemas=scott dumpfile=scott_with_data_exp.dmp logfile=scott_with_data_exp.log

-- Datapump Export (Schema without Data , Metadata Only)
expdp system/$MYPWD@testdb directory=mydump_dir schemas=scott dumpfile=scott_without_data_exp.dmp logfile=scott_without_data_exp.log content=METADATA_ONLY

-- Datapump Export (Tables & Sequences)
cat dan_objects.par
>>
include=table:"LIKE 'DAN!_TAB!_%' ESCAPE '!'"
include=sequence:"LIKE 'DAN!_TAB%!_SEQ' ESCAPE '!'"

expdp danbrother/$MYPWD@testdb directory=mydump_dir parfile=dan_objects.par  
dumpfile=DANBROTHER_OBJECTS.dmp logfile=DANBROTHER_OBJECTS.log

-- Datapump Export (SQL Query)
cat dan_query.par
>>
query=TEST_TAB:"where orderno in ('100001','100002','100005','100010')"

expdp danbrother/$MYPWD@testdb directory=mydump_dir tables=TEST_TAB parfile=dan_query.par
dumpfile=DISCARDED_ORDERS.dmp logfile=DISCARDED_ORDERS.log 

 -- Datapump Import  (With Truncate Option)
impdp system/$MYPWD@testdb directory=mydump_dir schemas=scott table_exists_action=truncate include=table_data dumpfile=testdb_dump_full.dmp logfile=testdb_scott_schema_imp.log

-- Datapump Import  (With Remap_Tablespace Option)
impdp system/$MYPWD@testdb directory=mydump_dir schemas=scott  dumpfile=scott_with_data_exp.dmp logfile=scott_with_data_imp.log remap_tablespace=(USERS:SCOTT_DATA,INDX:SCOTT_INDX)

-- Datapump Import  (With Remap_Schema Option)
impdp system/$MYPWD@testdb directory=mydump_dir dumpfile=scott_with_data_exp.dmp logfile=danbrother_with_data_imp.log remap_tablespace=(USERS:DAN_DATA,INDX:DAN_INDX) remap_schema=SCOTT:DANBROTHER