Re: Problems on copying a database user from one tablespace to another on the same instance

Probably your new user has the unlimited tablespace privilege, so it attempts to put the new objects in the "old" tablespace. Check this by logging in as the "new" user and select * from user_sys_privs.. This is a common problem with granting resource role to a user. You have to revoke unlimited tablespace from the new user in order for the new default tablespace of the new user to take effect.

• Doug Certified DBA "If everything feels like it's under control, you're probably not going fast enough" - Mario Andretti

On Sun, 10 Dec 2000 19:14:21 -0500, "Richard Sutherland" <rsutherland_at_In-Touch.net> wrote:

>Tablespaces don't belong to users/schemas. Users can be granted space
>quotas in certain tablespaces, but other users can use those same
>tablespaces. In other words, there is no way to make a tablespace private
>to a user.

Sure there is.. make sure no one else has any quota on it...

> When tables/indexes are created, if a tablespace is not
>specifed, the table/index is put into the user's default tablespace. Other
>than that, there is no relationship between a user and a tablespace.
>

>However, an export records the tablerspace of the object(s) being exported,
>and the import will put them in the same tablespace, even if you import them
>as a different user.
>

Unless the "different" user has no quota on the tablespace, then it will use the new default..

>One way to accomplish a change in tablespaces is to edit the export file,
>and change the TABLESPACE clause.
>
>Another way is to import them into the original tablespace (assuming NEWUSER
>has quota therein), then issue ALTER TABLE MOVE <newtablespace> [for tables]
>or ALTER INDEX REBUILD <newtablspace> [for indexes]. If the objects are
>partitioned, you will need to issue these statements at the partition level.
>
>A third option is to issue CREATE TABLE newtable TABLESPACE <newtablespace>
>AS SELECT * FROM <user1's table>.
>

Too much trouble... just make sure the new user has no quota on the old tablespace.. unless they have the unlimited tablespace privilege, the quotas will take effect.

>Richard Sutherland
>rvsutherland_at_yahoo.com
>
>Peter Kroiss <pk_at_faw.uni-linz.ac.at> wrote in message
>news:3a33d96d_at_alijku02.edvz.uni-linz.ac.at...
>> I tried to export a user with
>> BUFFER=8192
>> FILE=G:\export.dmp
>> COMPRESS=Y
>> GRANTS=Y
>> INDEXES=Y
>> ROWS=Y
>> CONSTRAINTS=Y
>> LOG=G:\export.log
>> OWNER=(USER1)
>> RECORDLENGTH=8192
>>
>> and then reimport the data into a new user on a new tablespace with
>> BUFFER=8192
>> FILE=G:\export.dmp
>> SHOW=N
>> IGNORE=N
>> GRANTS=N
>> INDEXES=N
>> ROWS=Y
>> LOG=g:\import.log
>> DESTROY=N
>> FULL=N
>> FROMUSER=(USER1)
>> TOUSER=(NEWUSER)
>> RECORDLENGTH=8192
>> COMMIT=N
>>
>> The problem is that the import always creates the tables in the the
>> tablespace from user1 and not in the default-tablespace
>> of the new user (NEWUSER).
>>
>> Peter
>>
>>
>>
>
  Received on Sun Dec 10 2000 - 18:43:08 CST