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.
官方文件:
另推薦選項: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

exp,imp相關工具說明

相關文章