This discussion is archived
5 Replies Latest reply: May 14, 2013 9:28 PM by 894936 RSS

script for copy data

894936 Newbie
Currently Being Moderated
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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Per my understanging we need to export data using TABLES option then import into production...
  • 4. Re: script for copy data
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks for your help i got the solution.

Legend

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