1 Reply Latest reply: Nov 5, 2013 11:38 AM by garywicke RSS

    Database Copy doubles rows in some tables ...



      Oracle EE on Solaris

      SQL*Developer 3.2.09 and 4.0EA on Windows XP Pro SP3

      I was wanting to use the Database Copy feature of SQL*Developer to copy all the objects, constraints, etc. from one database to another.

      The database consists of several tables, views, indexes and constraints.

      When I connect to both databases using the Tools -> Database Copy and specify the two (2) connections, I use the defaults by just clicking on 'Next' all the way to the Summary Page where I verify that the copy process will copy ALL tables, All indexes, ALL <whatever>.

      When the copy process is complete (no errors) several of the resulting tables have twice the number of rows as the source tables.  It only affects some of the tables but not all.  I haven't been able to understand the relationship of the ones that have the double rows, yet.

      My suspicion is that SQL*Developer is trying to actually import data into the views and hence the underlying tables and causing the data in those tables to be doubled.

      I plan on trying to confirm that later today.

      Has anyone seen this before?  If so, what is the cause?

      I'll update the thread when I learn more about the above view situation.

      Thanks very much for your feedback!!


        • 1. Re: Database Copy doubles rows in some tables ...

          I was able to replicate the issue.

          In looking at the log files it appears that during the 'Database Copy' operation that data is 'INSERTed' into the views which in my case doubled the number of rows in the target table.

          I created another view that included an aggregate function (SUM()) and when I did the Database Copy it generated an error saying it couldn't insert data into the view because 'virtual column not allowed here'.

          I tried several times to remove the views from the 'Data Copy' section of the wizard but it always came back in the Summary as wanting to copy the data for 'all views' even though I explicitly selected the tables but no views.

          It appears to default to 'all x' but I can't find how to de-select the views from the 'Data Copy' operation.

          I want the tool to copy the DDL so I get the view definitions but not the data.

          I find it odd that the wizard would want to copy data from a view in the first place since the data already exists in the base tables.

          Has anyone figured out how to do this?

          Thanks very much!!