Practice of transferring Oracle to massive data

Assume that you want to transfer a large amount of data (more than 80 Mb) from Oracle to another user or table space. You can use the following method to quickly transfer data. The following describes the related content.

I. How to create a new table

 
 

  
  
create table target_tablename tablespace   
  
  
target_tablespace_name nologging   
  
  
pctfree 10 pctused 60   
  
  
storage(initial 5M next 5M 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: These two methods do not use the data buffer zone in the SGA and the rollback segment of transaction processing when Oracle transfers data, nor do it 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.

Article from http://database.csdn.net/page/67885150-ebd0-489f-8eb7-c6e3c47907a5