How to transfer the table space of a table in Oracle

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.

How oracle transfers the table space of a table

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!