Re: Import into a different tablespace

In article <CzzMxD.Lxr_at_nl.oracle.com>, cgohring_at_uk.oracle.com wrote:

>rory_at_cronus (Rory Reynoldson) writes:
>: In article <3arff7$61o_at_larch.cc.swarthmore.edu>
>: mknod temp.dmp p
>: perl -nle '$_ =~ s/TABLESPACE "<OLD>"/TABLESPACE "<NEW>"/g; print
 $_;' \
>: < file.dmp > temp.dmp &
>: imp file=temp.dmp userid=blah commit=blah full=blah <etc>=blah
>: rm temp.dmp
>:
>: (replace <OLD> and <NEW> with various tablespace names)
>:
>: I love perl... (CPU time with perl5.0 on HP G50 to process a 77Meg
>: import file was 60 seconds... not significant)
>:
>: Rory Reynoldson
>
>And realize while you are doing it that it is completelly unsupported.
>
>When importing, imp will alwasy try to import the objects back into
>the same tablespace from which they were exported, unless the user has
>no quoata on that tablespace.
>I assume you are using v6.
>Before importing,do(as a DBA):
>SQL> revoke resource from <username>; (revokes blanket resource)
>SQL> revoke resource on <original tablespace> from <username>; (revokes
> resource on that tablespace)
>SQL> grant resource on <new tablespace> to <username>; (grants resource
> on the tablespace where you want the objects to go)
>
>Now perform the import.

I have tried playing with user quotas as described above and imp still tries to create the tables in the old tablespace.

Finally, I tried the perl script described above and successfully imported these files.

I really can't understand why imp did not behave as it is designed. The only thing I can figure is that the fact that the original tablespace was totally screwed up (ie, datafiles were deleted BEFORE the tablespace was dropped and WHILE the tablespace was online) may have something to do with my problems. Oracle isn't able to handle this tablespace properly anymore - an attempt to drop the tablespace freezes the session and does not succeed.

Thanks to everyone for all the tips. I'll post a summary shortly. Glenn Stauffer
DBA
Swarthmore College Received on Tue Nov 29 1994 - 16:43:20 CET