6 Replies Latest reply: Apr 30, 2012 1:21 AM by Nonuday RSS

    How to backup all the views in a table

    Nonuday
      I want export all the view in a schema.

      I used the below command:

      expdp A12/A12 schemas=USER INCLUDE=VIEW directory=pump_dir dumpfile=expdp_VIEW.dmp

      The command executed successfully but,


      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
      Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
      ******************************************************************************

      It seems nothing have been backed up..

      I don't want to export specifically. Is there any way to upload all the views using export or any other way.

      Kindly help me..

      Thanks in advance

      nonuday
        • 1. Re: How to backup all the views in a table
          12cdb
          Hi,

          You can try ..

          D:\>sqlplus sys@TEST as sysdba
          Enter password:
          SQL> set long 10000
          SQL> select dbms_metadata.get_ddl('VIEW','ALL_USERS', 'SYS') FROM DUAL ;
          DBMS_METADATA.GET_DDL('VIEW','ALL_USERS','SYS')
          -------------------------------------------------------------------------------
          CREATE OR REPLACE FORCE VIEW "SYS"."ALL_USERS" ("USERNAME", "USER_ID", "CREATE
          D") AS
          select u.name, u.user#, u.ctime
          from sys.user$ u, sys.ts$ dts, sys.ts$ tts
          where u.datats# = dts.ts#
          and u.tempts# = tts.ts#
          and u.type# = 1

          HTH
          • 2. Re: How to backup all the views in a table
            Helios-GunesEROL
            Hi;

            please see below thread:
            export view

            Regard
            Helios
            • 3. Re: How to backup all the views in a table
              Dean Gagne-Oracle
              This job exported views. You would not have seen this line:

              Processing object type SCHEMA_EXPORT/VIEW

              If the views were not exported. What were you expecting to see.

              Dean
              • 4. Re: How to backup all the views in a table
                Nonuday
                Dean Gagne wrote:
                This job exported views. You would not have seen this line:

                Processing object type SCHEMA_EXPORT/VIEW

                If the views were not exported. What were you expecting to see.

                Dean
                Yes, it even showed the export was successful, but the blocks size was 0kb.
                If I import the dump which I took with the *"include=view"* export command, will the views imported?

                And, can you please tell me when I googled about this it was mentioned *"Metadata of the objects(view,procedure,etc) will be exported"* can you explain it?

                Thanks in advance,
                Karthik
                • 5. Re: How to backup all the views in a table
                  Dean Gagne-Oracle
                  The 0KB being reported was for data. If all you included in the export job were views, then there is no table data in this job, so the 0KB is just reporting that.

                  If you import that dumpfile, you will import just views, since that is all that is in the dumpfile.

                  I have no idea what you googled or what it means. I'll guess that what it means is that when you only include metadata in your include parameters, then you won't get any data. Any data being exported needs to be exported when you either include tables, or do content=data_only, or content=all.

                  Dean
                  • 6. Re: How to backup all the views in a table
                    Nonuday
                    Dean Gagne wrote:
                    The 0KB being reported was for data. If all you included in the export job were views, then there is no table data in this job, so the 0KB is just reporting that.

                    If you import that dumpfile, you will import just views, since that is all that is in the dumpfile.
                    Dean, thanks a lot for helping me out. I was quite confused about this, you made me understood it.


                    nonuday