5 Replies Latest reply: Jan 7, 2013 9:53 PM by 983292 RSS

    Export view and import to a table

    983292
      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
          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
            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-Oracle
              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 .
                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
                  Thank you Dean Gagne and harry76

                  Your replies were very helpful.