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.
AUL> SCAN EXTENT FILE 4
2006-12-18 21:32:10
2006-12-18 21:32:24
The key is that we need to find out the old data object id, here I shutdown the database after truncate, no new data inputed after truncation. From the previous describe command output, we found that the new data object id is 9977, and segment header is (4,5235), the block next to segment header should be the old data block, we could get the old data object id from this block by ORADUMP command, if we created the table with several free list group, we need to skip more blocks. Following is the ORADUMP output:
AUL> ORADUMP FILE 4 BLOCK 5236
RDBA=0x01001474(4/5236)=16782452,type=0x06,fmt=0xa2,seq=0x02,flag=0x04
seg/obj=0x000026f8=9976,csc=0x0000.0006caf5,itc=3,typ=1 - DATA
FLG=0x32, fls=0, nxt=0x01001471(4/5233)=16782449
......
We found that the old data object id is 9976, then we start recovery, without specify the old data object id, now rows should be returned:
AUL> unload table anysql.t_truncate;
2006-12-18 21:33:37
Unload OBJD=9977 FILE=4 BLOCK=5235 CLUSTER=0 ...
2006-12-18 21:33:37
Then we specify the old data object id, and do a recover again, all 14 rows are recovered as following:
AUL> unload table anysql.t_truncate object 9976;
2006-12-18 21:33:45
Unload OBJD=9976 FILE=4 BLOCK=5235 CLUSTER=0 ...
P_MV_FACT_SALES|TABLE
TIME_DIM|TABLE
FACT_SALES|TABLE
MV_FACT_SALES|TABLE
SEG$|TABLE
NUMTEST|TABLE
T_OBJECTS|TABLE
T_LOBTEST|TABLE
T_INCLOB|TABLE
CF_XXK|TABLE
T_TESTDMP|TABLE
T_CLOBDEMO|TABLE
T_BLOBDEMO|TABLE
T_TRUNCATE|TABLE
2006-12-18 21:33:45
While database system is really complex, for this recovery, AUL cannot give you a 100% promise of data accuracy. Enjoy it!