Re: [EXP/IMP] Tablespace duplication logically OK but physically wrong.

"Manfred Pruntsch" <manfred.pruntsch_at_ifcos.com> wrote in message news:<bcmn04$jrtll$1_at_ID-51546.news.dfncis.de>...
> Hi,
>
> I duplicated a tablespace for testing purposes from user1/tblspace1 -->
> user2/tblspace2 in the way:
> 1. Export
> exp user/password PARFILE=export.par log=export.log
> TABLESPACES=tblspace1
> GRANTS=Y
> CONSISTENT=Y
> ROWS=Y
> INDEXES=Y
> FILE=tblspace1.dmp
>
> 2. Import
> imp user/password PARFILE=import.par log=import.log
> FILE=tblspace1.dmp
> GRANTS=Y
> IGNORE=N
> ROWS=Y
> INDEXES=Y
> SHOW=N
> FROMUSER=user_of_tblspace1
> TOUSER=user_of_tblspace2
> TABLES=(*)
>
> It seems to be that all works fine. The import log file looks good. I find
> the tables under tblspace2 as expected. Logically all looks fine but
> physically it doesn't:
> The imported data were put into the file according to tblspace1!
> Before import: 60% usage
> After import: 92% usage.
> The file belonging to tblspace2 didn't changed anyway (0,5% usage before and
> after).
> I want to have the imported data in the appropriate file of tblspace2. At
> the moment I have a tablespace with several hundreds of megabyte and the
> other one with only 1-2 MB. I would suggest that isn't the best
> constellation for a good performance.
> How do I get the import data from tblspace1 physically to tblspace2?
>
> Any hints would be appreciated.
> regards
> Manfred

do the following prior to import
revoke unlimited tablespace from user2;
alter user user2 quota 0 on tblspace1 quota unlimited on tablespace2; alter user user2 default tablespace tablespace2

Actually this is a FAQ and it is documented in the Oracle Server Utilities Manual (which no one seem to read)

Sybrand Bakker
Senior Oracle DBA Received on Tue Jun 17 2003 - 15:15:40 CEST