一、导入dmp文件:
1.创建表空间
create tablespace 表空间 datafile '路径\文件名.dbf' size 1500M autoextend on next 5M maxsize 3000M;注:路径必须为已创建2.创建用户create user 用户名 identified by 密码 default tablespace 表空间;3.更改用户的表空间alter user 用户名 default tablespace 表空间;4.为用户赋权GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW , DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, DBA,CONNECT,RESOURCE,CREATE SESSION TO 用户名;5.导入dmp文件运行-cmd-进入dmp文件所在的目录, imp 用户名/密码@服务名 file=f.dmp full=y ignore=y 注:无法转换为环境字符集句柄-数据泵不一致导致的,比如说你用expbd导出来的 用imp导入的时候就会出现这个错误exp导出来的用imp导入expdp导出来的用impdp导入6.导出dmp文件
exp 用户名/密码@服务名 file=d:\oa.dmp full = y
7.存储过程示例:
declarecol1 varchar(20);col2 varchar(20);test_sql varchar(2000);val varchar(1):='1';val1 varchar(50):='家庭成员基本信息';val2 varchar(50):='家庭成员登记';beginfor i in 1..17 loopcol1 := 'item.col_'|| lpad(i+12||'',3,'0');col2 := 'item1.col_'|| lpad(6*i-5||'',3,'0');test_sql := 'update NZZ_STATISTICS nzz set nzz.nation = (select trim('||col1||')from NZZ_RECORD t left outer join DEF_MODEL def on t.MODEL_ID=def.id left join NZZ_RECORD nr on nr.P_RECORD_ID=t.idleft join NZZ_RECORD nr1 on nr1.P_RECORD_ID=t.idleft join NZZ_RECORD_ITEM item on item.record_id=nr.idleft join NZZ_RECORD_ITEM item1 on item1.record_id=nr1.idwhere t.patient_name is not null and t.P_RECORD_ID is null and t.DEL_USER_ID is null and def.is_investigation='''||val||'''and (nr.name='''||val1||''' and nr1.name='''||val2||''') and '||col2||' is not nulland t.patient_name!='||col2||' and t.id=nzz.related_id)WHERE nzz.related_id IN (SELECT ID FROM NZZ_RECORD) and nzz.nation is null and nzz.member_no='||i;dbms_output.put_line(test_sql);EXECUTE IMMEDIATE test_sql;end loop;/*commit;*/end;