Re: Change of tablespace

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Thu, 25 Oct 2001 11:29:26 +0200
Message-ID: <ttfncb2ge09f5@corp.supernews.com>

"andy" <news_at_info-line.it> wrote in message news:3bd7d1a2.5432261_at_news.cis.dfn.de...
> On Oracle 7.3.4 i have created a user and load data on it. His
> tablespace is called TB1.
> I would like to load his data to another tablespace TB2.
> Making an export, drop the user, recreate user with new TB2 default
> tablespace, load data. Data will be load on TB1 tablespace.
> The only way is delete TB1 tablespcae before loading data?
>
> Thanks in advance.
>
> Andy.
>

No
Your user has the resource role and hence unlimited tablespace privilege, which means he can create objects in any tablespace. You need to
revoke unlimited tablespace
alter user <username> quota unlimited on TB2; alter user <username> quota 0 on system; etc
and have the default tablespace TB2

Hth,

Sybrand Bakker
Senior Oracle DBA Received on Thu Oct 25 2001 - 04:29:26 CDT