如何导入导出数据

  • 传统导出导入与数据数据泵导出导入的区别

在10g之前,传统的导出和导入分别使用EXP工具和IMP工具,从10g开始,不仅保留了原有的EXP和IMP工具,还提供了数据泵导出导入工具EXPDP和IMPDP.使用EXPDP和IMPDP时应该注意的事项：

①EXP和IMP是客户端工具程序,它们既可以在可以客户端使用,也可以在服务端使用。
②EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用。
③IMP只适用于EXP导出文件,不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出文件,而不适用于EXP导出文件。

数据泵导出包括导出表,导出方案,导出表空间,导出数据库4种方式.

  •  exp命令导出数据

用exp命令导出数据时有如下常用的命令参数：

参数
 
说   明
 
USERID
 
确定执行导出实用程序的用户名和口令
 
BUFFER
 
确定导出数据时所使用的缓冲区大小，其大小用字节表示
 
FILE
 
指定导出的二进制文件名称，默认的扩展名是.dmp
 
FULL
 
指定是否导出整个数据库，只有授权DBA权限的用户才可使用此参数
 
OWNER
 
要导出的数据库用户列表
 
ROWS
 
确定是否要导出表中的数据
 
TABLES
 
按表方式导出时，指定需导出的表和分区的名称
 
TABLESPACES
 
按表空间方式导出时，指定要导出的表空间名
 
LOG
 
指定一个存放导出信息的日志文件
 
COMPRESS
 
指明在装入期间是否将表中数据压缩到一个区域中。如果在卸出数据时，指定参数COMPRESS=Y，那么装入时，就会将数据压缩到一个初始区域中。这种选择可以保持初始化区域的原始大小。缺省为“Y”。

1.导出用户数据

exp system/dba@oradb10 file=d:/mini_back.dmp owner=mini log=d:/exp_mini.log  

--将mini用户下的数据导出

set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
set ORACLE_SID=smartkq

set W=%DATE:~13,1%
if '%W%'=='日' set D=0
if '%W%'=='一' set D=1
if '%W%'=='二' set D=2
if '%W%'=='三' set D=3
if '%W%'=='四' set D=4
if '%W%'=='五' set D=5
if '%W%'=='六' set D=6
exp smart/smart file=z:smart%D%.dmp log=z:smart%D%.log owner=smart
expnas.bat

2.按表方式导出数据

exp mini/mini@accp tables=(emp, dept) file=d:/mini_back_tab.dmp

--将表emp、dept导出

exp scott/HondaHsu2011@dw buffer=65000 file=e:fact_device_and_dim_date_new.dmp tables=(fact_device,dim_date_new)

3.全库导出

exp system/dba  buffer=8192 file=d:/exp_db.dmp full=y  compress=n  log=d:exp_db.log

4.添加过滤条件

 exp scott/HondaHsu2011@DW buffer=65000 file=e:dim_date_new.dmp tables=(dim_date_new) query="where year_id > 2010"

  •  imp命令导入数据

　　　用imp导入数据时有如下常用的命令参数

参数

说   明

USERID

确定执行导出实用程序的用户名和口令

BUFFER

确定导出数据时所使用的缓冲区大小，其大小用字节表示

COMMIT

指定是否在每个数组（其大小由BUFFER参数设置）插入后进行提交

FILE

指定导出的二进制文件名称，默认的扩展名是.dmp

FROMUSER

指定要从导出文件中导入的用户名

TOUSER

指定要将对象导入的用户名，FROMUSER与TOUSER可以不同

FULL

指定是否要导入整个导出文件，当指定了Fromuser、Touser时，不能再指定FULL.

OWNER

要导出的数据库用户列表， 当指定了Fromuser、Touser时，不能再指定owner.

ROWS

确定是否要导出表中的数据

TABLES

按表方式导出时，指定需导出的表和分区的名称,当指定了FULL是，不能再指定TABLES

TABLESPACES

按表空间方式导出时，指定要导出的表空间名

IGNORE

导入时是否忽略遇到的创建错误，默认为N

LOG

指定一个存放导出信息的日志文件
1.将整个文件导入数据库

imp scott/HondaHsu2011@dw file=E:fact_device_and_dim_date_new.dmp full=y;

2.导入表数据

imp atm01/HondaHsu2011@dw file=e:/fact_device_and_dim_date_new.dmp fromuser=scott touser=atm01 tables=(fact_device,dim_date_new) log=e:/imp_tab.log

--将scott用户的表导入到atm01用户

  • EXPDP命令导出数据

　　用expdp导出数据时有如下常用的命令参数

参数

说明

ATTACH              

连接到现有作业, 例如 ATTACH [=作业名]。             

COMPRESSION         

减小有效的转储文件内容的大小,关键字值为: (METADATA_ONLY) 和 NONE。                     

CONTENT             

指定要卸载的数据, 其中有效关键字为: (ALL), DATA_ONLY 和 METADATA_ONLY。                

DIRECTORY           

供转储文件和日志文件使用的目录对象。                

DUMPFILE            

目标转储文件 (expdat.dmp) 的列表,例如 DUMPFILE=scott1.dmp, scott2.dmp,dmpdir:scott3.dm                   

ENCRYPTION_PASSWORD 

用于创建加密列数据的口令关键字。                    

ESTIMATE            

计算作业估计值, 其中有效关键字为:(BLOCKS) 和 STATISTICS。                  

ESTIMATE_ONLY       

在不执行导出的情况下计算作业估计值。                

EXCLUDE             

排除特定的对象类型, 例如EXCLUDE=TABLE:EMP。         

FILESIZE            

以字节为单位指定每个转储文件的大小。                

FLASHBACK_SCN       

用于将会话快照设置回以前状态的 SCN。                

FLASHBACK_TIME      

用于获取最接近指定时间的 SCN 的时间。               

FULL                

导出整个数据库 (N)。                                

HELP                

显示帮助消息 (N)。                                  

INCLUDE             

包括特定的对象类型, 例如INCLUDE=TABLE_DATA。        

JOB_NAME            

要创建的导出作业的名称。                            

LOGFILE             

日志文件名 (export.log)。                           

NETWORK_LINK        

链接到源系统的远程数据库的名称。                    

NOLOGFILE           

不写入日志文件 (N)。                                

PARALLEL            

更改当前作业的活动 worker 的数目。                  

PARFILE             

指定参数文件。                                      

QUERY               

用于导出表的子集的谓词子句。                        

SAMPLE              

要导出的数据的百分比;                               

SCHEMAS             

要导出的方案的列表 (登录方案)。                     

STATUS              

在默认值 (0) 将显示可用时的新状态的情况下,要监视的频率 (以秒计) 作业状态。         

TABLES              

标识要导出的表的列表 - 只有一个方案。               

TABLESPACES         

标识要导出的表空间的列表。                          

TRANSPORT_FULL_CHECK

验证所有表的存储段 (N)。                            

TRANSPORT_TABLESPACES

要从中卸载元数据的表空间的列表。                    

VERSION             

要导出的对象的版本, 其中有效关键字为:(COMPATIBLE), LATEST 或任何有效的数据库版本。                
1.创建目录

使用EXPDP工具时,其转储文件只能被存放在DIRECTORY对象对应的OS目录中,而不能直接指定转储文件所在的OS目录.因此,使用EXPDP工具时,必须首先建立DIRECTORY对象.并且需要为数据库用户授予使用DIRECTORY对象权限。如下示例

SQL>create directory dump_dir as 'e:emp';

(注，此处只是逻辑上创建了目录，还需要创建物理目录)
SQL>grant read, write on directory dump_dir to scott;

2.导出表

expdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=dept.dmp tables=dept

3.导出方案

expdp system/HondaHsu2011@dw as sysdba directory=dump_dir dumpfile=sche_system.dmp logfile=sche_system.log schemas=system

4.导出表空间

expdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=users_table_space.dmp logfile=tb.log tablespaces=users

5.导出数据库

expdp system/HondaHsu2011@dw DIRECTORY=dump_dir dumpfile=full.dmp full=y

expdp scott/HondaHsu2011@dw DIRECTORY=dump_dir dumpfile=full.dmp full=y

注：提示scott用户没有相应的权限，给scott相应的权限或使用system来做全库导出。

SQL> grant exp_full_database to scott;

  • impdp命令导出数据
1.导入表

impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp tables=scott.dept remap_schema=scott:scott

impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp tables=scott.test1 remap_schema=scott:system

  第一种方法表示将dept表导入到SCOTT方案中,第二种方法表示将test1表导入的SYSTEM方案中.

  注意,如果要将表导入到其他方案中,必须指定REMAP SCHEMA选项.

  remap_schema=a:b 将数据的schema从a 转换为b
remap_tablespace=a:b 将数据的tablespace 从a 转换为b

2.导入方案

impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp schemas=scott

impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=schema.dmp schemas=scott  remap_schema=scott:system

3.导入表空间

impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=tablespace.dmp tablespaces=users

4.导入数据库

impdp scott/HondaHsu2011@dw directory=dump_dir dumpfile=full.dmp full=y

-----查看oralce OEM CONSOLE-------------
emtcl status dbconsole

imp userid=SCOTT/HondaHsu2011 file=E:ipb_grade_detail.dmp full=y;
imp userid=BIDATA/HondaHsu2011 file=E:ps_project.dmp full=y;
imp userid=civilize/HondaHsu2011 file=E:PUB_USER_REMINDER_RELATION.DMP full=y grants=n rows=y ignore=y;
imp userid=lcam_develop/lcam4ddl file=E:MM_RECEIPT.DMP full=y;
imp userid=comtop/comtop@SZ_SUPERVISORY1526 file=E:BICONTENT.dmp full=y;
imp userid=BICONTENT/HondaHsu2011@orcl108 file=E:BICONTENT.dmp full=y;
imp atm01/HondaHsu2011@dw file=e:/fact_device_and_dim_date_new.dmp fromuser=scott touser=atm01 tables=

(fact_device,dim_date_new) log=e:/imp_tab.log


EXP SYSTEM/MANAGER BUFFER=64000 FILE=C:FULL.DMP FULL=Y 
EXP lcam_read/lcam_read@lcam_0805 BUFFER=65000 FILE=E:MM_RECEIPT_ITEM.DMP TABLES=(MM_RECEIPT_ITEM) 
EXP SCOTT/HondaHsu2011@DW BUFFER=65000 FILE=E:FACT_DEVICE.DMP TABLES=(FACT_DEVICE);
EXP lcam_read/lcam_read@lcam_0805 BUFFER=65000 FILE=E:MM_RECEIPT_ITEM.DMP owner=BICONTENT
EXP BICONTENT/HondaHsu2011@DW BUFFER=65000 FILE=E:BICONTENT.DMP owner=BICONTENT
EXP lcam_0927/zcpzg1z_0927@lcam_0705 BUFFER=65000 FILE=E:MM_RECEIPT.DMP TABLES=(MM_RECEIPT);
EXP sz_730/lcam_sz730@lcam_gz730 BUFFER=65000 FILE=E:MM_RECEIPT.DMP TABLES=(MM_RECEIPT);
EXP lcam_130/zcpzg1z_130@lcam_130 BUFFER=65000 FILE=E:PUB_USER_VISIT_DATA.DMP owner=LCAM_130 tables=

(PUB_USER_VISIT_DATA) query="where user_visit_id > 72278";
exp lcam_130/zcpzg1z_130@lcam_130 BUFFER=65000 FILE=E:PUB_USER_REMINDER_RELATION.DMP tables=(PUB_USER_REMINDER_RELATION) 

query="where feature_value_id>36398241"
exp lcam_130/zcpzg1z_130@lcam_130 BUFFER=65000 FILE=E:PUB_USER_REMINDER_RELATION.DMP tables=(PUB_USER_REMINDER_RELATION) 

query="where modify_date is null"


----------------查询端口----------------------
netstat -aon|findstr "7001"

---------------虚拟机-------------------------
put E:upload_linuxlinux_11gR2_database_1of2.zip /home/oracle/common/
create spfile from pfile='D:oracleproduct10.2.0admindwpfileinit.ora.816201111039';
EXP gz_0730/lcam_gz0730@lcam_gz730 BUFFER=65000 FILE=E:EXPT
imp userid=gz_0730/lcam_gz0730@lcam_gz730 file=E:EXPT
常用的例子
[oracle@atmdb impdp]$ cat 0_expdp_scheme.sh 
#!/bin/sh
today=`date +%w`
echo  "----------Begin time is ATMDB_DMOLVIEW,PPMREAD,SYSBK,ATM_HIS,BDUSER,VIEW_ADMIN,ATMVIEW,JIESHUN,HRONLINE,ATM_EAI,ATMCENTER --------------" >/data/impdp/exptime0_$today.log
echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime0_$today.log
export ORACLE_SID=atmdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin
impdp lcc/lcc#123 directory=imp_dp schemas=ATMDB_DMOLVIEW,PPMREAD,SYSBK,ATM_HIS,BDUSER,VIEW_ADMIN,ATMVIEW,JIESHUN,HRONLINE,ATM_EAI,ATMCENTER logfile=0.log network_link=toatmold parallel=4
echo  "----------End time is  --------------" >>/data/impdp/exptime0_$today.log
echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime0_$today.log
0_expdp_scheme.sh 
[oracle@atmdb impdp]$ cat 1_0_expdp_exclude.sh 
#!/bin/sh
today=`date +%w`
echo  "----------Begin time is ATM --------------" >/data/impdp/exptime1_$today.log
echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime1_$today.log
export ORACLE_SID=atmdb
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/Apache/Apache/bin
impdp lcc/lcc#123 directory=imp_dp schemas=ATM logfile=1.log network_link=toatmold parallel=4 exclude=TABLE:"in('CHKINOUT_TEMP_HIS','CHKINOUT_HIS','AC_KQ_DAYDATA_STATIC_HIS','KQDAYDATA_HIS','ATM_KQ_DAY_SALARY_STATIC_HIS','KQDAYAPPLYDETAIL_HIS','CHECKON_ACCOUNT_ALIGNMENT_HIS','AC_KQ_INVERSE_WORK_HIS','AS_LOG_PUBLIC_OPERATION_HIS','ATM_REGISTER_TRIP_DTL_BAK','CHECKON_ACCOUNT_ALIGNMENT_BAK','PS_SBOM_CUSTOMER_TEMP')"
echo  "----------End time is  --------------" >>/data/impdp/exptime1_$today.log
echo $(date +%Y"."%m"."%d" "%k":"%M":"%S) >>/data/impdp/exptime1_$today.log
1_0_expdp_exclude.sh
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/HondaHsu/p/3291410.html