How to automate source control with Oracle database

I work in an Oracle instance that has hundreds of schemas and multiple developers. We have a development instance where developers can integrate their work before test or production.

We want to have source control for all the DDL run in this integrated development database. Currently this is done through a product Red Gate which we run manually after we make a change to the database. Redgate finds the changes between what is in the schema and what was last checked into source control and makes a script of the differences and puts this into source control.

The problem however is of course that running regdate can take some time and people run it infrequently or not at all for small changes. Also redgate will only look in one schema at a time and it would be VERY time consuming to manually run it against all schemas to guarantee that they are up to date. However if the source controlled code cannot be relied upon it becomes less useful…

What would seem to be ideal would be to have some software that could periodically (even once a day), or when triggered by DDL being run, update the source control (preferably github as this is used by other teams) from all the schemas.

I cannot seem to see any existing software which can be simply used to do this.

Is there a problem with doing this? (there is no need to address multiple developers overwriting each others work on the same day as we have this covered in a separate process) Is anyone doing this? Can anyone recommend a way to do this?

Answer

We do this with help of a PL/SQL function, a python script and a shell script:

• The PL/SQL function can generate the DDL of a whole schema and returns this as CLOB
• The python script connects to the database, fetches the DDL and stores it in files
• The shell script runs the Source Control to add the modifications (we use Bazaar here).

You can see the scripts on PasteBin:

• The PL/SQL function is here: http://pastebin.com/AG2Fa9zL
• The python program (schema_exporter.py): http://pastebin.com/nd8Lf0gK
• The shell script:

The shell script:

python schema_exporter.py
d=$(date +%Y-%m-%d__%H_%M_%S)
bzr add
bzr st | grep -q -E 'added|modified' &&  commit -m "Database objects on $d"
exit 0

This shell script is configured to run from cron every day.