1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to get data from another instance

Discussion in 'SQL PL/SQL' started by jagadekara, May 20, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Dear All,



    Today Unfortunately I deleted Data from one base table and apply commit. It's a Test instance.



    I have three instances in my company. So is there any chance to get back that data through other instance or through same instance.

    Please suggest me....
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    Try "export" from your 'backup' instance and "import" to your test instance.

    Regards,
    Rajen.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi Rajen,

    Thanks for reply, can you provide steps to export and import
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    The best option is to have a DBA do it: they usually use the Oracle standard tools (exp / imp) directly on the Database servers.

    However, you can do it via SQL Developer (as I do very often ... DBA scarcity ;)...) if you have the necessary connection details for both instances.

    Step 1: Export
    - Menu: Tools > Database Export
    - Choose your connection (backup instance), Format: insert, Browse: to select directory and file name of output, 'Next' (B)
    - Choose 'Tables' (only), 'Next' (B)
    - Name: 'name_of_your_table', 'Lookup' (B) - table name should be displayed in window list, Click/Select on it and click on ">" (to include in export list). 'Next' (B)
    - 'Next'
    - 'Finish'

    Step 2: Import
    - Open the "export.sql" file (default filename) in SQL Dev. window => list of "INSERT" statements + Table Creation statement (which you can ignore)
    - Run and Commit

    I recall you don't have same version of SQL Developer but hope these features are there (with may be slight differences).

    Regards,
    Rajen.
     
    jagadekara likes this.
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Can use EXPDB/IMPDB or DATAPUMP...

    http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Rajen,

    I got it. after export from source instance got .sql file which have table creation script and insert statements. So I ran those statements in destination instance.

    Now I have data in current instance.