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. 1. Create a new table using createtabletarget_tablenametablespacetarget_tablespace_namenologgingpctfree10pctused60storage (initial5Mnext5Mmin
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. 1. Create a new table using createtabletarget_tablenametablespacetarget_tablespace_namenologging pctfree10pctused60 storage (initial5Mnext5Mmin
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 quick transfer described below
Data method.
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 new 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,
It is fast to directly write data to a physical file just like the loading tool SQLLOAD.
All Versions later than ORACLE8I can be used.
