Tips for transferring massive Oracle Data"

If you want to transfer massive Oracle data (more than 80 Mb) to another user or related tablespace in actual operations. We recommend that you use the following methods to quickly transfer data. That is, the method for creating a new table and the method for directly inserting it .

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.

The above content is an introduction to the specific operation solution for quickly transferring massive Oracle data. I hope you will get some benefits.

Article by: http://database.csdn.net/c_oracle/tag/2