In Oracle, you can use the "alter table" statement to transfer the table space of a table. This statement can modify the data of the table. When used in conjunction with "move tablespace", the table space of the table can be moved. The syntax is "alter table Table name move tablespace new tablespace".
The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.
The table space syntax of transferring a table is as follows:
Move table table_name to the new new_tbsp table space
alter table table_name move tablespace new_tbsp;
Generate [all tables] under the specified tbsp_name table space Generate SQL statements to move new_tbsp table space
select 'alter table '|| table_name|| ' move tablespace new_tbsp;' from user_tables where tablespace_name = 'tbsp_name'
Extension: transfer different table spaces
Move the table space where [index] is located:
For example, move the index index_name to the new new_tbsp table space (fields of LOB data type need to be processed according to the following category 3)
alter index index_name rebuild tablespace new_tbsp;
--Generate [all indexes] under the specified user_name user to generate a SQL statement to move the new_tbsp table space
select 'alter index '||index_name||' rebuild tablespace new_tbsp;' from user_indexes where table_owner = 'user_name'
Move the [binary stream field] data storage table space, such as moving the binary stream field col_name in the table table_name To new_tbsp table space
alter table table_name move tablespace new_tbsp lob (col_name) store as (tablespace new_tbsp);
--Generate a SQL statement to move the CLOB type fields in the specified table table_name to new_tbsp table space
select 'alter table '|| table_name||' move tablespace new_tbsp lob ('|| column_name||' ) store as (tablespace new_tbsp);' from user_tab_columns where data_type='CLOB' and table_name='table_name'
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of How to transfer the table space of a table in Oracle. For more information, please follow other related articles on the PHP Chinese website!