mysql的大字段clob_Oracle数据库导出大字段(CLOB)数据问解及解决

问题描述：数据库导出数据日志报错

操作系统版本：RHEL AS4 U4

64bit

数据库版本：Oracle

Database 10g Enterprise Edition

Release 10.2.0.2.0 -

64bit

错误代号：ORA-01555

ORA-22924

查看导出数据命令

$ cat

/oradata/info/backup/scripts/exp_couser

exp user/passwd

buffer=1048576 compress=n owner=couser statistics=none compress=n

consistent=y file=/oradata/info/backup/exp_couser_`date

+%Y%m%d`.dmp log=/oradata/info/backup/exp_log_`date

+%Y%m%d_%H%M%S`.log

查看exp导出数据日志

$ cat

/oradata/info/backup/exp_log_20080218_010101.log

Connected to: Oracle

Database 10g Enterprise Edition

Release 10.2.0.2.0 - 64bit

Production

With the Partitioning, OLAP and Data

Mining options

Export done in ZHS16GBK character set and

AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects

and actions

. exporting foreign function library names

for user COUSER

. exporting PUBLIC type

synonyms

. exporting private type

synonyms

. exporting object type definitions for

user COUSER

About to export COUSER's objects

...

. exporting database

links

. exporting sequence

numbers

. exporting cluster

definitions

. about to export COUSER's tables via

Conventional Path ...

. . exporting tableFSM_COLTREE2XXFL84

rows exported

. . exporting tableFSM_RIGHT_ARTICLE

EXP-00056: ORACLE error 1555

encountered

ORA-01555: snapshot too old: rollback

segment numberwith

name "" too small

ORA-22924: snapshot too

old

. . exporting tableFSM_RIGHT_COLTREE369

rows exported

. . exporting tableFSM_RIGHT_DELEY227

rows exported

. . exporting tableFSM_RIGHT_GROUP3

rows exported

. . exporting tableFSM_RIGHT_USER8

rows exported

. . exporting tableFSM_SYSTEM_ADMIN1

rows exported

. . exporting tableLOG_ARTICLE_PUBLISH5688437

rows exported

. . exporting tableTBL_FSM_PRODUCTINFO948

rows exported

. . exporting tableTBL_FSM_PRODUCTS206

rows exported

. . exporting tableTBL_FSM_PRODUCT_TYPE7

rows exported

. . exporting tableTBL_FSM_SCREEN_TYPE12

rows exported

. . exporting tableTBL_FSM_STYLE_INFO7278

rows exported

. . exporting tableTBL_FSM_STYLE_POSITION159

rows exported

. . exporting tableTBL_FSM_USER2

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 with

warnings.

导出日志显示：表FSM_RIGHT_ARTICLE的记录未能正常导出，出现了ORA-01555和ORA-22924错误

查看数据库表空间使用情况，正常

查看数据库警告日志文件，未发现明显异常

查看ORA-01555和ORA-22924错误描述

$ oerr ora 01555

01555, 00000, "snapshot too old: rollback

segment number %s with name \"%s\" too small"

// *Cause: rollback records needed by a

reader for consistent read are

//overwritten

by other writers

// *Action: If in Automatic Undo

Management mode, increase undo_retention

//setting.

Otherwise, use larger rollback segments

$ oerr ora 22924

22924, 00000, "snapshot too

old"

//*Cause:The

version of the LOB value needed for the consistent read

was

//already

overwritten by another writer.

//*Action:

Use a larger version pool.

undo_retention参数指定了保留还原数据信息的时间(秒)

显示当前undo_retention参数值

$ sqlplus /nolog

$ conn / as sysdba

SQL> show parameter

undo_retention

NAMETYPEVALUE

----------------------------- -----------

---------

undo_retentioninteger900

修改undo_retention参数值

SQL> alter system set undo_retention =

10800 scope=both;

运行导出脚本exp...错误依旧...

SQL> alter system set undo_retention =

108000 scope=both;

运行导出脚本exp...错误依旧...

SQL> alter system set undo_retention =

1080000 scope=both;

运行导出脚本exp...错误依旧...

与此参数无关，改成3600

SQL> alter system set undo_retention =

3600 scope=both;

测试导出表fsm_right_article

10行记录，导出成功

$ 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\"

测试导出10,000行记录，导出失败

$ 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\"

经反复测试，确定可以成功导出2400行记录

$ 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\<=2400\"

编写一个shell脚本，循环执行导出2400条记录的命令，直到导出全部记录

总共记录有220979，每次导出记录2400行

第一次导出第1行到第2400行的记录

第一次导出第2401行到4800行的记录

…

第n次导出第(n-1)×2400+1行到n×2400行的记录

查看表结构，唯一索引的字段无number类型的，不能直接使用作为记数条件

以下SQL的作用是：先将表按主键字段排序，然后取第1行到第2400行的记录

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

将以上SQL写入exp导出命令的query参数中，注意转义符\的使用

$ 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`

done

运行exp_article.sh生成dmp文件和日志文件

$

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

查看导出日志文件是否出现错误

$ find /oradata/info/backup/article -name

"*.log" | xargs grep -i "with warnings"

/oradata/info/backup/article/article_108400.log:Export

terminated successfully with warnings.

/oradata/info/backup/article/article_110200.log:Export

terminated successfully with warnings.

查看出错的导出日志文件

$ cat

/oradata/info/backup/article/article_108400.log

Connected to: Oracle

Database 10g Enterprise Edition

Release 10.2.0.2.0 - 64bit

Production

With the Partitioning, OLAP and Data

Mining options

Export done in ZHS16GBK character set and

AL16UTF16 NCHAR character set

. exporting pre-schema procedural objects

and actions

. exporting foreign function library names

for user COUSER

. exporting PUBLIC type

synonyms

. exporting private type

synonyms

. exporting object type definitions for

user COUSER

About to export COUSER's objects

...

. exporting database

links

. exporting sequence

numbers

. exporting cluster

definitions

. about to export COUSER's tables via

Conventional Path ...

. . exporting tableFSM_RIGHT_ARTICLE

EXP-00056: ORACLE error 1555

encountered

ORA-01555: snapshot too old: rollback

segment number with name "" too small

ORA-22924: snapshot too

old

Export terminated successfully with

warnings.

导出日志表明：

108400至110800(108400+2400)的记录未被导出

110200至112600(110200+2400)的记录未被导出

修改导出脚本每次导出100条记录，导出108400至110800的记录(蓝色字体为修改的部分)

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=110800

i=108400

step=100

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`

done

运行exp_article.sh生成dmp文件和日志文件

$

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

查看导出日志文件是否出现错误

$ find /oradata/info/backup/article -name

"*.log" | xargs grep -i "with warnings"

/oradata/info/backup/article/article_108450.log:Export

terminated successfully with warnings.

修改导出脚本每次导出1条记录，导出108450至108550的记录(蓝色字体为修改的部分)

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=108550

i=108450

step=1

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`

done

运行exp_article.sh生成dmp文件和日志文件

$

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

查看导出日志文件是否出现错误

$ find /oradata/info/backup/article -name

"*.log" | xargs grep -i "with warnings"

/oradata/info/backup/article/article_108459.log:Export

terminated successfully with warnings.

查看相应的表记录

select * from

fsm_right_article

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 <= 108459 +1

)

where

r_n > 108459

)

发现content字段(clob类型)显示有问题(字样)

用同样方法找到110200至112600的错误记录

和研发部门同事确认这两条记录content字段的值并更新

SQL> update fsm_right_article set

content=’…’ where articleid = …

再运行导出脚本无错误显示，问题解决！