Oracle expdp跟expdp的使用

Oracle expdp和expdp的使用

1.同一个用户下的导入和导出
有的时候防止磁盘碎片可能需要的操作。
查看帮助：expdp -help
          impdp -help
创建目录
create directory data_pump_dir_scott as '目录的路径';
备注一下采用默认的导出导入文件目录data_pump_dir;
查看目录
select * from dba_directories;

set line 120;
col owner for a6;
col directory_name for a20;
col directory_path for a65;
select * from dba_directories;
授权目录
grant read,write on directory data_pump_dir to scott;
grant create session ,exp_full_database,imp_full_database to scott;


select count(1) from emp where job<> 'ANALYST' and sal>1250

参数文件scott_par.txt 内容如下：
DIRECTORY=DATA_PUMP_DIR
tables=(emp,dept)
DUMPFILE=SCOTT.DMP
QUERY=scott.emp:"where job<> 'ANALYST' and sal>1250 "

导出
expdp scott/tiger parfile=scott_par.txt

drop table emp;

drop table dept;

导入
impdp scott/tiger parfile=scott_par.txt


2.不同用户不同表空间之间移动
sqlplus /nolog
conn scott/tiger
set line 120;
set pagesize 30;
col object_name for a35;

查看用户对象
select object_name ,object_type,status from user_objects;
select object_name ,object_type,status from user_objects where object_name not like 'BIN%';

查看用户表对应的表空间
select table_name ,tablespace_name from user_tables;

查看用户索引和表空间的关系
col index_name for a20;
col index_type for a20;
col tablespace_name for a20;
select index_name,index_type,tablepspace_name from user_indexes;

导出参数文件expdp_par.txt
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=scott
DUMPFILE=schema_scott.dat
EXCLUDE=PACKAGE
EXCLUDE=VIEW
EXCLUDE=TABLE:"LIKE '%DUMP'"

导出
expdp scott/tiger parfile=expdp_par.txt




授权
sqlplus /nolog
conn sys/ticket as sysdba
grant read,write on directory data_pump_dir to jinfeng;
grant create session ,exp_full_database,imp_full_database to jinfeng;

impdp_par.txt内容如下:
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=scott
DUMPFILE=schema_scott.dat  --导入的文件
REMAP_SCHEMA=SCOTT:JINFENG    --导入用户的映射
REMAP_SCHEMA=USERS:TBS_DATA   --导入表空间的映射

导入:
impdp jinfeng/ticket parfile=impdp_par.txt

确认导入成功:
sqlplus /nolog
conn jinfeng/ticket
select index_name ,tablespace_name from user_indexes;
select table_name,tablespace_name from user_tables;