Oracle 创建用户，数据泵导入导出

1.连接数据库

# sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 22 10:54:12 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 

2.数据库正常，先确定表空间名、表空间剩余空间 

--查看剩余表空间大小
SELECT tablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROM dba_free_space GROUP BY tablespace_name;

3.创建用户

-- Create the user 
create user user_name identified by "user_password" default tablespace tablespace_name temporary tablespace TEMP profile DEFAULT;
--user_name 用户名,user_password 密码，tablespace_name 表空间名

--grant user 授权
grant connect to user_name ;
grant datapump_exp_full_database to user_name;
grant datapump_imp_full_database to user_name;
grant dba to user_name;
grant resource to user_name;
grant create view to user_name;
grant unlimited tablespace to user_name;

--drop user
drop user user_name cascade;

--ORA-01940错误,删除进程
select sid, serial# from v$session where username = 'user_name';
alter system kill session '195,531';

4.查询 directory_name、directory_path

--oracle查询directory_path
select * from all_directories;

5.放入导出DMP、LOG文件到directory_path 路径下

--复制DMP文件
cp -rf /dbbackup/histdata/202011/70_OA_11gXD_20201129.DMP /oracle/dbbackup

6.退出sqlplus，导入命令

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

--使用sysdba 权限导入，避免权限的问题
impdp \'/ as sysdba\' directory=DUMP_DIR dumpfile=70_OA_11gXD_20201129.DMP remap_schema=旧用户名:新用户名

7.导出数据(备份)

expdp test/123456@127.0.0.1/orcl compression=all DIRECTORY=DUMP_DIR DUMPFILE=TESTDB20211119.DMP