BULK COLLECT/FORALL statements with dynamic query and table name- Oracle PL/SQL

Question

I need help in optimizing this query to use bulk collect and forall statements. I have created backup tables (BCK_xxxx) to copy all data from original tables (ORIG_xxx) but I am having problems converting this to bulk collect. Most examples I saw in BULK collect includes already defining the table name and structure using %rowtype. However, I have hundreds of tables to backup so I need my query specifically the table name to be dynamic. This my original query that inserts/deleted data one by one without bulk collect and takes a lot of time:

DECLARE
--select all table names from backup tables (ex: BCK_tablename)
CURSOR cur_temp_tbl IS
    SELECT table_name
    FROM all_tables 
    WHERE OWNER = 'BCKUP'
    ORDER BY 1;  

--select all table names from original tables (ex: ORIG_tablename)
 CURSOR cur_original_tbl IS
    SELECT table_name
    FROM all_tables 
    WHERE OWNER = 'ORIG'
    ORDER BY 1;    
    l_tbl_nm VARCHAR2(30 CHAR);

BEGIN
    --first loop to delete all tables from backup
    FOR a IN cur_temp_tbl LOOP
       l_tbl_nm := a.table_name;                                    
       EXECUTE IMMEDIATE 'DELETE FROM '||  l_tbl_nm;
       l_deleted_cnt :=  l_deleted_cnt +1;            
    END LOOP;

    --second loop to insert data from original to backup        
    FOR b IN cur_original_tbl LOOP            
         l_tbl_nm := b.table_name;   
        CASE
          WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN
          l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_');
          ELSE
           l_tbl_nm := 'BCK_' || l_tbl_nm;
        END CASE;  

        EXECUTE IMMEDIATE 'INSERT INTO '  || l_tbl_nm || ' SELECT * FROM ' || b.table_name;
        l_inserted_cnt :=  l_inserted_cnt +1;
    END LOOP; 

    dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt);
    dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt);
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line(SQLERRM);
 dbms_output.put_line(l_tbl_nm);
END;

I am thinking of including the code below to add after my second loop but I am having problems how to declare the 'cur_tbl' cursor and 'l_tbl_data' TABLE data type. I am unable to use rowtype since the tablename should be dynamic and will change in each iteration of my second loop that will list all table names from original table:

TYPE CurTblTyp  IS REF CURSOR;
cur_tbl    CurTblTyp; 
TYPE l_tbl_t IS TABLE OF tablename.%ROWTYPE;
l_tbl_data l_tbl_t ;

OPEN cur_tbl FOR  'SELECT * FROM  :s ' USING b.table_name;
FETCH cur_tbl BULK COLLECT INTO l_tbl_data LIMIT 5000;
EXIT WHEN cur_tbl%NOTFOUND;     
CLOSE cur_tbl;         

FORALL i IN 1 .. l_tbl_data .count
EXECUTE IMMEDIATE 'insert into '||l_tbl_nm||' values (:1)' USING 
l_tbl_data(i);

Hope you can help me and suggest how I can make this code much simpler. Thanks a lot.

Answer 1

There is a 3rd option in addition to the delete and truncate options: that's rename/drop. You rename the old back up tables, recreate the new backups (CTAS). If the create - insert is successful you drop the renamed tables, if the new backup fails you rename the prior old backups back to the initial backup names. You basically trade temporary usage of disk space for redo logs.

You don't need bulk processing, CTAS is still faster than bulk processing.

Answer 2

It looks like you want to remove all rows from the existing backup tables, then re-copy the entire contents from the original tables to the backup tables. If this is correct, using DELETE for the deletion and any loop operation for the insert will be slow.

First, to remove the data, use TRUNCATE. Since you are going to repopulate, use the REUSE STORAGE option. This is the most efficient way to delete all rows from the table.

TRUNCATE TABLE <backup table> REUSE STORAGE;

Second, to repopulate, just INSERT with a SELECT.

INSERT INTO <backup table> SELECT * FROM <orig table>;

You can use these in your loops as you loop by table. No need to cursor through the table rows as this will be faster.

If you have a new table, you can do something similar with a CTAS...

CREATE TABLE <backup table> AS SELECT * FROM <orig_table>;

Answer 3

Have you used FORCE DELETE? It was first introduced by the Oracle Master J.B.E it is used to delete the data and ignores the constraint that the table may have and is a lot more faster than other delete statements.

FORCE DELETE FROM <table_name>;