Re: Import Question

Comments embedded.
On Sep 23, 12:05 pm, DA Morgan <damor..._at_psoug.org> wrote:
> ame..._at_iwc.net wrote:
> > Hi All,
>
> > How many database imports have you done? Well, once in a while you
> > forget some things and need some reminders.
>
> > First, if you do a full database export, and then import into another
> > database, do the users have to pre-exist?
>
> And the permissions must be granted.
>

I beg to differ on this one. A full export using a properly privileged account (DBA) will house the create statements for the database users. Additionally the grants will exist at the table level as that is one of the last tasks imp completes before starting the import of the next table/object. The users do not need to pre-exist when importing a properly executed full export.

> > Second, is Oracle smart enough to import things in an order that will
> > not produce errors?
>
> Depends on the version

Maybe in some antiquated, pre-1987 release of Oracle, but recent versions (recent being from 7 onwards) constraints are the last task of imp (and I think Oracle was smart enough in version 6 to do the same thing). One cannot violate a constraint which isn't enabled and they aren't until the end. From my experience full imports create and populate the objects in alphabetic order, which can give rise to the behaviour of imp switching owners in mid-stream.

>
> > So, if User#2 must have their objects imported
> > before User #1 because of some constraints or something, will Oracle
> > do that???
>

It won't matter whose objects import when as the constraints aren't enabled until all of the objects in the .dmp file are imported. It is not at all unusual for imp to import some of a user's objects then switch owners, import some objects, then switch back and continue importing. No constraint violations present themselves as the constraints are disabled until the import is nearly complete.

> > We are exporting an 8.1.7.4 database into a 10g database.......
>
> What is preventing you from running a test and finding out?
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

David Fitzjarrell Received on Tue Sep 25 2007 - 14:30:44 CDT