As businesses have become more web and e-commerce-enabled over the years, high availability computing solutions, in general, are becoming increasingly critical to businesses because of the extremely high cost of application downtime. According to a Gartner Group study, the cost of outages for corporations involved in e-commerce can easily reach millions of dollars per hour. Therefore, database availability and manageability have become increasingly important in today’s mission-critical environments. While unexpected outages often capture headlines, the most common cause of downtime is routinely application and database maintenance. One simple way to enhance availability and manageability is to allow users continuous availability to the database during routine data reorganization operations.
Challenges with online operations:
- As part of application requirements, changes to tables ( logical or physical) are occasionally required.
- Changes to tables often lead to application downtime
- Oracle DB has multiple options available to perform online operations.
- Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.
- Online table redefinition enables you to modify a table in several different ways while the table remains online.
The Online Reorganization and Redefinition feature in Oracle Database offers administrators unprecedented flexibility to:
- Modify table physical attributes and transform data and table structure while allowing users full access to the database.
- Improve data availability, query performance, response time, and disk space utilization, all of which are important in a mission-critical environment
- Make the application upgrade process more accessible, safer, and faster.
- Execute using Enterprise Manager or SQL*Plus command-line interface.
Online table redefinition was introduced in Oracle 9i as part of database availability features. We can redefine Oracle tables online with the DBMS_REDEFINITION package. Use Oracle Database online maintenance features to significantly reduce (or eliminate) the application downtime required to make changes to an application’s database objects.
Some of the use cases of Online Reorganization and Redefinition
- Convert BasicFile LOBs to SecureFile LOBs
- Modify the storage parameters of a table
- Add, modify or drop one or more columns
- Add or drop partitioning support
- Change partition structure
- Convert to IoT (or reverse)
- Add attribute clustering to a table
- Optimized bulk updates
Stay tuned for my next blog post on Online Reorganization and Redefinition. I will discuss various aspects of Online redefinition, including the benefits, examples, demos, considerations, best practices, and where this powerful feature fits into the overall Oracle Database Maximum Availability Architecture.
Additional resources
Here are some additional resources to help you get started with DBMS Online Reorganization and Redefinition