oracle 数据库导入、导出

http://flash7783./blog/648683  数据库导入/导出知识

 

connect system/密码@实例名;

--删除用户及表空间
drop user pubr cascade;
drop tablespace pubrts including contents;

--创建表空间、用户及授角色权限

CREATE TABLESPACE pubrts DATAFILE 'F:\oradata\ttfj\pubrts.dat' SIZE 200M REUSE AUTOEXTEND ON NEXT 50M;

create user pubr identified by bitservice default tablespace pubrts;
grant resource,connect to pubr;

--给用户授对象操作权限

grant create sequence to tt;
grant create any table,alter any table,drop any table to tt;;
grant create any view,drop any view to tt;
grant create any trigger,alter any trigger,drop any trigger to tt;
grant create any procedure,alter any procedure,drop any procedure to tt;
grant create any synonym to tt;
grant create any snapshot,execute any procedure to tt;
grant select any dictionary to tt;
grant select any sequence to tt;
grant select any table,update any table to tt;
grant insert any table,delete any table to tt;

--导入

host imp system/bitservice@ttfj file=E:\万州数据库及应用20100603\siweidb.dmp log=E:\万州数据库及应用20100603\siweidb.log fromuser=(ap,pubr,tt,archive,pb) touser=(ap,pubr,tt,archive,pb) buffer=655000 ignore=y

--导出

exp system/bitservice file=c:\tt20091002.dmp log=c:\tt20091002.log owner=(ap,pubr,tt,archive,tt_contract,account,pb) buffer=655000 compress=y

--编译 recompile.sql

set heading off;
spool c:\temp\compile.sql;
select 'ALTER ' || replace(OBJECT_TYPE,' BODY','') || ' ' || object_name || ' COMPILE;'
FROM user_objects where status = 'INVALID' and object_name not like '%==%';
spool off;
set heading on;
set echo on;
@c:\temp\compile.sql;
set echo off;

 

在数据库恢复时（导入），需要停止web服务，是数据库处于不被连接的状态，否则用户删除时报错，不能被删除。

如果不删除用户，只删除表空间，将导致用户下的object不能被删除（object包括function,procedure,synonym,package,Javasource,javaclass等）,同样object不能被导入。