AnySQL.net English

    Some people truncated there tables by mistake, and asked for how to recover the data on BBS. Here is the AUL/MyDUL solution for this case. Let's start with building a test table:

ASQL> DESC TRUNCDEMO

NO# NAME              NULLABLE TYPE
--- ----------------- -------- ------------
  1 COL1                       VARCHAR2(20)

ASQL> SELECT * FROM TRUNCDEMO;

COL1
-----
ROW 1
ROW 2

2 rows returned.

    Then we truncate the table, actually this operation just format the segment header, assign a new data object id, and change the extent allocation information, if truncate with reuse storage operation, extent allocate information will not be changed. Let's check the data object id before and after the operation:

ASQL> SELECT DATA_OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;

DATA_OBJECT_ID OBJECT_NAME
-------------- -----------
         13676 TRUNCDEMO

1 rows returned.

ASQL> truncate table truncdemo;

Truncate Table Succeed.

ASQL> SELECT DATA_OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;

DATA_OBJECT_ID OBJECT_NAME
-------------- -----------
         13677 TRUNCDEMO

1 rows returned.

    Because of system have lost the information before truncation of the table, so it could be treated as lost system tablespace. In the following example, we found that we could specify the data object id before truncation to recover the rows, by the new data object id we cannot recover the rows. If the freed space have been reused by other objects or newly inserted rows, nothing could be recovered.

    I have found a small mistake in the SQL Loader control file generated by AUL/MyDUL, if you use the control file directly, it will skip the first row of the text file. That's because I copy the SQL Loader parameters from another text unload tool which put the column name at the first line, while AUL/MyDUL did not put the column name as the first line of recovered text file. Please remove the "SKIP=1" in the "OPTIONS(..." line. So sorry for the mistake.

    Old example of the control file:

--
-- Generated by AUL/MyDUL, for table hr.test
--
OPTIONS(DIRECT=TRUE,READSIZE=4194304,ERRORS=-1,SKIP=1,ROWS=50000)

    I have updated the software and uploaded it, new example:

--
-- Generated by AUL/MyDUL, for table hr.test
--
OPTIONS(DIRECT=TRUE,READSIZE=4194304,ERRORS=-1,ROWS=50000)

    If you used to recover data by AUL/MyDUL in text format, please re-input the first line of the text file. Sorry again!

    I have announced that the full licence of AUL/MyDUL will be free in the remaining Oct 2006. And I published a CGI script on this site for license generation, now you can directly get it from the site, no need to mail or chat with me.

    Please fill in the register code, and click the "Generate" button.

Register Code:

    From next month, it will not be free, so user name and password will be prompted. You could become official user or wait for the next free duration.

    Update: In Oct 2006, user name is "free" and password is "200610".

    Lots of people do not know the steps (how to asking for a full license) of data recovery using AUL/MyDUL, so I write this article to make it clear.

    1, Download the latest AUL/MyDUL binary.

    2, Get in touch with me by MSN, Skype or AIM is preferred for fast response and technical support.

    3, Start AUL/MyDUL, tell me the register code (See the read part of the first line), do not exit the AUL/MyDUL window.

Register Code: FLGM-J7RC-LVM6-TX2C-VWP0
AUL : AnySQL UnLoader(MyDUL) for Oracle 8/8i/9i/10g, Trial Version 3.2.2

(C) Copyright Lou Fangxin 2005-2006 (AnySQL.net), all rights reserved.

AUL>

    4, I will generate the full licence code for this register code, it's "E2655B" for this run, to remove the limit (512MB, 4 Files), run the following command to register it.

AUL> SET LICENCE E2655B

    5, Verify the register result, you will see "Registed" in the output of "ULIMIT" command if successful, else failed.

AUL> ULIMIT
  Registered, Elapsed: 35

    6, Now we can open the configuration file and start the recovery.

AUL> open db10g.cfg
*  ts#  fno  rfn ver bsize     blocks filename
- ---- ---- ---- --- ----- ---------- --------------------------------
Y    0    1    1 a2   8192      32768 SYSTEM01.DBF
......

    In this license model, we could perform recovery without presence or remote login, it will greatly save the time for us.

    I published a CGI script on my site for online apply of AUL/MyDUL license, so you can get the full license even I am not online or available. In Oct 2006, you could get free license with free account, but since Nov, the free account will be disabled, you need to buy license, and I will set an account for you to apply license online.

    The cost of license will be much lower than the cost of service, and it's easy to use and with lot's of documents on my site, the most important is the support of newest release of Oracle. Buy license is the best solution for you.

    How to count the license count, it will be counted for each distinct day, for same date, 5 accesses will be treated as only one license, the following list will be counted as 3 license requests.

DAY=20061013 IP=58.212.85.189 CODE=5IFW-1D8F-UB4U-MT6J-K89C
DAY=20061013 IP=66.211.156.98 CODE=OC0Y-USYX-PP5C-YAGD-O6AN
DAY=20061014 IP=200.6.100.10 CODE=AU3I-KV2S-KQKM-30VS-TV46
DAY=20061014 IP=69.140.195.22 CODE=F901-OX9H-RTAN-NMTT-RDON
DAY=20061014 IP=69.140.195.22 CODE=GLHF-22F5-TR7F-0KL1-NQS5
DAY=20061014 IP=65.113.143.60 CODE=9WBJ-9Q1A-74W1-UK7C-PO88
DAY=20061014 IP=65.113.143.60 CODE=0SDJ-K3BI-KS4D-952X-0WQ9
DAY=20061014 IP=65.113.143.60 CODE=69S2-4TOG-5PY3-O710-EWFN
DAY=20061014 IP=65.113.143.60 CODE=UJ42-URTU-ZEPK-16NZ-2M7L

    The current price is 1000 USD/800 EUR for each license.

    If you contact me just when you need recovery service, I will not sell license at this price to you, it will be treated as recovery service.

    Updated: The count logic in SQL mode:

-- TABLE AUL_REQUEST (DAY,IP,CODE)
-- PK (DAY,IP,CODE)
SELECT SUM(CEILING(DAYCNT)/5)
FROM
(SELECT DAY, COUNT(*) DAYCNT FROM AUL_REQUEST)