数据库导入导出数据泵

--导入测试数据库
--connect sys/password as sysdba
---删除表空间
drop tablespace pt6 INCLUDING CONTENTS;
drop tablespace mpm INCLUDING CONTENTS;
drop tablespace oms INCLUDING CONTENTS;

--当首次导入时创建表空间 （运行脚本时会提示输入 &1 输入数据文件希望存放的物理地址 例如 D:\OracleData）
create tablespace pt6 datafile 'F:\Oracle_Data\omsADJ\pt6.dbf' size 100m autoextend on next 10m;
create tablespace mpm datafile 'F:\Oracle_Data\omsADJ\mpm.dbf' size 100m autoextend on next 10m;
create tablespace oms datafile 'F:\Oracle_Data\omsADJ\oms.dbf' size 100m autoextend on next 10m;


--删除用户
drop user pt6 CASCADE;
drop user mpm CASCADE;
drop user oms CASCADE;

--创建用户
create user pt6 identified by cape default tablespace pt6;
create user mpm identified by cape default tablespace mpm;
create user oms identified by cape default tablespace oms;


--赋权限
grant dba to pt6;
grant dba to mpm;
grant dba to oms;


grant all on dual to pt6 with grant option;
grant all on dual to oms with grant option;
grant all on dual to mpm with grant option;

expdp pt6/cape@mPMoms dumpfile=MPMoms20131122.EXPDB logfile=MPMoms20131122.log schemas=(pt6,mpm,oms)

impdp pt6/cape@192.168.0.20:1521/MPMoms dumpfile=MPMoms20131029_DKBK.EXPDB schemas=(pt6,mpm,oms)

数据泵导入导出 将文件放入MPMoms20131029_DKBK.EXPDB D:\app\Administrator\admin\MPMoms\dpdump

MPMoms为数据库实例

--导入测试数据库
--connect sys/password as sysdba
--当首次导入时创建表空间 （运行脚本时会提示输入 &1 输入数据文件希望存放的物理地址 例如 D:\OracleData）
create tablespace apps datafile 'D:\oracle\product\10.2.0\admin\MRO\apps.dbf' size 100m autoextend on next 10m;
create tablespace appsys datafile 'D:\oracle\product\10.2.0\admin\MRO\appsys.dbf' size 100m autoextend on next 10m;
create tablespace hr datafile 'D:\oracle\product\10.2.0\admin\MRO\hr.dbf' size 100m autoextend on next 10m;
create tablespace appview datafile 'D:\oracle\product\10.2.0\admin\MRO\appview.dbf' size 100m autoextend on next 10m;
create tablespace workflow datafile 'D:\oracle\product\10.2.0\admin\MRO\workflow.dbf' size 100m autoextend on next 10m;
create tablespace oa datafile 'D:\oracle\product\10.2.0\admin\MRO\oa.dbf' size 100m autoextend on next 10m;
create tablespace mro datafile 'D:\oracle\product\10.2.0\admin\MRO\mro.dbf' size 100m autoextend on next 10m;






--删除已有用户，首先运行 select * from v$database; 查看数据库信息
--在运行脚本时会提示 输入 sid,请输入select * from v$database查询的name字段值
set serveroutput on size 1000000;
declare
v_sid varchar2(1000);
cursor cur_user is select username from all_users where lower(username) in ('apps','appsys','hr','appview','workflow','bbp', 'oa', 'mro') order by decode( lower(username),'apps',chr(1), lower(username));
begin
select name into v_sid from v$database;
if upper(v_sid) = upper('&sid') then
  for rec_user in cur_user loop
    dbms_output.put_line('***********************************');
    dbms_output.put_line('开始删除用户'||rec_user.username);
    execute immediate ('drop user '||rec_user.username||' cascade');
    dbms_output.put_line('成功删除用户'||rec_user.username);
    dbms_output.put_line('***********************************');
    dbms_output.put_line('');
  end loop;
else
  dbms_output.put_line('请输入正确的SID!');
end if;
end;
/


--创建用户
create user apps identified by cape default tablespace apps;
create user appsys identified by cape default tablespace appsys;
create user hr identified by cape default tablespace hr;
create user appview identified by cape default tablespace appview;
create user workflow identified by cape default tablespace workflow;
create user oa identified by cape default tablespace oa;
create user mro identified by cape default tablespace mro;






--赋权限
grant dba to apps;
grant dba to appsys;
grant dba to hr;
grant dba to appview;
grant dba to workflow;
grant dba to oa;
grant dba to mro;






grant all on dual to appsys with grant option;
grant all on dual to apps with grant option;
grant all on dual to hr with grant option;
grant all on dual to appview with grant option;
grant all on dual to workflow with grant option;
grant all on dual to oa with grant option;
grant all on dual to mro with grant option;






/******************************************************
--connect appview
******************************************************/
grant create any table to apps;
grant alter any table to apps;
grant drop any table to apps;
grant create any index to apps;
grant drop any index to apps;


在CMD 下面执行：
--使用IMP导入数据文件, 下面的数据库实例名称及数据文件路径需要修改为实际的值
imp apps/cape@mro file=e:\SHICMRO.dmp fromuser=appsys touser=appsys fromuser=apps touser=apps fromuser=hr touser=hr fromuser=appview touser=appview fromuser=workflow touser=workflow fromuser=oa touser=oa fromuser=mro touser=mro ignore=y


imp apps/cape@10.216.60.39/mro file=e:\SHICMRO.dmp fromuser=appsys touser=appsys fromuser=apps touser=apps fromuser=hr touser=hr fromuser=appview touser=appview fromuser=workflow touser=workflow fromuser=oa touser=oa fromuser=mro touser=mro ignore=y 


--dos下导出
exp apps/cape@mro file=e:\MRO20121022.dmp owner=(appsys,apps,hr,appview,workflow,oa,mro)


exp apps/cape@10.216.60.69:1521/MRO file=e:\SHICMRO.dmp owner=(appsys,apps,hr,appview,workflow,oa,mro)


create synonym apps.train_apply_shic for trainexam.train_apply_shic_v ;


grant all on trainexam.train_apply_shic_v to apps with grant option ;