Vryjdfkk

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.

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.