导出CLOB的几个SQL语句:
1.导出含有大字段数据的M条记录
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. 导出指定记录记录
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。 先将表按主键字段排序,然后取第1行到第2400行的记录
exp user/passwd statistics=none compress=n file=/oradata/info/backup/article/article_$i.dmp log=/oradata/info/backup/article/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\)\”
编写一个shell脚本,循环执行导出2400条记录的命令,导出表的全部记录
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/article_$i.dmp log=/oradata/info/backup/article/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`
作者“ethan163”
如果觉得《oracle数据库查询如何导出大字段 Oracle数据库导出大字段(CLOB)数据-Oracle》对你有帮助,请点赞、收藏,并留下你的观点哦!