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

    Unanswered: Oracle 8i Export

    Dear All,

    On Oracle 8.1.7 what to do so that an export taken for a particular User on a previous date say 01-Sept-2003 can be imported on 10-Sept-2003 without dropping / recreating the User and redoing associated activities like grants etc.

    Thank you,
    Parveen

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    truncate all the tables of that user and then imp rows=y ignore=y

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    Thanks for the reply.

    Does it mean, I will have to truncate ~ 400 tables one by one manualy and then fire imp script??

    Could you please provide me the exact statement to be fired for importing the previous .dmp file.

    Thanks,
    Parveen

  4. #4
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi,
    You don't have to manually truncate the tables exactly:

    spool truncate.sql
    select 'truncate table '||owner||'.'||table_name||';' from dba_tables
    where owner='OWNER';
    spool off

    then you can run the truncate.sql file to do the truncates.

    Rgs,
    Breen.

  5. #5
    Join Date
    Sep 2003
    Posts
    5
    Thanks..

    Performing Select statement results 'No rows selected'

    But being novice for doing this DBA, could any one pl. provide me exact steps for restoring earlier dmp of a user.

    Regards,
    Parveen

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

    Are you sure you replaced 'OWNER' in the script I posted with the actual name of the user in question.

    Rgs,
    Breen.

  7. #7
    Join Date
    Sep 2003
    Posts
    5
    Thanks Breen,

    The querry has worked now by using USER name in Capital case.

    When the truncate sql is running one error "Unique/Primary Keys in table referenced by enabled foreign Keys"

    How to come out of this??

    Moreover what about restoring earlier dmp's Procedures/Triggers/Views etc. on cuurent enviroment?

    Thanks,
    Parveen

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

    This is due to the table (Table A) being truncated being referenced by a foreign key on another table Table B).

    If you run the truncate.sql file again it should finish off fine this time as Table B should now be truncated allowing truncation of Table A.

    To import the user data again:

    create a import.par file as follows
    ------------------------
    file=/PATH/export.dmp # edit this to your correct path to .dmp file
    fromuser=user_name # edit correct user name
    touser=user_name # as above
    log=import.log
    buffer=1048576
    commit=y
    ignore=y
    rows=y
    ---------------------------
    then do the import as follows:

    imp system/password parfile=import.par

    Also, as long as you didn't drop the procedures / triggers / views - they will still be there.

    Rgs,
    Breen.

  9. #9
    Join Date
    Sep 2003
    Posts
    5
    Hi

    While running imp, follwing error is produced

    IMP-00058: ORACLE error 12560 encountered
    ORA-12560: TNSrotocol adapter error
    IMP-00000: Import terminated unsuccessfully


    Pl. guide me further.

    Regards,
    Parveen

  10. #10
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    ORA-12560: TNSrotocol adapter error

    Action: The listener was unable to start a process connecting the user to the database server.

    Cause: Perform these steps:

    Turn on tracing and repeat the operation.
    Evaluate the contents of the trace file to diagnose the problem
    -------------------------------

    Did it start the import?

    Rgs,
    Breen.

Posting Permissions

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