oracle dmp文件导出与导入
ORACLE 10g导入 ORACLE 11g
一、expdp.sh导出dmp文件
export PATH=$PATH:$HOME/bin
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_USER=USER_NAME
export ORACLE_USER_PASSWORD=PASSWORD
export VERSION=patch3.1.0.1[R]
export DIR_NAME=backup
DMP_FILE=${ORACLE_USER}_${VERSION}_['date + %Y%m%d'].dmp
LOG_FILE=expdp_${ORACLE_USER}_${VERSION}_['date + %Y%m%d'].log
expdp $ORACLE_USER/$ORACLE_USER_PASSWORD@$ORACLE_SID dumpfile=$DMP_FILE schemas=$ORACLE_USER directory=$DIR_NAME logfile=$LOG_FILE version=10.2.0.3.0
-- version为目标(导入)oracle版本
二、创建表空间
sqlplus / as sysdba
--WINDOWS
drop tablespace tablespace_temp including contents cascade constraints;
drop tablespace tablespace_data including contents cascade constraints;
create temporary tablespace tablespace_temp
tempfile 'D:\oracle\product\10.2.0\oradata\tempfile\tablespace_temp.dbf'
size 1024m
autoextend on
next 50m maxsize 30240m
extent management local;
create tablespace tablespace_data
logging
datafile 'D:\oracle\product\10.2.0\oradata\datafile\tablespace_data.dbf'
size 1024m
autoextend on
next 50m maxsize 30240m
extent management local;
--LINUX
drop tablespace tablespace_temp including contents cascade constraints;
drop tablespace tablespace_data including contents cascade constraints;
--创建tablespace_data临时表空间
create temporary tablespace tablespace_temp
tempfile '/u01/tempfile/tablespace_temp.dbf'
size 1024m
autoextend on
next 50m maxsize 30240m
extent management local;
--创建tablespace_data数据表空间
create tablespace tablespace_data
logging
datafile '/u01/datafile/tablespace_data.dbf'
size 1024m
autoextend on
next 50m maxsize 30240m
extent management local;
三、创建用户及赋权
grant dba to USER_NAME;
grant connect,resource to USER_NAME ;
grant create public database link,drop public database link to USER_NAME ;
grant execute on dbms_job to USER_NAME;
grant create job to USER_NAME;
grant debug connect session to USER_NAME;
grant manage scheduler to USER_NAME;
四、创建目录及赋权
system或sysdba登录
CREATE OR REPLACE DIRECTORY DIR_NAME AS 'D:\BACKUP';
GRANT READ, WRITE ON DIRECTORY DIR_NAME TO USER_NAME;
五、导入数据库
impdp user_name/password directory=DIR_NAME dumpfile=xxx.dmp logfile=kdbase.impdp.log TABLE_EXISTS_ACTION=append version=10.2.0.3.0
-- version为目标(导入)oracle版本
最新文章
- 移动WEB开发之viewport
- 十一个行为模式之状态模式(State Pattern)
- CDH安装
- TCPL 札记
- 深度学习框架 Torch 7 问题笔记
- git服务器新增仓库
- navicat 链接不上mysql
- iOS音效
- [转载] ubuntu下定制Vim/Gvim及使用技巧
- How to changes to Table & EDT Relations[AX2012]
- [翻译][MVC 5 + EF 6] 7：加载相关数据
- git push后修改错误的commit message
- 浅谈PPM (Project Portfolio Management) - 1
- Node.js timer的优化故事
- VB 用代码创建的控件和接收事件
- How to Quickly Create a Copy of a Table using Transact-SQL
- ng-cordova和cordova区别
- 和为k的最长子数组及其延伸
- 微信小程序——实现动画循环播放
- oracle 表空间自己主动扩展大小