最近经常处理项目中的问题,没法没库什么都搞不了,于是记录一下导库的过程,给自己做个笔记,以备后用:
直接上命令:
1.--创建临时表空间
create temporary tablespace cnoocEng_temp tempfile '/home/oracle11g/product/oradata/mytablespace/cnoocEng_temp.dbf' size 128M autoextend on next 100M maxsize 10240M extent management local;
2.--创建表空间(使用大文件 (Bigfile) 表空间解决Oracle表空间最大只有128G的限制)
create bigfile tablespace cnoocEng logging datafile '/home/oracle11g/product/oradata/mytablespace/cnoocEng.dbf' size 1024M autoextend on next 100M maxsize 1536000M extent management local;
3.--创建用户
create user cnoocEng identified by sunway default tablespace cnoocEng temporary tablespace cnoocEng_temp;
4.--给用户授权
grant connect,resource,dba to cnoocEng ;
grant create any sequence to cnoocEng ;
grant create any table to cnoocEng ;
grant delete any table to cnoocEng ;
grant insert any table to cnoocEng ;
grant select any table to cnoocEng ;
grant unlimited tablespace to cnoocEng ;
grant execute any procedure to cnoocEng ;
grant update any table to cnoocEng ;
grant create any view to cnoocEng ;
--查询 DATA_PUMP_DIR
select * from dba_directories;
5.--数据泵导入
oracle11g:
impdp cnoocEng/sunway@192.0.0.0/orcl transform=segment_attributes:n directory=DATA_PUMP_DIR DUMPFILE=20190808.dmp remap_tablespace=MDMINTL(旧):cnoocEng(新) remap_schema=mdmintl(旧):cnoocEng
例:
impdp cnoocEng/sunway@192.168.183.139/orcl transform=segment_attributes:n directory=DATA_PUMP_DIR DUMPFILE=20190808.dmp table_exists_action=replace remap_tablespace=MDMINTL:cnoocEng remap_schema=mdmintl:cnoocEng LOGFILE=20190829impcnooc.log
接下来耐心等待就好
各个参数意义:
table_exists_action=replace 有旧表直接替换掉
remap_tablespace 表空间
remap_schema 用户名
LOGFILE 日志名称
附:其他命令
--删除表空间
drop tablespace cnoocEng including contents and datafiles cascade constraints;
--删除用户
drop user cnoocEng cascade;
数据泵导出命令:
expdp cnoocEng/sunway@192.168.183.139/orcl file=/oracle/admin/szqmdm/dpdump/szqmdm20190820.dmp log= /oracle/admin/szqmdm/dpdump/szqmdm20190820.log exclude=TABLE:\"IN\(\'BASE_UPLOADFILEBLOB\',\'LOG_MESSAGE\'\)\"
exclude:去除特殊表,减少空间