Export 3

[scohan]

On a SunOS with Oracle 8.1.5, I've tried a full export from one db to another. Everything seems to import ok except for three tables which try to import into the tablespace of the source db instead of the tablespace of the target db. All the other tables are imported successfully. I've set the user's default and temp tablespace to the tablespaces of the target tablespace. What should I do to get these tables to import? Thanks.

 

[loren256]

Dear Schoan,
Can't give a quick answer. Not enough info.
First, make sure you have specified your parameters correctly for your full export. Then check over your params for your import. Then check those three tables to see what they have in common. I can't understand what you mean by the tables "try" to import into the source DB. Are they doing it or not? If they do indeed import BACK into the source DB, how do you know this is what is happenning? If they "try" but can't, what error are you receiving?

One more thing, remember that, in full database mode, all database objects, EXCEPT those owned by the SYS schema are imported.

good luck, hope this helps, get back to us with an update,
loren256

 

[karluk]

I don't think SCohan is saying that the tables are attempting to import to the source database. The problem is more specific - the import is not flexible enough to put the tables into a tablespace that actually exists on the target db. Instead it attempts to create the tables in the same tablespace. Unfortunately that tablespace doesn't happen to exist on the target db - hence the error.

I see three options

1. Create the tablespace on the target db, so the import will work.
2. Create an id on the target db with a different name than the id on the source db. I'm fairly sure that the import will then use the default tablespace of the new id.
3. Create the tables in the desired tablespace prior to doing the import. Then run the import using 'ignore=y'. If needed, you can get the source code for the table creates by running an import with the 'indexfile' option.

 

[scohan]

karluk is correct. That's exactly what I'm trying to do. Thanks karluk for helping again. I'll attempt your options after putting out another fire.

 

[karluk]

I see that Metalink has documentation on this issue. Please read 1012307.6 - moving tables between tablespaces using export/import.

It looks as if my suggestions, except for #3, weren't exactly right. However I won't try to make corrections unless SCohan wants to pursue it. Otherwise I'll probably just make more mistakes.

 

[loren256]

Karluk,
I am not as good a guesser as you! You have certainly identified a common problem. Sounds reasonable. I don't think any one expects mistake free tips here.
loren256

 

[scohan]

I created the tables prior to importind and the ignore=y paramenter worked. Thanks, karluk.

 

[cyberdyne]

Hi karluk

Can u please guide me ? I am in a similar situation.
I want to export data from table in recursive way. and the impoert it into a another server with same login.
I dont know how to take recursive type of back up.
can u pls guide me ?

Thanks alot