In the Oracle Database Management System, create a table space when you create a library (Table), if you do not specify a table space, use the system user to save the tablespace.
In Oracle practical applications, we may encounter such problems. Considering the performance or other aspects, you need to change a table or a table space for a user. The usual approach is to first delete the table, then re-establish the table, specify the table space to the table space we need to change when new tables. If the user has saved a lot of data, this method is not very convenient, because there is a lot of data that needs to be backed up in advance. The following describes a method of reorganizing database table space using a database's export / import feature.
The following is a simple example, suppose to convert all the tables under the user OA from the tablespace A to the table space B, the specific step (in the Oracle 9i for Linux environment) as follows: 1.1. Export all tables under DB_ZGXT (DOS Console) Next) Export all tables under db_zgxt (under the DOS Console) 1. Export all tables under DB_ZGXT (DOS Console) Exp Oa / Password @ PARARMOUNT_SERVER FILE = D: /10_27_OA.dmp log = d: /10_27_oa.log
2. Remove all tables under OA (in SQL / PLUS)
You can use a batch method to delete all tables under DB_ZGXT, generate the batch of statements as follows: - SET Head Off Removes SET Head OffSpool C: /Drop_tables || Table_name || ';' from user_tables; spool off; @c: /drop_tables.sql; sql> @DP_TABLES.SQL 3. Export the statement of all tables under OA users using Import Parameters Indexfile Exported to All Tables (DOS Console under the DOS Console) Go to the file, where the table statement is added, there is no practical import IMP OA / Password @ paramount_server full = y file = d: /10_27_oa.dmp indexfile = d: /altertablespace_table_index.sql log = d: /altertablespace.log
Among them, after specifying the parameter indexfile, the system writes the statement that creates a table and the index to a file, here is AltertAbleSpace_Table_index.sql. This file contains all Create INDEX statements and creation tables (CreateTable) statements, but all the statements of all creation tables are added. Open and edit the file in any text editor, remove all the annotation flags of all creating table statements, replace all tablespace names by A with B, and add a comment sign on all creation index statements. After these works, the script file is run in SQL / PLUS, and these tables are created, and their table space changes from A to B. The import parameters indexes = n and ignore = y will import the table data of the DB_ZGXT user (under the DOS Console) 4. Use the import parameters indexes = n and ignore = y to import the OA user's table data into the library (DOS console Next) IMP OA / Password @ paramount_server full = y indexes = n file = d: /10_27_oa.dmp ignore = y log = d: /altertables, where parameter indexes = N is not conjunctive when data is imported into the database . Ignore = y refers to an error that ignores the table already exists in the import data process. Such Oralce imports data and some constraints into the table created in step 3. 5. Create an index
Re-open in the text editor, the altertablespace_table_index.sql script file created in step 3, this time, add the statement of all creating tables (CREATE TABLE) to the comment flag, then remove all created index statements Comment sign. Run the script file again in SQL / PLUS.
At this point, we successfully completed the work of all tables under OA users from table space A to table space B. Of course, you can only import a part of the table. Note: This article refers to an article online, I am modified to this article after the updated platform (Oracle 9i). Please contact me if you invade who is copyright.
