5 Replies Latest reply: May 14, 2013 11:28 PM by 894936 RSS

    script for copy data

    894936
      Hi i got one requirement where i need to copy the base data..
      below is the requirement specification.
      Team can you suggest me on the below requirement.
      Create a Copy table script to copy the base data tables from XYZ Test to XYZ Production. Following tables should be included in this script:
      •     ART_SUP_T
      •     BATCH_DEF_T
      •     BU_ADDRESS_T
      •     BU_TAX_REG_T............. AND SO on upto 50 to 60 tables.
      
      The copy script should have a logging mechanism, to log the following information:
      •     Table transferred
      •     Number of records transferred per table (read in source, committed in destination)
      •     Error logging
      The created log file should be human readable and saved as ASCII file with timestamp and *.log file extension.
        • 1. Re: script for copy data
          Billy~Verreynne
          Oracle supports a number of methods to push data and tables from one database to another. These range from using external Oracle utilities such as Data Pump, to internal database features like replication, materialised views, and so on.

          Writing an o/s script to manually address this, is highly questionable.
          • 2. Re: script for copy data
            894936
            Hi Team,

            I dint understood exactly what does it mean...
            could you please elaborate on this...
            I am using the below script to copy the data from one environment to another
            SET ARRAY 200
            SET COPYCOMMIT 1000
            
            COPY FROM mmmm/mmmm@prod.com -
            TO mmmm/mmmm@test.com-
            APPEND  A -
            USING -
            SELECT * -
            FROM  A
            For multiple tables how do i need to implement... can u provide some script.

            for example i have Two tables in Prod ( A, B table) and two tables in Test (A, B table)
            Now i want to copy the data from prod to test.
            with the below mentioned details like
            •     Table transferred
            •     Number of records transferred per table (read in source, committed in destination)
            •     Error logging
            The created log file should be human readable and saved as ASCII file with timestamp and *.log file extension.
            • 3. Re: script for copy data
              971895
              Per my understanging we need to export data using TABLES option then import into production...
              • 4. Re: script for copy data
                Billy~Verreynne
                891933 wrote:

                I dint understood exactly what does it mean...
                could you please elaborate on this...
                On what specifically? Technical solutions to the problem?

                The issue is that the problem is not defined well enough to suggest a specific solution.

                For example, the script you have posted pushes the very same data, uncompressed, twice over the network. From source server to SQL*Plus client and from SQL*Plus client to target server. SQL*Plus does not readily support conditional processing. Or exception handling.
                for example i have Two tables in Prod ( A, B table) and two tables in Test (A, B table)
                Now i want to copy the data from prod to test.
                with the below mentioned details like
                •     Table transferred
                •     Number of records transferred per table (read in source, committed in destination)
                •     Error logging
                The created log file should be human readable and saved as ASCII file with timestamp and *.log file extension.
                Why ASCII log file? That is very primitive. Why not use a log table? Why not directly push the data from the source database to the destination database? How much new tables be handled (exist on source and not on destination)? How must changes to existing database tables be handled? How must referential data integrity be handled? What about database triggers - should triggers fire on the destination database when data from the source database is inserted? Should triggers be disabled during the sync and enabled afterwards?

                What about partition tables with new partitions that do not exist as yet on the destination database? What about other database objects such as synonyms, views and stored procedures? What about privileges and grants - how must this be reflected on the destination database?

                These, and numerous other, questions need to be asked and comprehensively answered. Before deciding on a specific technical solution.

                And no, a simplistic SQL*Plus copy data script is not something that I would consider as a solution - whatever the requirements. The SQL*Plus client is not suited in this regard to execute, manage and control the data copying process. However simplistic IMO.
                • 5. Re: script for copy data
                  894936
                  Thanks for your help i got the solution.