Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Import + Sequences

Re: Import + Sequences

From: Greg <>
Date: 14 Mar 2006 16:20:37 -0800
Message-ID: <>

I've done a Solaris to a Linux 3.0 import/export conversion w/o a problem.

Now here are somethings you should do/think about:

  1. Don't use sys or system to do your export/import -- waste of time and sys should not be used for this anway.
  2. Don't do a full database export -- again, waste of time
  3. Setup your new database on Linux (w/all of the settings you want, datafiles, etc).
  4. Create your new users (w/the proper passwords, privs, etc).
  5. Make sure you are using the correct version of exp. If your old system is and your new system is, make sure you export with and import with (e.g.: don't use to export from a database).

Now, you can do it one of two ways:

  1. Export using the userid/password of whom you are wanting to move (do this individually for each user don't use a full export/import using system or sys). Get everything, constraints, triggers (indexes are optional), etc and do a full import into your new database and everything should go in just fine.
  2. Export an empty schema of the user, import into the new database, then disable triggers and contraints (constraints=R) for the user, then take another export, import that, and then re-enable your tiggers, contraints, etc and you should be good to go (although this is double the work of #1).

I've seen where if you use system/xxxxxx as your userid in exp and then use fromuser=xxxxx touser=xxxxx during import, jobs sometimes end up belonging to system and not the user -- so be careful.

I've also seen issues with direct=y. I'd suggest not using direct=y (although I know it slows things down).
Make sure you are using consistent=y

If you are having sequence issues, then is sounds like things are still processing in your old database and sequences are out of wack (or were not created correctly in the first place). I'd use TOAD to grant your all your create sequence statements for the given user and apply them into your new database... I think TOAD will create the statement with a drop statement first and then a create statement with the last sequence number.

-Greg Received on Tue Mar 14 2006 - 18:20:37 CST

Original text of this message