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.csdn.net/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 */