Oracle

Some of our users have faced the situation when they need to change the connection details of the existing Database Diagram file.

Let’s assume the situation when you have several databases with identical schemas which are only located on different MySQL servers and you have created a database model that was saved in a Database Diagram file. Now, you want to avoid redesigning the database model from scratch and only reassign the existing database model to another MySQL server.

Here are some steps you can follow to achieve this:

1. Create a database diagram.

2. Arrange objects on the diagram so you can easily read them (create containers, notes, etc).

3. Save the diagram and close dbForge Studio for MySQL.

4. Open the Database Diagram file using a third-party text editor, for instance, notepad.

Open Database Diagram as XML

5. Find the <Connection></Connection> tag that may look as follows:

...
<Connection>
    <Name>MyConnectionToDB</Name>
    <ConnectionString>User Id=root;Host=db;Port=3310;Database=sakila;Ping Interval=120;Character Set=utf8;</ConnectionString>
</Connection>
...

and make the following changes:

...
<Connection>
    <Name>new_connection_name</Name>
    <ConnectionString>User Id=new_user_id;Host=new_host;Port=new_port;Database=new_db_name;Ping Interval=120;Character Set=utf8;</ConnectionString>
</Connection>
...

i.e. modify the <Name></Name> tag (change it to something different) and the <ConnectionString></ConnectionString> tag (connection details for another server).

6. Find all the <DbOid></DbOid> tags that may look as follows:

...
<DbOid>
    <Path>sakila.actor</Path>
    <TypeName>Table</TypeName>
</DbOid>
...

and change the database name to the new one:

...
<DbOid>
    <Path>new_db_name.actor</Path>
    <TypeName>Table</TypeName>
</DbOid>
...

Make sure you specify the new database name in the same letter case as it exists on the server.
7. Save the modified file with the new name.

Now, you have a new Database Diagram file adjusted to another server.

NOTE: The article applies to dbForge Studio for MySQL, dbForge Studio for SQL Server, and dbForge Studio for Oracle.

We’ve analysed user feedbacks received after the release of the completely redesigned dbForge Studio and defined prior points for further development of the product.

As a result we’ve devoted the new release of dbForge Studio for Oracle to improving the possibilities for users who work with PL/SQL code and data. And in addition we’ve improved the application performance and added support for Win-x64 platform.

dbForge Studio for Oracle v3.1

New Features

• 150 improvements in the code autocompletion system
  We’ve made nearly 150 changes and improvements in the dbForge Studio for Oracle component that helps users edit PL/SQL code. Among them:
  • Oracle SQL and PL/SQL syntax support is expanded
  • Work of quick info hints for schema objects is improved
  • Keywords case is changed automatically on typing (if the corresponding options are set)
  • Usability shortcomings and errors are fixed
• SQL document works with additional PL/SQL file types:
  • Support for editing .pls, .plb, .pks, .pkb, .pck Oracle PL/SQL files is added
  • Support for editing files with PL/SQL code created in some competitor products is added
• Data export to SQL statements
  • Possibility to export data to INSERT, UPDATE, DELETE, and MERGE statements is added
  • Data can be exported from a table or data grid using a wizard
  • Quick export from data grid without opening wizard
• Convenient work with result sets of several SELECT queries
  If there is more than one SELECT statement in a document, after execution query results will be displayed on separate tabs in the Data window.Such approach allows working with each data set independently.
• Editing data of object fields
  The new product version provides possibility to edit data in object fields in tables using a pop-up editor.
• Support for editing temporary tables
  There is a capability to create and edit temporary tables in Table Editor.
• Document Outline window for code navigation is improved
  • Grouping nodes by IF, FOR, etc. flow-control statements blocks is added
  • Displaying labels and navigation among them is added
• More convenient working with query execution plan
  • Quick query plan obtaining (without turning the Profiling mode on) is added
  • Displaying of the EXPLAIN PLAN results without creating an additional table in the user’s scheme is added
• Performance improvements
  • Time required on application startup and connection opening is reduced
  • Building of the Database Explorer tree, refreshing it, and receiving table list is quicker now
  • Getting metadata for code completion becomes faster
  • Work of Schema Export with large amount of objects and data is improved
  • Data Editor working speed is increased
  • Object Viewer and Property Browser windows working speed is increased
  • Navigation to schema object editors from code is improved
• Win-x64 Native Support
  Now the application does not require 32-bit Oracle client software installed when working with 64-bit Windows operating system – the application works with 64-bit Oracle client software.

Availability

Consumers can give the updated dbForge Studio for Oracle a test drive by downloading the free Express edition or the 30-day trial Professional edition at the product download page. dbForge Studio license price starts at $149.95.

To leave feedback, users can go to the dbForge Studio for Oracle feedback page. The Devart team is looking forward to receiving any comments and suggestions.

When developing databases, we frequently need to check what changes were made to its schema, if any mistakes were made, and, if there are any, we should roll back to the working version saved in the source control system. To do this, it’s enough to compare schemas of version 1.1 and 1.2, and generate an UPDATE SCRIPT using any Database Diff tool.

So what functionality should be available in such tool?

Firstly, the tool should be easy-to-use and user-friendly. A user should not be obliged to learn a pile of topics in the tool documentation before using it. It should be enough to perform the following set of steps:
1) Select Source and Target
2) Select a schema or several schemas (if the tool provides such possibility) for comparison
3) Tune comparison process by checking the needed options (an optional step)
4) View comparison results
5) Tune script generation options (an optional step)
6) Generate an update script and/or execute it directly to the target database
Secondly, the tool should support comparison of all object types you might have in your schemas – otherwise, how the tool is supposed to look for differences in them? And, at last, the tool should be fast. This point is especially important for searching data differences, when comparison of several schemas is performed, or the size of the project itself is large.

Now let’s look at some tools offering schema comparison functionality.
(more…)

This article is a step-by-step instruction for configuring Oracle Instant Client so that our tools for Oracle could work with it.

(more…)

Having successfully released our data comparison tool for Oracle we went further by starting the new one. dbForge Schema compare for Oracle is targeted to help database developers perform sustainable change management process. The tool is designed to:

• compare Oracle schemes
• analyse changes between databases
• generate schema comparison reports
• synchronize databases immediately or save sync scripts for later use

In order to allow our users make changes and improve product even before the first release we decided to publish early access preview of the dbForge Schema Compare for Oracle.

Visit product page http://www.devart.com/dbforge/oracle/schemacompare/ download product and leave us a feedback.

Recently we were working over the release of the version 2.0 of  dbForge Data Compare for Oracle. One of the main features added to the new version was synchronization of the LOB data. When we developed the first release of the product we postponed this feature because of challenges of synchronizing LOB data. These challenges caused by nature of data stored in BLOB or CLOB columns. It’s large and simply does not fit into SQL script. But we’ve found the solution.

In this article we will examine basic methods of loading LOB data into a table.

(more…)

During replicating data in one or another way, it happens that synchronized objects may differ. This situation may take place in the following cases:

• If changes made in one of copied databases were not captured
• The changes were captured but not forwarded
• The changed were forwarded but not applied due to some reasons

It should be admitted that full or partial synchronization may be required due to some other reasons, not related to replication. But the question is how to synchronize database objects?
(more…)

In the process of developing databases many developers and testers encounter a problem of synchronizing data between local and remote Oracle database. Changes made in a local database must be reflected in a remote database. It’s also necessary to check test results with the model database, to find non-synchronized tables that appeared after these tests, to have possibility to return the test database to its initial state when testing newly developed versions of databases intensively. Another problem may be to create CRUD operations quickly without making too much effort to find different objects and to write DML statements. Now let’s find out how one can synchronize data of Oracle databases.

Simple solutions:
(more…)

When you perform data maintenance operations, sometimes, it’s necessary to disable or enable all foreign keys in the user schema.

Here is the script that solves this task:
(more…)

Devart today unveiled a new data comparison tool, dbForge Data Compare for Oracle. It delivers automatic data comparison in Oracle databases, provides a convenient GUI to manage the differences, and generates SQL*Plus-compatible synchronization script to synchronize the data.

Read more: dbForge Data Compare for Oracle news…