Reorganizing Tablespaces - Oracle 8i

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 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, revoke it. Set JOE’s quota on tablespace A to zero. Also revoke all roles that might have such privileges or quotas.
  2. Note:Role revokes do not cascade. Therefore, users who were granted other roles by JOE will be unaffected.

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

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 8i Topics