Re: Importing in an other tablespace

"Frank Demuynck" <frank.demuynck_at_entelec.be> wrote in message news:<3c4feef3$0$33510$ba620e4c_at_news.skynet.be>...
> Hi,
>
> I want to move a user who is created in the tablespace users to an other
> tablespace on an other server.
>
> I created several users on the first server with the following script
>
> BREAK ON &getuser;
>
> CREATE USER &&getuser
> IDENTIFIED BY entelec
> DEFAULT TABLESPACE USERS
> TEMPORARY TABLESPACE TEMP
> PROFILE DEFAULT;
>
> GRANT CONNECT TO &&getuser;
>
> GRANT RESOURCE TO &&getuser;
>
> In those users we created our tables, sequences, ...
>
> We installed oracle on a new server. There I created seperated tablespaces
> (I want for each user a different tablespace). I placed also the tablespace
> "users" ofline.
>
> I did an export from a user of the first sever.
>
> I created the same user on the new server (DEFAULT TABLESPACE ts_entelec)
> with the folowing script
>
> BREAK ON &getuser;
>
> CREATE USER &&getuser
> IDENTIFIED BY entelec
> DEFAULT TABLESPACE ts_enelec
> TEMPORARY TABLESPACE TEMP
> PROFILE DEFAULT;
>
> GRANT CONNECT TO &&getuser;
>
> GRANT RESOURCE TO &&getuser;
>
> The import on the new server failed. I've got the following errors;
>
> IMP-00003: Oracle message code num encountered
> Cause: Import encountered the referenced Oracle error.
> Action: Look up the Oracle message in the ORA message chapters of this
> manual, and take appropriate action.
>
> ORA-01542 tablespace 'string' is offline, cannot allocate space in it
> Cause: An attempt was made to allocate space in an offline tablespace.
> Action: Bring the tablespace online or create the object in other
> tablespace.
>
> IMP-00017: following statement failed with Oracle message num: str
> Cause: Import failed to execute the statement from the export file because
> of an Oracle error.
> Action: Look up the accompanying Oracle message in the ORA message chapters
> of this manual and take appropriate action.
>
> So the import want to create the table in the tablespace users who is
> offline
> But I want that the import create the tables in the new tablespace
>
>
> Frank Demuynck
> frank.demuynck_at_entelelec.be
> frank_at_entelec-control.com

because of the resource role your user has unlimited tablespace privilege and hence can create tables in any tablespace. You need to revoke the unlimited tablespace privilege and issue alter user <username> quota 0 on <orig_tablespace> quota unlimited on ts_entelec.
Then your objects will end up in the new tablespace

Hth

Sybrand Bakker
Senior Oracle DBA Received on Thu Jan 24 2002 - 07:26:40 CST