Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Posts
    10

    Unanswered: import and overlay data

    I have exported data from 8.1.5 on NT 4 and have loaded (using import) the data and structures into 8.1.7 on Win2000. Now the second time that I export/import, I would like to overlay the existing data in my target database. I ended up, truncating all the tables and then ran the import the second time, but would like the import to overlay data or truncate the tables when I choose.

    I didn't see an option of the import or the sql*loader (can I use sql*loader from the export dump file?)

    I'm from the DB2 world and understand how to do it there, can someone tell me what parameters I need to set to overlay the existing data or if it's possible? If not possible, what is your work-around or alternate tool?

    Doug

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi doug,
    In your parameter file have 'ignore=y' - that ignores duplicate data during the import.
    Rgs,
    Breen.

  3. #3
    Join Date
    Sep 2002
    Posts
    10

    import and overlay

    I guess I should have stated that I am using the ignore=y parameter. But, when the import tries to insert the row of data, if that row already exists, an error occurs and the ignore=Y lets the import continue.

    First, I'm loading million of rows in this export and wouldn't think the most efficient way would be to let each INSERT error off just to load some new records.

    Ideally, I could just lay my new table data on top of the out-dated table data, without getting the millions of 'oops, you're already here' errors.

    Side question.
    If I were wanting to refresh a test environment from production, what utilities should I use? export/import or backup/restore or what else does Oracle have to do this function?

    thanks,doug

  4. #4
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Sorry Doug,
    I thought 'ignore=y' would work differently.
    I don't use SQLloader but my understanding is that it should be able to do this.

    Export / Import should be fine to refresh a test environment.

    Rgs,
    Breen.

  5. #5
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: import and overlay

    Just a quick clarification, ignore=y has nothing to do with data. It has to do with objects. It tells Oracle to ignore that fact that the object you are going to import already exists. If a table already exists, then import will start loading the data into it.

    The catch is, if there are constraints on the table and the table already has data in it, then any rows that are part of the import and that violate the constraints will cause an error.

    To answer your other question, if I am doing a COMPLETE refresh of test, then I drop the user from the test database, clean up any problems caused by this, and then import the user from a production export. As with everything, there is a catch. Database links don't seem to be included in the export/import process. So, anything that is dependent on a database link will cause an error during the import process. The database links must be created before the import.

    HTH,
    Patrick
    Originally posted by kestner
    I guess I should have stated that I am using the ignore=y parameter. But, when the import tries to insert the row of data, if that row already exists, an error occurs and the ignore=Y lets the import continue.

    First, I'm loading million of rows in this export and wouldn't think the most efficient way would be to let each INSERT error off just to load some new records.

    Ideally, I could just lay my new table data on top of the out-dated table data, without getting the millions of 'oops, you're already here' errors.

    Side question.
    If I were wanting to refresh a test environment from production, what utilities should I use? export/import or backup/restore or what else does Oracle have to do this function?

    thanks,doug

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •