Following is a example of recover CLOB data of Oracle Windows 10g R2 using the AUL 4, let's create the demo table first:
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.
Then connect to sys and issue an checkpoint command to make sure the data actually written to data files. For column CLOB2 and CLOB3, they contain no data (null value). column CLOB1 contains a very small value, I am testing the inline CLOB now, let's store the CLOB data with the same file (output to screen) of other columns:
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
Seems we have successfully recovered the data in CLOB1 column. Now we will start larger LOB values, I will using AnySQL to populate CLOB2/CLOB3 columns with a 64KB text file, as following:
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.
Go ahead to recover the same table, this time we store each CLOB value in it's own file, as following:
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
Check the contents in the T_CLOBDEMO.TXT file:
C:\MYDUL>cat T_CLOBDEMO.TXT
1|LOB_01001208_1F17.dat|LOB_01001208_1F52.dat|LOB_01001208_1FA7.dat
From the OS level, let's view the size of generated files:
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
AUL 4 will generate a SQL*Loader control file for loading:
--
-- 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
)
But we need to remove the "DIRECT=TRUE" option, and set ROWS to a small value (such as 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
It seems AUL 4 successfully recover all the data. I will try different platform, different Oracle version, and also test BLOB data type. If you have interesting, please do some test for me. Thanks.