Source-controlling Oracle databases
Source control (also known as version control or revision control) is the management of changes to a file or set of files.
Source control systems do many useful things:
- You can see a history of every change made to a file: what was changed, who changed it, and why
- You can easily revert to earlier versions of a file, so mistakes can easily be undone, and there's no risk in experimenting
- Teams can work on a single file without blocking each other or overwriting their changes
- Teams can work from a single, shared repository of files, without having to constantly send files back and forth – everyone works from the same page
Benefits of source-controlling databases
Normally, workarounds are needed to make sure developers have the latest database changes. That means discussing updates in person or email, and pausing development until everyone's up to date. Some development teams share work with backups and restores, or a custom in-house solution; these are usually time-consuming and subject to human error.
With source control, your team can quickly get the latest changes and commit their own. It's easy to track who changed what, when they did it, and why. If conflicts occur, you can compare the differences before committing a change.
Prevent and contain disasters
Without source control, it's easy for a database change to accidentally lead to an overwritten table, data loss or a bug, even in projects with single developers modifying a simple database. Again, the problem is amplified in teams.
Source control systems reduce the likelihood of disasters by increasing the transparency of the development process and helping you catch bugs early. If problems do occur, mistakes can be easily undone by reverting to a previous version.
Resuming partially completed work
Normally, resuming partially completed work can be slow; developers have to spend time understanding each other's code and the state of the work. For large or distributed development teams, this problem is amplified.
By allowing developers to easily document their changes in a source control system, teams can easily review the history of a project, getting up and running much more quickly.
Many organizations are required to comply with regulatory standards, including the Sarbanes–Oxley Act and the Statement on Auditing Standards No. 70: Service Organizations (SAS70) (both US). Databases contain critical data, so compliance auditors usually require organizations to account for every change to a database.
By source-controlling databases, it's easy to:
- manage how schema and data changes are made
- document schema changes
- maintain a detailed history of who made which changes
- document database schema and access permissions
- revert to previous versions if problems occur
Source control tools for Oracle
Red Gate's dedicated source control tool for Oracle databases, Source Control for Oracle, connects your database directly to your SVN or TFS source control system. By automatically storing a complete history of your Oracle database schema changes, Source Control for Oracle makes implementing source control easy.
Another Red Gate tool, Schema Compare for Oracle, lets you compare and deploy changes from the files under source control, useful for spotting changes, reverting to a previous version and deploying upgrades. You can learn more about this method in this article.