导出
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
*/
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
*/