Reorganizing Tablespaces

If a user's quota allows 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, then the system uses the default tablespace for that user as long as the table is unpartitioned, contains no LOB or VARRAY columns, is not a type table, and is not an index-only table with an overflow segment. This scenario can be used to move a 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, then revoke it. Set joe's quota on tablespace A to zero. Also revoke all roles that might have such privileges or quotas.

    When you revoke a role, it does not have a cascade effect. Therefore, users who were granted other roles by joe will be unaffected.

  2. Export joe's tables.
  3. Drop joe's tables from tablespace A.
  4. Give joe a quota on tablespace B and make it the default tablespace for joe.
  5. Import joe's tables. (By default, Import puts joe's tables into tablespace B.)