查询表空间

查看数据中创建的目录

1
select * from dba_directories

创建导出目录

1.登录sysdba用户

1
sqlplus / as sysdba
1
su -p oracle -c "sqlplus / as sysdba"

2.创建Oracle目录

1
CREATE OR REPLACE DIRECTORY dump_dir AS '/folder_path';

3.对此目录给用户授权

1
GRANT READ,WRITE ON DIRECTORY dump_dir TO username;

4.退出sysdba用户

1
exit;

导出dump文件

按表空间导出

1
expdp system/manager TABLESPACES=tablespace_name_01,tablespace_name_02 dumpfile=dump_file_name.dmp DIRECTORY=dump_dir;

按用户导出

1
2
expdp username/password@orcl schemas=username dumpfile=dump_file_name.d
mp DIRECTORY=dump_dir;

按表导出

1
expdp username/password@orcl TABLES=table_name_01,table_name_02,table_name_03 dumpfile=dump_file_name.dmp DIRECTORY=dump_dir;

dump文件导入

按用户导入

1
impdp username/password schemas=username dumpfile=dump_file_name.dmp DIRECTORY=dump_dir;

按表空间导入

1
impdp system/manager TABLESPACES=tablespace_name dumpfile=dump_file_name.dmp DIRECTORY=dump_dir;

按数据库导入

1
impdb system/manager FULL=y dumpfile=dump_file_name.dmp DIRECTORY=dump_dir;