Re: Importing a .dmp into a different tablespace (RH7.1, Oracle 81610)

From: Fred Pierce <fpierce_at_avialantic.com>
Date: Sat, 02 Nov 2002 10:31:50 -0500
Message-ID: <3DC3EFE6.4040005_at_avialantic.com>

The answer is in all these answers - just a bit muddled I think.

First, it might be worthwhile to check the contents of the import file -   imp file=intranet.dmp show=Y Use either fromuser/touser of full=Y. Since you're just looking it doesn't matter. Since your import output doesn't show anything being imported (should show "imported x rows..." etc. assuming there is data) I'm wondering if there's anything in the export. If it's not what you expect then the problem may be with the export.

Setting the default tablespace the way you did *should* have worked. Assuming the original tablespace doesn't exist or no quota import should put it where it is allowed to.

The other way to do it is to create the table structure in the new tablespace with the new owner. This is where the ignore=Y comes in. If imp sees the table exists, it will import just the data, provided ignore is set. Otherwise it will skip it with an "object already exists" message.

It also might help to simplify the check by using dba_tables - select owner, table_name, tablespace_name where whatever... Less typing anyway.

HTH fdp

---

Fred Pierce (DNRC)- fpierce_at_avialantic.com Mid-Atlantic Aviation on the Web - http://www.avialantic.com ** Mid Atlantic Air Museum WWII Weekend Airshow June 6-8 2003 - www.maam.org/maamwwii.html **
*** World Airshow News - www.worldairshownews.com **

---

michela rossi wrote:

> Firstly - sorry for cross-posting.
>
> Thanks, but I tried that - didn't work. Below is some more information
> on what's happening:
>
> - Re: the importing and not working: this is what I get.
>
> imp devintranet/blah file=intranet.dmp
>
> Import: Release 8.1.6.1.0 - Production on Thu Oct 31 16:51:28 2002
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
>
>
> Connected to: Oracle8i Release 8.1.6.1.0 - Production
>
> JServer Release 8.1.6.0.0 - Production
>
> Export file created by EXPORT:V08.01.06 via conventional path
>
> Warning: the objects were exported by FRED, not by you
>
> import done in US7ASCII character set and US7ASCII NCHAR character set
>
> . importing FRED's objects into DEVINTRANET
>
> Import terminated successfully without warnings.
>
> - Following this, if I do:
>
> sqlplus system/blah
>
> select segment_type, segment_name from dba_segments WHERE segment_type
> IN
> ('TABLE','INDEX') AND tablespace_name = UPPER('service_name') // This
> should [Quoted]
> list along list of tables .....
>
> I do not get any tables listed (obviously altering service_name to be
> devintranet) where I know that I should do. So it looks like data and
> tables
> were not imported. Also, I know that if the import works successfully,
> I
> get
> much longer listing of reports of the import than just "import done in
> US7ASCII character set and US7ASCII NCHAR character set".
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> "magpies" <-_-> wrote in message news:<aprhib$9k11_at_imsp212.netvigator.com>...
>

[Quoted] >>I think you can try:
>>1. Create (or Alter) a user with default tablespace, say 'data_ts1'
>>2. run 'imp' using 'fromuser, touser' option, e.g.
[Quoted] >>     imp devintranet/blah file=intranet.dmp fromuser=<owner> touser=<target
>>user>
>>
[Quoted] >>I think it may work.
>>
>>
>>"michela rossi" <michela_rossi66_at_hotmail.com> wrote in message
>>news:1ed550fd.0210310703.7b01d4c4_at_posting.google.com...
>>
>>>>>I have a .dmp file, say, fred.dmp - produced from a tablespace
>>>>>
>>>called fred, with user fred. I wish to create a new tablespace and
>>>user and import fred.dmp into that tablespace. I've read round various
[Quoted] >>>newsgroups and it does seem that there is a bit of an issue making
>>>Oracle import into a different tablespace from its' source.
>>>
>>>
>>>>>I am attempting to do this on RedHat 7.1 and Oracle 8.1.6.1.0.
>>>>>
>> 
>>
>>>>>So, I have fred.dmp in /tmp/fred.dmp
>>>>>
>> 
>>
>>>>>I create a new table space and user using the following commands:
>>>>>
>>>svrmgrl
>>>connect internal;
>>>create tablespace devintranet datafile
>>>'/ora8/m02/oradata/ora8/devintranet01.dbf' size 50m autoextend on
>>>default storage (pctincrease 1);
>>>create user devintranet identified by blah default tablespace
>>>devintranet temporary tablespace temp quota unlimited on devintranet;
[Quoted] >>>grant connect, resource, ctxapp, javasyspriv, query rewrite to
>>>devintranet;
>>>revoke unlimited tablespace from devintranet;
>>>alter user devintranet quota unlimited on devintranet;
>>>exit;
>>>
>>>
>>>>>Next, I try to import fred.dmp into devintranet:
>>>>>
>>>imp devintranet/blah file=intranet.dmp
>>>
>>>But - this doesn't work - does anyone know what I should do to force
>>>Oracle to do this import?
>>>
>>>Also: supposing I've reached the pt where I've finished experimenting
[Quoted] >>>with devintranet - what commands should I use to completely remove
>>>both the user and the tablespace, so that I can safely remove any
>>>resulting .dbf file that has been created?
>>>
>>>Any help would be most most welcome,
>>>Thanks, Michela.
>>>

Received on Sat Nov 02 2002 - 16:31:50 CET