Oracle7 Server Utilities

Reorganizing Tablespaces

If a user's quotas allow it, the user's tables are imported into the same tablespace from which they were exported. However, if the tablespace no longer exists or the user does not have the necessary quota, the system uses the default tablespace for that user.

If the user is unable to access the default tablespace, then the tables cannot be imported. This scenario can be used to move user's tables from one tablespace to another.

For example, you need to move JOE's tables from tablespace A to tablespace B after a full database export. Follow these steps:

1. If JOE has the UNLIMITED TABLESPACE privilege, revoke it. Set JOE's quota on tablespace A to zero. Also revoke all roles that might have such privileges or quotas. Note: Role revokes do not cascade. Therefore, users who were granted other roles by JOE will be unaffected.

2. Export JOE's tables.

3. Drop JOE's tables from the tablespace.

4. Give JOE a quota on tablespace B and make it the default tablespace.

5. Create JOE's tables in tablespace B.

6. Import JOE's tables. (By default, Import puts JOE's tables in tablespace B.) Note: An index on the table is created in the same tablespace as the table itself, unless it already exists.