oracle导入导出错误

zzgat

我将本机数据导出dmp，再将导出数据导入新装服务器，新服务器登录时提示：执行ksql:select r,par,p.* from SA_OPRole par optional join SA_OPPermission p on p.sPermissionRoleID=par and par.sValidState = 1 and p.sValidState=1 join SA_OPRole r on par=r.sParentRole AND r IN ('8673C06DD046473A96FC278C14139894', 'BA762BB0BA5D45DDAEFA85E6C878E391', 'DEMO_Roles')时出错！
导出命令  exp x5sys/x5@orcl owner=x5sys file=d:\x5sys.dmp 新服务导入： imp x5sys@x5@orcl file=d:\x5sys.dmp full=y

jishuang

导入的时候有提示导入数据错误吗？
把控制台详细的错误信息发一下

zzgat

IMP-00041: 警告: 创建的对象带有编译警告
"CREATE FORCE VIEW "WTSYS"."DEMO_BOOKVIEW"                           ("FBOOK"
"ID","FBOOKNAME","FPRICE","FISBN","FAMOUNT") AS "
"select"
"      DEMO_Book.fBookID, DEMO_Book.fBookName, DEMO_Book.fPrice, DEMO_Book.fISB
N,"
" DEMO_BookStore.fAmount"
"from DEMO_Book"
"      left join DEMO_BookStore on DEMO_Book.fBookID = DEMO_BookStore.fBookID"
成功终止导入, 但出现警告。

F:\orcal\Administrator\product\11.2.0\dbhome_1\BIN>imp wtdoc/password@orcl file=
d:\oa\wtdoc.dmp full=y
IMP-00041: 警告: 创建的对象带有编译警告
"CREATE FORCE VIEW "WTAPP"."V_SA_OPORG"                           ("SID","SN"
"AME","SCODE","SLONGNAME","SFNAME","SFCODE","SFID","SORGKINDID","SSEQUENCE","
""SVALIDSTATE","SPARENT","SLEVEL","SPHONE","SFAX","SADDRESS","SZIP","SDESCRI"
"PTION","VERSION","SPERSONID") AS "
"select "SID","SNAME","SCODE","SLONGNAME","SFNAME","
"       "SFCODE","SFID","SORGKINDID","SSEQUENCE","SVALIDSTATE","SPARENT","
"       "SLEVEL","SPHONE","SFAX","SADDRESS","SZIP","SDESCRIPTION","VERSION","
""SPERSONID""
"from x5sys.Sa_Oporg"
IMP-00041: 警告: 创建的对象带有编译警告
"CREATE FORCE VIEW "WTAPP"."V_SA_OPPERSON"                           ("SID","
""SNAME","SCODE","SIDCARD","SNUMB","SLOGINNAME","SPASSWORD","SPASSWORDTIMELI"
"MIT","SPASSWORDMODIFYTIME","SMAINORGID","SSAFELEVELID","SSEQUENCE","SVALIDS"
"TATE","SDESCRIPTION","SSEX","SBIRTHDAY","SJOINDATE","SHOMEPLACE","SDEGREE","
""SGRADUATESCHOOL","SSPECIALITY","SSCHOOLLENGTH","STITLE","SMARRIAGE","SCARD"
"NO","SCARDKIND","SFAMILYADDRESS","SZIP","SMSN","SQQ","SMAIL","SMOBILEPHONE""
","SFAMILYPHONE","SOFFICEPHONE","VERSION","SPHOTO","SCOUNTRY","SPROVINCE","S"
"CITY","SPOSITIONS","SSCHOOL","SSTUDY","SENGLISHNAME","SMAINORGFID","SMAINOR"
"GFNAME","SMAINSEQUENCE") AS "
"select p."SID",p."SNAME",p."SCODE",p."SIDCARD",p."SNUMB",p."SLOGINNAME",p.""
"SPASSWORD",p."SPASSWORDTIMELIMIT",p."SPASSWORDMODIFYTIME",p."SMAINORGID",p."
""SSAFELEVELID",p."SSEQUENCE",p."SVALIDSTATE",p."SDESCRIPTION",p."SSEX",p."S"
"BIRTHDAY",p."SJOINDATE",p."SHOMEPLACE",p."SDEGREE",p."SGRADUATESCHOOL",p."S"
"SPECIALITY",p."SSCHOOLLENGTH",p."STITLE",p."SMARRIAGE",p."SCARDNO",p."SCARD"
"KIND",p."SFAMILYADDRESS",p."SZIP",p."SMSN",p."SQQ",p."SMAIL",p."SMOBILEPHON"
"E",p."SFAMILYPHONE",p."SOFFICEPHONE",p."VERSION",p."SPHOTO",p."SCOUNTRY",p."
""SPROVINCE",p."SCITY",p."SPOSITIONS",p."SSCHOOL",p."SSTUDY",p."SENGLISHNAME"
"", o.sFID as sMainOrgFID, o.sFName as sMainOrgFName,o.sSequence as sMainSeq"
"uence"
"from x5sys.SA_OPPerson p"
"  left join x5sys.SA_OPOrg o on p.sMainOrgID = o.sID"
IMP-00041: 警告: 创建的对象带有编译警告
"CREATE FORCE VIEW "WTAPP"."OA_PUB_EXECUTE"                           ("FID""
","FMASTERID","FTASKID","FACTIVITYFNAME","FACTIVITYLABEL","FOPINION","FVERDI"
"CT","FSTATE","FSTATENAME","FCREATEOGNID","FCREATEOGNNAME","FCREATEDEPTID",""
"FCREATEDEPTNAME","FCREATEPOSID","FCREATEPOSNAME","FCREATEPSNID","FCREATEPSN"
"NAME","FCREATEPSNFID","FCREATEPSNFNAME","FCREATETIME","FUPDATEPSNID","FUPDA"
"TEPSNNAME","FUPDATETIME","VERSION","FBIZKIND") AS "
"select  replace(sys_guid(), '-', '') as fid,"
"                t.SDATA1 as fMasterID,"
"                t.sid as ftaskid,"
"                '' FACTIVITYFNAME,"
"                '' FACTIVITYLABEL,"
"                t.sContent FOPINION,"
"                '' FVERDICT,"
"                t.sstatusid FSTATE,"
"                t.sstatusname FSTATENAME,"
"                t.sexecutorognid FCREATEOGNID,"
"                t.sexecutorognname FCREATEOGNNAME,"
"                t.sexecutordeptid FCREATEDEPTID,"
"                t.sexecutordeptname FCREATEDEPTNAME,"
"                t.sexecutorposid FCREATEPOSID,"
"                t.sexecutorposname FCREATEPOSNAME,"
"                t.sexecutorpersonid FCREATEPSNID,"
"                t.sexecutorpersonname FCREATEPSNNAME,"
"                t.sexecutorfid FCREATEPSNFID,"
"                t.sexecutorfname FCREATEPSNFNAME,"
"                t.sactualstarttime FCREATETIME,"
"                t.sexecutorpersonid FUPDATEPSNID,"
"                t.sexecutorpersonname FUPDATEPSNNAME,"
"                t.sactualfinishtime FUPDATETIME,"
"                t.version VERSION,"
"                t.stypename FBIZKIND"
"  from x5sys.sa_task t"
" where t.skindid = 'tkTask'"
IMP-00041: 警告: 创建的对象带有编译警告
"CREATE FORCE VIEW "WTAPP"."V_OA_DC_READOPINION"                           ("
""FID","FMASTERID","FPERSONID","FPERSONNAME","FUPDATETIME","FOPINION","FACTI"
"VITYFNAME","FSTATE") AS "
"select SYS_GUID() fID,"
"       r.fmasterid,"
"       r.fpersonid,"
"       r.fpersonname,"
"       CASE WHEN e.fupdatetime IS NOT NULL then e.fupdatetime else e.fcreat"
"etime end fupdatetime,"
"       e.fOpinion,"
"       e.FACTIVITYFNAME,"
"       case when e.fcreatepsnid is null then '未阅' else '已阅' end as fSta"
"te"
"  from oa_dc_readerrange r"
"  left join oa_pub_execute e on r.fmasterid = e.fmasterid"
"                            and r.fpersonid = e.fcreatepsnid"
"                            and e.factivityfname = 'readerOpinion'"
成功终止导入, 但出现警告。

zzgat

我导入你们提供的dmp没有问题。登录正常。我考虑是我导出命令的问题。但不知道错在什么地方

jishuang

把登录时控制台报错的信息错误信息复制到txt文件中，上传txt文件看一下

zzgat

你能不能先给我提供下导出的命令，我再将数据导出下试试

zzgat

我的导出命令和你们的导入方法一样吗

zzgat

导入.zip (1.57 KB, 下载次数: 1591)

2013-6-19 16:43 上传

点击文件名下载附件 文件已经上传。你看看问题在什么地方

jishuang

把视图先编译一下再导出

参考6楼，把你现在登录报的错误发一下

zzgat

登录提示：执行ksql:select r,par,p.* from SA_OPRole par optional join SA_OPPermission p on p.sPermissionRoleID=par and par.sValidState = 1 and p.sValidState=1 join SA_OPRole r on par=r.sParentRole AND r IN ('8673C06DD046473A96FC278C14139894', 'BA762BB0BA5D45DDAEFA85E6C878E391', 'DEMO_Roles')时出错