由于一直要将项目的线上DB,拿回来还原,记性又不好,烂笔头一下。
1、导出
exp orcl/system rows=y indexes=n compress=n buffer=65536
feedback=100000 volsize=0 full=y
file=exp_fulldb_yyyymmdd.dmp(磁带设备则为/dev/rmt0)
log=exp_fulldb_yyyymmdd.log
orcl/system :数据库实例/用户名
file:备份文件名
2、导入
imp orcl/system fromuser=zbdba touser=system rows=y indexes=n commit=y buffer=65536 feedback=100000 ignore=n file=d:/zbdba.dmp log=d:/imp_zbdba.log
orcl/system :数据库实例/用户名
fromuser=zbdba : 指定备份中那个用户下的表
touser=system:指定导入到哪个用户下
file:指定源文件
可使用.bat执行,也可在cmd下执行(cmd下貌似需要先cd 到oracle的bin下,如D:/oracle/ora92/bin 中然后再
Example :
imp EHR_BBG_101124/1@cpic fromuser=ehr36 touser=EHR_BBG_101124 file=D:/Application/eHR/HZ_XN/BuBuGao/ehr361212.DMP
--删除表
drop user ehr_bbg_081010 cascade
--查找Session
select 'EHR_BBG_081010',sid,serial# from v$session;
--Kill Session
alter system kill session '100,2216';
--create user userName identified by password
create user "EHR_BBG_101124" identified by "1";
GRANT "AQ_ADMINISTRATOR_ROLE" TO "EHR_BBG_101124";
GRANT "CONNECT" TO "EHR_BBG_101124";
GRANT "DBA" TO "EHR_BBG_101124";
--创建User的完成格式
CREATE USER "ehr_bbg_101124" PROFILE "DEFAULT"
IDENTIFIED BY "1" DEFAULT TABLESPACE "1024M"
TEMPORARY TABLESPACE "10M"
QUOTA UNLIMITED --无限制限额
ON "...."
QUOTA UNLIMITED
ON "...."
ACCOUNT UNLOCK;
GRANT "AQ_ADMINISTRATOR_ROLE" TO "ehr_bbg_101124";
GRANT "CONNECT" TO "ehr_bbg_101124";
GRANT "DBA" TO "ehr_bbg_101124";
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => '...',
consumer_group => 'SYS_GROUP', --使用者组
grant_option => FALSE
);
END;
---创建TableSpace
create tablespace data_test datafile 'D:/db/data_1.dmp' size 2000M;