Shulou(Shulou.com)05/31 Report--

This article mainly introduces "analyzing the migration of tablespaces, tables and indexes in Oracle". In daily operation, I believe many people have doubts in analyzing the migration of tablespaces, tables and indexes in Oracle. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "analyzing the migration of tablespaces, tables and indexes in Oracle". Next, please follow the editor to study!

During the operation of Oracle database, due to disk storage limitations or insufficient planning, physical migration of tablespace data files or logical storage migration of database tables and indexes are needed in the later stage. Record the relevant migration methods this time.

1. Migrate the tablespace (change the storage path):

1. Offline the modified tablespace:

Alter tablespace users offline

2, copy the tablespace to the location you want to store:

Cp / u01/app/oracle/oradata/ORCL/user01.dbf / data/app/oracle/oradata/ORCL/user01.dbf

3. Go to the database to modify the storage path of the tablespace:

Alter tablespace users rename datafile'/ u01 to to'/ data/app/oracle/oradata/ORCL/user01.dbf'

(the original path was changed to the current storage path)

4, online the tablespace

Alter tablespace users online; 2. Modify the storage path of the table:

Move the emp table from the users table space to the tmp table space:

Alter table emp move tablespace tmp

(the data and table structure of the emp table will be moved to the tmp table space together)

Move all tables in tablespace users to tmp tablespace:

Select 'table name' | | table_name | | 'move tablespace tmp;' from user_tables where tablespace_name='USERS'

The output is in the form of a script and can be executed in batches.

PS: after the table is moved, all the indexes involved are invalidated and the indexes need to be rebuilt.

Third, move the table space on the index of the table:

Move the index ind_emp to the index tablespace tmp_ind:

At this point in alter index ind_emp rebuild tablespace tmp_ind;, the study on "analyzing the migration of tablespaces, tables and indexes in Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.