How to modify tablespace in Oracle

Method: 1. Use the "alter table table name move tablespace table space name" statement to modify the table space; 2. Use the "alter index index name rebuild tablespace table space name" statement to modify the index space of the table.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How does Oracle modify the table space

1. Use imp/exp. Export the source database first, then create a new database, create the table space, and then import it. (It is said that this is possible, provided that the new library cannot have a table space with the same name as the source library. It needs to be verified!)

2. Use a script to make modifications. According to current understanding, the table space and table index space need to be modified in the case of full length. If a table with BOLB fields is involved, the modification method is different!

Modification script under normal circumstances:

1. Modify the table space

alter table TABLE_NAME move tablespace TABLESPACENAME

Query all tables under the current user

select 'alter table  '|| table_name ||'  move tablespace tablespacename;'  from user_all_tables;

2. Modify the table The index space

alter index INDEX_NAME rebuild tablespace TABLESPACENAME

Query all indexes under the current user

select 'alter index '|| index_name ||' rebuild tablespace tablespacename;' from user_indexes;

Recommended tutorial: "Oracle Tutorial"

The above is the detailed content of How to modify tablespace in Oracle. For more information, please follow other related articles on the PHP Chinese website!