下面是用AUL 4.0.0 Beta版本恢复Windows上10g R2数据库CLOB数据的一个演示, 首先是创建测试表:
SQL> CREATE TABLE T_CLOBDEMO
2 (
3 ID NUMBER,
4 CLOB1 CLOB,
5 CLOB2 CLOB,
6 CLOB3 CLOB
7 )
8 LOB(CLOB3) STORE AS (DISABLE STORAGE IN ROW)
9 /
Table created.
SQL> INSERT INTO T_CLOBDEMO VALUES (1,'INLINE CLOB', NULL,NULL);
1 row created.
然后到SYS用户下, 运行ALTER SYSTEM CHECKPOINT以将数据写到数据文件中, 现在CLOB2和CLOB3列中是空值, CLOB1中的值够小, 应当是INLINE CLOB, 我们将CLOB的数据和其他列的数据恢复到一个文件中.
AUL> set lob_convert 1
Current LOB_CONVERT is : 1-GBK
AUL> set lob_storage 0
Current LOB_STORAGE is : 0-INLINE
AUL> set clob_edian big
Current CLOB_EDIAN is : BIG
AUL> unload table anysql.t_clobdemo;
2006-12-17 19:38:08
Unload OBJD=9956 FILE=4 BLOCK=4611 CLUSTER=0 ...
1|INLINE CLOB
2006-12-17 19:38:08
可以看到数据被成功恢复, 接下来我们用AnySQL来将CLOB2和CLOB3列中插入内容(一个64KB大小的文本文件), 如下所示:
ASQL> VAR P_LOB CLOB
ASQL> DEFINE P_LOB=TNSNAMES.ORA
ASQL> SET QUERYONLY FALSE
ASQL> UPDATE T_CLOBDEMO SET CLOB2 = :P_LOB , CLOB3 = :P_LOB;
1 rows affected.
ASQL> COMMIT;
Commit Succeed.
ASQL> SELECT DBMS_LOB.GETLENGTH(CLOB2), DBMS_LOB.GETLENGTH(CLOB3) FROM T_CLOBDEMO;
DBMS_LOB.GETLENGTH(CLOB2) DBMS_LOB.GETLENGTH(CLOB3)
------------------------- -------------------------
63681 63681
1 rows returned.
现在我们来继续恢复, 这一次将LOB值恢复到独立的文件中, 如下所示:
AUL> SET CLOB_EDIAN BIG
Current CLOB_EDIAN is : BIG
AUL> SET LOB_STORAGE 1
Current LOB_STORAGE is : 1-FILE
AUL> SET LOB_CONVERT 1
Current LOB_CONVERT is : 1-GBK
AUL> UNLOAD TABLE ANYSQL.T_CLOBDEMO TO T_CLOBDEMO.TXT;
2006-12-17 19:45:49
Unload OBJD=9956 FILE=4 BLOCK=4611 CLUSTER=0 ...
2006-12-17 19:45:49
下面来看一下文件T_CLOBDEMO.TXT中的内容:
C:\MYDUL>cat T_CLOBDEMO.TXT
1|LOB_01001208_1F17.dat|LOB_01001208_1F52.dat|LOB_01001208_1FA7.dat
从OS角度来检查一下恢复出来的文件的大小:
C:\MYDUL>DIR LOB*
Volume in drive C has no label.
Volume Serial Number is 5CF4-08FE
Directory of C:\MYDUL
2006-12-17 19:45 11 LOB_01001208_1F17.dat
2006-12-17 19:45 63,681 LOB_01001208_1F52.dat
2006-12-17 19:45 63,681 LOB_01001208_1FA7.dat
3 File(s) 127,373 bytes
0 Dir(s) 9,588,129,792 bytes free
数据装载可以用生成的T_CLOBDEMO_sqlldr.ctl进行SQL*Loader装载:
--
-- Generated by AUL/MyDUL, for table ANYSQL.T_CLOBDEMO
--
OPTIONS(DIRECT=TRUE,READSIZE=4194304,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'T_CLOBDEMO.TXT' "STR X'0d0a'"
INTO TABLE T_CLOBDEMO
FIELDS TERMINATED BY X'7c' TRAILING NULLCOLS
(
ID CHAR ,
LOBF_00002 FILLER CHAR(32),
CLOB1 LOBFILE(LOBF_00002) TERMINATED BY EOF ,
LOBF_00003 FILLER CHAR(32),
CLOB2 LOBFILE(LOBF_00003) TERMINATED BY EOF ,
LOBF_00004 FILLER CHAR(32),
CLOB3 LOBFILE(LOBF_00004) TERMINATED BY EOF
)
不过需要将DIRECT=TRUE去掉, 并将ROWS=50000调小一些(如100):
C:\MYDUL>sqlldr anysql/anysql control=T_CLOBDEMO_sqlldr.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Dec 17 19:59:20 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 1
现在已经将恢复出来的数据成功装载到数据库中了, 我将会在不同的平台, 不同的数据库版本上及BLOB类型上进行这个测试. 如果你有兴趣, 也可以试试.