Thoughts on a lasting ORACLE architecture

Learn how to build and maintain a good ORACLE database

We all know, that data quality and availability are just two of a huge set of aspects when it comes to the value of data in an enterprise environment. While availability may be an issue to operations, quality and even more adaptability and maintanability is definitely adressed to development. Learn what topics have to be adressed, discussed and which rules need to be set up to begin with developing long-lasting architectures for maintainable data structures and storages.

These thoughts are primarily valid for OLTP applications, although some of them (especially naming conventions) are valid for DWH concepts as well (if you are on data vault 2.0, they are already inherited by the concept itself).

Naming conventions

How to setup naming conventions for all types of objects to get a maintainable environment.
This is your concrete floor to build your application on.

The good scripts, the bad and the ugly

Scripting is always needed. Either to deploy your objects, tweak them or bring them to production. Learn what a good script is about.

Good SQL practice

These lessons are less dealing with performance considerations but with maintainability and readability aspects.

Good PL/SQL practice

Learn how to build a sustainable PL/SQL framework with reusable components to decrease development efforts. Plan your packages, procedures and functions and reduce/eliminate side effects!

Work with views and triggers

Views and triggers can hide business logic or assist to implement them. Views and triggers can be used to implement the „last man standing“ in regards to data quality and security.

Set up basic quality reporting

All the efforts in implementing conventions should be monitored to keep a tidy environment. These lessons deal with easy reporting on your already set up ruleset.

Setup basic reporting on usage

If you want to report on a e.g. daily basis, how your application is used, identify hot spots or report due to regulatory needs, consider to build up logging and reporting features.

Implement a job framework

Wrote your jobs on your own and used CRON to execute on a regularly basis and used SENDMAIL? Better rely on a job framework to trigger ORACLE jobs. Jobs can be used to asynchronous GUI interaction as well.

How to deploy to an environment

How do one deploy to a non development environment? What to do to deploy to QS or any other consolidation system? What if I need different data sets in different environments? How should my deployment process and my software repository be organised?