oracle expdp/impdp 导入导出笔记

查看默认备份目录

会显示对应的用户和其备份路径

select * from dba_directories;

对备份目录进行授权（DATA_PUMP_DIR 为directory名称，可以自行创建，也可以查询后用已有的）

grant read,write on directory DATA_PUMP_DIR to epay;

如果想新创建directory，则为如下语句：

SQL>create directory DATA_PUMP_DIR as '/home/dumpdata/';

如果要更改dumpdir目录的路径，则为如下语句：

SQL>create or replace directory DATA_PUMP_DIR as '/home/dumpfiles';

如果需要赋予用户权限

SQL>grant read,write on directory DATA_PUMP_DIR to epay;

导出

expdp epay/epay@orcl schemas=epay dumpfile=expdp001.dmp DIRECTORY=DATA_PUMP_DIR;

SQL> create user epay identified by epay;

SQL> grant connect,resource,dba,IMP_FULL_DATABASE to epay;

导入（确认表空间一样）

impdp epay/epay@helowin schemas=epay dumpfile=expdp001.dmp DIRECTORY=DATA_PUMP_DIR table_exists_action=replace exclude=user

如果导入失败，删除用户

drop user epay cascade;

其他

查看所有用户：select * from all_users;

查看表空间：select tablespace_name from dba_tablespaces;

查看用户具有怎样的角色：select * from dba_role_privs where grantee='用户名'；

查看某个角色包括哪些系统权限：select * from dba_sys_privs where grantee='DBA'

查看oracle中所有的角色：select * from dba_roles;