Change the tablespace of the Oracle database table
(Shi Wei, July 20, 2001) 17:56 In the Oracle Database Management System, create a table space when you create a library table (Table), if you do not specify a table space, use the system user to save the table space . 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 that assumes that all tables under DB_ZGXT will be converted from the tablespace A to the table space B, and the specific steps (in the Oracle 8.0.5 for NT environment) are as follows: 1. Export all tables under DB_ZGXT (DOS Under the console)
Exp80 Owner = db_zgxt file = exp0326.dmp log = exp0326.log system prompts to enter the username and password, the answer is complete, the system starts to export all the tables under DB_ZGXT to the file called EXP0316.DMP. 2. Delete all tables under DB_ZGXT (in SQL / PLUS) can remove all tables under DB_ZGXT by batch, and generate batch statements as follows:
SELECT 'DROP TABLE' || User_Tables || ';' from user_tables; 3. Import all the tables under DB_ZGXT users using import parameters (under the DOS console)
Imp80 full = y file = exp0326.dmp indexfile = db_zgxt.sql log = IMP0326_1.log system prompts to enter username and password, the answer is completed, the system starts to import file EXP0326.DMP into DB_ZGXT users. Among them, after specifying the parameter indexfile, the system writes a statement that creates a table and an index, here is DB_ZGXT.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. 4. Adopt import parameters indexes = N and IGNORE = Y Table Data Import Database (DOS Console)
