Key DBA Skills – Change Scripts
Change scripts are a key DBA skill for all individuals responsible for deploying change to an existing database. The reasons are pretty obvious. Unlike code, which can simply be replaced with a newer version, a database cannot be modified without also modifying the data.
You can’t drop a table and replace it with a new table definition without also dropping the pre-existing data the table contained. So, how is this done?
- Alter the table in place
- Create a new table with additional new features rather than modifying the existing table
- Use a temporary table as an interim step
Sometimes you can alter a table in place. Many times you can do this if you are adding a new column to the table, and it is not important what order the columns are created in the table. You can also use this technique if you are extending the data type such as making an INT column into a BIGINT, or making character column longer. This doesn’t always work.
The second technique is where you create a new table with the same key primary as the original table, adding new columns to the new table. Of course you may have to join this new table to the original to capture the data. But, it does follow the open/closed principle of software development. Your old software should continue to work, and new software can be extended with the new table.
The final technique is the most complicated. A new table with the desired data structure is created. Data is copied from the old table. Foreign key constraints have to be dropped. The old table is dropped or renamed. The new table is renamed the same as the original table. Foreign key constraints have to be re-created. Indexes and statistics have to be re-created. Sometimes constraints have to be re-created to follow your naming conventions. They could not be created until the old table was dropped because of name conflicts.
Some tools automate this process better than others. Still, the software professional needs to understand what they are and how to deploy them during the development cycle so that changes to the production database are well tested and reliable.
Share your tips on change scripts by leaving a comment below, or sending an Email to btaylor@sswug.org,
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Database Tables Holding Data in Oracle XML DB Repository
This article illustrates how you might query the XMLType tables that hold the data available via Oracle XML DB Repository. In particular, you’ll look at how you might optimize XQuery queries that use functions fn:doc and fn:collection to reference the repository data, accessing the underlying storage tables directly instead.
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)