oracle expdp导出数据和impdp导入数据

oracle expdp导出数据和impdp导入数据

1.导出

#1.切换root用户创建文件夹并赋予权限,要导出用的逻辑目录
su - root 
#创建文件夹
mkdir /xx/xx/dmp
mkdir xxx
#赋予权限
chmod -R 777 /data
#2.切换oracle用户,横杠有空格
su - oracle
#3.连接oracle用户
sqlplus / as sysdba
#4.创建expdp导出目录，默认导出目录为D:\app\Administrator/admin/orcl/dpdump/（其中前面为Oracle安装目录）
create or replace directory dmp as '/data/dmp'; 
#5.查看目录是否创建成功
select * from dba_directories;

#6.创建成功,赋予用户目录权限
grant read,write on directory DMP to c##securitymanage_ez;
#7.退出sqlplus导出数据库(一定要退出sqlplus用oracle用户)
expdp "C##SECURITYMANAGE_EZ"/"securitymanage_ez"@vracdb schemas="c##securitymanage_ez" directory=DMP dumpfile=20231219.dmp logfile=20231219.log;

检查导出日志

impdp导入
1.首先找到用户下的表空间删除,免得导入有冲突
drop tablespace SECURITYMANAGE_XF_DATA including contents and datafiles cascade constraint;
2.检查用户是否存在存在即删除
SELECT * FROM ALL_USERS;
删除用户以及用户下的级联关系
drop user securitymanage_xf cascade;
3.建立表空间并自增长
create tablespace SECURITYMANAGE_XF_DATA
logging datafile ‘D:\xfdata\xfdata.dbf’
size 500M autoextend on next 100M maxsize unlimited
extent management local;

4.创建用户赋予临时表空间和默认存储表空间
create user SECURITYMANAGE_XF
identified by SECURITYMANAGE_XF
default tablespace SECURITYMANAGE_XF_DATA
temporary tablespace TEMP
profile DEFAULT;
5.为新建用户赋予权限
grant connect,dba,resource,unlimited tablespace to SECURITYMANAGE_XF;
6.创建逻辑目录
create directory impdpxf as ‘D:\xfdata’;
7.为用户赋予该目录的读写权限
grant read,write on directory impdpxf to SECURITYMANAGE_XF;
8.导入命令
impdp SECURITYMANAGE_XF/SECURITYMANAGE_XF@orcl DIRECTORY=impdpxf DUMPFILE=‘20231219.dmp’ remap_schema=c##securitymanage_ez:SECURITYMANAGE_XF remap_tablespace=SECURITYMANAGE_EZ_DATA:SECURITYMANAGE_XF_DATA full=y
#full为必填,remap为转换表空间,remap_schema为转换用户