To change the table space of an Oracle database table

oracle| Data | database
In an Oracle database management system, you assign a table space (tablespace) when you create a library table (table), and if you do not specify Tablespace, use the tablespace of the system user.

We may encounter such problems in Oracle Applications. In terms of performance or other considerations, you need to change the table space for a table or for all the tables of a user. The usual practice is to delete the table first, then rebuild the table, and assign the table space to the table space we need to change when we create a new table. If the user has saved a large amount of data, this approach is not very convenient, because a large number of data needs to be backed up in advance. The following is a method of using the export/import function of the database to rearrange the table space of the database.

The following is a simple example, assuming that all tables under User Oa are to be converted from tablespace A to tablespace B, and the steps (in Oracle 9i for Linux environments) are as follows:
1.1. Export all tables under the DB_ZGXT (DOS console) under Export DB_ZGXT (under DOS Console) 1. Export all tables under DB_ZGXT (under DOS console)
EXP oa/password@pararmount_server file=d:\10_27_oa.dmp Log=d:\10_27_oa. LOG


2. Delete all tables under OA (in Sql/plus)

You can delete all the tables under DB_ZGXT by batch, and the statements that generate the batch are as follows:
--The set head removes the header information
SET head off
SPOOL C:\drop_tables.sql
Select ' Drop table ' | | table_name| | '; ' from User_tables;
Spool off;
@c:\drop_tables.sql;
SQL > @drop_tables. sql
3. Import parameters Indexfile imported all the tables under the OA user (under DOS console)
Export the statements of tables and indexes to a file, where the statement is annotated and not actually imported

IMP oa/password@paramount_server full=y file=d:\10_27_oa.dmp indexfile=d:\altertablespace_table_index. SQL Log=d:\altertablespace. LOG


Where the parameter indexfile is specified, the system writes the statement to create the table and index to a file, which is altertablespace_table_index. In SQL. The file contains all the CREATE INDEX statements and create TABLE (createtable) statements, but all the statements that create the table are commented on. Open and edit the file in any text editor, remove all annotation flags that create the table statements, replace all tablespace names with a B, and annotate all Creation index statements. When these work is done, the script file is run in Sql/plus, and the tables are created, and the table space is changed from a to B.
Import parameters Indexes=n and Ignore=y will db_zgxt user's table data into the library (under DOS console)
4. Import parameters Indexes=n and ignore=y the OA user's table data into the library (under DOS console)

IMP oa/password@paramount_server full=y indexes=n file=d:\10_27_oa.dmp ignore=y log=d:\altertablespace. LOG

Where parameter indexes=n means that data is not indexed when it is imported into the database. Ignore=y refers to an error that ignores a table already exists (table already exists) during the import of data. This allows the Oralce to import data and some constraints into the table created in step 3rd.


5. Create an index

Reopen the Altertablespace_table_index created in step 3rd in the text editor. SQL script file, this time, add a comment flag to all the statements that create the table (create table), and then remove all the CREATE INDEX statements from the annotation flag. Run the script file again in Sql/plus.

At this point, we have successfully completed the work of converting all the tables under the OA user from table space A to tablespace B. Of course you can import only a subset of the tables.

Note: This article refers to an article found on the Internet, I am in the updated platform (Oracle 9i) after the actual operation modified to this article. If you infringe on the copyright, please contact me.