DBMS_REDEFINITION
No
database is 100% self-reliant or
self-maintaining, which is a good thing for DBA
job security. However, the last few major
versions of Oracle have greatly increased its
self-diagnostic and self-monitoring
capabilities. Only database structural
reorganization remains one of those tasks best
left to the DBA to decide when it is appropriate
to perform and when to schedule its execution.
That is because data is the life blood of any
modern organization, and while doing various
database reorganizations, the following
possibilities exist
-
The
process could blow-up mid-stream, so data
may be left offline
-
The process is
resource-intensive and takes significant
time to execute
-
Data could be
momentarily inconsistent between key steps
-
Probably advisable to
consider doing a backup operation just prior
to
The key point is that
structural reorganizations are generally
important events in any database’s life cycle.
Even when a reorganization activity can
theoretically be performed entirely online with
little or no downtime, it is often a safer bet
to perform any such activities in a controlled
environment. Because the one time something that
can not go wrong does, the DBA will be in a
better situation to resume or recover if there
are not frantic customers breathing down his
neck. So schedule any reorganization event with
extreme caution and over- compensation.
Now
with all
that said, Oracle provides a robust and reliable
package for performing many common online table
level reorganizations – dbms_redefinition. Much
like the dbms_metadata package,
dbms_redefinition provides an almost limitless
set of use cases or scenarios that it can
address. Many people will probably just use the
OEM graphical interface, but
here is
a very common example that should fulfill as
key need as well as serve as a foundation
for one’s own modifications. The following are
the key basic steps:
1.
Verify that the table is a
candidate for online redefinition
2.
Create an interim table
3.
Enable parallel DML
operations
4.
Start the redefinition
process (and do not stop until step 9 is done)
5.
Copy dependent objects
6.
Check for any errors
7.
Synchronize the interim
table (optional)
8.
Complete the redefinition
9.
Drop the interim table
A
common question is what is happening behind the
scenes here? In other words, how and what is
Oracle doing? Essentially, the redefinition
package is merely an API to an intelligent
materialized view with a materialized view log.
So a local replication of the object shows while
the reorganization occurs. Then it refreshes to
get up-to-date for any transaction that occurred
during reorganization.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
