Oracle expdp和expdp的使用

900f1su90g

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;