AnySQL.net English

    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:

    Following is a example of recover BLOB data of Oracle Windows 10g R2 using the AUL 4, let's create the demo table first:

SQL> CREATE TABLE T_BLOBDEMO
  2  (
  3    ID NUMBER,
  4    BLOB1 BLOB,
  5    BLOB2 BLOB,
  6    BLOB3 BLOB
  7  )
  8  LOB(BLOB3) STORE AS (DISABLE STORAGE IN ROW)
  9  /

Table created.

SQL> INSERT INTO T_BLOBDEMO VALUES (1, '3031323334', 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 BLOB2 and BLOB3, they contain no data (null value). column BLOB1 contains a very small value (string "01234"), I am testing the inline BLOB now, let's store the BLOB data with the same file (output to screen) of other columns:

AUL> UNLOAD TABLE anysql.t_blobdemo;
2006-12-17 21:43:27
Unload OBJD=9966 FILE=4 BLOCK=4859 CLUSTER=0 ...
1|01234
2006-12-17 21:43:27

    Then I will use AnySQL to populate BLOB2 and BLOB3 with an image file (test.jpg) :

    Now I will recover the two demo table in DMP format, to check whether it will succeed. As following:

AUL> set clob_edian big
  Current CLOB_EDIAN is : BIG
AUL> set lob_convert 1
  Current LOB_CONVERT is : 1-GBK
AUL> set charset 852
  Current CHARSET is : 0x0354
AUL> set output_style dmp
  Current OUTPUT_STYLE is : DMP
AUL> unload table anysql.t_clobdemo to t_clobdemo.dmp;
2006-12-17 22:27:40
Unload OBJD=9956 FILE=4 BLOCK=4611 CLUSTER=0 ...
2006-12-17 22:27:40
AUL> unload table anysql.t_blobdemo to t_blobdemo.dmp;
2006-12-17 22:27:46
Unload OBJD=9966 FILE=4 BLOCK=4859 CLUSTER=0 ...
2006-12-17 22:27:46

    Now import data into T_CLOBDEMO table:

C:\MYDUL>imp system/oracle file=t_clobdemo.dmp fromuser=mydul touser=anysql ignore=y

Import: Release 10.2.0.1.0 - Production on Sun Dec 17 22:28:33 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by MYDUL, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing MYDUL's objects into ANYSQL
. . importing table                   "T_CLOBDEMO"          2 rows imported
Import terminated successfully without warnings.

    And check the CLOB column length in SQL*Plus with DBMS_LOB package:

    I have seems somebody truncated their tables by mistaken several times, because of they connect to wrong database and run the script without careful check. To save data, the first thing they need to do is stop the database, and take a backup of the tablespace where the tables locate. For truncation, Oracle just reformat the segment header, and other blocks remain untouched, so we are able to recover the data, however if the blocks are touched by oracle again, it will be formatted to hold new rows. Following is an example of how to recover the rows back, let's build a demo table first:

SQL> CREATE TABLE T_TRUNCATE AS SELECT * FROM TAB;

Table created.

SQL> SELECT COUNT(*) FROM T_TRUNCATE;

  COUNT(*)
----------
        14

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> TRUNCATE TABLE T_TRUNCATE;

Table truncated.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

    The truncated table will be assigned a new data object id, we could get it from DESC command output:L

AUL> desc anysql.t_truncate

Storage(OBJ#=9976 OBJD=9977 TS=4 FILE=4 BLOCK=5235 CLUSTER=0)
No. SEQ INT Column Name                   Type
--- --- --- ----------------------------- ----------------
  1   1   1 TNAME                         VARCHAR2(30) NOT NULL
  2   2   2 TABTYPE                       VARCHAR2(7)
  3   3   3 CLUSTERID                     NUMBER

    To recover the rows, we need run scan extent command to generate the extent map to a text file (AULEXT.TXT), because segment header is formatted by Oracle.

    All the blocks of a chunk must be continual with a single extent, because you cannot specify a chunk size larger than the next extent size. Based on this rule, I add different chunk size support to AUL 4. Following are two demo tables, they both contains one column and only one row, populated with the same data.

SQL> SELECT TABLE_NAME,COLUMN_NAME,CHUNK FROM USER_LOBS;

TABLE_NAME      COLUMN_NAME       CHUNK
--------------- ------------ ----------
T_CHUNK2        COL1              32768
T_CHUNK1        COL1              16384

    Then we will use the chunk option of unload command to specify the LOB chunk size (how many blocks?) of this table, default is one. There is one limit that all the LOB columns should have the same chunk size in a single table. Then we start to unload the two LOB value to OS files :

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_chunk1 chunk 2;
2006-12-19 12:58:18
Unload OBJD=9986 FILE=4 BLOCK=5243 CLUSTER=0 ...
LOB_0100147C_1FE8.dat
2006-12-19 12:58:18
AUL> unload table anysql.t_chunk2 chunk 4;
2006-12-19 12:58:25
Unload OBJD=9989 FILE=4 BLOCK=5523 CLUSTER=0 ...
LOB_01001594_1FE8.dat
2006-12-19 12:58:25

    The above command created two data files on disk, LOB_0100147C_1FE8.dat and LOB_01001594_1FE8.dat, let's check the size and do a compare with the original data file: