Using Oracle 11g, I want to copy a table from the primarily used schema, production database into my own personal schema, development database. I've been doing this daily like so:
insert into my_schema.table_name@development_DB
(select * from primary_schema.table_name@production_DB);
... and for a table of 6.7 million rows and 355 columns, this takes about 30 minutes.
I've been reading up on bulk collects and forall statements. I wrote code for a generic table-transfer procedure below, but it doesn't perform much better. For 6.7 million rows and 355 columns, it performs in 27:30. Nasty, right? So is there a way to tweak my bulk collect to perform at these "dramatically improved speeds" I keep hearing about??? I'm open to alternative methods as well.
create or replace procedure transfer_data(pis_table_name VARCHAR2) AS
ls_src_table VARCHAR2(100) := '<PRIMARY SCHEMA>.' || pis_table_name || '@<PRODUCTION DATABASE>';
ls_tgt_table VARCHAR2(100);
LS_USERNAME VARCHAR2(100);
ls_anon_block VARCHAR2(10000);
ls_does_usr_have_tbl VARCHAR2(1);
ls_does_<PRIMARY>_have_tbl VARCHAR2(1);
le_tbl_not_exists EXCEPTION;
E_INVALID_USER EXCEPTION;
CURSOR c_does_user_have_tbl IS
SELECT DISTINCT 'Y'
FROM USER_TABLES
WHERE upper(TABLE_NAME) = '' || upper(pis_table_name) || '';
CURSOR c_does_<PRIMARY>_have_tbl IS
SELECT DISTINCT 'Y'
FROM ALL_TABLES
WHERE upper(TABLE_NAME) = '' || upper(pis_table_name) || ''
AND UPPER(OWNER) = '<PRIMARY>';
BEGIN
SELECT USER INTO LS_USERNAME FROM DUAL;
IF LS_USERNAME != '<PRIMARY>' THEN
ls_tgt_table := LS_USERNAME || '.' || pis_table_name;
ELSE
RAISE E_INVALID_USER;
END IF;
OPEN c_does_user_have_tbl;
FETCH c_does_user_have_tbl INTO ls_does_usr_have_tbl;
CLOSE c_does_user_have_tbl;
ls_does_usr_have_tbl := COALESCE(ls_does_usr_have_tbl,'N');
OPEN c_does_<PRIMARY>_have_tbl;
FETCH c_does_<PRIMARY>_have_tbl INTO ls_does_<PRIMARY>_have_tbl;
CLOSE c_does_<PRIMARY>_have_tbl;
ls_does_<PRIMARY>_have_tbl := COALESCE(ls_does_<PRIMARY>_have_tbl ,'N');
ls_does_usr_have_tbl := COALESCE(ls_does_usr_have_tbl,'N');
IF ls_does_usr_have_tbl = 'Y' THEN
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || ls_tgt_table;
COMMIT;
ELSIF ls_does_<PRIMARY>_have_tbl = 'Y' AND ls_does_usr_have_tbl = 'N' THEN
EXECUTE IMMEDIATE 'CREATE TABLE ' || ls_tgt_table || ' AS
SELECT *
FROM ' || ls_src_table || '
WHERE 1 = 0';
ELSE
RAISE le_tbl_not_exists;
END IF;
ls_anon_block := 'DECLARE
TYPE ltype_src_tbl IS TABLE OF ' || ls_src_table || '%ROWTYPE;
ltable_src ltype_src_tbl := ltype_src_tbl();
CURSOR c_src_table IS
select *
from ' || ls_src_table ||';
BEGIN
OPEN c_src_table;
LOOP
FETCH c_src_table BULK COLLECT INTO ltable_src LIMIT 1000;
EXIT WHEN c_src_table%NOTFOUND;
FORALL I IN ltable_src.first .. ltable_src.last
INSERT INTO ' || ls_tgt_table || ' VALUES ltable_src(I);
END LOOP;
CLOSE c_src_table;
COMMIT;
END;';
EXECUTE IMMEDIATE (ls_anon_block);
DBMS_OUTPUT.PUT_LINE(sql%rowcount || ' rows inserted.');
COMMIT;
EXCEPTION
WHEN le_tbl_not_exists THEN
DBMS_OUTPUT.PUT_LINE('Table ' || ls_src_table || ' does not exist!');
WHEN E_INVALID_USER THEN
DBMS_OUTPUT.PUT_LINE('This procedure is not designed for transferring data to main schema!');
END;