Comments embedded.
HJR
--
Resources for OracleT: www.geocities.com/howardjr2000
=========================================
"Rick Denoire" <100.17706_at_germanynet.de> wrote in message
news:9ci2tto1g0lm9cbqrkt4t7mb5v192tmkc4_at_4ax.com...
> "Howard J. Rogers" <howardjr_at_www.com> wrote:
>
> >I think you've made rather hard for yourself by misunderstanding what
Import
> >does when the paths to the data files are different between different
> >databases. Import couldn't give a monkeys: Table A from source
tablespace
> >DATA01 will quite happily be created in target tablespace DATA01,
regardless
> >of where the datafiles for the target tablespace are physically located.
> >
> >So, no, Import will not "fail if the paths of the DB Files differ".
>
> Are you saying that the import will succeed even if the needed
> tablespaces are missing before doing the import? If tables go to the
> system tablespace, that wouldn't not be a "success" for us; the
> resulting DB would be plain wrong.
>
I didn't say it would end up in the system tablespace -unless you happen to
be running import as a User who has system as his *default* tablespace.
Which SYS probably has.
> I have read that if the paths differ, the tablespaces will have to
> exist prior to importing the dump. Let me know if you think this is
> true, because it would mean that there is no sure way to import a dump
> if some additional information about the exporting DB is missing.
>
Nope. It creates objects in the same tablespace where possible, or the
User's default tablespace if not.
> So, yes, I agree that Table A would be created at import as you
> described. You did not mention if you are assuming that the
> corresponding tablespace exists before doing the import on the target
> DB, specially in the case where datafile paths differ. Please correct
> me if I am wrong.
>
> >Import doesn't even fail if the tablespace names themselves are
different:
> >at that point, it creates the tables in the default tablespace of the
User
> >doing the import (which is why it's not such a good idea to run import as
> >SYS (which is what "/ as sysdba" does for you), since his default
tablespace
> >is invariably going to be SYSTEM).
>
> Well, how could I import a dump as a different user from SYS or SYSTEM
> if all other users have to be dropped before doing the import?
You've kerflummoxed me now with the mother of all non sequiteurs! Why on
earth are you dropping Users from the target at all?
>At
> import time, no other users exist. They have to be dropped in order to
> destroy their objects. Their objects must be destroyed, because the
> import process would then fail to create them.
That's what the ignore=y parameter is for. Truncate them if need be, but
there's no reason to drop them or get rid of their users.
>They must be created,
> because otherwise the DB is not considered to be a duplicate of the
> original one.
>
Weird. You are making life so hard for yourself.
> >If you're doing a full import then, yes, all Users should be created for
> >you.
>
>
> >I'm intrigued to know how the dump file was produced in the first place,
> >since a full database export would not ordinarily include SYS's schema at
> >all, so quite what all those error messages about 'importing SYS's
object'
> >are all about, Lord alone knows.
>
> The command was:
> imp sys/syspw file=file.dmp ignore=y log=file.log full=y
>
Well, that seems fine. You're certainly doing a full import, and you've got
ignore on (so no, there is definitely no need to drop previous objects: if
they already exist, import will simply move on to the 'insert into...'
statement). I wouldn't be doing this as SYS. You want a User which has
been granted the dba role, and whose default tablespace is not SYSTEM -er,
'system' springs to mind.
> >To investigate further, I'd like to see the complete command line that
was
> >used to export the original database, and the command line you are using
> >when running Import.
>
> About the exporting command, I can hardly find out. It was supposed to
> be a full export. I don't understand this SYS's objects stuff either
> and would be very glad if anyone could elaborate on that.
>
> Thanks
>
> Rick
Received on Sat Oct 20 2001 - 15:15:37 CDT
