• 以spool方式導(dǎo)出文本數(shù)據(jù)

SET feedback off
SET newpage none
SET pagesize 50000
SET linesize 20000
SET verify off
SET pagesize 0
SET term off
SET trims ON
SET heading off
SET trimspool ON
SET trimout ON
SET timing off
SET verify off
SET colsep |
spool d:\dataexp\tableName.txt
    select SERIAL_ID || ',' || TSSH_SERIAL_NO || ',' || SERVICE_FIELD || ',' || SERVICE_TYPE || ',' || CHANNEL_TYPE || ',' || PROVINCE_CODE || ',' || SOURCE_AREA || ',' || to_char(ACCEPT_TIME,'yyyymmddHH24miss') || ',' || to_char(REPLY_TIME,'yyyymmddHH24miss') || ',' || CALL_TYPE || ',' || to_char(FINISH_TIME,'yyyymmddHH24miss') || ',' || to_char(VISIT_TIME,'yyyymmddHH24miss') || ',' || TITLE || ',' || CONTENT || ',' || STATUS || ',' || ACCEPT_SATISFACTION || ',' || HANDLE_RESULT || ',' || HANDLE_SATISFACTION || ',' || VISIT_SATISFACTION || ',' || to_char(CREATE_TIME,'yyyymmddHH24miss') || ',' || NOTE || ',' || REPLY_FLAG || ',' || SERVICE_ITEM || ',' || WAIT_TIME || ',' || CALL_ID || ',' || CALL_NO || ',' || OPER_CODE || ',' || to_char(RING_TIME,'yyyymmddHH24miss') || ',' || to_char(START_TIME,'yyyymmddHH24miss') || ',' || to_char(END_TIME,'yyyymmddHH24miss') from table_name where length(content) > 10 and rownum < 100001 order by serial_id desc;
spool off

• 使用expdp（數(shù)據(jù)泵）方式導(dǎo)出數(shù)據(jù).（服務(wù)器端導(dǎo)出）

1. 先使用sqlplus登陸
    sqlplus / as sysdba;
2. 在sqlplus命令行炼幔，創(chuàng)建導(dǎo)出數(shù)據(jù)存放目錄
    create directory expDir as '/home/oracle/dataexp/expDir';
   查看管理員目錄
    select * from dba_directories;
3. 在sqlplus命令行猫牡，把存放目錄授權(quán)給導(dǎo)出用戶
    grant read,write on directory expDir to tssh;
4. 退出sqlplus命令行腺晾，使用系統(tǒng)命令expdp使用用戶模式導(dǎo)出整個(gè)用戶下的數(shù)據(jù)
    expdp user/pwd schemas=orcl directory=expDir dumpfile=exp_file.dmp logfile=exp_file.log job_name=my_job
    
    directory 參數(shù)需要放在前面卷胯，否則會(huì)出現(xiàn)錯(cuò)誤
    job_name 參數(shù)最后不需要加 ; 子刮，否則job_name就會(huì)帶 ;

• 使用exp（普通）方式導(dǎo)出數(shù)據(jù).（客戶端導(dǎo)出）

1. 在cmd（或者linux命令行）執(zhí)行以下語句
    exp userid=user/pwd@orcl full=y file=exp_file.dmp log=exp_file.log

• 使用imp（普通）方式導(dǎo)入數(shù)據(jù).

1. 對(duì)于oracle11g，需要執(zhí)行以下語句窑睁，否則空表不導(dǎo)入
    alter system set deferred_segment_creation=false;
2. 在cmd（或者linux命令行）執(zhí)行以下語句
    imp userid=user/pwd@orcl full=y ignore=y file=exp_file.dmp log=exp_file.log

• 導(dǎo)出實(shí)例

1. 按條件精確導(dǎo)出某條數(shù)據(jù)[導(dǎo)出指定ID數(shù)據(jù)]
    expdp user/pwd directory=expDir dumpfile=exp_file.dmp logfile=exp_file.log  TABLES=TABLE_NAME QUERY=TABLE_NAME:\"WHERE SERIAL_ID = 100000005776\"
2. 按指定時(shí)間導(dǎo)出數(shù)據(jù)[導(dǎo)出7月份數(shù)據(jù)]
    expdp user/pwd directory=expDir dumpfile=TABLE_NAME_201707.dmp logfile=exp_file.log  TABLES=TABLE_NAME QUERY=TABLE_NAME:\"WHERE to_char\(CREATE_TIME, \'yyyy/mm\'\) = \'2017/06\'\"
3. 采用并行方式導(dǎo)出
    expdp user/pwd directory=expDir dumpfile=exp_file_%U.dmp logfile=exp_file.log  TABLES=TABLE_NAME QUERY=TABLE_NAME:\"WHERE SERIAL_ID = 100000005776\" parallel=10

• 導(dǎo)入實(shí)例

1. 導(dǎo)入表數(shù)據(jù)挺峡，表已存在增量導(dǎo)入
impdp user/pwd directory=expDir dumpfile=user_data_201706.dmp logfile=exp_file_201706.log TABLES=TABLE_NAME,TABLE_NAME_2 table_exists_action=APPEND
2. 采用并行方式導(dǎo)入
impdp user/e6mu3Z03 directory=expDir dumpfile=user_data_all_20170905_%U.dmp logfile=user_data_all_imp_20170905.log table_exists_action=APPEND parallel=10

-- schema 變更
impdp IISP/iisp REMAP_SCHEMA=user:IISP directory=expdir dumpfile=exp_177_season3_%U.dmp logfile=imp_177_data.log table_exists_action=APPEND parallel=10

• 參考資料

1. 數(shù)據(jù)泵方式導(dǎo)數(shù)據(jù)
   http://blog.csdn.net/haiross/article/details/27580199/
2. 日期轉(zhuǎn)換
   http://www.cnblogs.com/lslvxy/p/3457049.html