PowerDIFF for ORACLE
- Technical FAQ

• What is the current version of PowerDIFF for ORACLE ?
  The current version is PowerDIFF for ORACLE 1.1, available as Standard Edition and Enterprise Edition.
  
  
• Is PowerDIFF a table structure compare wizard or a table data compare wizard ?
  It is both. And even a combined structural and data comparison is possible with PowerDIFF for ORACLE - using two separate compare runs. Besides tables you can compare indexes and constraints - but also source objects like views (written in SQL) and trigger, procedures and functions (written in PL/SQL). Last, but not least, users, roles, privileges (grants) and even a lot of ORACLE meta data and global parameters are comparable with PowerDIFF for ORACLE.
  
  
• What kind of algorithm is used in PowerDIFF to compare database objects ?
  A carefully designed, sophisticated and highly flexible method is implemented. This allows almost all types of database objects to be compared by definition and/or by data. For table and view comparisons, columns that are permutated in their order can be considered as being 'equal' or 'unequal' - depending on the customizing settings made before the comparison is started. Very sophisticated compare runs can thus be realized - considering only logical differences, but ignoring the details of the physical design of the tables. PowerDIFF can also compare data of tables (views) that have different column definitions, as e.g. added or missing columns in one of both data sets.
  
  
• Can I compare different database instances with PowerDIFF for ORACLE ?
  Yes. Two ORACLE database instances on any machines can be compared to each other from any other machine within the network. The necessary database links are automatically generated by the tool - provided the DBA has granted the privileges - otherwise a warning is displayed.
  
  
• How much time does a table data comparison take - for instance for a table of one million rows ?
  PowerDIFF is highly optimized to compare table data of big tables. This holds for the Standard and the Enterprise Edition. A typical value is 5 minutes for a comparison of two tables of one million rows each, differing in some thousand rows.
  
  
• What is the difference between the Standard and the Enterprise Edition of PowerDIFF ?
  The Standard Edition is restricted to 1024 database objects per type (tables, indexes, constraints etc.), 256 columns per table or view, 256 rows of trigger or procedure source code and 512 kB BLOB/CLOB data size.
  The limitations of the Enterprise Edition are 131072 database objects per type (tables, indexes, constraints etc.), 1000 columns per table or view and 16 MB BLOB/CLOB data size.
  
  
• Is there any functional limitation for the Standard Edition - compared to the Enterprise Edition of PowerDIFF ?
  There is in fact one little difference: Only the Enterprise Edition can order the diff script in such a way that the relational dependence of the objects is taken into account. This is done by PowerDIFF to avoid ORA errors when trying to apply the diff script. If the chosen data set (set of objects to be compared) does not contain all relationally dependent tables, a warning is given in the script and the missing tables are outlined. In the Standard Edition the user is responsible to apply the DELETE, INSERT and UPDATE statements for different tables in the correct relational order.
  
  
• What types of database servers does PowerDIFF support?
  PowerDIFF is available for Oracle (Oracle 8i, 9i, 10g, 11g) using the ODBC and ORACLE Net interface. This means that all types of Oracle database management systems are visualizable, scriptable, comparable and editable, even across different database versions. Our QA team has tested PowerDIFF successfully with the following ODBC drivers:
  • ODBC 8.1.7.0 - 8.1.7.8.1
  • ODBC 9.0.1.2 - 9.0.1.5
  • ODBC 9.2.0.5 - 9.2.0.6
  • ODBC 10.1.0.3 - 10.1.0.4
  • ODBC 10.2.0.0
  • ODBC 11.1.0.1
  Note: We don't recommend to use the 8.1.7.7 ODBC driver because of a serious problem (crash) when getting data from long raw or BLOB objects in this version. The 9.2. drivers before version 9.2.0.6 have problems with dealing longs and long raws - the data of these rows are therefore not delivered in PowerDIFF. And also other drivers than those from Oracle - like e.g. the Microsoft drivers for ORACLE - are not recommended.
  
  
• Can I run PowerDIFF against a Oracle 7.3. database ?
  Oracle 7.3.3 and 7.3.4 is supported by PowerDIFF with restricted functionalitiy. A lot of, but not all operations work correctly. We strongly recommend, however, to use a more recent ODBC driver (e.g. 9.2) when dealing with Oracle 7.3 server databases, among other reasons because of better performance. With some restrictions a comparison between a 7.3 and a 8i, 9i or 10g database server is, however, still possible.
  
  
• Can I use PowerDIFF to compare the data dictionaries of different Oracle versions ?
  Yes. It is, for example, possible to compare all SYS views of the Oracle 11 server with those of the Oracle 10 server to look into Oracle details. In this example you see all added views (often related to the new functionalities), all removed (obsolete) views and all changed views and can comfortably read them. By this you may gain a more detailed understanding of the new features of a new Oracle version.
  
  
• Can I use PowerDIFF for other database systems than Oracle as e.g. MS SQL Server or IBM DB2 ?
  No, sorry, currently not. Although the ODBC interface, of course, allows to address more database management systems (following the SQL-92 ODBC standard), a number of things implemented in PowerDIFF are quite different for database systems different from Oracle. The following products are planned for a launch in 2009 or 2010: PowerDIFF for MSSQL (Microsoft SQL Server), PowerDIFF for IBM DB2, PowerDIFF for Sybase SQL and PowerDIFF for PostgreSQL.
  
  
• What types of object can be visualized, compared and edited with PowerDIFF for Oracle ?
  The following types of database objects (type categories) are implemented:
  • Tables (including temp tables, but not index organized tables)
  • Indexes (b*tree + bitmap)
  • Primary Key constraints
  • Check constraints
  • Internal referential integrity constraints (referencing tables in the same database schema)
  • External referential integrity constraints (referencing tables in another database schema
    )
  • Views (not materialized views)
  • Triggers
  • Procs (functions, procedures, packages, package bodies)
  • Sequences
  • Users
  • Roles
  • Privileges.
  In contrast to other ODBC tools on the market, all objects (tables, views etc.) accessible by the Oracle privileges of the current user (which is determined by the used connection string) are availabe, including all SYS and SYSTEM objects. Currently not supported are materialized views (snapshots), types and type definitions and some other very special object types.
  
  
• Which data types are supported by PowerDIFF ?
  Almost all Oracle data types as e.g. BINARY, NUMBER, CHAR, VARCHAR2, RAW, LONG RAW, LONG, BLOB, CLOB. The corresponding ODBC (SQL-92) data types are mapped to the Oracle data types as far as possible. A comparison of tables (views) with LONG (RAW), BLOB or CLOB data types is restricted to the columns not having these data types. This is due to an Oracle internal restriction that does not allow these columns to be used in aggregable statements (i.e. GROUP BY, ORDER BY, MINUS SELECT, INTERSECT SELECT).
  
  
• Sometimes PowerDIFF displays < not available > as table definition. What does this mean ?
  Some very special Oracle data types (as e.g. owner defined data types and some special XML types) are not available by ODBC. In theses cases this message is displayed by PowerDIFF. In these very rare cases, you need another tool (e.g. Oracle Enterprise Manager) to display and compare manually the table contents. Very often in these cases even SQL*Plus does not display the correct data content.
  
  
• How do I visualize the objects of a database schema by PowerDIFF and can I visualize only those database objects of a certain schema that are visible by another user/database schema ?
  Yes. Use the single mode (one connection), connect as the first user, then click on the "select schema" button, chose here the second user and wait until the objects are shown on the left side in the main window. If both users are the same, you see the objects of this user - otherwise those of the second user that are allowed to be seen by the first user by the granted Oracle privileges.
  
  
• How do I script out a database object by PowerDIFF ?
  Use the single mode (one connection mode), open the database (database schema), select the object in the left-side object browser and open the "scripts" tab on the right side of the display window. With a number of options you can adjust the way how to script out the object in detail. Possible parameters are: Tablespace clauses, storage conditions, name of the schema in front of object name and others. The generated script is shown in an edit window in which it can be further refined and copied by drag-and-drop and stored to a file. By default, a SQL script following the ODBC-92 standard is generated.
  
  
• Can I run scripts directly from PowerDIFF ?
  Yes, any SQL clause or script of any complexity on any Oracle database can be executed via the PowerDIFF SQL processing window, especially those, of course, that are generated in a former compare run by PowerDIFF itself. In order to run the script:
  1. Connect and open the SQL processing window,
  2. Type or paste the SQL command you want to process and
  3. Press the return button (exactly as in SQL*Plus).
  Every standard SQL or PL/SQL script is supported and even a script of scripts. If you own the dba role, you can as well shut down and start up the database from this window - and all other operations allowed in SQL*Plus.
  
  
• I want to compare two differently named objects (as e.g. tables A and B) both belonging to one database schema. Is this task possible, too, by PowerDIFF ?
  No, sorry, this is not possible in the current version of PowerDIFF. Maybe this feature will be implemented in a forthcoming version. You can, however, copy one object (let's say B) to the database schema of object A (by script), rename this object to the name of A (again by script) and then in a second step compare both objects, now in different schemas, but having the same names.
  
  
• I want to compare two schemas both having a number of (irrelevant) temporary tables. Can I exclude temporary tables from the schema comparison ?
  The question is how to recognize the temp tables. If cou can define the temp tables by their name, you can, of course, simply exclude them manually before the compare run is started. For this purpose please use the "select objects" or "delete objects" function. A second possible variant maybe is to compare all objects and then filter the result set (in the result window below) to show only the differences of the objects belonging to both database schemas. For this purpose use the "1 < > 2" option in the result window. You then see only the objects that are present and different in both compare sets. Differently named temp tables are thus excluded.
  
  
• Can PowerDIFF also compare global database parameters as e.g. sizing parameters (block size, size of SGA, PGA, buffer pool, keep pool) or optimizer settings in a compare of two database instances ?
  Yes, it can - which holds for almost all global database parameters (e.g. the INIT.ORA parameters). This indeed is a very useful feature for Oracle DBAs. Imagine for instance a test and a production database instance with some of those parameters differing. By defining a compare run that only compares the global database parameters you get directly the desired results and often get the reason for performance differences in both instances - without the tedious procedure of reading and comparing a huge number of instance parameters step by step.
  
  
• How are triggers and stored procedures visualized in PowerDIFF ? Some of my triggers are short and written in-line and therefore hardly readable by many Oracle line tools (as e.g. SQL*Plus).
  The source code of Trigger and Procs (meaning procedures, functions and packages) can be shown either as it is or expanded into a readable form (with several lines and a number of formatting options including TABs). We tried a lot of codings and almost all of them are shown perfectly by using one of the possible options under "trigger settings" or "procs settings". SQL syntax highlighting is currently not implemented. At least in the script mode, however, the source code is pretty readable, can be stored and edited further on (to make changes or refinements) and, of course, can be applied again to the database using the SQL processing window.
  
  
• Can I make unintentional database changes by PowerDIFF ? I want to use it for a production database and want to be shure not to delete or change any objects etc. by using PowerDIFF ?
  This is a a very important question. In contrast to other tools, it is almost impossible to do such unintentional changes by PowerDIFF. The reason is simply that PowerDIFF normaly operates in the read-only mode. This holds for both the single mode (one connection mode) and the compare mode (two connection mode). Only in the edit mode (separate SQL edit processing window) database changes can be done and even there only changes by valid SQL scripts are possible. Thus without knowing the exact SQL syntax to make a valid change, the happening of an accidental database change is almost impossible. This may appear to some users as disadvantage. It, however, has the huge advantage that even without knowing all features of this tool in detail, you can be shure to always operate in a safe mode. This indeed, is a rather big advantage. A drop table operation e.g. is only possible by hacking "DROP TABLE ..." into the SQL processing window.
  
  
• I want to compare two databases consisting each of 100 tables of which about 20 tables contain "static" initialization data. These data must be 100 % identical to insure a properly working application. The rest of the tables contain productive data, i.e. data that are allowed to differ, but where the table structure must be exactly identical (including constraints and indexes) to insure the proper working of the application. Can I manage such a task with PowerDIFF ? Any idea about the time of such a compare run ?
  Yes, you can. This indeed is a very good example for the power of PowerDIFF. Currently, you can not do this in a one single job, but need two runs for this job.
  1. First open the two databases, select all tables (the default) and then start a first compare run with all the compare settings set to default. This means that all relevant differences except table and view data are compared. Such a run usually takes some minutes (typically 100 objects per minute).
  2. In a second run you chose only the 20 tables containing the "static" data for each database as the comparing set (right click "select options" in the table browser) and activate the compare option "table data". Further decide whether you want to compare also the data of tables that already differ in the table structure (normally this makes no sense). These settings as many others (like the compare direction of table data, beginning with the first or the last row) are achievable by pressing "Advanced settings" in the compare settings (start compare wizard) dialog. Now the second run reveals all differences in the "static" initialization area of the two databases - if present.
  If both compare runs do not reveal important differences (often allowed are e.g. differences in time stamp fields), both databases are "identical" in the sense of your application. If the static data are not to large, the complete comparison time should not exceed some minutes (this, of course, is vastly dependent on the performance of database and network - and the amount of table data, of course). This by the way is a nice example of a smart installation verification process by using PowerDIFF. This task may become one of your daily quality assurance management tasks.
  
  
• Can I compare SYSTEM and SYS objects by PowerDIFF ?
  Yes, all these objects are visible, comparable and editable. This is one key advantage of PowerDIFF - compared to other (ODBC) comparison tools on the market. Especially, the user-friendly and fast visualization of SYS dba views (like dba_tablespaces, v$loghist, v$sysstat etc.) is a great feature for Oracle DBAs.
  
  
• How does PowerDIFF visualize table data und table structure differences ?
  For table data differences, every considered row of a table is displayed in the upper part of the comparison output window as it is present in the first database and in the lower part as it is in the second database. Differences are marked by a horizontal red arrow which is either displayed per line or per line and additionally per column. Rows that are only present in one of both databases (schemas, sets) are marked by a vertical red arrow. By this way, a small number of differences can be made transparent very efficiently and the differences are found at first sight. For table structure differences a similar type of visualization is implemented - here the columns are COLUMN NAME, DATA TYPE, NOT NULL and DEFAULT (and, optionally, a number of further parameters like ORACLE metadata of the tables) and the lines are the columns of the table. See the demo screenshots for further details.
  
  
• In which way does PowerDIFF generate compare data scripts ? The problem is: If many data rows are different, the scripts get soon very large. Does PowerDIFF here always produce INSERT INTO statements or is it possible to generate SQL loader data files - which are loaded much more efficiently by ORACLE ?
  Well, normally each difference in table data is scripted out as an UPDATE TABLE, INSERT INTO TABLE or DELETE FROM TABLE statement. In the case where not all columns of a row are different only the different columns are updated by the UPDATE TABLE command which prevents statements that are bigger than necessary. In the case you mention here, i.e. the INSERT INTO TABLE of many data, it would certainly be more efficient to generate a SQL loader script file than a file with a number of INSERT INTO TABLE statements as in the moment. In the moment this is not implemented, but perhaps in a forthcoming version of PowerDIFF.
  
  
• PowerDIFF has another order to compare objects (e.g. in the batch run) if compared to other similar tools on the market. Other tools compare table by table including for each table the constraints and indexes. I noticed that PowerDIFF in contrast first compares all table structures (column definitions) including check constraints (table by table), then the indexes and then the other constraints (PK and referential) - before finally all other objects follow - like trigger, procedures, users, privileges. Why ?
  That's true. The first reason is that this type of order generally allows a faster scripting of the objects and the second is the possibility to apply the generated script (if scripting is activated) direcly to the database because the order in which it is generated is already correct. Referential keys e.g. are always creatable because in the run before all tables have already been created. This by the way is also the order in which Oracle's EXPORT and IMPORT utilities operate.
  
  
• Is there a way to redirect the individual object difference scripts generated by PowerDIFF to one "overall difference" file?
  Yes. PowerDIFF writes for each identified object that differs a script to drop the old and generate the new object. These scripts can be collected into an overall difference script and this script can, of course, stored into a file and e.g. applied at a later time to the database.
  
  
• I have a table in two databases which is identical, but the field order is different. Can I tell PowerDIFF to ignore this difference ?
  Yes. You can sort the fields (columns) of the table in any order before the compare is run. For this purpose use the tables "Set Object" function.
  
  
• Is it possible to compare only a subset of data, instead of an entire table, using PowerDIFF ? For example, I want to synchronize only rows that meet certain conditions.
  Yes. In order to do this you should use the "Set Object" function of PowerDIFF. In this dialog, you can define "virtual tables" in sense of quite complex SQL queries (including WHERE, GROUP BY and ORDER BY conditions) to restrict the data that should be compared. You can script out (and in some cases compare) these "virtual tables" in the same way you would to for regular tables. Note: For queries with a GROUP BY clause a successful comparison is currently not possible.
  
  
• I want to migrate data between a source table T1 which has the column order as c1,c2,c3,c4 into a destination table T2 which has the colum order as c1, c3, c2, c4. My intention is that the values of c2 of the source table should go into c2 - not c3 - of the destination table. Is there any way in PowerDIFF to change the mapping of the columns and apply this task ?
  Yes. In order to do this you can use the "Set Object" function and define the first object (the object to migrate) with order 1,2,3,4 and the second object (the object to migrate to) with order 1,3,2,4. The first object has the rows to migrate, the second will be empty in this migration example. As both objects now have the same "virtual table" name (because you don't add a WHERE, GROUP BY or ORDER BY clause and the order of the columns is by name exactly the same) you can run a comparison between these two objects and the finally generated "overall difference script" will do the desired task.
  
  
• Does PowerDIFF supports scheduling of a compare job ?
  No, currently not. Maybe in a forthcoming version. You can, of course, use a third party scheduler for this purpose.
  
  
• Does PowerDIFF allow to script out details of the storage clause for objects and indexes ?
  To some amount, yes. Visualized (and compared) under the "scripts" page are schema name, tablespace name and object name, but in the moment not any further details of the storage clause. This is definitely planned for a forthcoming version of PowerDiFF for ORACLE. You can, however, visualize and compare most storage clause informations (meta data as e.g. cache flag, statistical informations of blocks or leaves, physical storage parameters...) under the "storage/size" page. Thus, visualization and comparison of differences in these parameters is possible, but currently not the automatical generation of an update script. Partition modes and partitions of tables and indexes are visible by the sys views dba_tab_partitions and dba_index_partitions, but again in the moment not directly in the "scripts" tab of the object page. A general storage clause feature will come in a forthcoming version of PowerDIFF.
  
  
• I'm a DBA and want to shift the indexes of one application schema (about 600 indexes) to another tablespace and simultaneously check and rebuild them using another physical database design (storage clauses, tablespaces, extent sizes etc.). Can I benefit from PowerDIFF in order to do this job ?
  Yes, you can. We suggest to run PowerDIFF with two diff sets, one consisting of all relevant indexes and the other with no objects at all. You can generate such an empty compare set by either starting with the "display at start - no objects" option (when opening the database connections) or by dropping all objects from the compare set after a successful open operation. Please note: Dropping here (PowerDIFF is a read-only tool in the compare mode) does not mean that the database objects themselves are dropped in the database. They only are excluded from the compare set. Then generate the overall difference script by a compare run and store it to the disk. This script contains all CREATE INDEX .. DDL commands. After some manual corrections (e.g. in the extent sizes of each index), perhaps a tablespace shift and the replacement of CREATE INDEX .. by ALTER INDEX ... REBUILD (using find and replace) copy the script to the SQL edit processing window and let PowerDIFF apply it to the database. This operation is some sort of standard task for a DBA (database administrator) and a good example where PowerDIFF can assist the DBA in performing his daily work faster und more reliable - without the need of a detailled knowledge of dynamical SQL.
  
  
• Does PowerDIFF also compare and synchronize triggers, stored procedures and functions ?
  Yes. PowerDIFF compares stored procedures as well as functions and triggers of Oracle databases - and thus allows do-it-yourself synchronization or migration by applying the generated difference scripts. Packaged functions or procedures are thereby considered as similar objects as procedures and therefore treated analogously. For packages the package header definition and the package body definition is distinguished.
  
  
• What about wrapped Oracle packages (functions, procedures) ? Can these objects as well be compared by PowerDIFF ?
  Wrapped objects (functions, procedures, packages ) are displayed in PowerDIFF as they are. This means that a manual or automatical comparison is, of course, possible - thereby a comparison of the wrapped code is done. If the wrapped codes are identical then the original code is certainly also identical, otherwise "something" obviously has to be different. Of course, in this case, you cannot identify whether this is a relevant or an irrelevant difference (as e.g. added space characters or a comment) without unwrapping the objects. The object definition by the way is always shown in clear text. So at least this information is comparable up to 100 %.
  
  
• Which facilities provides PowerDIFF to exclude data from a comparison because these data are expected and tolerated to be different in a comparison (as e.g. time stamps) and which other possibilities exist to customize the objects to be compared individually ?
  This a very good question. First, you can tell PowerDIFF to ignore some fields of tables or views which are to be compared. In order to do this, select a field (column) in the "set object - select columns" dialog and exclude it from the comparison. Second, the order of the columns can be defined by either chosing it in the "set object - select columns" window. The order of the recordsets can be changed by adding a "ORDER BY" expression in this window. In addition, the number of recordsets of a table or view can be reduced by adding a WHERE clause here. Furthermore a global setting is possible to ignore differences of certain kinds like e.g. time stamp differences or differences that are only present because of schema name differences (with the option "ignore schema name differences"). A number of other possibilities exist in order to customize the compare process individually, e.g. by defining special SQL statements to be compared rather than the original tables or objects themselves. Furthermore the compare sets themselves can be adjusted arbitrarily by a preselection of the objects that define this "diff set" (often a logical unit of the database application) to be compared between the databases. Here - e.g. for long compare operations - also only a part of a unit can be compared in one run and other parts in the next run. PowerDIFF can also be adjusted to compare table and view data beginning from the first or the last row and in amounts of a certain junk size. For structural comparisons, not only logical comparisons, but also physical comparisons of object structures within two databases are possible - a often very important feature in comparing small test systems with big production systems or fast and slow databases. By such an investigation a DBA can find out important differences in the physical database design which often are resonsible for performance bottlenecks in one of the systems.
  
  
• I have a database with a lot of similar logical structures (schemas) representing the same "logical database" a number of times within the same database instance. Similar here means "identical up to the name of the schema" which, of course, is always different. Unfortunately, there are also FK constraints and parts of the source code (in triggers and procedures) which reference from one schema to another and therefore are not converted correctly to another schema by using the Oracle EXP and IMP utility. The problem is that IMP into another schema does not correct the FKs and source code dependings to another schema, a lot of manual corrections are therefore required afterwards. The questions now are: a) can I use PowerDIFF to do the conversion and b) can I use PowerDIFF to verify that my logical databases are similar in the sense of above ?
  Yes, both tasks are possible. a) Generate the "overall difference" script for the first schema, let it run against a second empty schema and manually replace with an editor all findings of the first schema with the name of the second schema. Then apply the script to the database. b) In order to compare the two "databases" with respect to their content up to schema names, use the option "ignore schema name differences" available as setting before the comparison is run. In this case, every identified difference that is only a difference in the schema name is ignored. Using this method, you get the type of comparison you want.
  
  
• I want to verify the migration of a SAP system from one ORACLE database to another one. The SAP system consists of about 67000 tables, some hundred views and 120000 constraints. Is this task possible and which version of PowerDIFF is needed ?
  Yes it is. You need the professional version of PowerDIFF for ORACLE. This version allows to script out and compare up to 131072 objects of each object class (tables, indexes, constraints, views ...). The standard version is insufficient because it is restricted to 1024 objects. As far as we know no other limitation is relevant because SAP does not store data in LOB data types (LONG, LONG RAW, BLOB, CLOB) and the number of columns per table is small. A full data comparison of two SAP systems will consume a lot of time (many hours). So either restrict the data to be compared to a reasonable amount (e.g. only the first 1000 rows of each table) or schedule the job to the night where traffic on database und network is reduced. We also recommend to install PowerDIFF as close as possible to the database in order to minimize network traffic.
  

PowerDIFF - the ultimate database diff tool.