ORACLE数据库的导入导出、创建用户、给予权限
在服务器上进行操作前
先看下环境
su oracle //进入oracle用户
cd ~ //进入oracle的根目录
echo $ORACLE_SID //看看当前所在的linux的实例
cat .bash_profile //如果上面没显示实例,或者显示的不是想要的实例,就先看看这个配置
export ORACLE_SID=你要的实例 //如果不是想要的实例就这样切换到你要的实例
source .bash_profile //通过这个重新加载一下配置
一、expdp导出oracle用户数据
将目标数据库的用户对象导出、将服务器上的导出
进入服务器的界面(Windows进cmd就行了) 输入
expdp 导出的用户名/导出用户的密码 schemas=用户名 dumpfile=用户名.dmp directory=DATA_PUMP_DIR[导入的目录] logfile=[日志文件名].log;
ps. 注意这个不是sql,不用在sql界面执行,在服务器的命令界面执行就行了
这个会将数据库的用户导出到服务器上的指定目录,可以通过Xftp将文件拉到本地
导出的文件是dumpfile后面写的,一般是用户名.dmp,把这个文件拉到我们需要导入的数据库的DATA_PUMP_DIR下,这个目录在哪里可以通过
select * from dba_directories;
如果觉得这个路径不大好,可以修改,但是一定要注意这个目录及目录下的文件权限给到oracle用户
create or replace directory DATA_PUMP_DIR as '/u01/oracle/app/oradata/XXXXXXXX';
查出来
找到DATA_PUMP_DIR的存放路径,将从服务器上拉取的用户名.dmp放入到此目录的路径下
举例和说明:
expdp system/admin@127.0.1.1:1521/oradb DIRECTORY=DATA_PUMP_DIR DUMPFILE=src_pif_20210205.dump SCHEMAS=src_pif CONTENT=METADATA_ONLY logfile=src_pif_20210205.log
解析上句
expdp
system/admin@127.0.1.1:1521/oradb
导出的用户/密码@数据库IP:数据库端口/数据库实例名
DIRECTORY=DATA_PUMP_DIR
导出的dmp包放哪里,这里指定放在DATA_PUMP_DIR下,可以在数据库中通过select * from dba_directories; 查询这个目录的路径,导出后在这个路径下拿到dmp包
DUMPFILE=src_pif_20210205.dump
导出的文件叫什么名字,这里是按日期给了个名字,后续可以在上面设置的导出的路径下找到这个名字的dmp包
SCHEMAS=src_pif
按用户导出,这里是指定导出的用户是哪个
CONTENT=METADATA_ONLY
这句表示仅导出这个用户下的表结构不包含数据(视自己情况而定,看需不需要数据也导出,需要导出数据就不加这个)
logfile=src_pif_20210205.log
导出这个命令的日志,如果失败了,可以通过这个日志看是什么原因,这个日志和dmp一样 也在上面设置的导出路径下
二、导入前的准备,建表空间等
创建对应的用户表及用户
#1.创建表空间
CREATE TABLESPACE 表空间名 LOGGING DATAFILE 'D:\Oracle\app\oracleDB\oradata\kirin\用户名.ORA'[放自己本地表空间的存放路径] SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED PERMANENT EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
#2.删除需要创建的用户(这里以test为例)
drop user test cascade ;
#3.创建用户
create user test[用户名] identified by test[密码];
#4.授予test用户创建会话的权限
#不给会报ora-01045 :user system lacks create session privilege; logon denied
grant create session to test;
#5.给用户逻辑目录读写权限
#不给这个会报以下异常
# ORA-39002: 操作无效
# ORA-39070: 无法打开日志文件。
# ORA-39087: 目录名 DATA_PUMP_DIR 无效
grant read,write on directory DATA_PUMP_DIR to test;
#6.给用户表空间权限
#不给会报
#ORA-31626: 作业不存在
#ORA-31633: 无法创建主表 "XXX"
#ORA-01031: 权限不足
grant dba,resource,unlimited tablespace to test;
三、导入导出的数据
将服务器上导出的用户导入对应用户
导入前,先进行第一步说的,将dmp放到DATA_PUMP_DIR路径下
#两种方式一种是导入目录的,一种是直接写死路径的
impdp 导入此用户的用户名/导入的用户密码@127.0.0.1:1521/orcl[这里是导入到哪个实例数据库连接,我的是本地] DIRECTORY=DATA_PUMP_DIR[存放dmp的那个目录] DUMPFILE=用户名.dmp[需要导入的文件名] LOGFILE=记录的日志名称.log;
imp 导入此用户的用户名/导入的用户密码@127.0.0.1:1521/orcl[这里是导入到哪个实例数据库连接,我的是本地] file='D:\Oracle\app\oracleDB\admin\orcl\dpdump\XXX.dmp' full='y'
将数据从一个用户导入到另外一个用户
imp 用于执行的用户/用于执行的用户的密码 file=/u01/app/oracle/admin/oracle11g/dpdump/XXX.dmp[导入的文件的地址] fromuser=导入的数据来源用户 touser=导入到哪个用户 ignore=y[加这个参数表示忽略一些报错]
#例如:
imp pif/pif file=/u01/app/oracle/admin/oracle11g/dpdump/JIUBAN.dmp fromuser=pif touser=src_pif ignore=y
#impdp导入,schemas选择dump包里面的需要导入的用户,remap_schema=源用户:目标用户,源用户2:目标用户2
impdp fpcp/fpcp@127.0.1.1:1521/orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=user1_user2_user3_20201207.dump SCHEMAS=user1,user2,user3 remap_schema=user1:user1_new,user2:user2_new,user3:user3_new logfile=user1_user2_user3_20201207.log
例如:
impdp fpcp/fpcp@127.0.1.1:1521/orcl DIRECTORY=DATA_PUMP_DIR DUMPFILE=user1_user2_user3_20201207.dump SCHEMAS=user1,user2,user3 logfile=user1_user2_user3_20201207.log
解析上句
impdp
fpcp/fpcp@127.0.1.1:1521/orcl 用来导入的用户/密码@数据库IP:端口/实例(这里的用户只要有导入的权限即可,用sys也行,这里不是最终导入的用户)
DIRECTORY=DATA_PUMP_DIR 导入的dmp所在路径
DUMPFILE=user1_user2_user3_20201207.dump 导入的dmp名称
SCHEMAS=user1,user2,user3 此次导入的用户,如果dmp包只有一个用户就只写这一个用户
logfile=user1_user2_user3_20201207.log 日志