0

I want to move old records from TABLEA to TABLEA_AUDIT. TABLEA have around 1.5 million records and it have two nested tables.

No of Records : 1557951 Size : 1024 MB

I tried it by,

1.using Create table as select * from

CREATE TABLE
TABLEA_AUDIT
COLUMN TABLEA_STAGE NOT SUBSTITUTABLE AT ALL LEVELS
NESTED TABLE TABLEA_STAGES STORE AS AUDIT_TABLEA_STAGES,
NESTED TABLE TABLEA_MODELS STORE AS AUDIT_TABLEA_MODELS
AS
SELECT *
FROM
TABLEA
WHERE COULMN1 IS NOT NULL
AND TRUNC(UPDATED_DT) < '01-MAR-18';

Result : 2hours of waiting and didnt get any result

2.Tried CREATE TABLE AS with no logging by follwoing below blog. http://www.dba-oracle.com/t_fast_copy_data_oracle_table.htm

Result : 2hours of waiting and didnt get any result

3.Developed a new procedure to copy the records and created a new DBMS_JOB

EXECUTE IMMEDIATE 'CREATE TABLE TABLEA_AUDIT AS SELECT * FROM TABLEA';

Result : Job is running for more than 2hours and no result.

4.Created the table and developed a procedure to insert bulk records

set serveroutput on size unlimited
set timing on
declare 
type audit_type is table of TABLEA%rowtype;
v_type audit_type;
CURSOR temp_cur is
select  *
FROM TABLEA a
WHERE COLUMN1 IS NOT NULL
AND TRUNC(UPDATED_DT) < '01-MAR-18';
BEGIN

    OPEN  temp_cur;
    / collect data in the collection /
    FETCH temp_cur BULK COLLECT INTO v_type;
    / close the pointer /
    CLOSE temp_cur;

    FORALL i in v_type.first .. v_type.last
    INSERT INTO TABLEA_AUDIT VALUES v_type(i);

    FORALL i in v_type.first .. v_type.last
    DELETE  FROM TABLEA WHERE PRIMARY_KEY_COL = v_type(i).PRIMARY_KEY_COL;               

    COMMIT;
END;
/

Ps: We have tried the same with out any indexes in new AUDIT (TABLEA) table.
Result : Block running for more than a hour.

Without Nested table I can copy 7,716,204 records in less than a minute. Size : 960.28125 MB

Thanks.

3
  • Is this a one off thing or do you intend to do this regularly? If it's just a one off: Is there no chance to just let it run? Over night or the weekend maybe? In mean it seems like you've spent at least seven hours on it already. Maybe it had finished in that time? Maybe an index on TRUNC(UPDATED_DT), COULMN1 could support the SELECT but on the other hand, given your last statement, the retrieval doesn't seem to be the actual problem. Maybe consider looping over a cursor and commit after every some rows. Could be the growing transaction log that hits the breaks here.
    – sticky bit
    Apr 30, 2018 at 11:45
  • Its a one time task and I have already checked the index.
    – User12345
    Apr 30, 2018 at 12:26
  • What about the suggestion regarding the transaction resources growing?
    – sticky bit
    Apr 30, 2018 at 13:07

1 Answer 1

1

In case you won't need the data immediately, you can try extracting table structure, then rename it to backup, and create a new table with the same structure and name.

ALTER TABLE table_name RENAME TO new_table_name;

This will show the table structure. you can use the result to create new table.

>describe table_name

many suggestions are out there to copy by the select statement onstackoverflow. one is:

create table table_name as select * from table_name_oldd where 1=0;

It creates structure but won't copy any value because the condition is false.

2
  • Sorry to say but this will not help me in this case as I'm moving the records by condition.
    – User12345
    Apr 30, 2018 at 5:23
  • @User12345, thanks for the feedback, I hope you find a useful answer later. just don't forget making full backups while trying. Apr 30, 2018 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.