Re: How to move a user's tables from one tablespace to another tablespace.

From: Brijesh <brijesh_at_enteract.com>
Date: 1997/07/08
Message-ID: <33C13BE9.C20CED7D@enteract.com>#1/1

Hi Poon

    If it is one table you can recreate it in user tablespace as temporary table and the drop original table and then rename the temp table to original table.

But if you want to move all the user's tables the follow the following procedure

1. EXPORT the user's tables
2. set users default tablespace to the tablespace you want
3. set tablespace Quota for this user on System tablespace to 0
4. drop all the tables
5. import from exported file

 This may help

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.

Poon Chak Yau wrote:

> Hello,
> I want to move a user's tables from SYSTEM table space to
> another table space. Can anyone tell me how to do it without
> affecting operations?
>
> Regards,
Received on Tue Jul 08 1997 - 00:00:00 CDT