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_DIRtables=(emp,dept)DUMPFILE=SCOTT.DMPQUERY=scott.emp:"where job<> 'ANALYST' and sal>1250 "导出expdp scott/tiger parfile=scott_par.txtdrop table emp;drop table dept;导入impdp scott/tiger parfile=scott_par.txt2.不同用户不同表空间之间移动sqlplus /nologconn scott/tigerset 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.txtDIRECTORY=DATA_PUMP_DIRSCHEMAS=scottDUMPFILE=schema_scott.datEXCLUDE=PACKAGEEXCLUDE=VIEWEXCLUDE=TABLE:"LIKE '%DUMP'"导出expdp scott/tiger parfile=expdp_par.txt授权sqlplus /nologconn sys/ticket as sysdbagrant 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_DIRSCHEMAS=scottDUMPFILE=schema_scott.dat --导入的文件REMAP_SCHEMA=SCOTT:JINFENG --导入用户的映射REMAP_SCHEMA=USERS:TBS_DATA --导入表空间的映射导入:impdp jinfeng/ticket parfile=impdp_par.txt确认导入成功:sqlplus /nologconn jinfeng/ticketselect index_name ,tablespace_name from user_indexes;select table_name,tablespace_name from user_tables;
?
