create a backup table with million records 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) &lt; '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) &lt; '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.

&gt;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.