exp远程导出数据
1.安装oracle客户端(因为exp工具是在oracle客户端工具里面)
2.配置tnames.ora,如以下找到路径,可以修改文件,也可以直接netca创建,下面我们直接修改文件
配置文件路径为:E:\app\Loren.Huang\product\11.2.0\client_1\network\admin(此路径根据你安装oracle路径)
添加如下配置
ORCL_200_42 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.42)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )


解释:
ORCL_200_42为别名,这个是导出语句时连接的字符串,HOST为oracle数据库服务器IP,PORT为oracle listener端口号,SERVICE_NAME为服务名

配置完成可以tnsping测试一下
C:\Users\netdata>tnsping ORCL_200_42
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 23-1月 -2017 09:11:08
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
已使用的参数文件:
E:\app\Loren.Huang\product\11.2.0\client_1\network\admin\sqlnet.ora
已使用 TNSNAMES 适配器来解析别名
尝试连接 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.200.42)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (40 毫秒)


如有以上测试,证明连接数据库正常
导出语句测试
D:\>exp shimg/123456@ORCL_200_42 file=e:\tain_web20170123.dmp log=e:\tain_web20170123.log
EXP-00091: 正在导出有问题的统计信息。
. . 正在导出表                       ZYX_PRICE导出了        1252 行
EXP-00091: 正在导出有问题的统计信息。
. . 正在导出表               ZYX_TICKET_DETAIL导出了           2 行
EXP-00091: 正在导出有问题的统计信息。
. 正在导出同义词
. 正在导出视图
. 正在导出存储过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
导出成功终止, 但出现警告。


有以上错误由于客户端的字符集跟服务器端不一致导致的
设置字符集
D:\>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
此字符集根据数据库查询得来
SQL> select userenv('language') from dual;


USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8




我们重新导出
D:\>exp shimg/123456@ORCL_200_42 file=e:\tain_web20170123.dmp log=e:\tain_web20170123.log
注:
exp支持的模式
用户模式： 导出用户所有对象以及对象中的数据；
表模式： 导出用户所有表或者指定的表；
整个数据库： 导出数据库中所有对象。

shimg为用户名
123456为密码
ORCL_200_42为tnames配置的别名
file指定文件路径及文件名
log指定导出日志路径及文件名


Export: Release 11.2.0.1.0 - Production on Mon Jan 23 09:31:41 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character se
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SHIMG
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SHIMG
About to export SHIMG's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SHIMG's tables via Conventional Path ...
. . exporting table                ACCOUNT_COMMENT        490 rows exported
. . exporting table                   ACCOUNT_INFO         35 rows exported
. . exporting table                  ACCOUNT_LEVEL          1 rows exported
. . exporting table                  ACCOUNT_LOGIN         38 rows exported
. . exporting table                ACCOUNT_MESSAGE         14 rows exported
. . exporting table                   ACCOUNT_ROLE         14 rows exported
. . exporting table                 ACCOUNT_WECHAT         51 rows exported
. . exporting table                    COUPON_INFO         78 rows exported
. . exporting table                   COUPON_ORDER        230 rows exported
. . exporting table                   COUPON_STOCK         89 rows exported
. . exporting table                  GOODS_COMMENT         44 rows exported
. . exporting table            GOODS_COMMENT_COUNT         14 rows exported
. . exporting table                     GOODS_INFO         62 rows exported
. . exporting table                 GOODS_INFO_APP          1 rows exported
. . exporting table                    GOODS_ORDER        235 rows exported
. . exporting table                   GOODS_PHOTOS         56 rows exported
. . exporting table                    GOODS_STOCK         56 rows exported
. . exporting table            GOODS_STORE_COMMENT          3 rows exported
. . exporting table      GOODS_STORE_COMMENT_COUNT          0 rows exported
. . exporting table               GOODS_STORE_INFO         15 rows exported
. . exporting table                  HOTEL_COMMENT         70 rows exported
. . exporting table            HOTEL_COMMENT_COUNT         11 rows exported
. . exporting table                     HOTEL_INFO         44 rows exported
. . exporting table                 HOTEL_INFO_APP          4 rows exported
. . exporting table                    HOTEL_ORDER         57 rows exported
. . exporting table                   HOTEL_PHOTOS         74 rows exported
. . exporting table             HOTEL_ROOM_AMOUNTS       2401 rows exported
. . exporting table                HOTEL_ROOM_INFO         69 rows exported
. . exporting table              HOTEL_ROOM_PRICES         53 rows exported
. . exporting table                  HOTEL_SERVICE          4 rows exported
. . exporting table             HOTEL_SERVICE_INFO          1 rows exported
. . exporting table                     NEWS_CLASS          4 rows exported
. . exporting table                      NEWS_INFO         14 rows exported
. . exporting table                     ORDER_INFO        429 rows exported
. . exporting table                  ORDER_OPERATE        359 rows exported
. . exporting table                  ORDER_PROMOTE          0 rows exported
. . exporting table                     ORDER_SYNC        310 rows exported
. . exporting table             ORDER_SYNC_HISTORY        732 rows exported
. . exporting table              ORDER_USER_NOTIFY        481 rows exported
. . exporting table      ORDER_USER_NOTIFY_HISTORY         86 rows exported
. . exporting table               PAGE_AREA_DETAIL          0 rows exported
. . exporting table                 PAGE_AREA_INFO          3 rows exported
. . exporting table                   PARK_COMMENT         75 rows exported
. . exporting table             PARK_COMMENT_COUNT         16 rows exported
. . exporting table                  PARK_INFO_APP         11 rows exported
. . exporting table                  PARK_INFO_WAP          5 rows exported
. . exporting table                    PARK_PHOTOS        119 rows exported
. . exporting table                     PARK_VOICE          2 rows exported
. . exporting table                   PAY_ACC_INFO          8 rows exported
. . exporting table                     PAY_WX_ACC          7 rows exported
. . exporting table                   PERSONALPLAN        167 rows exported
. . exporting table                         PHOTOS         37 rows exported
. . exporting table             POINT_ACCOUNT_INFO        331 rows exported
. . exporting table             POINT_CHANGE_DETAL        120 rows exported
. . exporting table                       QQ_LOGIN          0 rows exported
. . exporting table                   REGISTRATION          0 rows exported
. . exporting table                 REPAST_COMMENT         49 rows exported
. . exporting table           REPAST_COMMENT_COUNT          7 rows exported
. . exporting table                    REPAST_INFO         69 rows exported
. . exporting table                REPAST_INFO_APP          2 rows exported
. . exporting table                   REPAST_ORDER         37 rows exported
. . exporting table                  REPAST_PHOTOS         39 rows exported
. . exporting table         REPAST_RESTAURANT_INFO         30 rows exported
. . exporting table              RESTTAURANT_RECOM         10 rows exported
. . exporting table                  ROUTE_COMMENT         77 rows exported
. . exporting table            ROUTE_COMMENT_COUNT         18 rows exported
. . exporting table                ROUTE_CORP_INFO         13 rows exported
. . exporting table                     ROUTE_INFO          7 rows exported
. . exporting table                 ROUTE_INFO_APP          2 rows exported
. . exporting table                    ROUTE_ORDER         44 rows exported
. . exporting table                   ROUTE_PHOTOS         60 rows exported
. . exporting table                     ROUTE_PLAN       1160 rows exported
. . exporting table               SECONDS_CATEGORY          0 rows exported
. . exporting table                   SECONDS_KILL         41 rows exported
. . exporting table                  SECONDS_ORDER         99 rows exported
. . exporting table                   SECONDS_TIME         59 rows exported
. . exporting table               SHIPPING_ADDRESS          0 rows exported
. . exporting table                      SHOW_INFO          0 rows exported
. . exporting table                     SHOW_ORDER          0 rows exported
. . exporting table                SHOW_ORDER_SEAT        227 rows exported
. . exporting table              SHOW_SESSION_AREA          0 rows exported
. . exporting table              SHOW_SESSION_INFO          0 rows exported
. . exporting table              SHOW_SESSION_SEAT          0 rows exported
. . exporting table            SHOW_SESSION_TICKET          0 rows exported
. . exporting table              SHOW_THEATER_AREA          0 rows exported
. . exporting table              SHOW_THEATER_INFO          0 rows exported
. . exporting table              SHOW_THEATER_SEAT          0 rows exported
. . exporting table               SHOW_TICKET_AREA          8 rows exported
. . exporting table              SHOW_TICKET_MODEL          0 rows exported
. . exporting table                     SINA_LOGIN          0 rows exported
. . exporting table                    STATIC_INFO          1 rows exported
. . exporting table                       STRATEGY          1 rows exported
. . exporting table               STRATEGY_COMMENT          0 rows exported
. . exporting table         STRATEGY_COMMENT_COUNT          0 rows exported
. . exporting table                STRATEGY_PHOTOS         39 rows exported
. . exporting table                   STRATEGY_WAP          2 rows exported
. . exporting table                     SUPPLY_BNS        194 rows exported
. . exporting table                    SUPPLY_INFO         16 rows exported
. . exporting table                    SUPPLY_SITE         89 rows exported
. . exporting table                       SYS_AREA       3236 rows exported
. . exporting table                  SYS_BASE_INFO          1 rows exported
. . exporting table                 SYS_CHECK_CODE        171 rows exported
. . exporting table                SYS_DATA_FILTER          4 rows exported
. . exporting table                       SYS_DICT         28 rows exported
. . exporting table                SYS_DICT_DETAIL        146 rows exported
. . exporting table                  SYS_FUNCTIONS         77 rows exported
. . exporting table                       SYS_HELP         11 rows exported
. . exporting table              SYS_IMAGE_LIBRARY       3882 rows exported
. . exporting table               SYS_MSG_TEMPLATE         24 rows exported
. . exporting table             SYS_NOTIFY_HISTORY        502 rows exported
. . exporting table               SYS_NOTIFY_QUEUE          0 rows exported
. . exporting table                    SYS_OPT_LOG        467 rows exported
. . exporting table                      SYS_PARAM          4 rows exported
. . exporting table                  SYS_RAND_BASE       9999 rows exported
. . exporting table                       SYS_ROLE         11 rows exported
. . exporting table                  SYS_ROLEPLUGS        417 rows exported
. . exporting table                       SYS_SITE         19 rows exported
. . exporting table                   SYS_TEMP_FBT
. . exporting table               SYS_TEXT_LIBRARY       2526 rows exported
. . exporting table                SYS_TIME_DETAIL          0 rows exported
. . exporting table                  SYS_TIME_MAIN          0 rows exported
. . exporting table              TICKET_MODEL_INFO         37 rows exported
. . exporting table             TICKET_MODEL_PRICE        435 rows exported
. . exporting table             TICKET_MODEL_STOCK        341 rows exported
. . exporting table                   TICKET_ORDER         75 rows exported
. . exporting table               TICKET_PARK_INFO         28 rows exported
. . exporting table               TIME_TASK_RECORD          0 rows exported
. . exporting table                   TRACK_RECORD          8 rows exported
. . exporting table                     TRANS_INFO       1872 rows exported
. . exporting table                     TRANS_LOGS      11198 rows exported
. . exporting table           WAP_PAGE_AREA_DETAIL         89 rows exported
. . exporting table             WAP_PAGE_AREA_INFO         13 rows exported
. . exporting table                        WEATHER          0 rows exported
. . exporting table                   WEB_MENU_SEO          0 rows exported
. . exporting table                   WEIXIN_LOGIN          0 rows exported
. . exporting table                    ZYX_COMMENT         42 rows exported
. . exporting table              ZYX_COMMENT_COUNT         11 rows exported
. . exporting table               ZYX_HOTEL_DETAIL          1 rows exported
. . exporting table                       ZYX_INFO         46 rows exported
. . exporting table                      ZYX_ORDER         32 rows exported
. . exporting table                     ZYX_PHOTOS         47 rows exported
. . exporting table                      ZYX_PRICE       1252 rows exported
. . exporting table              ZYX_TICKET_DETAIL          2 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
官方文档:http://docs.oracle.com/cd/A87860_01/doc/server.817/a76955/ch01.htm
另推荐选项:buffer
exp的buffer最好〉64000
imp的buffer最 好>;100000
imp中commit=y可以避免回滚段益出
TABLES选项导出指定表,如TABLES=(dept,manager)
rows=n 只导出/导入表结构
direct=y 直接路径方式导出，就是直接使用direct io来读取数据文件，不经过buffercache
query指定语句查询
如:
D:\>exp shimg/123456@ORCL_200_42 file=e:\tain_web20170123.dmp log=e:\tain_web20170123.log tables=SYS_IMAGE_LIBRARY query=\" where id>5000\"
Export: Release 11.2.0.1.0 - Production on Mon Jan 23 10:02:38 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table              SYS_IMAGE_LIBRARY       2713 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
也可用par文件指定
其他可选择选项
D:\>exp help=y
Export: Release 11.2.0.1.0 - Production on Mon Jan 23 10:04:33 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
     Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.


Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)
OBJECT_CONSISTENT    transaction set to read only during object export (N)
FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE             template name which invokes iAS mode export