Re: extract data from a table with missing extents

Try getting the extent definitions for the table from dba_extents, omitting the ones from the lost data file.

Get file_id, block_id, blocks.
Then construct a rowid range for each extent using the dbms_rowid package. Use row number 0 as the starting point for the first block of each extent and row 32767 as the end point for the last block (block_id + blocks -1) in each extent.

The do:

    insert into tableX
    select /*+ rowid */ * from bad_table     where rowid between

        {first rowid of first block of an extent}     and

        {last rowid of last block of the extent}

I think there is another note somewhere on Metalink which describes this in more detail if the above notes are insufficient. (There are also a couple of items on my website about using rowid ranges (but under a miscellaneous article relating to Large Updates)).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


ls cheng wrote in message ...


>Hi

>

>We have accidently delete one of datafile in our development database,

>the database is in noarchive log mode. We want to recover data in that

>tablespace which a datafile is missing, the good news is that there is

>only one table in that tablespace and we can afford loose some data

>(but not other tables that is why we cannot restore backup from

>yesterday). I follows the steps from Note 33405.1 Extracting Data from

>a Corrupt Table using SKIP_CORRUPT_BLOCKS or Event 10231 from Metalink

>but it is not working, I tried both methods but everytime I do create

>table xx as select * from yy it returns the error it cant read the

>missing datafile. What am I doing wrong?

>

>I am using Oracle 8.1.7 on Windows 2000

>