Oracle Database Migration
Prepare, migrate and optimize your Oracle schemas and PL/SQL code with Visual Expert
Migrating an Oracle database to a newer version is a complex project. It's not just about transferring data: you also need to adapt a large volume of PL/SQL code, clean up obsolete elements that have accumulated over time, and ensure no regressions are introduced. Oracle developers and DBAs must therefore exercise extra vigilance at each step of the project.
Fortunately, specialized tools can make the job much easier. Visual Expert offers a combination of tools to help you tackle each phase of an Oracle migration with confidence. Visual Expert is a static PL/SQL code analysis tool, ideal for exploring, understanding and documenting existing code.
In this article, we'll take a look at the typical stages of an Oracle migration, and for each one we'll examine the difficulties encountered and how this tool helps to overcome them, thanks to specific features.
Let's take the example of an Oracle 11g database containing mainly internal PL/SQL code (stored procedures, triggers, etc.), migrated to Oracle 19c (or 21c).
Step 1: Assessing the volume of code and objects
Objective: establish a clear scope for analysis and estimate the migration workload.
Challenges
The first step in any migration project is to assess the magnitude of the work. You need to inventory all database objects (tables, views, indexes...) as well as all PL/SQL code (packages, procedures, functions, triggers). This volume estimate is used to plan the workload and necessary resources.
Without tools, this means manually browsing the Oracle catalog and PL/SQL code, which is tedious and error-prone. An Oracle database can contain thousands of objects and millions of lines of code. Getting an accurate global view from the start is a challenge for architects and project managers.
Automatic inventory of code and objects
Once the database has been analyzed, Visual Expert can calculate detailed code metrics: total number of lines of PL/SQL code (distinguishing between comments and actual instructions), number of procedures and functions, number of packages, triggers, etc. It also lists objects by type.
Database Modeling
Visual Expert can also be used to model the existing database. It automatically generates an entity-relationship diagram that gives an idea of the complexity of the data model: number of tables and relationships, breakdown into functional sub-domains, etc.
An architect will thus be able to see whether certain parts of the model are very dense (many interconnected tables) or isolated. In this way, he or she can assess which areas of the model will be the most critical to migrate (a denser area often implies more associated logic).
Step 2: Cleaning up the code
Objective: eliminate unused items to reduce technical debt and risks.
Challenges
Before migrating, it's wise to sort through the code and objects: why migrate elements that are no longer used (old procedures no longer called, forgotten temporary or test tables, obsolete views or synonyms, etc.) to the new version.
Migrating them would consume time for nothing and increase the risks (e.g.: an obsolete package may contain code incompatible with 19c).
However, manually detecting unused objects is very difficult in a large database. Developers are reluctant to delete code for fear of breaking hidden functionality. As a precaution, they often migrate everything, at the cost of unnecessary effort and a heavier technical debt.
Dependency analysis for dead code detection
Visual Expert helps with this task by listing the "dead code" in the Oracle database. With this information, the team can decide which objects will not be migrated to Oracle 19c, thus eliminating unnecessary elements.
A complete static analysis of dependencies allows to identify potentially unused PL/SQL objects (those never referenced/called by other procedures, triggers, or known external applications).
Unused tables and cleanup suggestions
It can also spot tables never used by the code (never referenced by SELECT/INSERT/UPDATE/DELETE queries), suggesting that they may not be (or no longer) exploited by the application.
Finally, it also flags other elements to clean up: empty procedures, duplicate code to consolidate, etc.
Read More:
Step 3: Identify obsolete components to be replaced
Objective: identify depreciated technologies to be replaced before migration.
Challenges
Besides unused elements, the team will need to handle necessary but obsolete elements.
Over time, Oracle deprecates certain features and introduces new ones. For example, the DBMS_JOB function has been superseded by DBMS_SCHEDULER and is no longer promoted; certain data types like LONG have been made obsolete in favor of CLOB/BLOB. It is therefore recommended to replace these elements to avoid keeping legacy and unsupported technologies.
The difficulty lies in systematically detecting all places where these obsolete elements are used in the database schema and code. An oversight can easily go unnoticed (e.g., a LONG type column hidden in a technical table). Developers and DBAs must therefore be thorough in their detection to plan the necessary replacements. Manually scanning thousands of lines of code or browsing the Oracle dictionary is not a realistic solution. Tools are therefore necessary.
Visual Expert Solutions
In-depth dependency analysis
Visual Expert excels at this task thanks to its in-depth dependency analysis: A complete static analysis of dependencies allows to identify potentially unused PL/SQL objects (those never referenced/called by other procedures, triggers, or known external applications).
- You can list all procedures, triggers, functions, packages, subprograms, and types that reference obsolete objects such as Oracle function, packages, subprograms.
- You can also list tables and columns based on a deprecated type, such as LONG or DBMS_XMLGEN packages etc.
- Visual Expert also includes an automatic code inspection engine, whose rules alert you to the use of obsolete elements.
Visual Expert acts as a compatibility scanner, sifting through existing code in accordance with best practices, to avoid unpleasant surprises during testing.
Design modernization suggestions
By modeling the data, Visual Expert helps identify design archaisms that should be modernized to take advantage of improvements provided by Oracle. For example:
- Some tables use a composite identifier instead of a unique sequence/identity identifier.
- Some relationships are not declared by a constraint (historically handled by code).
- Perhaps some very long, unrelated text fields could be converted to CLOB?
In this way, Visual Expert provides food for thought on structural changes to be made in addition to code changes. The diagram can then be annotated with the planned modifications (new relationships, new types). These elements will be used in the next stage of impact analysis
Step 4: Impact analysis of changes to be made
Objective: anticipate all the consequences of change to avoid regression.
Challenges
Once you know what to change (functions to replace, columns to modify, etc.), you need to assess where these changes will have an effect. This is impact analysis, crucial to avoid breaking anything during migration.
- For example: replacing an obsolete function means modifying the code in all the procedures that call it. But these procedures may in turn be called elsewhere. You'll need to check the consequences of all these modifications.
- Similarly, changing a LONG column to CLOB will require you to modify the procedures that manipulate this column, and to check the upstream/downstream components (views, interfaces, etc.).
Without a tool, mapping these impacts is laborious: you must manually follow the thread of multiple dependencies (who calls what, who uses which table/column). The team's fear is to miss a reference, and discover the problem in testing or production, in a piece of code that had not been updated.
Visual Expert Solutions
Interactive impact analysis
Visual Expert answers questions like "What happens if I change X?" with an interactive impact analysis:
If you select an object (a function or a column, for example) and run the impact analysis, Visual Expert lists all the objects that reference it.
For example: "If I modify the 'customer_name' column, which procedures, triggers, views and packages will be affected?" or "Who calls function XYZ? Which scripts reference it?
Dependency visualization
The tool can represent dependencies in the form of E/R diagrams or matrices. For example, its CRUD matrix cross-references data with the functions that manipulate them, specifying the type of operation (Create/Read/Update/Delete) performed by each procedure.
In seconds, Visual Expert accomplishes what would take hours of manual work. Thus, the team prepares each change (estimating the time needed, listing objects to modify) and also eliminates the risk of oversight: no hidden dependency will be ignored.
Step 5: Making the changes
Objective: apply changes consistently and securely.
Challenges
After the analysis phase comes the change phase (code refactoring, schema modifications).
While Visual Expert is an analysis tool, not an editing tool (modifications will be made with your standard code editor), it still provides valuable assistance during implementation.
Visual Expert Solutions
Modification tracking dashboard
The team can use it as a dashboard for tracking modifications. After making a change, the new code can be analyzed to verify that no occurrences of the old element remain. This quality check can be performed periodically during implementation.
Code and schema comparison
Visual Expert also offers a code/schema comparison function that can be used between the initial schema and the migrated schema. The tool understands the structure of code and objects, and generates a report of structured differences: objects deleted or added, changes in procedure code, changes in column types, etc. This checklist enables you to ensure that each planned point has been dealt with, and to detect any unintentional deviations.
Step 6: Technical documentation for the migrated database
Objective: automatically generate complete, up-to-date and usable documentation.
Challenges
Once the migration is complete, you'll need to produce up-to-date technical documentation. This will serve both as an inventory of fixtures for maintenance purposes, and as a record of the changes made.
But manually documenting a schematic and thousands of lines of code is not realistic, and time is often short at the end of a project. It's time-consuming, unprofitable and prone to errors. It is therefore essential to automate documentation.
By default, the documentation must include:
- Database structure (schematics, diagrams),
- Data dictionary (columns, types),
- PL/SQL code (list and description of modules, parameters, calls),
- And possibly before/after migration comparisons.
Visual Expert Solutions
Automatic code documentation
Visual Expert automatically generates code documentation. From its analysis repository, it can produce HTML pages describing the application in detail. Newcomers to the team won't have to guess how the code works; they can consult this repository.
- For each package/procedure/trigger, it will specify: its formatted source code, a list of its parameters, an explanation, as well as its references (e.g. "this procedure calls such tables, is called by such other object..."). These references are clickable hyperlinks in the documentation, making it navigable like a website.
- Specific reports can also be generated: for example, a list of all objects modified during migration with their new status, as well as a CRUD matrix or call diagram to illustrate interactions between components.
Data model documentation and business logic
Visual Expert is also an asset for post-migration data model documentation.
- Once the migration is complete, he will update the entity-relationship diagram to produce an up-to-date visual diagram of the entire system.
- This diagram can be adjusted: for example, it can be divided into a number of thematic sub-diagrams, which are easier to consult. Each sub-diagram can cover a functional area (billing, customer management, etc.).
- Once the diagrams are satisfactory, Visual Expert can export them as PDFs or high-resolution images for integration in the final documentation.
Explaining and commenting code with AI features
To go beyond a technical documentation Visual Expert can also:
- Explain the business purpose of the code
- Describe its inner logic.
- Generate comments in the code
By combining an overview of the data model, a detailed code documentation, and functional explanations, Visual Expert covers all aspects of the database.
The effort is minimal thanks to these tools, which improves the overall quality of the project, without overloading the team.
Step 7: Post-migration optimization
Objective: improve the performance and maintainability of the migrated database.
Challenges
A successful migration doesn't stop at the version upgrade. The database must also function optimally. However, moving to a higher version can reveal unsuspected problems. Migration is also an opportunity to address performance issues that were not addressed in the previous version (missing indexes, slow queries, etc.).
These post-migration optimizations are sometimes overlooked for lack of time. So we need to identify the most urgent ones: which of the hundreds of queries and procedures should be optimized first?
Visual Expert Solutions
Performance analysis
Visual Expert integrates a performance optimization module that collects and analyzes execution statistics generated by Oracle. Correcting these post-migration problems will improve database performance and maintainability.
- It can identify the procedures with the longest average execution times, or those executed most frequently. It can also indicate the time consumed by each SQL within a procedure. This information can be used, for example, to target the 10 most costly queries.
- It also analyzes performance in relation to code structure: it breaks down processing including strings of calling functions, to generate a graph of calls with execution times. This makes it possible to visually identify which functions are slowing down processing:
Code quality and optimization
- Visual Expert also checks code quality and detects certain bad practices affecting performance (e.g. use of nested loops in PL/SQL, instead of SQL joins).
- It automatically detects missing indexes (database columns without indexes, but which are nevertheless used in Where/Group by/Order by/Having clauses in SQL queries), unnecessarily slowing down SQL query execution.
- Finally, Visual Expert can be used to analyze the slow queries it has found: the DBA can display the query execution plan to see how it is processed by Oracle (index traversal, complete scans, hash joins, sorting, etc.). In this way, he can quickly move from analysis to action for SQL optimizations.
- When an object or query slows down the application, Visual Expert can optimize its code:
This combination of functions will improve application performance, whether as part of an overall preventive approach, or to specifically eliminate certain bottlenecks.
You can also use Visual Expert to measure performance before and after migration, to assess the real impact of the optimizations you've made.
Step 8: Ongoing post-migration monitoring with Visual Expert
Objective: Maintain PL/SQL code quality and performance over the long term.
Challenges
Once the migration has been completed, it is essential to preserve the technical gains. Visual Expert helps secure, document and control the evolution of Oracle schemas and PL/SQL code over time.
These post-migration optimizations are sometimes overlooked for lack of time. So we need to identify the most urgent ones: which of the hundreds of queries and procedures should be optimized first?
Visual Expert Solutions
Proactive maintenance
- Systematic impact analysis before each change
It is recommended to systematically perform an impact analysis before each significant change in the Oracle database and PL/SQL code. This practice ensures comprehensive consideration of dependencies and minimizes regression risks. - Regular performance checks
You can monitor execution times of procedures and SQL queries to detect and quickly address new problems. They could appear, for example, following query modifications that would require the creation of new indexes to avoid degrading performance. Visual Expert will detect and report these cases to the DBA who can take the necessary measures.
Quality control and issue resolution
- Regular verification of code quality and security
Visual Expert can automatically and periodically inspect code to detect the appearance of quality or security problems. Teams could, for example, be using a deprecated Oracle data type or function out of habit. This regular control will help maintain a high level of quality and security over time. - Fixing issues found in the code
For each problem found in the code, Visual Expert can suggest a potential solution - Documented tracking of schema and code changes
Visual Expert will keep track of all changes made to the schema and PL/SQL code, making version management and traceability of changes much simpler.
Automated Code Inspection Summary
This continuous monitoring reduces maintenance costs and secures future evolutions, enabling teams to concentrate more on post-migration functional improvements.
Conclusion
Migrating an Oracle 11g database to 19c/21c is a complex project that can be greatly simplified with the right tools. With Visual Expert, development and administration teams have access to a complete toolbox covering code analysis, schema management, modeling and documentation.
Every stage of the migration process is made easier: precise identification of the scope, clean-up, adaptation to new features, controlled modifications, technical documentation and ongoing optimization.
After the initial migration, Visual Expert continues to provide substantial support with regular impact analyses, performance checks and periodic code quality controls. These features guarantee the long-term stability, quality and performance of the Oracle database, and ensure a sustainable return on investment for the migration project.
By combining Visual Expert with code editing tools, and with rigorous management of project steps, technical teams can successfully conduct ambitious migrations while reducing risks, securing developments, and facilitating future maintainability of their Oracle database.
Summary table by stage
Oracle Deprecated and Desupported Features
Stay informed about Oracle features that are deprecated or removed in recent versions. Use this list to anticipate necessary changes during your migration projects.
Explore the full list deprecated and desupported Oracle features (18c - 23c)