Red Gate forums: Data Compare for Oracle

RE: Command Line interface

MelissaOlson — Fri, 26 Apr 2013 19:06:44 GMT

This is for continuous integration and all handled through an ant file so that coupled with the size of the files will prevent me from doing anything manually. Our schema with just 2 tables and about 20,000 rows of data is hitting close to the 50mb size so all of our other schemas will be well over this.

I don't know sql *loader yet but it looks like it expects a csv file...I'll look at this next.

I may be going at this the wrong way...after building my schema from the schema compare maybe I can use a flashpoint or some type of back up to get the data back in there.

Thanks,
Melissa

RE: Command Line interface

Michael Christofides — Fri, 26 Apr 2013 18:14:16 GMT

Ah yes, ok.

There are several suggested solutions to that issue posted here:
https://forums.oracle.com/forums/thread.jspa?threadID=2217135

If the script is <50mb I'd probably try SQL Developer, else would any of the other solutions suit?

Regards,
Michael

RE: Command Line interface

MelissaOlson — Fri, 26 Apr 2013 17:04:44 GMT

I tried that but the script is inserting data that is so large it runs into sqlplus' limitation of 2499 characters per line. I tried opening the file in word as suggested by the oracle site and saving it as a text file with cr/lf but then sqlplus couldn't read it due to the cr/lf special characters. I also tried just cr and just lf but neither of those worked either. I've also tried the sql ant task but the way the script is generated from the tool sql can not consume it...I found that out while working with the .sql generated by the schema compare. I followed several sites that directed me to switch out the ";" or "/" end of file markers with no success so the schema compare I had to go the sqlplus route but for the data I can't seem to get it working...it's been a bit of a long road

RE: Command Line interface

Michael Christofides — Fri, 26 Apr 2013 16:46:26 GMT

Hi Melissa,

Once you have the script saved (it's just a .sql script) you could use your editor from the command line to run it, for example:
http://serverfault.com/questions/87035/run-oracle-sql-script-and-exit-from-sqlplus-exe-via-command-prompt

Would that work for you?

Best regards,
Michael

RE: Command Line interface

MelissaOlson — Fri, 26 Apr 2013 16:24:21 GMT

Hi there,
I've looked through the help and have used the command line to generate a script to be used later but now I need to deploy that script. Is there a way to deploy the script from the command line without having the project do the compare again?
Thanks!

RE: Registering database every time , taking long time

Michael Christofides — Tue, 23 Apr 2013 09:41:10 GMT

Thank you for sending in the screenshot and error report Pavan.

For anybody else that comes across a similar error in future, this one turned out to be an "ORA-01555: snapshot too old".

RE: Registering database every time , taking long time

Michael Christofides — Mon, 22 Apr 2013 18:42:42 GMT

ginjupalli.pavan wrote:

I cannot find any option in this forum for me to attach the screenshot of the error but here is the text of error
X Creating Deployment Script
"Exception has been thrown by the target of the invocation"

Please do feel free to email the screen shot in to oracle@red-gate.com, and send the error report in to if there was one.

Best regards,
Michael

RE: Registering database every time , taking long time

ginjupalli.pavan — Mon, 22 Apr 2013 18:36:04 GMT

Michael Christofides wrote:

Hi again,
Regarding he initial db registration, we are planning to release a version of Data Compare soon that should speed this up in some cases, but please do let us know specifics if you've hit an issue.
Regarding the database hanging for a large deploy script, has this happened to you? If so please do provide us details and explain what you believe to be the cause, or how breaking the deployment scripts down could help.
Best regards,
Michael

RE: Registering database every time , taking long time

Michael Christofides — Mon, 15 Apr 2013 11:18:08 GMT

Thank you Cliff, much appreciated.

RE: Registering database every time , taking long time

ursusmaj — Mon, 15 Apr 2013 11:15:54 GMT

To be clearer, my reference to "before" was on another site, so the number of tables involved will have been different i.e. probably under 60,000 but not much below that level. Also, the hardware and OS were likely much different too.

The 66,300 tables is the number in each schema (i.e. both source and target DB's have that number of tables). This is a PeopleSoft Financials installation, so it is all in the one schema.

In reality, as I suspect is often the case with ERP installs, the number of tables I want to compare is a small subset of the total number. Perhaps 1% or even less.

The idea of up front filtering would work well for me in theory, but is depends very much on the flexibility of the actual implementation. I will provide my comments against the suggestion.

RE: Registering database every time , taking long time

Michael Christofides — Mon, 15 Apr 2013 10:29:37 GMT

Thank you Cliff, that's frustrating.

We've looked into this and the SQL we're using at those stages is the same in previous versions, did you say this had been noticeably faster on the same schema(s) before?

You mentioned 66,300 in the schema(s), are you comparing multiple schemas at once here? If so, please do try them one at a time, but I guess you may mean in each of the source and target schemas.

Of the 66,300 or so tables, how many are you wishing to compare immediately? We've been considering a feature to allow up front filtering of tables, described here:
http://redgate.uservoice.com/forums/174014-oracle-tools/suggestions/3724558

Please do add your votes/comments to that request if that would be helpful.

RE: Registering database every time , taking long time

ursusmaj — Wed, 10 Apr 2013 11:16:31 GMT

Thanks for the quick response. I do have version 2.1.0.325 and the option "Check tables for data" is unchecked in my project.

Some further info:

Registering database one goes to 5% immediately and then quite quickly to 30%, 10 minutes to go to 50%, another 10 to hit 70%. It sits there for more than 30 minutes on 70%. When it eventually completes, the same is repeated on the second database, although timings are much worse as the system is swapping heavily by this time. In fact the swapping occurs quite quickly after 30% on the first database.

In terms of tables in the schema there are approximately 66,300 in the schema(s).

RE: Registering database every time , taking long time

Michael Christofides — Wed, 10 Apr 2013 10:03:55 GMT

Hi Cliff,

Thanks for getting in touch. The latest release 2.1.0.325, which is available via help->check for updates, has an option called "Check tables for data". This should now be unchecked by default, which should increase the speed of registering large schemas.

Do you have this version? If so could you look to see if this option is unchecked?

We added the functionality not too long ago in order to help filter out empty tables of which several users had lots. We have now added it as an option for those users, but hopefully this takes the performance back to the level experienced previously for you.

Hope that helps, let us know how you get on.

Michael

RE: Registering database every time , taking long time

ursusmaj — Wed, 10 Apr 2013 09:01:26 GMT

I am also seeing very slow database registration when comparing large schemas (e.g. PeopleSoft).

The application memory usage in particular is the main issue I see - it grows to 3.8 Gb on a 4 Gb server and then causes swapping which makes the performance spectacularly bad. This is on the 64-bit version of Data Compare on 2008 R2.

I plan to add additional memory since the actual required memory looks to be around 5-6 Gb in my specific case.

Given that in most cases (I suspect), people are looking to compare a sub-set of the schema, I think a mechanism to speed the initial registration up is essential.

As an aside, I have used previous versions of Data Compare on other sites and did not see this behaviour previously - granted it was never "quick" for large schemas but it does seem to have got noticeably worse in the newer versions.

RE: Massive Deployment Script w/ 1.5 million DELETE Statements

Michael Christofides — Thu, 28 Mar 2013 16:20:33 GMT

This issue was resolved through our support system.

For anybody else interested, when the target only check-box is checked it means that we include these "differences" in the deployment script. Including differences where the data only appears in the target means we drop those rows.

Just to avoid confusion it is explained in detail here:
http://www.red-gate.com/supportcenter/content/Data_Compare_for_Oracle/help/2.1/dco_SettingUpTheSynchronization

There are also a couple of ways of breaking large deployment scripts into smaller ones. You can either select which tables to compare, or use a where clause on an individual table.

Michael Christofides
Product Manager - Oracle tools