oracle 导入导出

导出
expdp weboa/Enterprise0830++@weboa dumpfile=sav1.dmp logfile=sbv1.log directory=DATA_PUMP_DIR version=10.1.0.2.0
导入
impdp weboa/samxon123@oa DIRECTORY=DM_PMML_DIR dumpfile=sav1.dmp logfile=20140101.log version=10.1.0.2.0


PL/SQL: 数字或值错误 :  字符串缓冲区太小 
http://blog.youkuaiyun.com/yfleng2002/article/details/7794784


show parameter trig  
alter system set "_system_trig_enabled"=false; 


job 一般发生在expdp导出数据包含了job(如:全库导出,用户导出),然后导入到目标库,而该job号已经存在导致
查询job
select job, what from dba_jobs; 
删除 job
exec dbms_job.remove (62);


由于ORACLE_HOME不同，需要手工建立表空间，如果知道源数据库用户及密码，建议用户也手工创建。


Select * From Dba_Tablespaces 查询所有表空间
1、在源端查询创建的所有表空间及DDL语句
2、在目标端创建所有表空间
3、在目标端创建所有用户
4、在源端导出数据
创建目录-->授予读写权限-->导出数据
expdp system/oracle directory=yi dumpfile=20140101.dump full=y parallel=10 exclude=statistics logfile=20140101.log
5、在目标端导入数据
创建目录-->授予读写权限-->导入数据
impdp system/oracle directory=yi dumpfile=20140101.dump full=y parallel=10 table_exists_action=truncate logfile=20140101.log
6、最后根据报错信息，手动修正
7、比对数据，进行修正
1 新建表空间




--SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name) FROM DBA_TABLESPACES TS;




/*
  CREATE TABLESPACE "EXAMPLE" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\example01.dbf' SIZE 104857600
  AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M
  NOLOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
*/


  /*CREATE TABLESPACE "OA_SYS" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\OA_SYS' SIZE 524288000
  AUTOEXTEND ON NEXT 204800 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\OA_SYS' RESIZE 1557659648*/
  
  /*
  
  
  CREATE TABLESPACE "OA_REPORT" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\OA_REPORT' SIZE 524288000
  AUTOEXTEND ON NEXT 106496 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
 
*/


 /*


  CREATE TABLESPACE "OA_ORGCODE" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\OA_ORGCODE' SIZE 209715200
  AUTOEXTEND ON NEXT 106496 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
*/


/*


  CREATE TABLESPACE "WF_PROCESS" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\WF_PROCESS' SIZE 524288000
  AUTOEXTEND ON NEXT 204800 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO*/
  
  /*
  CREATE TABLESPACE "OA_MEETING" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\OA_MEETING' SIZE 524288000
  AUTOEXTEND ON NEXT 204800 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
 */
 
 /*
 
  CREATE TABLESPACE "OA_PROJECT" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\OA_PROJECT' SIZE 314572800
  AUTOEXTEND ON NEXT 106496 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
  */
  
  /*
  
  CREATE TABLESPACE "OADOC" DATAFILE 
  'E:\ORACLE\PRODUCT\10.1.0\ORADATA\OA\OADOC' SIZE 314572800
  AUTOEXTEND ON NEXT 204800 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
  */