http://flash7783.javaeye.com/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不能被導入。

oracle 數據庫導入、導出