This discussion is archived
5 Replies Latest reply: Jan 7, 2013 7:53 PM by 983292 RSS

Export view and import to a table

983292 Newbie
Currently Being Moderated
Hi all,

I want to export a view and import to a table in another scheme. Export data should be the data which is available at the creation of view. Import schema has a table which match this view and data should be imported to this table.
I tried using expdp and impdp. But impdp import as normal view ( data is different when selecting from this imported view ).
Is this possible ? If it is possible how to do this ?

Thanks,
  • 1. Re: Export view and import to a table
    User286067 Journeyer
    Currently Being Moderated
    user10450258 wrote:
    Hi all,

    I want to export a view and import to a table in another scheme. Export data should be the data which is available at the creation of view. Import schema has a table which match this view and data should be imported to this table.
    I tried using expdp and impdp. But impdp import as normal view ( data is different when selecting from this imported view ).
    Is this possible ? If it is possible how to do this ?

    Thanks,
    create table schema2.table_name as select * from schema1.view_name ? with appropriate privileges, it is as simple as that.

    Raj
  • 2. Re: Export view and import to a table
    983292 Newbie
    Currently Being Moderated
    Thanks for the reply. But i want a dump file and import will be done by another party. This can be done using a table. I want to know whether the same could be done using a "view",
  • 3. Re: Export view and import to a table
    Dean Gagne Expert
    Currently Being Moderated
    This is not something that is currently available in Data Pump. Data Pump has been asked to support this, but as of 11.2.0.3, this is not available. If this is something you need done by your customer, then the best solution would be to create another table with the data that you need and then export that table, then drop the table.

    Dean
  • 4. Re: Export view and import to a table
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    You can sort of do it with external tables which wouldn't involve actually creating an additional normal table inside the database but still isn't exactly what you want - have a look at the external table example in the link below:

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

    I thought you may be able to load the created dumpfile directly into another database using impdp but that doesn;t seem to be allowed - i guess some of the metadata etc is not generated.

    Cheers,
    Harry
  • 5. Re: Export view and import to a table
    983292 Newbie
    Currently Being Moderated
    Thank you Dean Gagne and harry76

    Your replies were very helpful.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points