Oracle exppd/impdp

--触发器、存储过程都会导出/导入
--https://www.cnblogs.com/huacw/p/3888807.html
CREATE OR REPLACE DIRECTORY EXP_DIR AS 'E:/';

--全部导出/导入
EXPDP TEST001/[email protected] DIRECTORY=EXP_DIR DUMPFILE=TEST001_01.DMP PARALLEL=5
IMPDP TEST001/[email protected] DIRECTORY=EXP_DIR DUMPFILE=TEST001_01.DMP FULL=y;

--追加数据
	table_exists_action：
	skip 是如果已存在表，则跳过并处理下一个对象；
	append是为表增加数据;
	truncate是截断表，然后为其增加新数据；
	replace是删除已存在表，重新建表并追加数据;
impdp TEST001/[email protected] DIRECTORY=EXP_DIR DUMPFILE=TEST001_01.dmp TABLE_EXISTS_ACTION=append


--带where导出
expdp TEST001/[email protected] directory=exp_dir dumpfile=test001_02.dmp tables=tbl01,tbl03 query=tbl01:\"where mid<10\",tbl03:\"where mid<5\"
--delete from tbl01 where mid<10;delete from tbl03 where mid<5;
impdp TEST001/[email protected] DIRECTORY=EXP_DIR DUMPFILE=test001_02.dmp TABLE_EXISTS_ACTION=append


--truncate 
--https://blog.csdn.net/huoshuyinhua/article/details/74177860 
--必须更新全局索引，否则失效
alter table t_ent_shuru truncate PARTITION SKPART1807 update global indexes;


--本机备份、删除、导入测试
expdp ic_emos2/[email protected] directory=dump_dir dumpfile=t_ent_shuru_SKPART1807.dmp tables=t_ent_shuru:SKPART1807
alter table t_ent_shuru truncate PARTITION SKPART1807 update global indexes;
impdp ic_emos2/[email protected] directory=dump_dir dumpfile=t_ent_shuru_SKPART1807.dmp table_exists_action=append
--测试2  2条记录导出，更改1条记录时间，导入备份。重复记录导入失败
--测试2  创建临时用户,新建表,导入数据,MERGE
	impdp ic_emos2/[email protected] directory=dump_dir dumpfile=t_ent_shuru_SKPART1807.dmp table_exists_action=append remap_schema=ic_emos2:test001 
	MERGE INTO IC_EMOS2.T_ENT_SHURU T1
	USING TEST001.T_ENT_SHURU T2
	ON(T1.MAIN_ID=T2.MAIN_ID)
	WHEN MATCHED THEN
	  UPDATE SET T1.SO2=T2.SO2
	WHEN NOT MATCHED THEN
	 INSERT (MAIN_ID,T_ENT_ID,SO2,NOX,GAS,DATA,T_STATION_ID,GUID) 
	 VALUES (T2.MAIN_ID,T2.T_ENT_ID,T2.SO2,T2.NOX,T2.GAS,T2.DATA,T2.T_STATION_ID,T2.GUID)


--分析表  http://czmmiao.iteye.com/blog/1483190
exec DBMS_STATS.GATHER_TABLE_STATS(NULL,'T_ENT_SHURU', GRANULARITY => 'GLOBAL', CASCADE => TRUE);
select table_name, t.num_rows, t.last_analyzed  from tabs t order by NUM_ROWS desc nulls last;