purpors: exp pro data to uat data
exp and imp will not create user
expdp and impdp will create user
1.from table to table
-> IN PROD TS
exp TS/TEST_Data130@TSprod file=D:\projects\TS\TS_TS_20160908_update.dmp log=D:\projects\TS\eais_all_exp_2016_09_02.log TABLES=(TS_record_his,TS_record)
->IN UAT
imp TS/gatepassuat_data2@TSuat file=D:\projects\TS\TS_TS_20160908_update.dmp log=D:\projects\TS\TS_upd_imp_2016_09_05.log fromuser=TS touser=TS tables=(TS_record_his,TS_record)
expdp/impdp
-> IN PROD user:TS
expdp system/oracle123@TSprod file=TS_TS_20160908_update.dmp directory=TMP_DIR log=TS_all_exp_2016_09_02.log tables=('TS.TS_record_his','TS.TS_record')
->IN UAT
impdp system/oracle123_@TSuat directory=DUMP_DIR dumpfile=TS_TS_20160908_update.dmp log=TS_all_imp_2016_09_02.log table_exists_action=replace remap_schema=TS:TS
--tables=('TS_record_his','TS_record') table_exists_action=replace remap_schema=TS:TS;
--tables=TS.TS_record_his,TS.TS_record remap_schema=TS:TS;
-> in prod SQL> select * from dba_directories;
OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH ------------------------------------------------------------------------ SYS TMP_DIR D:\dba\tmp
SYS XMLDIR D:\app\Administrator\product\11.2.0\dbhome_1\rdbms\xml
SYS ORACLE_OCM_CONFIG_DIR D:\app\Administrator\product\11.2.0\dbhome_1/ccr/state
OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH ------------------------------------------------------------------------ SYS DATA_PUMP_DIR D:\app\Administrator/admin/TSprod/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 D:\app\Administrator\product\11.2.0\dbhome_1/ccr/state
-> in UAT SQL> select * from dba_directories;
OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS DUMP_DIR D:\dba\dump
SYS XMLDIR D:\app\Administrator\product\11.2.0\dbhome_1\rdbms\xml
SYS ORACLE_OCM_CONFIG_DIR D:\app\Administrator\product\11.2.0\dbhome_1/ccr/state
OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS DATA_PUMP_DIR D:\app\Administrator/admin/TSuat/dpdump/
SYS ORACLE_OCM_CONFIG_DIR2 D:\app\Administrator\product\11.2.0\dbhome_1/ccr/state
2.from schema to schema
-Prepare:
c:\app\oracle\dump: datapump
select count(*) from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','OPER','BACKUPDB'))
select owner,object_name from dba_objects where owner IN
(select username from dba_users where account_status='OPEN' and username not in ('SYS','SYSTEM','OPER','BACKUPDB'))
and statuS='INVALID';
-charater
SELECT * FROM v$nls_parameters WHERE PARAMETER='NLS_CHARACTERSET';
set NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
-rollout
in prod
expdp system/oracle123@testuat file=TS_TS_20160928_update.dmp directory=datapump log=TS_all_exp_2016_09_28.log SCHEMAS='etestquery','testrusr','testdata' parallel=2
in uat
impdp tuser/tuser file=TS_TS_20160928_update.dmp directory=datapump 'etestquery','testrusr','testdata' table_exists_action=replace parallel=2;
in prod
expdp system/oracle123@testuat file=TS_TS_20160928_update.dmp directory=DATA_PUMP_DIR log=TS_all_exp_2016_09_28.log SCHEMAS='etestquery','testrusr','testdata' parallel=2;
in uat
impdp tuser/tuser file=TS_TS_20160928_update.dmp directory=DATA_PUMP_DIR SCHEMAS='etestquery','testrusr','testdata' table_exists_action=replace parallel=2 log=TS_all_imp_2016_09_28.log;
执行@$ORACLE_HOME/rdbms/admin/utlrp.sql脚本编译数据库失效对象。
Run the utlrp.sql script as SYS user.
@%ORACLE_HOME%/rdbms/admin/utlrp.sql
3.ps
使用imp进行数据导入时，若表已经存在，要先drop掉表，再进行导入。
而使用impdp完成数据库导入时，若表已经存在，有四种的处理方式：
1) skip：默认操作
2) replace：先drop表，然后创建表，最后插入数据
3) append：在原来数据的基础上增加数据
4) truncate：先truncate，然后再插入数据