Export big field (CLOB) data from Oracle Database

Export Several SQL statements of CLOB:

1. Export M records containing big field data

Exp user/passwd statistics = none compress = n consistent = y file =/oradata/info/backup/exp_article.dmp log =/oradata/info/backup/exp_article.log tables = fsm_right_article query = \" where rownum \ <= 10 \"

2. Export the specified record

Exp user/passwd statistics = none compress = n consistent = y file =/oradata/info/backup/exp_article.dmp log =/oradata/info/backup/exp_article.log tables = fsm_right_article query = \" where recordid = n \"

3. Sort the table by the primary key field, and then take the records from 1st rows to 2400th rows.

Exp user/passwd statistics = none compress = n file =/oradata/info/backup/article _ $ I. dmp log =/oradata/info/backup/article _ $ I. log consistent = y tables = fsm_right_article query = \ "where articleid in \ (select articleid from \ (select rownum as r_n \, article \. articleid from \ (select articleid from fsm_right_article order by articleid desc \) article where rownum \<= 2400 \) where r_n \> 0 \)\"

Write a shell script, execute the command to export 2400 records cyclically, and export all records of the table

 

Vi/oradata/info/backup/scrips/exp_article.sh

 

#! /Bin/bash

Export ORACLE_BASE =/oracle/app/oracle

Export ORACLE_HOME =/oracle/app/oracle/product/10.2.0/db_1

Export NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

Export ORACLE_SID = info

Export PATH =/oracle/app/oracle/product/10.2.0/db_1/bin: $ PATH: $ HOME/bin

Count = 220979

I = 0

Step = 2400

While [$ I-le $ count]

Do

Exp user/passwd statistics = none compress = n file =/oradata/info/backup/article _ $ I. dmp log =/oradata/info/backup/article _ $ I. log consistent = y tables = fsm_right_article query = \ "where articleid in \ (select articleid from \ (select rownum as r_n \, article \. articleid from \ (select articleid from couser \. fsm_right_article order by articleid desc \) article where rownum \ <= $ I + $ step \) where r_n \> $ I \)\"

I = 'expr $ I + $ step'

File = 'expr $ file + 1'

Author: "ethan163"