Quick transfer of large amounts of data in Oracle

If you want to transfer a large amount of data in Oracle (more than 80 Mb) to another user and another tablespace. You can use the following method to quickly transfer data.

I. How to create a new table

Create table target_tablename tablespace Target_tablespace_name nologging Pctfree 10 pctused 60 Storage (initial 5 M next 5 M minextents 1 Maxextents unlimited pctincrease 0) As select * from username. source_tablename where condition;

Note: The newly created table does not have the index and default value of the original table. Only non-null constraints can be inherited. Other constraints or indexes need to be re-created.

2. Direct insertion

INSERT/* + APPEND */INTO target_tablename SELECT * FROM username. source_tablename where condition; COMMIT;

Note:

Using the INSERT/* + APPEND */method generates an exclusive lock of 6 for target_tablename. If you run this command, DML operations on target_tablename will be queued after it, it is not suitable for the table operations used by the OLTP system.

Note: When transferring data, these two methods do not use the data buffer zone and rollback segment of transaction processing in SGA, nor do they write online transaction logs, writing data directly to physical files, just like the Database loading tool Solload, is fast. All Versions later than Oracle8i can be used.