The following are our answers to frequently asked questions about TOYS.
Oracle® is a registered trade mark of Oracle Corporation. Microsoft® and Windows® are registered trade marks of Microsoft Corporation.
What is TOYS?
TOYS is an acronym for Tool for Organizing Your Schemas.
TOYS is a tool for comparing database schemas and providing the ability to syncronize these schemas with a designated reference model. That is, TOYS compares and synchronizes physical database structures.
TOYS does support some general development tasks such as generation of DDL to create schemas and the creation of schema reports, however it is not intended to be a general SQL database programmer's tool. TOYS does not support PL/SQL development and debugging. It's schema browser is intended for browsing snapshots and is not intended to be an interactive browser.
There are a miriad of tools that already do these fuctions. TOYS simply focuses on the job of comparing and synchronizing databases.
Does TOYS work on UNIX or LINUX?
TOYS operates on Microsoft Windows XP, 2000, NT4, 98 and Me. It does not operate on any UNIX platform or on LINUX.
Does TOYS operate as a command-line program?
A command line utility implementing a sub-set of TOYS functions is due for release in 2008. This will operate on the Microsoft Windows platform only (i.e. XP, 2000, NT4, 98 and Me).
The following functions are supported:
- Load a snapshot from a file.
- Save a snapshot to a file.
- Capture a snapshot.
- Compare snapshots.
Is TOYS multi-threaded in its operation?
Yes to a degree.
TOYS uses a very simple multi-threaded approach. It runs longer running tasks such as loading and saving files, retrieval of schema meta-data, etc. in a background thread. The foreground thread is reserved for the GUI. This means that the GUI is still responsive during long running operations. At this stage, all tasks are performed as a "modal" operation and thus you cannot do anything else within TOYS until the operation completes [or you cancel the operation].
So although it is multi-threaded there is room for improvement!
What is meant by schema capture?
When we talk about capturing schema, we mean to "capture" all meta-data describing the schema objects. This meta-data is read from the database server's catalogs. This captured meta-data is considered to be a snapshot of the schema structures at the point in time when the capture was performed.
These snapshots may be saved to files as XML and then later re-loaded. Snapshots are simply meta-data.
What is meant by the term core schemas and how does one decide which schemas to include or exclude?
The term core schemas is used [by TOYS] to mean the set of schemas being captured, compared or synchronized.
Often, tables in the core schemas have foreign keys that reference external schema [i.e. schema that are not in the core schemas]. Additionally, external schemas may reference tables in the core schemas. These are refered to [in the TOYS documentation] as outward references and inward references respectively.
Where cyclic references exist [e.g. schema A references schema B and vice-versa] the schemas should usually be compared and upgraded as a group. If for example, all references in schema X are outwards to schemas A, B or C then schema X can quite happily be compared and synchronised on its own. There is however an implied dependency of schema X on schemas A, B and C and thus the latter three schemas will most likely need to be synchronised prior to synchronizing schema X. It should be understood that the synchronization of schemas A, B and C may drop and replace inward references from schema X. Additional foreign keys in X that reference A, B or C are not created when synchronizing A, B and C. Additionally, any existing foreign keys in schema X which reference A, B or C and which are incompatible with the updated schemas [i.e. schemas A, B and C] are not re-created. When schema X is synchronized, these foreign keys get created.
All the above is saying, is that if schema X depends on schemas A, B and C then if you synchronize schemas A, B, C without X then you may break some references from schemas X to A, B or C. For foreign keys, these "broken" references are dropped during the synchronization of A, B and C but the "fixed" references (if any) get created when you synchronize schema X. If you synchronize A, B, C and X together then all this happens at the same time.
Thus, if you find that these dependencies between schema X and the group A, B and C are too difficult to track then you will probably want to include schema X with A, B and C.
Another way of looking at all this is that the core schemas form a logical database and most likely correspond to an application.
When I use TOYS to browse, first I have to "capture" a database snapshot. Why?
TOYS is not intended as an interactive schema browser. The browse facility is to allow you to view the contents of your database snapshots. The reason TOYS uses snapshots is that this facilitates saving the schema meta-data (i.e. database structure) at any point in time. These "snapshots" are saved in text form (XML) and can be readily stored in source code management systems such as CVS, RCS, SCCS, Visual Source Safe, PVCS, etc. This allows easy retrieval, inspection (via the browser), comparison etc. The fact that you can capture your "live" database and browse it immediately is a bonus. You may however find that other tools are more suited for interactive browsing as that is what they were purpose built for.
What is the purpose of the option View -- Options -- DB Capture --> Use Base Tables and when should I use it?
This option determines whether TOYS uses the base catalog tables as opposed to using the regular catalog views such as DBA_TABLES, USER_TABLES, etc.
When enabled, TOYS will use the base table catalogs on the proviso that your session has the SELECT ANY TABLE privilege [Oracle 7 and 8] or has SELECT ANY DICTIONARY privilege [Oracle 9]. If your session does not have this privilege then TOYS ignores this option setting as there is no choice but to use the regular view catalogs. So, what are the effects of using the base catalog tables? Answer: speed and accurracy.
First, speed. It is significantly faster to use the base catalog tables. Why is this so? Because, TOYS can perform certain specializations when accessing the base tables directly. These specializations are not possible via the regular catalog views.
Second, accurracy. There are a number of bugs in the catalog views. These are mentioned in other FAQs. For a detailed discussion of the various issues read the topic in the Help manual [ Help -- Reference -- Schema Capture -- Issues with Oracle catalogs ]. To our knowledge these are more prevelant in 8.0 through 9i. Release 9iR2 seems to have resolved most of the issues.
For example, the view DBA_OBJECTS in 8.1.5 does not decode the object type of 'LOB'. This was fixed in 8.1.7 (or possibly 8.1.6). Furthermore, 'LOB PARTITION' and 'LOB SUBPARTITION' are incorrectly decoded. The information is correctly recorded by Oracle in the base caralogs, it is just not presented correctly in the regular catalog views.
So why would you want to disable the base table lookups? The major reason for this is if you you are using a very recent version of Oracle and suspect TOYS is producing incorrect results or fails to perform the schema cature.
You should note that TOYS does not use the base tables queries for most queries against Oracle 7 and only uses base table queries to a limited extent against Oracle 8.0. Furthermore, base table queries are often automatically disabled for many queries when accessing a version of Oracle with a point release greater than that which was used to develop TOYS. For example, at the time of writing this answer, TOYS was tested up to Oracle 9iR2 (9.2.0).
When a schema capture is performed with this version of TOYS against any version of Oracle greater than or equal to 9.3 [i.e. 10G], many of the queries will fall back to using the regular view catalogs. This is done for safety reasons as the view catalogs are much more stable than the base table catalogs.
I'm performing a capture and my session has the DBA role but I can only see my own schema, why?
When capturing meta-data in Oracle 9i, TOYS checks for the system privilege SELECT ANY DICTIONARY to determine whether you have the rights to capture multiple schema. If you do a fresh install of Oracle 9i then the standard role DBA will have this privilege. If you have updated your database from Oracle 8i then the DBA role may be missing this privilege.
This omission in the upgrade scripts may afftect other tools as well. To fix it, you need to grant this privilege to the DBA role.
What is meant by schema comparison?
Schema comparison simply means comparing two database schemas to determine structural differences. The data contained in these schemas (if any) is not compared.
Applications often span more than a single schema. TOYS allows comparison of one logical database against another logical database. Each logical database is one or more schemas. If you had a financial application, you might have General Ledger, Accounts Receivable and Accounts Payable installed in Oracle schemas "GL", "AR" and "AP" respectively. TOYS allows you to compare these to some reference schemas on an individual basis (that is to compare say "GL" to reference "GL" and then in a separate comparison, compare "AP" with "AP", etc.). Alternatively, TOYS allows you to compare all schemas in the application as a single logical database. You might, for example, have taken a snapshot of your application in your development environment and compare that with your production environment as follows:
| DEV | | PROD |
| GL | <==> | GL |
| AR | <==> | AR |
| AP | <==> | AP |
How does TOYS compare schemas?
TOYS uses a very simple concept of operating on meta-data "snapshots". These snapshots are meta-data extracted from the database catalogs at a point in time. TOYS reads (or captures) meta-data from the database catalogs and constructs an in-memory model of the database structure. This is referred to as a "snapshot" and is analagous to a photographic snapshot in that it is a "picture" of the database structure at some point in time. These snapshots contain all the relevant meta-data describing the schemas that you nominated.
In order to compare two databases you first have to capture a snapshot of each of them. Thus you can connect to two different [physical] databases and capture your meta-data snapshots. Alternatively, you can capture both snapshots from the same [physical] database. In this second senario, the nominated schema names will be different. As a third alternative, TOYS allows you to save a snapshot to a file [as XML] and to reload this snapshot file at a later time. When comparing databases, TOYS is really comparing snapshots and does not care where the snapshots are sourced from. This makes it very easy to take "snapshots" of your databases and to see how their database structures have changed since the earlier snapshots were taken.
To compare two snapshots, TOYS presents you with a wizard. When you click Finish, the comparison happens immediately (typically a second or less). On completion, a tree view of the differences is presented. TOYS uses simple icons to indicate object differences. Code objects such as procedures and functions are displayed as text with color highlighting of changed sections.
What does the code compare options in TOYS do?
The code compare options control what happens when SQL text is being compared. SQL text is the text within the following:
- View definitions
- Function and procedure definitions
- Package and package body definitions
- Type and type body definitions
- Trigger definitions
Using the code compare options you can make TOYS do any of the following.
Perform an exact match. This is, a character by character compare. Only whitespace at the start and end of the source document is ignored.
Perform an in-exact match. This is a comparison of the text ignoring embedded spaces and capitalization. These are the type of options provided by the UNIX diff utility.
Perform a SQL syntax aware comparison. This extends the usual diff semantics by the following.
- Optionally ignoring single line and multi-line SQL comments.
- Ignoring capitalization inside identifiers (except if quoted).
- Matching quoted identifiers to their unquoted equivalents.
- Ingoring embedded spaces in lines except within literals and quoted identifiers.
Using these options you should be easily able to determine differences between schema objects.
Notes:
Choosing anything but an exact comparison will considerably slow the comparison process if there are a lot of text differences in code objects. TOYS always performs an exact comparison and only if it finds differences [and you have checked the appropriate options] does it proceed to perform an in-exact / SQL-aware comparison.
Wrapped source is never compared against source that is not wrapped. If the source for one object is wrapped and the source for other is not wrapped then the code is considered to be different. Wrapped sources are always compared using exact match semantics.
Comments on table and table columns (ditto views) are not considered to be SQL text. As such they are always compared exactly.
Tools like SQL*Plus strip trailing spaces from the end of text lines. Thus if SQL*Plus is used to create program units, all trailing spaces are stripped. This means that if you perform an exact comparison with source that was created with a different tool [and has some trailing whitespace] differences may show up. Simply choose a SQL syntax aware comparison or an In-exact match and for Spaces choose Ignore Spans -or- Ignore All.
At the time of writing, the presentation of the SQL syntax aware comparison differences is not always handled properly when there are multi-line comments and comments are being ignored. This is due to attempting to display the differences in-line. The actual comparison is correct but when displayed in-line [as opposed to side-by-side which is not currently supported] some lines get repeated. We are looking at ways of resolving this.
What rules does TOYS use to auto-map schemas?
Whenever you compare databases, TOYS needs to know which schemas in the reference database are to be matched against the schemas in the working database. In the simple case of one schema in each, the answer is easy. What happens when both databases contain many schemas and the names are different?
TOYS provides you with a mapping dialog to allow you to match up the schemas in the working database with those in the reference database. This dialog presents the schemas and roles present in the working and reference database snapshots in two lists side by side. The list on the left (right) has schemas and roles from the working (reference) database. Using this dialog you have to match up each of the schemas /roles on the left to those on the right. To get you started TOYS applies a few simple rules to auto-map as many entries as possible. Here are the rules used.
The PUBLIC and SYS schema (if present) are matched to their like named couterparts or to "nothing" if the other side does not contain the same schema. This mapping cannot be changed.
The SYSTEM schema (if present) is matched against it's like named couterpart providing both schemas are the same type. That is, both are core schemas or both are ref schemas. This mapping can be changed.
Considering only the remaining unmatched items, if there is exactly one core schema on each side, these are matched. This mapping can be changed.
Considering only the remaining unmatched items, items with same name and of compatible types are matched. Compatible types are as follows:
- Core is compatible with Core and Ref and vice-versa.
- Role is only compatible with itself.
This mapping can be changed.
Considering only the remaining unmatched items, if either side is empty (but not both), all remaining entries are matched to "nothing". This mapping can be changed. Note that this last rule is not applied when synchronizing.
Now let us say for example that you are comparing a single schema against another schema. In this simple case, the mapping is performed for you even though the schema names are different.
Alternatively, if you compare schemas from one physical Oracle database against a different physical Oracle database and the schema names are identical then the mapping is also done for you.
In more complex cases, you will need to do some or most of the schema mapping.
Why does TOYS force me to map schemas and roles I don't care about?
Whenever you compare databases and the schema and role names are different you will need to map the schemas and roles in the two snapshots. If the snapshots contain grants [i.e. you captured grants] or the core schemas reference external schemas then you are likely to have to do some extra mappings. Unfortunately, at the time of writing, TOYS does not look at whether you are actually comparing grants, foreign keys, etc. it just looks at all the schemas and roles and requires that you map all of the ones in the reference snapshot. If you know that you won't be using them then map them to nothing and TOYS will map them to their own name.
We do recognize that this is unnecessary and hope to change this behaviour some time soon.
TOYS seems to match up indexes in a strange manner. As an example, I had an index on my EMP table called EMP_I01 on the column ENAME. In the new data model the table EMP was re-named to EMPLOYEE, the column ENAME was renamed to EMPLOYEE_NAME and this index was re-named to EMPLOYEE_I01. When I did a comparison in TOYS and indicated that the table and column had been renamed, it just ignored the index name and matched the indexes EMP_I01 <==> EMPLOYEE_I01. Futhermore it indicated that the index names were different. Why are indexes not matched on name?
TOYS matches indexes first "by definition" and then attempts to match any remaining unmatched indexes by name providing they have a user assigned name. By definition, we mean that indexes are matched by their column lists [or index column expressions for function based indexes]. The name of the index [i.e. the index owner and name] is seen to be much less important than the definition of the index.
Because TOYS correctly identifies indexes by definition, a change of index name can be effected by a simple re-name command.
As an example, suppose that in our working database we have an index on the EMP table on the columns FAMILY_NAME and DATE_OF_BIRTH and we named it EMP_I04. Now suppose our industrious data modeller has renamed the table to EMPLOYEE and the index to EMPLOYEE_I02. When you do the comparison, you have to tell TOYS that EMP <==> EMPLOYEE. It will match the two tables then match the indexes on their columns. TOYS will indicate that the name of the table has changed from EMP to EMPLOYEE [but you already knew that]. TOYS will also indicate that the name of the index has changed from EMP_I04 to EMPLOYEE_I02. If you synchronize [and assuming index renaming is supported by the RDBMS], the table would be re-named with the command
ALTER TABLE emp RENAME TO employee
and then the index would be re-named with the command
ALTER INDEX emp_i04 RENAME TO employee_i02
If TOYS matched indexes exclusively by their names then in the above example the existing index EMP_I04 would be dropped and then re-created as EMPLOYEE_I02 just because it's name had changed!
TOYS considers name and ownership to be properties of the index. These properties are compared and are reported as differences like other properties such as uniqueness, index type, placement, etc.
Note that a change of columns (or a change to the expressions of a function index) would cause a drop and [re-]create of the index. You cannot avoid this. When an index is identical except for name, TOYS simply issues an ALTER INDEX old-name RENAME TO new-name statement.
If table columns are re-named, and you indicate this to TOYS, it will perform column name substitution when matching indexes. Providing the RDBMS supports re-naming of table columns [i.e. Oracle 9i Release 2] then TOYS will simply rename the column with the command
ALTER TABLE employee RENAME COLUMN dob TO date_of_birth
and the indexes, constraints, etc. will not require changing.
Clusters have at most one index, so there indexes are matched implicitly. Neither the index definitions or their names are used for matching purposes.
Thus for both table and cluster indexes, the index name is simply a property. So too is ownership!
TOYS does not show the columns, constraints, indexes and triggers for tables being created or dropped. Why?
One of the most fundamental problems when comparing database structures is being overloaded with detail which is distracting and irrelevant. TOYS attempts to minimize this "noise". It tries to show you only the real differences.
If a table is dropped from your reference schema then of course its sub-components are dropped. Who wants to be told that each of it's columns, constraints, indexes, triggers, etc. are absent? The Oracle catalogs present meta-data in tabular form. Given data in this structure, it is a lot easier to report all this irrelevant garbage then to filter it. Many other schema compare tools take this simpler approach.
When developing TOYS, we chose to use an "intelligent" approach to comparing schema and to present you with a consise picture of the true differences. As such, TOYS treats a schema as a hierachy of objects and not as unrelated collections of objects.
Using this hierarchical approach, TOYS considers table columns, indexes, constraints and triggers to be children of their table. These child items are ignored unless the equivalent parents are present in both of the schema being compared. That is, in this case, the same table must be present in both the working and reference databases. Furthermore, some of these child objects have children of their own. For example, table columns may have not-null constraints, grants and LOB storage segments. Again these will be ignored unless the same column appears in both of the tables being compared.
By definition, not-null constraints are always column constraints. As such, if the column is extra or absent in the other table the column is simply reported as such and the non-null constraint is ignored. However, the waters muddy a little with check, unique and referential constraints which can be either column constraints or table constraints. TOYS allows you to choose whether to include these constraint types in the comparison when none of their columns are common to both tables. At the time of writing this does not extend to function based indexes which are always included because without parsing the index column expressions one cannot determine what columns are involved. What this means is that if one or more columns are dropped from a table in the reference database, all check, unique and referential constraints (i.e. constraints other than not-nulls) will also be reported as being absent in the reference database table but you can [by choosing the appropriate options in the compare wizard] elect to ignore these.
Indexes are handled in a similar manner.
All is not perfect however. The reporting of LOB storage differences leaves room for improvement. When the parent column is added or dropped, any related LOB storage is ignored. This is the desirable behaviour. However, if a column data-type is changed from say LONG to CLOB, the additional LOB storage segment is also reported as extra. With a little more work this could be made optional.
When I compare my schemas, TOYS indicates differences in many of my views, triggers and program units (procedures, packages, etc). The problem seems to be that I have explicitly coded schema names into the code and TOYS shows these lines as being different.
I have correctly associated [mapped in TOYS] all schemas in the databases being compared. TOYS has correctly compared synonyms, sequences, tables, clusters and indexes. It has even correctly matched the program units. The problem is that the text of my program units shows up these "spurious" differences. Why?
This problems has plagued Oracle developers since forever. For practical purposes, the number of Oracle databases on a single host is limited. It is normal to have a few databases on a single host but things get out of hand if you attempt to install dozens of databases on the same host. It just isn't practical.
Why is this an issue?
Well, the development cycle often calls for lots of "logical" databases. There are only a few choices:
- Keep everything in a single schema.
- Break the application into multiple schema and always restrict one instance of it to any one physical Oracle database.
- Break the application into multiple schema and take steps to allow multiple instances to co-exist in a single physical Oracle database.
If you are doing either of the first two then you won't have this problem [you'll have other issues to overcome].
Handling the third method means having the same "logical" schema instantiated in a single Oracle database under different schema names. When you do this their are a number of issues to overcome.
Oracle places all schema names in a single global catalog. There is no facility for selecting a different default catalog as there is only one. Thus if we want multiple instances of the same "logical" schema to co-exist we are forced to use different names. So why is this an issue?
When objects in one schema need to refer objects in other schema they do so in two ways:
- Directly by name
- Indirectly by using a synonym.
All of this really only becomes important in views and program units. That is in views, triggers, fuctions, procedures, packages and types. For other object types the schema name is easily accessible in the meta-data without having to parse source code. For code objects, schema names may be used as object "qualifiers" but doing so requires parsing of the source code. Parsing source code is not an easy thing to do reliably. What all this means is that if you want to be able to compare your program units then you should avoid using explicit schema names in your source code [or find a tool that reliably parses the source code of program units].
TOYS allows you to "map" schema names and then uses these "mapped" schema names to match ownership of objects. That is, the schema names in one database do not have to be the same as those in the other database. You must however correctly map [associate] the equivalent schemas before comparing the databases. TOYS does not [currently] parse code to "substitute" schema names during comparisons. Consequently, if you hard code schema names into your code then "spurious" differences may show up in the code of the following object types:
- Function Indexes
- Triggers
- Views
- Snapshots
- Functions
- Procedures
- Packages
- User Defined Types
One work-around is to use private synonyms to reference objects outside of your schema. Typically, you would also define application prefixes which most likely equate to abbreviated logical schema names. Thus if the PAYROLL schema [let us give it an application prefix of "PAY"] wishes to reference the EMPLOYEE table in the HR [Human Resources] schema [and for argument sake we are using prefixes on our object names] then we have:
PAYROLL . HR_EMPLOYEE (synonym) --> HR . HR_EMPLOYEE (table)
or in SQL*Plus terms:
SQL> Remark Connect as owner of the PAYROLL schema.
SQL> CREATE SYNONYM hr_employee FOR &&HR_schema. . hr_employee;
The lexical substitution variable &&HR_schema is used to force SQL*Plus to prompt for the required schema name and substitute it into the DDL prior to executing it. Now that we have the synonym in place, the code that used to look like this:
CREATE VIEW pay_current_employees AS SELECT * FROM &&HR_schema. . hr_employee WHERE ... ;
now looks like this:
CREATE VIEW pay_current_employees AS SELECT * FROM hr_employee WHERE ... ;
In other words, we have moved the hard coded references to external schemas out of our code (views in this case) into private synonyms. Tools like TOYS can handle mapped schemas in synonym definitions. The text of views and program units can now be identical in all instances of the schemas. A word of caution though, you cannot do this with user defined types!
Yes, I admit that creating private synonyms is a little extra work but it does provide for self documentation of the interfaces between your schemas and the rest of the world. It also allows for easier cross checking with the privileges granted between schemas.
Alternatively, look at it this way. If it becomes a pain to define interfaces across schema boundaries then you will work hard to properly define your schema boundaries and minimise this coupling! Of course, if the data modeller gets to define the schema boundaries and you get to have the pain, well ... c'est la vie!
Why does TOYS show the underlying tables for materialized views?
A materialized view is implemented by Oracle creating or re-using an underlying table [i.e. container table]. When the container table is prebuilt it has a life cycle that is independent of the materialized view. Container tables that are not prebuilt have the same life cycle as their materialized view. That is, they get implicitly created when the materialized view is created and implicitly dropped when the materialized view is dropped.
For comparison purposes, TOYS treats the materialized view and the container table as distinct objects. Thus when the materialized view exists in both the working and reference databases, both the materialized view and the container tables are shown as separate objects. When a materialized view is absent (or extra), the container table is also shown as absent (or extra). TOYS [currently] does not attempt to treat as special, the case where a materialized view exists in one database and not in the other and a table with the same name also does not exist in the other database. This means that when a materialized view is added (or removed), its container table will also be reported as added (or removed).
To understand why it is necessary to show the container tables [at least some of the time] consider the following example using a materialized view / table named "A".
| Case | Object Type | Work | Ref |
| #1 | Mat.View | - | - |
| Table | A | - |
| #2 | Mat.View | - | - |
| Table | A | A |
| #3 | Mat.View | A | - |
| Table | A | - |
| #4 | Mat.View | A | - |
| Table | A | A |
| #5 | Mat.View | A | A |
| Table | A | A |
| #6 | Mat.View | A | A |
| Table | A | - |
In cases #1 and #2 we only have tables so we can ignore these cases.
In case #3 we have a materialized view in one database but not in the other. This results in both the table and the materialized view being reported as absent. We considered handling this case specially but decided against it as doing so makes it harder to understand what is going on. To understand why this is so, see case #4.
Case #4 is complicated. We have a materialized view in one database and only a table [with the same name] in the other. Do we report that materialized view "A" is absent? Do we also report that table "A" is extra? We have chosen to report that the materialized view is absent and then compare the tables for equivalence. This is conceptually very simple but gives you a little extra work to do as you have to compare two objects instead of one.
Case #5 could have been handled by comparing materialized views and including the table properties with those of the materialized view, however this approach would then have been inconsistent with case #4.
Case #6 is not possible in Oracle 9i but is possible in Oracle 8. In 8i, bugs allowed you to drop the container table (fixed in 8.1.7) or to rename the container table giving it a different name to it's materialized view. In Oracle 8.1.7 having an container table with a different name to it's materialized view poses an additional problem as the materialized view can be dropped but the table cannot be dropped. The Oracle server tells you that you must drop the materialized view instead.
Note that TOYS does not attempt to resolve this bug. If you are using 8.1.7 and have re-named a materialized view leaving your catalogs inconsistent then you will need to correct this before using TOYS to synchronize your databases. There are two possibilities.
If you have not dropped the materialized view at this point then simply re-name the container table back to its original name. You will now be able to drop the materialized view [including the container table].
If you have already dropped the materialized view then you need to re-create it using the ON PREBUILT TABLE option. You can then drop the materialized view and finally drop the table as a separate step.
Note also that this bug was fixed in Oracle 9i by disallowing the commands:
ALTER MATERIALIZED VIEW mat-view-name RENAME TO new-name
ALTER TABLE table-name RENAME TO new-name
RENAME table-name TO new-name
on the materialized view or it's container table. If you attempt to rename either, Oracle issues:
ORA-32318: cannot rename a materialized view
When comparing schemas, does TOYS compare the row data in tables?
TOYS does not compare row data when comparing schemas. We note that this is useful in some circumstances and are considering adding this as a tool (wizard) in its own right. We do not intend to make it part of the schema comparison.
Remembering that the squeaky hinge gets the oil, why not express your need for this feature and email our support team.
What is meant by schema synchronization?
Let us say we are developing a Payroll application and that we have a development database with version 17 of the Payroll schema installed. When the data modellers release version 18 of the schema, we are likely to want to make our "working" schema identical to this "reference" schema. This action is referred to as "schema synchronization". In this context, the synchronization is of the schema stuctures and not the data contained in those structures. You may of course, wish to preserve this data.
There are two basic techniques for doing this.
The simplest method is to drop everything in your existing Payroll schema and to run the new DDL creation script (version 18 in our example) to create a brand new schema. Next you have to re-grant any privileges [depending on your situation]. Finally you have to populate the schema with reference data, meta-data, test data, etc.
The alternative is to alter the current schema objects to agree with the new schema definitions. When doing this, data has to be preserved and possibly transformed. When objects are re-built [dropped and re-created] any existing grants have to be re-issued, data and physical attributes have to be preserved etc.
In many situations, the first method is suitable. In many other situations, method two is preferable.
Can TOYS correctly synchronize all Oracle databases?
The fundamental principle that TOYS (and other synchronization tools) are based on is that the data model is "static" [or mostly so]. This means that objects are constructed "as designed". Some applications allow end users to "configure the system" recording this configuration in meta tables and then generate objects from this meta-data. These "dynamic" schemas will never agree with any reference model. Futhermore, there is no standard for recording this user defined meta-data thus making it very difficult [if not impossible] to compare the "dynamic" parts of these schema with anything.
The best that can be hoped for, is to "filter out" these dynamic objects and compare the static parts.
Currently, TOYS does not facilitate any form of "filtering" of objects. The generated synchronization script will attempt to drop all these generated objects. As an interim work-around you could remove these drop commands [manually] from the script but we would not recommend that you use TOYS to attempt to synchronize schemas for these types of applications.
Why and under what circumstances does TOYS use error trapping in the synchronization script?
TOYS attempts to produce a robust synchronization script. However, many things can go wrong. Here are some of them.
- Generating the synchronization script against one database and running it against another database which has a different structure (i.e. missing or different objects).
- Adding constraints which the existing data violates. For example, adding a not-null constraint to a column that currently contains NULLs.
- Changing column data types such that either (i) the existing column data values cannot be converted -or- (ii) some values fail the conversion.
- Insufficient resources to create new or temporary objects.
- Insufficient privileges.
TOYS attempts to balance the requirement to produce a robust synchronization script with the requirement to easily complete the process. Thus, if the problem can be corrected later without corruption of the database TOYS will usually turn error trapping off. If corruption would occur TOYS will turn error trapping on. TOYS activates error trapping in SQL*Plus by inserting a command WHENEVER SQLERROR EXIT FAILURE. If the Oracle server raises an exception, SQL*Plus exits immediately thus terminating execution of the synchronization script.
What conversion rules does TOYS use when migrating data from an old table structure to a new table structure? This is called re-definition in some of the Oracle literature.
TOYS attempts to provide "intelligent" conversion of data where possible. You may always supply a SQL expression as the source for any column in any table being re-built. This expression can be any valid SQL expression that selects from the old table. If you do not supply an expression, TOYS generates one as follows.
For new columns, TOYS uses the default expression if one is defined on the new column. If no default is defined and the column is nullable then NULL is used. If the column does not permit nulls then TOYS uses a suitable constant such as 0 for NUMBER columns, sysdate for DATE columns, etc.
For pre-existing columns which are identically defined, TOYS simply "maps" the old column to the new column (i.e. a direct copy).
For pre-existing columns which have changed definitions, TOYS attempts to provide a suitable conversion expression (refer Help). As a last resort, it simply "maps" the old column to the new column. As an example of a conversion expression suppose column "JOB" was defined as "CHAR (15)" and the new definition is "VARCHAR2 (20)", the conversion expression would be rtrim("JOB"). This conversion removes the trailing spaces before insertion into the new table. It does not truncate column values. That means that if the new column definition had a reduced length say "VARCHAR2 (10)" then it is possible for the synchronization script to fail at this point (e.g. let us say that the old value was "ADMINISTRATOR " which gets right trimmed to "ADMINISTRATOR" (length 13) and the new column has length 10).
What does the option Prefer Fast Copy [in the synchronization script] do?
TOYS preserves table data by either (i) unloading table data to external files and re-loading -or- (ii) copying table data internally using SQL or PL/SQL. The option Prefer Fast Copy only applies to internal copying. With this option enabled, TOYS will use an INSERT INTO table SELECT ... in preference to a PL/SQL block where ever possible. The insert is much faster but may fail on large tables due to insufficient undo segment space.
What does the option Use Logging [in the synchronization script] do?
Logging provides the ability of the Oracle RDBMS to recover to the last completed transaction. This is the expected behavior of an RDBMS. There are times when you have complete control of the upgrade process and the whole database. Under these special circumstances, you may elect to turn off all logging for the synchronization process and on completion, perform a hot [or cold backup] of your database. What needs to be understood is that in the event of media failure you lose everything. A half way option is provided whereby you can disable logging for indexes only. In the event of media failure you can simply rebuild these indexes (after the database has been recovered of course).
Can I run the generated database synchronization script against databases other than the one it was generated for?
Not safely!
When generating the synchronization script, TOYS assumes that the script will be run against the same working database that was used to generate the synchronization script. It is recommended that you generate a synchronization script against your target database. That is, your working database should be a live snapshot.
TOYS assumes that when it issues a drop command that the object does currently exist. Futhermore, when it issues commands to modify objects (say to add a column to a table) that those objects are exactly as it thinks they are. For example, if new-column already exists in the table then the statement ALTER TABLE table ADD new-column ... will fail.
When new tables and indexes are created, TOYS must determine where to place these. Depending on your chosen options, TOYS may omit or include the TABLESPACE clause. If omitted, all new objects are going to get created in the user's default tablespace. This is usually acceptable in development databases but not usually desirable in production databases. If the tablespace is included then all target databases must have identical tablespace names or the script will fail. For simplicity, we have assumed that you use locally managed tablespaces (LMTs) and thus storage parameters are not an issue here.
TOYS allows you to compare and synchronize databases where corresponding schemas and roles have different names. It does this by allowing you to map schema and role names. If you generate a synchronization script and wish to run it against multiple target databases then the names of schemas and roles must be identical in all target databases.
Finally, TOYS has to manipulate not-null, check and foreign key constraints by name. It needs to do this to rename, drop, disable and enable them. If these constraints have system generated names (e.g. SYS_C0010094) then the commands will only work against the instance for which they were generated. TOYS can generate code to search the catalogs and determine the names of not-null and foreign key constraints. This does tend to bloat the synchronization script. Additionally, it cannot be guaranteed to work for check constraints.
If you really want to use the same synchronization script against multiple databases, here is what you can do to help.
Always force all target databases to be identical. They must never be allowed to be different. This is for the schemas in question. This applies to schema and role names, to object grants, tablespace names, storage parameters [if appropriate] etc.
Explicitly name your constraints.
Not-null constraints can [mostly] be created or dropped without using a name. Not-null constraints can even be enabled / disabled without using a name by dropping the constraint and then re-creating it enabled / disabled.
Like many other things there is an exception to the rule: Oracle does not allow you to modify a clustered table column so the constraint name is required to drop / enable / disable a not-null constraint in this particular case. If you want to add a not-null constraint to a clustered column, bad luck, Oracle does not let you do it [same reason]. You will have to use a check constraint instead. Worse still, if you want to add or modify a DEFAULT for a clustered column you cannot do that either [same reason]. But don't feel bad, Oracle's own EXPort and IMPort utilities will lose any of your DEFAULTs on clustered columns [and sometimes the whole table] for this same reason. One can only assume that almost no one uses clusters! If you do use clusters then make sure you name any not-null constraints on the clustered columns.
Check constraints can only be [reliably] dropped, disabled and enabled by name so you have got to name these!
Primary key and unique key constraints can mostly be manipulated without using their names. If you are sure that you will never ever change your mind and want to give them names later, then you can get away without naming your unique constraints!
Oracle 9iR2 introduced the ability to re-name constraints but the re-name command requires that the constraint's existing name be supplied. Thus, if it is to be used to rename constraints with system generated names then the generated DDL commands will only be valid for the database against which it was generated! That is, you cannot run the generated DDL against any other database without editing it and manually supplying the current system generated names of the constraints in question.
As far as foreign keys go, you should always name them!
Enable the option to alert when TOYS uses non persistent (system generated) constraint names when generating the synchronization script. If TOYS alerts about using generated names, you will have to tailor the script for each database.
Enable the option to force TOYS to avoid the use of system generated constraint names when generating the synchronization script. With this option enabled, TOYS will generate PL/SQL code to lookup the constraint's name in the catalogs at execution time. This is not done for check constraints.
Generate the synchronization script against the earliest version of the RDBMS. For example, if you are supporting your application on both Oracle 8i and 9i, then generate the script against the 8i database.
Why is the RDBMS version important?
TOYS considers the version of Oracle to determine the support for manipulating objects. For example, can indexes and constraints be re-named? Can table columns be dropped, re-named etc.? Since these capabilities vary from version to version of the Oracle server, TOYS will generate the appropriate DDL commands for the nominated version of Oracle. Because Oracle has [generally] maintained "upward" compatibility, a script generated against 8.0 will run against both 8.0, 8i and 9i. This assumes that you only use 8.0 features. The price you pay for explicitly forcing TOYS to generate a script to run against an earlier version of Oracle is that the synchronization process may operate less than optimally against the later version. If for example, TOYS needs to re-name some primary keys, then with an RDBMS prior 9iR2 the constraints must be dropped and then re-created. If any of these primary keys belongs to an index organized table (IOT) then the table must be re-built. In 9iR2, the constraints are simply [and efficiently] re-named.
TOYS seems to be a little long winded when re-naming objects. For example, I had a table with the following indexes being re-named/dropped as follows:
"I01" being dropped
"I02" no change
"I04" being renamed to "I03"
"I05" being renamed to "I01"
and TOYS produced the following:
DROP INDEX i01
ALTER INDEX i04 RENAME TO i03
ALTER INDEX i05 RENAME TO "$toys$1"
ALTER INDEX "$toys$1" RENAME TO i01
Why the temporary when anyone can see that you can just re-name "I05" to "I01" as the original "I01" is already dropped by the time the re-naming takes place?
To avoid conflicts such as "A" --> "B", "B" --> "A", TOYS employs a simple strategy of using a temporary name whenever an object is re-named to a pre-existing name. That is, to any name in the same namespace in the working database. This avoids a clash. There is no consideration given to the objects deleted prior to the re-naming step. Since the cost of re-naming objects [when supported by the RDBMS] is minimal, TOYS does not attempt to eliminate [some of] these temporaries.
Does TOYS create an optimal synchronization script?
The DDL synchronization script generated by TOYS attempts to be "optimal". However sometimes it produces additional DDL statements that are not required. The case of handling temporaries during re-naming of objects is discussed in another FAQ. Another example of this is that when an object is being modified and if any grants are different, TOYS re-issues all grants for the object. Generally these do not affect the execution times of the DDL script. Of much greater significance are the [potentially] long running commands that create indexes, enable constraints and copy table data. Given the capabilities of the Oracle server you are targeting, TOYS will attempt to optimize the upgrade process, however, it will not always do so. Here are some cases where TOYS may generate less than optimal DDL.
When a clustered table becomes unclustered. TOYS [assuming internal preservation] will unload the clustered table into a temporary [heap] table, create the new table and then copy the rows [possibly modified] from the temporary. Obviously this is double handling.
When a column is re-named and if a re-name is NOT supported by the target server, TOYS will simply re-build the table. Sometimes this will be more efficient than using a process of add column, update new-column from old-column, drop old column. Sometimes it won't be!
TOYS does not currently support any means of external preservation of table data during table rebuilds. TOYS uses SQL -or- PL/SQL to copy table data. PL/SQL is not a particularly efficient method for doing this. It is also highly restrictive in handling of LONG and LONG RAW columns.
Another issue is resource [i.e. tablespace] usage. When using internal preservation, TOYS preserves all tables before re-building any of them. Thus, if you have a lot of clustered tables and their clusters need re-building, TOYS will need space to preserve all clustered tables being re-built. That is, copies of all the clustered tables are made before the clusters are dropped with their tables.
Does TOYS permit on-line use of the database while the synchronization is executing?
Currently, TOYS does not facilitate use of that part of the database being updated while the synchronization process executes. Futhermore, TOYS assumes that it has exclusive use of those parts of the database it is updating. If this is not the case then the process is likely to fail. It is up to you to ensure that end-users are denied access while the synchronization process executes.
Future releases of TOYS will use the on-line redefinition facility [introduced in Oracle 9i] to enable on-line synchronization [for simple cases]. It is expected that while this allows the database to be used during the synchronization processing it will require significantly greater resources.
In Oracle 8, TOYS failed to drop a primary key when the supporting (unique) index was owned by another schema, why?
In early versions of Oracle there was a bug which meant that if a unique index was created in one schema and later used as a supporting index for a primary key in another schema you could not drop the primary key. You had to actually drop the table. Oracle [sort of] fixed this. In 9i if you do this the schema owning the primary key also needs the system privilege DROP ANY INDEX to drop (or disable) its own primary key. Not exactly intuitive! One could argue that this is no different to granting INDEX on a table to another schema and if that schema creates an index on your table you cannot drop that index you have to drop the table [assuming of course that you do not have the system privilege DROP ANY INDEX]. From 9i, TOYS gets around this problem by using the KEEP INDEX clause on the DROP [or DISABLE] primary [or unique] key commands. In versions prior Oracle 9i, if you grant anyone index privileges on your tables you had better have the system privilege DROP ANY INDEX.
What facilities of Oracle 9i or object types does TOYS support?
First it should be understood that TOYS is concerned about application related objects (i.e. schemas) and does not attempt to manage physical Oracle databases. That is, TOYS is not concerned about tablespace and data file definitions, about rollback segment definitions, about user profiles, about user and role definitions.
TOYS is concerned about the definitions of schemas including the logical and physical definitions of the objects within those schemas.
See Oracle Features Supported by TOYS for a definitive list of thoses features in Oracle 9i that are supported.
What determines the Oracle version that TOYS chooses when generating DDL?
When you capture meta-data, TOYS determines the version of Oracle and records it in the snapshot as part of the meta-data. If you then generate DDL from this snapshot (meta-data), TOYS uses the recorded version to determine the syntax etc. of the generated DDL. When generating DDL, you may override the default setting. If you do so, be aware that this option may not provide all of the functionality that you require. Although the generated DDL is [usually] valid for the target database version, loss of semantic content may result.
The sorts of things that can go wrong are as follows:
Loss of semantic information. For example, if you have a database snapshot taken of an Oracle 9i database containing a foreign key with an action of ON DELETE SET NULL and you generated DDL for Oracle 7.3, the ON DELETE SET NULL action is discarded and the action reverts to ON DELETE NO ACTION.
Invalid content. For example, you may issue a grant on an object and that grant is not supported in the earlier version used as the target for your DDL. The syntax of the grant will be valid but the grant will fail.
Unsupported features. New objects types, data types and features are being continually added to Oracle. When you generate DDL from a snapshot containing these features and target it at an early Oracle version, these object types or features are silently discarded or converted to something "close". Probably not what you want!
Code that is not backward compatible. TOYS does not attempt to parse and/or convert the SQL code in view queries, triggers and program units. Thus when you include code in your 9i database that uses syntax or features that is not compatible in say 8i and then attempt to synchronize you will end up with invalid objects.
The compatibility option is more important when you are generatng a synchronization script. TOYS uses the recorded version of the working database as the default version for the generated DDL. You can override this.
When synchronizing, the target Oracle version is most important because it not only determines the syntax of the DDL but also the semantics of the synchronization process. That is, whether objects are simply re-named or dropped and re-created, whether columns can be dropped, etc. Currently, TOYS does not validate that features are actually available in the target database version. This option is intended to be used only by those who insist on generating synchronization scripts that will be executed on multiple databases with multiple Oracle versions. If you do so then the onus is on you to ensure that all of the Oracle features you use are compatible across all target databases. That is, if you generate a synchronization script for an Oracle 9i database and set the target version to be Oracle 8i then you should not use features that are not available in 8i.
Why does TOYS show VARCHAR2 data types as VARCHAR2 (20 BYTE) when I defined it simply as VARCHAR2 (20)?
TOYS is Oracle 9i savy. That is, Oracle 9i supports a concept of defining character data types with a length explicitly specified in bytes or characters. In versions of Oracle prior 9i, the length of character data types was implied as being in characters if the character set was fixed width encoding otherwise it was implied as being in bytes. Out of the box TOYS assumes character lengths should be specified in characters (as does the SQL92 standard). Consequently, it displays character data types with a length specified in characters without the CHAR qualifier. Character data types with a length specified in bytes are displayed with the BYTE qualifier.
In contrast, SQL*Plus displays the CHAR/BYTE qualifier for CHAR and VARCHAR2 columns when the length semantics are different to those defined for the session. That is, the session's setting for NLS_LENGTH_SEMANTICS. Since, TOYS is likely to be dealing with multiple databases and most likely many different settings it was thought less confusing to use a more static approach. Otherwise, as you switch between views the qualification semantics would change.
You can change how TOYS displays character length qualifiers by choosing the menu option View -- Options, then select the Browser tab. Finally, click the appropriate radio button in Display Char Length Unit. You may elect to Omit the qualifier, show the qualifier for byte only, show the qualifier for char only or to Always display the qualifier.
Note that this option only affects the display of character data types. It does not affect the generated DDL which is handled differently.
In the DDL generated for versions of Oracle prior Oracle 9i, the qualifier is always omitted as it would be a syntax error if present. In the DDL generated for Oracle 9i, the qualifier is conditionally included. Refer to the Help.
So which should I use for length specifications in Oracle 9i, byte or char?
Almost everyone would agree that specifying the lengths of character columns in characters is the most logical thing to do. The SQL92 specification talks only in character terms.
For various reasons we have a legacy of CHAR and VARCHAR2 being specified in BYTEs. If you live in an ASCII [or even an 8-bit single-byte] world then a byte equals a character and much of this is really a non issue. If you don't, you will probably face issues of storing characters in variable-width or fixed-width multi-byte formats.
If all your databases are Oracle 9i then the most logical thing to do is to setup your databases with the parameter NLS_LENGTH_SEMANTICS set to CHAR and simply think in terms of characters for all your length specifications. A word of caution though, those tables already built stay "as built". No point trying EXPort and IMPort as these utilities preserve the length semantics [as you would expect]. If you migrate your application from an Oracle 7 or 8 database using the EXP/IMPort utilities you can end up with column declarations in BYTEs no matter what the setting of NLS_LENGTH_SEMANTICS.
The main point to note is that the CHAR | BYTE qualifier should be seen as a migration aid. That is, it is intended as a means of getting your CHAR and VARCHAR2 length specifications to be CHAR. Setting the value of NLS_LENGTH_SEMANTICS to CHAR or BYTE for the session prior to executing table creation scripts may be a lot easier than explicitly stating it for every column, particularly during the transition from 8 to 9i.
If some of your databases are Oracle 8 or 8i then you have more issues. If all these Oracle 8 databases have single-byte database charactersets then the simplest approach is to set the 9i databases to use length semantics of CHAR as discussed above.
So you are still here! Well this probably means you have some Oracle 8 databases setup with variable-width multi-byte database charactersets and unfortunately these use an implied length semantics of BYTE. The hard part is if you have to support the same application across Oracle 8 and 9 and live with these multi-byte charactersets. In this scenario, you are going to have to use length semantics of BYTE in the Oracle 9i environments. This is the default setting anyhow.
What is the purpose of the option Oracle UTF-8 Encoding and why should I change it from DEFAULT?
This option determines the value that TOYS uses to set the NLS_LANG environment variable. This variable is used by the Oracle drivers and works as follows. If the character set specified by this variable is the same as the database character set then no character set conversion is performed. This is the most efficient means of operation. If the character set specified by this variable is different to the database character set, Oracle performs character set conversion to the nominated character set.
TOYS uses Unicode (UTF-16 encoding) for all internal character handling. To get all character data into Unicode, TOYS exchanges all character data to/from Oracle as UTF-8 encoded sequences. Thus, TOYS needs to tell the Oracle driver to use a UTF-8 character set. Oracle currently supports the following UTF-8 encodings:
| Oracle CharSet | Description |
| AL24UTFFSS | UTF-8 (Unicode 1.1) Oracle 7.2 - 8i |
| UTF8 | UTF-8 (Unicode 2.1 - 3.0) Oracle 8.0 - 9i |
| AL32UTF8 | UTF-8 (Unicode 3.0/3.1) Oracle 9iR1/9iR2 |
By default (i.e. with DEFAULT as the setting), TOYS uses UTF8. This supports the full Unicode 2 characterset and will work with any Oracle 8 or 9 client drivers. There are two possible problems:
You are running a [now very old] Oracle 7 client driver which does not support UTF8. In this case you need to choose the only supported UTF-8 character encoding (i.e. AL24UTFFSS). Be aware that this encoding supports Unicode 1.1 and could result in data loss in the translation.
If you are running a 9i/9iR2 database with a UTF-8 database character set then it is most likely set up with AL32UTF8 and you will pay for conversion. Yes, AL32UTF8 is a different character set to UTF8!
Note that if you choose AL32UTF8 and your database character set is UTF8 (or AL24UTFFSS) you will still pay for character set conversion. Note that AL32UTF8 supports the full Unicode 3.1 standard, UTF8 only supports Unicode 2. If you are going to change this option, you must do so before making any connection to Oracle. The Oracle drivers are loaded when the first connection is attempted. The drivers are not released until you close TOYS.
Functionally, this setting makes little or no difference [with the exception that Oracle 7 drivers will not support the later encodings].
Our recommendations?
If your Windows system has the Oracle-7 client installed then use AL24UTFFSS.
If your Windows system has the Oracle-8 client installed then use DEFAULT (i.e. UTF8).
If your Windows system has the Oracle-9 client installed you may use any setting. AL32UTF8 is best but if not available then use UTF8.
TOYS does not always order grants in the same order. What ordering does it use?
TOYS attempts to sort grants on any object (including column level grants if appropriate) into the following sort order:
- Grantor (object's owner first)
- Grantee
- Column-Name (non-column grants first)
- Privilege-Name.
However, where a complex sequence of grants has been performed, TOYS will re-order as necessary to sequence the grants in the correct order such that chains like the following will be handled correctly.
Assuming the object in question is owned by schema HR, and we have:
- HR --grants-with-grant-option-to--> SAM
- SAM --grants-with-grant-option-to--> PAY
- PAY --grants-with-grant-option-to--> TRAIN
If TOYS used the strict sorting outlined above we would have:
- HR --grants-with-grant-option-to--> SAM
- PAY --grants-with-grant-option-to--> TRAIN
- SAM --grants-with-grant-option-to--> PAY
which would fail to execute as schema PAY would not have received the privilege at the point it attempts to grant it to schema TRAIN.
Note that these are very contrived cases. Usually most grants are performed by the owner of the object (or in 9iR2, by someone possessing the system privilege GRANT ANY OBJECT PRIVILEGE which is basically the same thing).
Will the generated DDL always re-create exactly what was there before?
This depends. Quite often you will omit the physical attributes such as storage and placement so these attribues will most likely differ from the original. In a similar fashion, you may omit certain object types and hence these will be missing from the generated script. What is more, they may cause the script to fail.
So assuming you generate a script with all objects included and ignoring physical attributes, what other differences will you see?
There are some [hopefully rare] cases that may result in differences. For example.
Suppose you issue the following DDL:
CREATE TABLE x ( a NUMBER PRIMARY KEY, b NUMBER ); /* Assume assigned name of PK is SYS_C004556 */
ALTER INDEX sys_c004556 RENAME TO x_pk;
The generated DDL would look like this in 8i:
CREATE TABLE x ( a NUMBER, b NUMBER );
CREATE UNIQUE INDEX x_pk ON x ( a );
ALTER TABLE x ADD PRIMARY KEY ( a );
-or- like this in 9i:
CREATE TABLE x ( a NUMBER, b NUMBER );
CREATE UNIQUE INDEX x_pk ON x ( a );
ALTER TABLE x ADD PRIMARY KEY ( a ) DISABLE;
ALTER TABLE x MODIFY PRIMARY KEY USING INDEX x_pk ENABLE;
This is OK but if the table was an IOT the separate create index step is not possible and you will be left with an index with a system assigned name [i.e. not exactly the same as the original].
In this case, the generated DDL would look something like:
CREATE TABLE x ( a NUMBER, b NUMBER, PRIMARY KEY ( a ) ENABLE ) ORGANIZATION INDEX;
TOYS does not attempt to generate PL/SQL blocks to determine the system assigned name and rename the index. Of course, you are free to perform a synchronization using the original snapshot in which case TOYS will re-name the index.
A similar outcome happens if you re-name the primary key of an IOT and leave the underlying index with a system generated name. If it encounters this case, TOYS will omit the primary key's name from the CREATE TABLE statement leaving you to re-name it later. An alternative approach would be to create the IOT's primary key with it's user assigned name. Unfortunately this incorrectly names the underlying index to be the same as the primary key and introduces a [be it very small] chance of failure [i.e. an index name clash].
The point of all this is that nothing is perfect but you are unlikely to be affected by any minor "hiccups" in the generated DDL.
Is TOYS internationalised? In other words, can it handle say French and German?
TOYS has not yet been internationalised. Thus text and labelling on all scripts, reports, forms, dialogs and messages issued by TOYS are in English. However, if you connect to an Oracle server with a NLS setting specifying French then any error messages returned by Oracle will be in French [courtesy of Oracle, not TOYS].
Having said that, TOYS does use Unicode to handle all character data. See question How does TOYS handle non ASCII character sets?
How does TOYS handle non ASCII character sets?
All character data is handled internally by TOYS as UTF-16 encoded Unicode. Meta-data (snapshots) are saved in Unicode (UTF-8). HTML reports are also written in Unicode (UTF-8). Because, UTF-16 is used internally to handle Unicode strings, Unicode 3.1 and above is supported. This means that the supplemental characters [mostly Asian] that were introduced in Unicode 3.1 are supported.
When working with TOYS you mostly don't have to worry about character sets. There are however a couple of areas where the issue of character sets raises it's ugly little head.
Character data is written to Windows in the current Windows character set. Thus if your Windows environment specifies a character set that cannot display French characters and your meta-data contains French characters then these French characters will not display correctly. Typically they are displayed as some type of question mark. The actual characters are preserved internally in TOYS it is just that they cannot be displayed. Note that this is meta-data we are talking about. That is the names of database objects, definitions of views, triggers, procedures, etc. We are not talking about the data contained within your application's tables.
By default, DDL (SQL) scripts are written in the current Windows character set. Thus, similarly to the situation above, the French characters will be replaced by a system replacement character most likely invalidating the script. TOYS will notify you that a translation error has occurred but cannot fix your script. It is simply that the character cannot be represented in the chosen character set.
Having said that, most developers use 7-bit ASCII when naming their database objects, writing stored procedures, etc. and thus will not have these problems. For those who insist on using other than 7-bit ASCII, you will need to be very careful in your selection of character sets.
For example, if you are generating DDL scripts for a database having a French character set then you need to write the script using a character set (say ISO-8859-1) that can handle the French characters.
TOYS will allow you to do this when generating the script. To choose a different character set encoding other than the Windows default, you simply select a different character set from the Encoding combo box. You may choose from any character set installed on your Windows system.
Note that internally, TOYS stores all meta-data as Unicode (UTF-16 encoded) so it is only the point of creating the DDL script that the choice of target character set becomes an issue!
If you are targeting a UNIX / LINUX system then choosing UTF-8 encoding is often a good choice. You must of course have UTF-8 as your UNIX character set.
SQL*Plus [under Windows] always assumes that the input file is encoded in the current Windows character set.
Since the database snapshot files are XML can I edit these?
It is not intended that you edit the snapshot files, however, there is nothing to prevent you from doing so. It is very easy to corrupt an XML file making it malformed or invalid. Having said that, an XML editor can ease the pain. You must of course understand the XML document structure. That is, the structure of the database snapshot files.
If you must edit these snapshot files, then email us for a copy of the DTD. Since, we believe that the DTD will not be stable [at least initially] we do not want to publish the DTD just yet.
As the DTD evolves, we will provide conversion tools to facilitate migration of archived snapshots. These tools will translate the snapshot files from one version to another. They will not help anyone who writes programs or creates XSLT scripts that directly process the XML snapshot files.
Note that the XML snapshot files are UTF-8 encoded and if they contain any non 7-bit ASCII characters, you may find some regular text editors get confused. If you use MS Notepad to edit any XML snapshot files containing any non 7-bit ASCII, Notepad will automatically detect they are UTF-8 encoded and when you save the modified file it will prepend the three UTF-8 bytes (0xEF 0xBB 0xBF) as the Unicode Byte-Order Mark (BOM). Microsoft uses a BOM character as a signature for all Unicode files. TOYS happily accepts the BOM but some other tools might not!
As a further note, you can have TOYS include the Unicode Byte-Order Mark (BOM) automatically when it creates any UTF-8 encoded file. You enable this by choosing the menu option View -- Options, then select the Files tab. Finally, check the oppropriate file type in the option group Add BOM to UTF-8 files. If you check this option for scripts it will be ignored for encodings other than UTF-8.
We have not found too many editors that recognize the BOM on UTF-8 files. Notepad and Vim both do. SQL*Plus does not.
When I describe a table in SQL*Plus or look in the schema report generated by TOYS some table columns are shown as NOT NULL. However, when I generate a DDL creation script, TOYS generates a create table statement with the column allowing nulls. What is going on?
The column is actually defined in the database without a NOT NULL constraint. SQL*Plus and other tools query the "current nullability state" from the USER_TAB_COLUMNS catalog view (or similar). This is the current state and equates to the combination of the column having an enabled NOT NULL constraint OR the column being included in an enabled primary key. The case we are discussing is one where the column does not have a NOT NULL constraint but it is included as one of the columns in an enabled primary key. If you disable the primary key, the column will show as nullable. Given this, the correct way of defining the column is to define it as allowing NULLs. The definition of the table should also include the definition of the primary key [enabled in this case]. Defining the table in this way will [re-]create the table exactly as per it's definition.
Suppose you create a table as follows:
CREATE TABLE x ( a NUMBER PRIMARY KEY, b NUMBER );
Now if you describe the table in SQL*Plus you get:
SQL> desc x
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B NUMBER
SQL*Plus shows the column as "NOT NULL". What does this mean? Suppose you now disable the primary key as follows:
ALTER TABLE x DISABLE PRIMARY KEY;
Now describing the table in SQL*Plus you get:
SQL> desc x
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER
SQL*Plus shows the column as nullable. Remember that we have not changed the column. We simply disabled the primary key [of which the column was part of]. Let us now constrain column "A" to be NOT-NULL as follows:
ALTER TABLE x MODIFY a NOT NULL;
So now if you describe the table in SQL*Plus you get:
SQL> desc x
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B NUMBER
What this demonstrates is that the current nullability of the column is not the same as having a NOT-NULL constraint on the column.
Wait there's more! Many people think that CHECK constraints of the form CHECK (column-name IS NOT NULL) are the same as NOT-NULL constraints. They are not! Yes they do the same thing but they are handled differently within the Oracle server. For example suppose we do the following:
CREATE TABLE x ( a NUMBER CONSTRAINT x_c01 CHECK(a IS NOT NULL), b NUMBER );
Now if you describe the table in SQL*Plus you get:
SQL> desc x
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER
SQL*Plus shows the column as being nullable. But if we attempt to insert into table "X" we get:
SQL> INSERT INTO x VALUES (NULL, NULL);
INSERT INTO x VALUES (NULL, NULL)
*
ERROR at line 1:
ORA-02290: check constraint (TEST.X_C01) violated
SQL>
So we have SQL*Plus [correctly] showing the column as being nullable, but when we attempt to insert NULLs into column "X"."A" we get a constraint violation. Note however that it is a CHECK constraint and not a NOT-NULL constraint.
A foreign key is defined in the database with a delete rule of SET NULL but is shown in the schema report generated by TOYS (and generated DDL scripts) as having a delete rule of NO ACTION. Is this a bug?
This particular problem is a bug in the USER_CONSTRAINTS catalog view (also DBA_ and ALL_) in Oracle 8.0 and 8i. This bug defines the delete rule as CASCADE or NO-ACTION. That is, the SET-NULL action was not decoded. This has been fixed in 9i.
If you have the privilege SELECT ANY TABLE when capturing the schema meta-data (in 8.0 or 8i) then TOYS can retrieve directly from the base catalog tables (e.g. SYS.OBJ$, SYS.TAB$, SYS.USER$, etc.). Not only is this faster but it avoids known bugs in the user catalog views (such as this one). This assumes you do not disable use of the base table catalogs as follows:
Choose the menu option View -- Options, then select the DB Capture tab. Finally, click one of the options for Use Base Tables. You may select one of Never -or - For all databases prior this version -or -Always. If you select the middle option then you need to enter an Oracle version string (2 or 3 segments only, e.g. "9.2.5").
Normally you do not need to change this setting. The default is to use the base catalog tables.
Work-arounds (for 8.0 and 8i):
Use an Oracle login with SELECT ANY TABLE privilege when capturing the meta-data (this assumes that you do not disable use of the base table catalogs).
-- OR--
Have your DBA perform the capture and send you the saved snapshot (as XML). You can load this into TOYS. Just remember that if you compare it against your own snapshots taken from an Oracle 8 database then yours will be missing the ON DELETE SET NULL details!
-- OR--
Fix the DBA_CONSTRAINTS, ALL_CONSTRAINTS and USER_CONSTRAINTS catalog views. Try Oracle for a fix. The expression that decodes the DELETE_RULE should look like this:
decode(c.type#, 4,
decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),
NULL),
A column's default was removed in the latest version of my schema. When I synchronized my existing database, TOYS modified the column as follows:
ALTER TABLE employee MODIFY salary DEFAULT NULL
Now when I difference the updated (working) database with the original (reference) database, I get a difference in the column's data default. In the reference database, the column has no default where as in the working database it shows as "NULL". Why?
An expression of "NULL" for a column's default is not [exactly] the same as not having a default value. Some other [SQL92 compliant] RDBMSs provide a means of dropping a column's default. In a RDBMS that is fully SQL92 compliant, a column's default is dropped as per the following example:
ALTER TABLE employee ALTER COLUMN salary DROP DEFAULT
However, the Oracle server [as of 9iR2] does not support a DROP DEFAULT construct!
We thought that dropping the table (or even the column) to remove the default was a little drastic. The above method of "dropping column defaults" was implemented as the default method by TOYS because it is efficient and DML operations on the table work identically to actually dropping the column's default. However, reverse engineered DDL and schema comparisons may show this as a minor difference. Note that other differences in this table may trigger a re-build of the table, in which case, TOYS will remove the data default from the column in the new table. More precisely, the table is re-created without the column default.
If you wish, you can enable the option that forces TOYS to always re-build tables when the data default is dropped and the RDBMS does not support the DROP DEFAULT option for the alter column command [i.e. Oracle 7 thru Oracle 9iR2].
You enable this option by choosing the menu option View -- Options, then select the Synchronization tab. Finally, check the option Force Drop of Column Defaults.
TOYS issued the following error message:
TOYS-49: Cluster "HR"."PERSON" has the same name as a table or view. You need to enable base table queries so that this can be resolved.
I have set the option Use Base Tables to Always so what is the problem?
The use of base tables for retrieving meta-data is discussed in another FAQ (see What is the purpose of the option View -- Options -- DB Capture --> Use Base Tables...).
Assuming that you have SELECT ANY TABLE privilege [Oracle 7 and 8] or have SELECT ANY DICTIONARY privilege [Oracle 9], then what is most likely happening here is that you are using an older version of TOYS against a very recent version of Oracle and the use of base tables has been automatically disabled. Unfortunately you have also named the cluster "PERSON" the same as either a view or a table. The Oracle catalog view DBA_TAB_COLUMNS does not contain an OBJECT_TYPE column which is required to determine whether to associate the retrieved column with "PERSON" cluster or the "PERSON" table/view. The base tables contain the required information but your version of TOYS is not going to use it. You need to download the latest version of TOYS which will know about your Oracle version and will use base table queries.
I tried to capture a schema and got an error DATA-CORRUPTION-ERROR: DBI: Error on retrieval of character data-type. Corrupt or invalid UTF-8 sequence. What does this mean?
As discussed in the FAQ question How does TOYS handle non ASCII character sets? and in the FAQ question What is the purpose of the option Oracle UTF-8 Encoding and why should I change it from DEFAULT?, TOYS passes all data to / from the Oracle server as UTF-8. TOYS translates the UTF-8 sequences into Unicode (UTF-16 encoded) which it uses internally. TOYS will raise the above error if it encounters invalid UTF-8 sequences in the data passed to it by Oracle.
So how do we get invalid UTF-8 sequences into an Oracle database?
The most common cause is the move towards UTF-8 as the database character set. This is a good idea but unfortunately there appear to be implementation issues which need to be resolved. Basically, if the character set on the client is set to the same as the character set on the server then Oracle does not validate that the character data passed to it is actually valid. This is not much of a problem with single byte character sets because who cares about a few wrong characters? With multi-byte character sets like UTF-8 the waters muddy.
As an example, let us assume that our database character set is UTF8 and we are running SQL*Plus on Windows and we like UTF-8 so much that we set our NLS_LANG setting to UTF8. This must be a good idea because it avoids Oracle having to do any translations. Right? Err..., should be, but no. In this case it is wrong! If we can guarantee that the client will only pass valid UTF-8 sequences to Oracle then this would be fine.
Because the database has a UTF8 character set and your client is UTF8, there is no translation taking place. That is, the Oracle client realizes it does not have to perform character set conversions [ i.e. UTF8 <==> UTF8 ] and therefore passes the data straight through. This is exactly what we wanted, efficiency!
Now suppose you execute a DDL script via SQL*Plus. SQL*Plus always assumes that files that it reads [or writes] are in the operating system's character set. It reads your script file and basically passes the DDL statements straight to Oracle. No translation is performed. And just to save us some time, Oracle does not perform any validation either! If the file contains characters that have code-points greater than 0x7F (decimal 127) you are going to have problems. SQL*Plus will pass these characters straight to Oracle and Oracle stores them assuming they are valid UTF8. But we know they ain't! Anyhow, now we have a corrupt database but let's look on the bright side ... we did it efficiently!
So what now? Well if the above data is meta-data and you try to capture a schema that has corrupt meta-data, TOYS is going to complain. If the corrupted item is an identifier, TOYS will tell you the internal query that failed, tell you the column and the row number of the offending data and provide you with a dump [up to 256 bytes] of the corrupt data item and finally TOYS will tell you to bugger off! If the offending item is a table / column comment or a piece of program unit source, TOYS will notify you about the offending item and ask if you wish to continue or abort the capture.
Note that detection of corrupt UTF-8 sequences only happens when the database character set is one of the UTF-8 character sets and TOYS is set to use the same UTF-8 character set. That is, Oracle knowing that the character sets are identical simply passes the UTF-8 data straight through. On the other hand, if the character sets are different, translation is required, so Oracle [at least in 9iR2] silently replaces the corrupt UTF-8 sequences with the Unicode replacement character. This of course silently corrupts your snapshot.
So you still need convincing? Examine the table below which describes the UTF-8 encoding scheme and you will quickly realize why any 8-bit character with a code-point in the range 0x80 .. 0xBF is not a valid UTF-8 byte sequence yet these are used by the Latin character sets (and others).
UCS* (ISO 10646) / Unicode to UTF-8 mapping
| Unicode | | UTF-8 |
Start
Char | End
Char | Data
Bits | Binary Byte Sequence
(x - data bit) |
| 0 | 7F | 7 | 0xxxxxxx |
| 80 | 7FF | 11 | 110xxxxx 10xxxxxx |
| 800 | FFFF | 16 | 1110xxxx 10xxxxxx 10xxxxxx |
| 10000 | 1FFFFF | 21 | 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx |
TOYS has been working fine for months. Now when I try to capture multiple schema it raises the following error. Why?
Multiple schema capture is not available in the free edition. Please choose a single schema only.
The free edition of TOYS has a grace period of 120 days in which all available features are enabled. After the grace period, the additional features are disabled. You should download the latest version of TOYS to obtain a fully enabled program. The Help document provides a full description of the features that are available in the free and commercial editions of TOYS.
TOYS Frequently Asked Questions
Email our Frequently Asked Questions team