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

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. 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.

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>.

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:14:21 CST