Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Posts
    5

    Unanswered: problem in import.

    Hi freinds,

    I am on ora8i with win 2k adv server.

    I have a problem during import data from full exp dmp into test db

    >exp system/manager file=c:\expdat.dmp log=c:\exp.log full=y
    rows=y direct=y

    >imp system/pwd file=c:\expdat.dmp log=c:\exp.log full=y rows=y
    ignore=y

    My intention is the Test db should contain latest data. The export dmp
    I take every night at 0 hours and trying to import it into test db every
    morining.

    I have checked the following before importing..

    1 . Truncated/dropped tables
    2. Dropped users.

    While importing data ,I get so many errors..

    1 . row rejected errors with unique key violant and terminating import
    with warnings. So,

    How can we import data into test db successfully ?


    an immediate reply is appreciated.

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    i suggest to drop all tables instead of truncating them.
    then see what happens.

    import will create the tables, load the data, then create the constraints.

    try that.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: problem in import.

    Originally posted by prasadvd
    Hi freinds,

    I am on ora8i with win 2k adv server.

    I have a problem during import data from full exp dmp into test db

    >exp system/manager file=c:\expdat.dmp log=c:\exp.log full=y
    rows=y direct=y

    >imp system/pwd file=c:\expdat.dmp log=c:\exp.log full=y rows=y
    ignore=y

    My intention is the Test db should contain latest data. The export dmp
    I take every night at 0 hours and trying to import it into test db every
    morining.

    I have checked the following before importing..

    1 . Truncated/dropped tables
    2. Dropped users.

    While importing data ,I get so many errors..

    1 . row rejected errors with unique key violant and terminating import
    with warnings. So,

    How can we import data into test db successfully ?


    an immediate reply is appreciated.
    -----
    you do a FULL export of all your oracle-data, then you import all these rows into another instance ...
    Next time you re-import the complete data into the target-instance, of course you run into a lot of duplicate keys ..

    what I suppose you should rather look at:
    backup and recovery concepts of Oracle - there you can do incremental backups and recovery to others databases / instances ...

    http://download-uk.oracle.com/docs/c...ro.htm#1005111

  4. #4
    Join Date
    Aug 2003
    Posts
    5
    The_Duck,

    I think it is not possible to drop all tables since it contains reference kys


    If possible, How can we drop all tables with references.?


    Any more suggestions ?

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi,

    Drop the schema that owns the tables.

    SQL> drop user USER cascade;

    Should do it.

    Rgs,
    Breen.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by Breen
    Hi,

    Drop the schema that owns the tables.

    SQL> drop user USER cascade;

    Should do it.

    Rgs,
    Breen.
    Do what he says. That is the easiest way.

    Does he need to re-create the user/schema before the import? I forget. I assume the Import would do it for you.

    or

    drop table_name cascade constraints;
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Aug 2003
    Posts
    5
    Thank u very much The_duck and Breen. I did it successfully.

  8. #8
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi the Duck,

    I think you have to create the user first but I'm not 100% sure.

    Rgs,
    Breen.

  9. #9
    Join Date
    Sep 2003
    Location
    Washington, DC
    Posts
    13

    it depends

    If you do a full import, you don't need to recreate the users before importing the schemas. If you do import in user mode (using FROMUSER and TOUSER parameters), then the user must be created prior to import. Also, object privileges must be re-granted when doing a user mode import.

Posting Permissions

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