LBS导入的脚本备存

2017-02-04 10:43:09 访问(1731) 赞(0) 踩(0)

-- 创建临时表空间 --
-- 也可以共享指定一个专用的临时表空间 --
create temporary tablespace lbs1701db_temp 
tempfile 'lbs1701db_temp.dbf' 
size 32m 
autoextend on 
next 32m maxsize 1024m 
extent management local; 

-- 创建表空间 --
create tablespace lbs1701db_data 
logging 
datafile 'lbs1701db_data01.dbf' 
size 2048m 
autoextend on 
next 32m maxsize 4096m 
extent management local; 

-- 创建用户 --
create user lbs1701db identified by lbs1701db 
default tablespace lbs1701db_data 
temporary tablespace lbs1701db_temp; 


grant connect,resource to lbs1701db; 

grant create view to lbs1701db;

grant read,write on directory lbs2016dir to lbs1701db; 

-- 给dba权限 --
grant dba to lbs1701db;


-- 错误脚本 --
-- impdp lbs1701db/lbs1701db@IOT_10_21_129_15 DIRECTORY=lbs2016dir DUMPFILE=GPS_AUTO_BACK_1.DMP TABLESPACES=TSGPSMIS REMAP_SCHEMA=gpsmisuser:lbs1701db remap_tablespace=TSGPSMIS:lbs1701db_data logfile=lbsimpdp.log sqlfile=sec_expdp.sql

-- 通过 sqlfile=sec_expdp.sql --
-- 查看导入的脚本情况 --
-- 看用没有seq等的缺失 --
impdp lbs1701db/lbs1701db@IOT_10_21_129_15 DIRECTORY=lbs2016dir DUMPFILE=GPS_AUTO_BACK_1.DMP schemas=gpsmisuser REMAP_SCHEMA=gpsmisuser:lbs1701db remap_tablespace=TSGPSMIS:lbs1701db_data logfile=lbsimpdp.log sqlfile=sec_expdp.sql

-- 这个是错误的导入脚本 --
-- 因为导出是按用户导的，不是按表空间导的 --
-- impdp lbs1701db/lbs1701db@IOT_10_21_129_15 DIRECTORY=lbs2016dir DUMPFILE=GPS_AUTO_BACK_1.DMP TABLESPACES=TSGPSMIS REMAP_SCHEMA=gpsmisuser:lbs1701db remap_tablespace=TSGPSMIS:lbs1701db_data logfile=lbsimpdp.log

-- 正确的导入脚本 --
-- 关键点:schemas=gpsmisuser --
-- 因为原先的gpsmisuser是dba权限，所以要赋予lbs1701db用户dba权限 --
impdp lbs1701db/lbs1701db@IOT_10_21_129_15 DIRECTORY=lbs2016dir DUMPFILE=GPS_AUTO_BACK_1.DMP schemas=gpsmisuser REMAP_SCHEMA=gpsmisuser:lbs1701db remap_tablespace=TSGPSMIS:lbs1701db_data logfile=lbsimpdp.log 



-- 除去dba权限 --
revoke dba from lbs1701db;


-- 给回原先的资源权限 --
grant connect,resource to lbs1701db; 

grant create view to lbs1701db;

grant read,write on directory lbs2016dir to lbs1701db;