9 Replies Latest reply: May 6, 2013 8:12 AM by user10274093 RSS

    export VIEWS

    user10274093
      Hi,
      on 11G R2,
      can we export only views ?
      How ?
      I saw TABLES parameter, but not VIEWS.

      Thanks.
        • 1. Re: export VIEWS
          Mihael
          expdp ... include=VIEW
          • 2. Re: export VIEWS
            mseberg
            Hello;

            An option might be to spool them to a file :
            select
            '-- '
            || to_char(sysdate, 'DD.MM.YYYY HH24:MI:SS')
            || chr(10)
            || chr(10) || chr(10)
            || 'create or replace view '
            || lower(all_views_a.view_name)
            || chr(10)
            || '('
            || chr(10) sql_header
            from all_views all_views_a
            where upper(all_views_a.owner) = upper('&db_user.')
            ;
            -- columns of view
            select
            decode(all_tab_columns_a.column_id
            ,1 , ' ' || lower(all_tab_columns_a.column_name)
            , ' ' || ',' || lower(all_tab_columns_a.column_name)
            ) view_column
            from all_tab_columns all_tab_columns_a
            where upper(all_tab_columns_a.owner) = upper('&db_user.')
            order by all_tab_columns_a.column_id
            ;
            -- end of view columns
            select
            ')' || chr(10) || 'as' sql_middle
            from dual
            ;
            -- generate view definition
            select
            all_views_a.text view_text
            from all_views all_views_a
            where upper(all_views_a.owner) = upper('&db_user.')
            ;
            -- end of view
            select
            '/' || chr(10) || chr(10) sql_footer
            from dual
            ;
            Best Regards

            mseberg
            • 3. Re: export VIEWS
              TSharma-Oracle
              You have to use Datapump utility and *'INCLUDE'* parameter.

              expdp test@test directory=export_test dumpfile=test.dmp logfile=test.log schemas=test include=view

              Export: Release 10.2.0.4.0 - 64bit Production on Friday, 01 March, 2013 8:57:05

              Copyright (c) 2003, 2007, Oracle. All rights reserved.
              Password:

              Connected to: Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
              Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": test/********@test directory=export_test dumpfile=test.dmp logfile=test.log schemas=test include=view

              Estimate in progress using BLOCKS method...
              Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
              Total estimation using BLOCKS method: 0 KB
              Processing object type SCHEMA_EXPORT/VIEW/VIEW
              Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
              Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
              ******************************************************************************
              Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
              X:\EXPORTS\TEST\TEST.DMP
              Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 08:58:17
              • 4. Re: export VIEWS
                user10274093
                Thanks Mihael,
                and if we want to export only some views and not all ?
                • 5. Re: export VIEWS
                  Mihael
                  include=view:"in ('NAME1', 'NAME2', ...)"
                  • 6. Re: export VIEWS
                    Dean Gagne
                    Are you trying to export view definition or the data that would be selected by the view?

                    Dean
                    • 7. Re: export VIEWS
                      user10274093
                      I was just asked to save the views. The I thouht of exporting them.

                      How to export data ?
                      How to export definitions ?
                      Thanks and regads.
                      • 8. Re: export VIEWS
                        Dean Gagne
                        You can export just the definition of the views.

                        expdp user/password include=view ...

                        To get the data, you would have to create a table as select * from the view, and then export the newly created table.

                        Hope that helps.

                        Dean
                        • 9. Re: export VIEWS
                          user10274093
                          Thanks.