oracle 导入导出项目实操

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

create tablespace train datafile '&1\train.dbf' size 100m autoextend on next 10m;
create tablespace trainexercise datafile '&1\trainexercise.dbf' size 100m autoextend on next 10m;
create tablespace trainexam datafile '&1\trainexam.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', 'train', 'trainexercise', 'trainexam') 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 train identified by cape default tablespace train;
create user trainexercise identified by cape default tablespace trainexercise;
create user trainexam identified by cape default tablespace trainexam;

--赋权限
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 train;
grant dba to trainexercise;
grant dba to trainexam;

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 train with grant option;
grant all on dual to trainexercise with grant option;
grant all on dual to trainexam 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;

--使用IMP导入数据文件, 下面的数据库实例名称及数据文件路径需要修改为实际的值
imp apps/cape@train file=e:\database_trainning.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=train touser=train fromuser=trainexam touser=trainexam fromuser=trainexercise touser=trainexercise

--dos下导出
exp apps/cape@train file=e:\database_trainning.dmp owner=(appsys,apps,hr,appview,workflow,oa,train,trainexam,trainexercise)
 exp pt6/cape@10.216.60.47/182OCM file=e:\DATABASE\1820ocm20130613.dmp log=D:\DATABASE\1820ocm20130613.log  owner=(pt6,ocm)

 

imp apps/cape@orcl file=d:\DataBase\database_trainning.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=train touser=train fromuser=trainexam touser=trainexam fromuser=trainexercise touser=trainexercise
------------------------------------------------------------------------------------------------------------------------------
数据泵导出
expdp apps/cape@10.216.60.69:1522/JG460 dumpfile=jg460DB20131023.dmp logfile=jg460DB20131023.log  owner=(appsys,apps,hr,appview,workflow,oa,mpm)

Oracle数据导入步骤

目录
TITLE 1
1 TITLE1 1
1.1 TITLE2 1
1.1.1 TITLE3 1
 
1 部署
1.1 本机导出数据的EXP命令
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>cd F:\app\mac\product\11.2.0\dbhome_1\BIN

C:\Documents and Settings\Administrator>f:

F:\app\mac\product\11.2.0\dbhome_1\BIN>expdp system/cape@mpm410lvdi dumpfile=FAIPMS20121110.DMP schemas=(apps,appsys,oa,hr,workflow,mpm,hrmpm,mesmpm)
1.2 数据库初始化
1.2.1 创建表空间
导出的文件中包含6个用户，需要创建6各创建表空间：
以dba身份登录： connect sys/password as sysdba
create tablespace apps datafile 'E:\admin\oradata\JG460\apps.dbf' size 100m autoextend on next 10m;
create tablespace appsys datafile 'E:\admin\oradata\JG460\appsys.dbf' size 100m autoextend on next 10m;
create tablespace hr datafile 'E:\admin\oradata\JG460\hr.dbf' size 100m autoextend on next 10m;
create tablespace appview datafile 'E:\admin\oradata\JG460\appview.dbf' size 100m autoextend on next 10m;
create tablespace workflow datafile 'E:\admin\oradata\JG460\workflow.dbf' size 100m autoextend on next 10m;
create tablespace oa datafile 'E:\admin\oradata\JG460\oa.dbf' size 100m autoextend on next 10m;
create tablespace mpm datafile 'E:\admin\oradata\JG460\mpm.dbf' size 100m autoextend on next 10m;

1.2.2 拷贝EXPDP文件
将导出文件，例如“JGEXP20111202.DMP”拷贝至Oracle目录“app\mac\admin\ORCL\dpdump”中；
1.2.3 使用命令行导入
使用命令行导入：impdp system/cape@JG460 dumpfile=JG460_1308211802.DMP schemas=(apps,appsys,hr,oa,workflow,mpm)
1.2.4 解决非法对象问题
如果出现由于权限不足导致视图或者包编译无法通过的情况，需要执行以下命令来为用户“mpm”赋相应的权限：
以dba身份登录： connect sys/password as sysdba
grant execute on Dbms_Rls to mpm;
grant select on dba_policies to mpm;
grant select on Dba_Objects to mpm;
执行成功后重新编译视图和包（可使用PL-SQL Developer登录mpm用户后对非法对象进行重新编译）。