How to modify the size of an Oracle database table

In actual Oracle applications, many people may encounter such problems. For performance or other considerations, you need to change the tablespace of a table or all tables of a user. The common practice is to first Delete the table and then re-create the table. When creating a new table, specify the tablespace to be changed. If the user has saved a large amount of data, this method is not very convenient, because a large amount of data needs to be backed up in advance. The following describes how to reorganize the tablespace of a database by using the Export/Import function of the database.

The following is A simple example. Assume that you want to convert all the tables under the user db_zgxt from tablespace A to tablespace B. The specific steps (in the Oracle 8.0.5 for NT environment) are as follows:

1. Export all tables under db_zgxt (in the Dos console)
EXP80 OWNER = db_zgxt FILE = Exp0326.DMP LOG = Exp0326.LOG

The system prompts you to enter the user name and password. After the answer is complete, the system begins to export all the tables under db_zgxt to an exp02.16.dmp file.

2. Delete all tables under db_zgxt (in SQL/PLUS)

You can delete all tables under db_zgxt in batches. The statements for generating batch processing are as follows:
Select 'drop table' | user_tables | ';' from user_tables;

3. Use the import parameter INDEXFILE to import all tables under the db_zgxt user (in the Dos console)
IMP80 FULL = y file = Exp0326.DMP INDEXFILE = db_zgxt. SQL LOG = Imp0326_1.LOG

The system prompts you to enter the user name and password. After the answer is complete, the system starts to import the file Exp0326.dmp to the db_zgxt user.

After the INDEXFILE parameter is specified, the system writes the statements for creating tables and indexes to a file, which is db_zgxt. SQL. This file contains all the create index statements and CREATETABLE statements. However, the comment mark is added to all statements used to CREATE a table. Open and edit the file in any text editor, remove the comment mark of all table creation statements, and replace all table space names with A and B, at the same time, add a comment mark to all the index creation statements. After these tasks are completed, run the script file in SQL/PLUS, and these tables are created, and their tablespace is changed from A to B.

4. Import the table data of db_zgxt to the database using the import parameters INDEXES = N and IGNORE = Y (under the Dos console)
IMP80 FULL = y indexes = n file = Exp0326.DMP IGNORE = y log = Imp0326_2.LOG

The INDEXES = N parameter indicates that no index is added when data is imported into the database. IGNORE = Y indicates that an error that already exists (table already exists) is ignored during data import. In this way, Oralce imports data and some constraints to the table created in step 1.

5. Create an index

In the text editor, re-open the db_zgxt. SQL script file created in step 1. This time, add a comment mark to all statements used to CREATE a TABLE, then, remove the comment mark from all the create index statements. Run the script file again in SQL/PLUS.

So far, all the tables under the db_zgxt user have been successfully converted from tablespace A to tablespace B.