Splitting a massive table with BLOBS over DB Link in Oracle

Question

I have an interesting problem. I am migrating data from one DB to another including millions and millions of BLOBs with their metadata amounting to literally TBs of data. Due to numerous reasons, this must be done via DB Link and the view on the other side that contains the BLOBs is not indexed. This is because there is no one unique key and there is complicated logic to uniquely identify the document (only one reason we are migrating to a new system). What I'd like to do is the following:

1) Split up the rows into separate chunks to transfer so I can access in parallel and also so I do not lose everything if there is a crash 2) Insert into staging tables 3) Once everything is over, I will merge the staging tables into production tables and dump the temporary ones

I cannot open a cursor on the view due to the Oracle restriction on LOB references over DB link. I use insert into to get around the Oracle restriction but I really need to slice the rows. I have tried running my script several times but my session would get killed after 2 days and I lose everything.

I tried chunking it via rownum to use pagination like chunking but it did not work. Here is my code (help from http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html):

-- for second chunk
procedure process_chnk_2 (
    l_slice     pls_integer;
)   
begin
    execute immediate 'insert /*+ append */ into DESTINATION_TABLE
                        select *
                            from 
                            ( select q.*
                                    ,rownum rnum
                                from
                                ( select *
                                    from migr_view.MIGR_BLOB@DB_LINK) q
                                where rownum <= l_slice * 2
                            ) where rnum >= l_slice
                        ';
    commit;
end process_chnk_2;

This doesn't work since you will still need to query using the BLOB.

I had thought about selecting one of the text columns and then joining the original view like this:

-- for second chunk
procedure process_chnk_2 (
    l_slice     pls_integer;
)   
begin
    execute immediate 'insert /*+ append */ into DESTINATION_TABLE
                        select *
                            from 
                            ( select q.text_that_is_not_quite_a_key
                                    ,q.second_that_is_not_quite_a_key
                                    ,q.third_that_is_not_quite_a_key
                                    ,q.fourth_that_is_not_quite_a_key
                                    .
                                    .
                                    .
                                    ,rownum rnum
                                from
                                ( select *
                                    from migr_view.MIGR_BLOB@DB_LINK) q
                                where rownum <= l_slice * 2
                            ) z
                            , migr_view.MIGR_BLOB@DB_LINK x
                            where z.rnum >= l_slice
                              and z.text_that_is_not_quite_a_key = x.text_that_is_not_quite_a_key
                              and z.second_that_is_not_quite_a_key = x.second_that_is_not_quite_a_key
                              and z.third_that_is_not_quite_a_key = x.third_that_is_not_quite_a_key
                              and z.fourth_that_is_not_quite_a_key = x.fourth_that_is_not_quite_a_key
                              .
                              .
                              .
                        ';
    commit;
end process_chnk_2;

However, we are talking about joining a table with millions and millions of rows and no indexes just to get the slices... Do you know how long this will take?

I cannot use tools, even Data Pump... only script... Thanks for your help!

Answer 1

I am trying this idea put together in the past two days- I'm only proposing it as an answer but I'd like to hear your suggestions as this is UGLY!! While we cannot identify unique keys in any sorted order, by mixing keys and extracting the digits, we can actually find a range of values to slice on and it is fairly evenly distributed.

  l_stmt := q'{insert /*+ NOLOGGING NOWAIT */  
          into  DESTINATION_TABLE
          select *
          from   migr_view.migr_blob@DB_LINK
          where  to_number(regexp_substr(not_key_1 || not_key_2 || not_key_3..., 
          '[[:digit:]]+')) >= }' || i_start_slice || q'{ 
          and to_number(regexp_substr(not_key_1 || not_key_2 || not_key_3..., 
          '[[:digit:]]+')) <  }' || l_end_slice;