Monday, February 04, 2008

Hear hear...

I give a presentation on "worst practices" from time to time




One of the many sections in there regards source code control/configuration management (remember, this is a worst practice talk, not a best practice - so the slides are meant to be ironic, the opposite of what is true)


In there I talk about how many people don't seem to consider their "database schema" (including code for goodness sake!) to be something that you put under source code control.  Nope, you just let everyone in there and let them make changes - in the database directly. 


They consider database code to be less than code - not worthy of being under any sort of change management.  That is why I liked this blog entry I read this morning.

They got it right - dead on.

Think about it...



Blogger Cristian Cudizio said....

Great Tom! Thank you for giving us such link. My job is related other than to DBA also to administer application deployment. The guidelines for database version control explained by Scott Allen are in my opinion very very interesting.

Mon Feb 04, 11:58:00 AM EST  

Anonymous Sokrates said....

Tom absolutely agree !

,it is only 2 mouse-clicks to
".. one of the best tools you can purchase to help you in get your database under version control where it belongs":

(take the very first hyperlink to
and from there the first hyperlink again to Microsoft's tool)

where is Oracle's support for Schema-Version-Control ?

Could you please add some links for us ?

Mon Feb 04, 12:27:00 PM EST  

Blogger Thomas Kyte said....


you can use a tool from anywhere (eg: you would not necessarily goto Sun for java source code control...)

We do have the CM pack the CM pack but use whatever you already are using for the most part (plsql code, it is, well, code. .sql scripts are - well, code)

Mon Feb 04, 12:38:00 PM EST  

Blogger Denis said....


It would be great if version control was built into the database. At least simple things like an ability to ask any object in the database (via data dictionary or supplied package or whatever) - "What version are you?" And build in version specification into the DDL commands like CREATE and ALTER + provide a database parameter to have an ability to make it mandatory, so no database change is made without specifying version as part of the modification command. Or even make versioning automatic, so every time and object is altered, it's version is incremented in the data dictionary.

It would also be great to have an ability to say "Hey, here is definition of version X of object Y, could you either bring the object in the database to this version or give me delta in a form of DDL or XML document, etc." Obviously not all changes can be made using this approach as many cases require scripting a migration path, but I think it is possible to at least build a defined framework within the database for it, so a product/process can be easily built on top of it. I.e. the version control of the database would be standartised by the database!

I think DBMS_METADATA package was a step in the direction I am very briefly outlining here, but I am not sure what vision is driving it and how far it will go?.. We had a chat about it during your seminar in Dublin a couple of years ago and I remember you saying that nothing was being planned along those line at that time. I wonder if anything has changed since? or can you tell?

Thank you.


Mon Feb 04, 04:23:00 PM EST  

Blogger Thomas Kyte said....


watch soon for details of stuff coming....

Mon Feb 04, 04:28:00 PM EST  

Blogger Gary Myers said....

Can I add that commenting out old code and putting comments around new code does NOT constitute version control...despite what I've seen at some places

Mon Feb 04, 04:37:00 PM EST  

Anonymous Vadim Tropashko said....

And what is "version control system" may I ask? It is a specialized database! So instead of suggesting that code should be kept under some kind of external source control system, it is oracle RDBMS that should be enhanced to have a decent version control system functionality.

Mon Feb 04, 05:05:00 PM EST  

Blogger Thomas Kyte said....


that is just like saying "and what is an HR system, it is a specialized database!"

"and what is LDAP..." (we built one of those, it is a product, an application however, not a core feature of a general purpose database)

"and what is (anything) but a specialized database"

No, this argument doesn't work.

Mon Feb 04, 05:31:00 PM EST  

Anonymous Anonymous said....

Individuals and interactions over processes and tools.

Well, doesn't that mean we should listen to gurus and "get things done" rather than dig deep into testing and verifying?

Mon Feb 04, 08:29:00 PM EST  

Blogger Thomas Kyte said....

@last anonymous person

what is the logic you used to get from that statement to "we don't need to test and verify"?

Mon Feb 04, 08:54:00 PM EST  

Blogger Dominic said....

Perfect. Exactly what I'm speaking on at the Hotsos Symposium this year :-)

Presentation Title

Release Management for Database Applications


While everyone likes to develop fresh versions of the hottest code, the reality is that our code lives on much longer than we would like sometimes. An ability to accurately identify the time line of changes to a database and package them for delivery into multiple testing environments is crucial to maintaining the integrity of custom database applications.

There are many commercial tools which purport to address this problem, but most of them do not integrate well into a standard source-code control and configuration management process. This paper will discuss techniques for managing disparate development, system test, quality assurance and production database images throughout a system life-cycle beyond initial development. Several approaches to change management along with their pros and cons will be discussed

Mon Feb 04, 09:58:00 PM EST  

Anonymous Anonymous said....

@ dennis

Check this link

Tue Feb 05, 05:54:00 AM EST  

Anonymous Anonymous said....

Tom how do you do version control on asktom??

Can you share the information with us

Tue Feb 05, 06:58:00 AM EST  

Blogger Thomas Kyte said....

@last anonymous person

the asktom application is very small, the application is just exported (from apex) and that is stuffed into source code control. the .sql scripts (well, the schema really hasn't been modified since dirt was invented) are as well.

And then that is extracted from source code control to be executed against the real system.

Tue Feb 05, 07:01:00 AM EST  

Blogger Arun Mathur said....

Hi Tom,

I was wondering if I can get your opinion on something:

Assuming you need to "install" a database, and you have two components, both of which are maintained well under versioning control:

1) A database export (verified that it can be imported into a database)
2) A master script that can be run via SQL*Plus ie a script generated and tweaked via the showfile option.

Do you have any preference on which of the two you would use?


Tue Feb 05, 04:31:00 PM EST  

Blogger Thomas Kyte said....


the export would likely be an OK way to go - nice, self contained, no paths to mess with...

I've seen both done, the sqlplus script is by far the most used, since you can sort of program it with PLSQL and deal with errors. The dmp file would be problematic for error handling.

Tue Feb 05, 04:45:00 PM EST  

Anonymous Mark Brady said....


One link away from the blog you linked to was a post about database development. The first rule listed was,

1. Never use a shared database server for development work.

The author didn't make it clear whether he meant code development or schema development. Too often these two overlap -- while writing code you realize you need a new column.

I think that multiple schema changes occuring to individual copies could get quite confusing, whereas code is, just as you say, code. All the same challenges apply.

He lists lots of "tools" supposed to make this easier. Most seem to do nothing but perform schema diffs. I think diffs are a bad idea for promotion - not every change goes every release.

Perhaps the answer/opinion I'm looking for is "new question" under the AskTom rules. Maybe you'd consider a new blog entry?

On a seperate note, I'm salivating over the idea that I could potentially add custom properties to schema objects. I don't know why this hasn't been done before. A single note field and only for tables and columns seems so 1970's.

Wed Feb 06, 01:52:00 PM EST  

Blogger Thomas Kyte said....


... I think that multiple schema changes occuring to individual copies could get quite confusing, whereas code is, just as you say, code. All the same challenges apply. ...

schema changes are code. Just like in the old days when you pulled the cobol copy book to modify a structure, or you updated a C header to change a struct.

Wed Feb 06, 01:56:00 PM EST  

Anonymous Galen Boyer said....

Hi Tom,

I couldn't agree anymore strongly on source code control. The thing that I would like to point out is that the worst offenders of not following this source control creed are the DBAs. Most environments I've been in for that past 10 years have had source code control for all of the database code in "development" but I have yet to see an environment where the DBA's code is under the same form of protection as well as scrutiny. And I'm talking even simple stuff like, the init.ora being in source control.

Sat Apr 05, 09:52:00 AM EDT  

Blogger javor said....

Hi Tom,

Keeping schema definition and its changes as any other source code under version control is exactly something what I'm trying to achieve in a database-oriented development project. And in general I'm looking for ideas how to setup a good configuration management framework.

Here are a few of my current key ideas:
(1) Try to keep all source code related to the project in a centralized source code repository.
To be precise - in addition to "source code" (PL/SQL, shell scripts, C++, Java,...) I mean also documentation, DDL definitions, etc. The general idea is that all this stuff is related should be versioned as a whole (instead of keeping RDBMS in one side, Java source at other, TCL sources, bash scripts, docs, etc...: having those scattered doesn't seem nice).
So in this respect having a general purpose version control system vs some just-RDBMS oriented one seems to be the better. And my tool of choice here is currently Subversion.

(2) Keep objects in version control repository modularized in separate files.
The idea here is that if I change a single piece of my code; or if I alter a single table - this should be easy to trace in the version control system after I commit my changes there. I.e. we should have separate source files for the individual modules (i.e. single file per table; single or two files per package, etc.). In this respect having a whole-database or whole-schema export dump stuffed into Subversion as single dump file doesn't seem nice: how in this way could I trace all the changes which happened to table X or to PL/SQL package Y!

following these simple at first sight two points doesn't always seem so easy.
* There are always some tools (e.g. like design tools, OWB, etc.) which save their definitions to a single bundled large binary file; or even in RDBMS repository. Putting these under version control doesn't seem to be so straightforward as C source files.
I'm still thinking about what would be the best way to manage such objects - for now I'm tending to load them in separate repository and keeping some link in the main one (this somehow defeats point (1) but otherwise the main repository will be "bloated" with some large binary objects which don't add much value in tracing what has changed).

* PL/SQL and other DDL (tables, views, etc.): seems straightforwardto script those to sql (or Sql*Plus) but...
- Different developers use different development tools. Usually the object is created in a database and then saved from the tool to file(s). The problem is that there is no standard, and different tools format their sources differently. E.g.: For PL/SQL: some tools use single file for package spec/body; other tools - separate; some tools place a "/" at source end (e.g. sqlplus compatible); others - not. And with table DDL it's even worse - some tools don't support some features completely (partitioning, compression, etc.); some add unnecessary to keep physical attributes (initial & next extent).
- Customization of export DDL to files is not flexible enough in tools which I've seen so far.
For example we have range partitioned tables (for different historical periods) and we have a convention to have a default PDEFAULT partition values less than MAXVALUE; a new DAYyyymmdd for data relevant to different days. When exporting this table's DDL to file - I want to be able to avoid saving all DAYyyymmmdd partitions, saving just the PDEFAULT one is enough. Tools ususally support some general options like "ignore physical attributes" and similar but this doesn't seem enough.
IMO - would be good if Oracle provided some API to manage metadata, to filter unnecessary stuff, perform changes, to specify formatting rules, etc. DBMS_METADATA seems to provide some such feature but again - it's not flexible enough and it doesn't format results very nicely (at least not in sync with our coding conventions).

* Promoting changes from version control repository to production Oracle database.
I'm still researching the options here and looking for best solution but seems we'll have to create some build scripts, or patching tools. Some challanges here:
- Ability to deploy only selected changes in non-destructive manner. Ultimately DDL source is not exactly same stuff as usual source code (e.g. C, Java): wiping out previous version and compiling new one over it doesn't seem nice if you have data in table which structure you want to change. And in fact you have complete "CREATE TABLE ..." DDL in the version control but change can be performed with just "ALTER ...". I haven't found yet a tool which to parse the two "CREATE TABLE..." DDL's and to propose an "ALTER TABLE ...".
- Currently I'm thinking about to employ Oracle's change management pack - unfortunately change sync is not available in 11.1. What I see in 11.1 is just dictionary comparison which doesn't generate "delta" change scripts.

So I tried the still available in 10.2 console change management pack:
-- Doesn't properly work with local bitmap indexes: it attempted to create bitmap index on target partitioned table without "LOCAL" keyword which failed.
-- Not much flexibility again in specifying what options to ignore and which changes from all detected to actually apply. (Well, being able to change the generated TCL script is nice but this could be a time consuming task copared to writing self-made patch script).
-- In my test: instead of just ALTER-ing table definition, it generated something like that: (1) rename original table; (2) create new one with the new definition; (3) Load the saved in step 1) data in to the new table. Would be nice if we could avoid such copy since we can have hundreds of gigabytes in the production table... And reloading data could take much time - i.e. table won't be available for use with consistent data until that finishes.

So in conclusion - putting Oracle RDBMS schema into version control and configuration management framework is still more challenging compared to managing "traditional plain source code".


Sat Apr 11, 11:28:00 AM EDT  

Blogger zinc losangeles said....

@ Javor

In Oracle 11.1, you can compare two database objects by leveraging the metadata stored about them and generate alter statements as your "differences output".


SELECT dbms_metadata.compare_alter('TABLE','OBSERVATIONS', 'OBSERVATIONS', 'SCHEMA1', 'SCHEMA2')
FROM dual;

This will generate a set of ALTER TABLE SCHEMA1.OBSERVATIONS sql commands to modify, rename, drop, and add columns and constraints to transform the table structure exhibited in SCHEMA1 to match SCHEMA2.

With the addition of database links, you can even compare objects residing in different databases.

Please see the Oracle 11g docs on DBMS_METADATA to learn more.

Thu Nov 05, 11:53:00 AM EST  

Blogger Gary Myers said....

Couldn't find compare_alter in the 11gr1 docs but 11gr2 does document it under dbms_metadata_diff

Wed Nov 25, 05:16:00 PM EST  


<< Home