Often DBAs are often forced to perform object reorganization, also calledonline application maintenance, while the database is in use. Oracle’s online table reorganization feature lets you modify a tables structure while users continue to use it online. Oracle lets you perform both in-place object reorganization where you modify database objects without making an intermediate copy of the objects and copy-based reorganization where you first make changes to intermediate objects.Online table and index reorganizations use the copy-based approach.
Oracle Database 11g offers better online redefinition cap abilities that go a long way to reducing the headaches we all had to endure during most redefinition projects.The following sections summarize the new online redefinition capabilities.
Support for New Objects
Oracle Database 11g provides support for redefining more types of objects online.
Enhanced Online Index Creation and Rebuild
It is usually much faster to rebuild an index online rather than to drop and re-create it.Even though Oracle has provided an online index creation and rebuilding feature for quite a while, the results of trying to rebuild an index online were un predict able in a real-life production environment.Although Oracle claimed that the online create/rebuild process applied exclusive locks on the underlying table only during the beginning and ending of the index creation/rebuild process for “brief” periods of time, it was common to experience a considerable slowdown in the execution of DML statements by the rest of the users in the database.
In Oracle Database 11g,Oracle finally provides a real online index creation and rebuild feature by making it unnecessary to apply an exclusive lock (X) on the underlying tables.Instead, the database will use shared exclusive locks (SX) on the objects, thus enabling DML to continue uninterrupted while the DDL is executing.The changing of the locking strategy applies to the following types of statements, among others.
None of the commands listed here requires exclusive locks in Oracle Database 11g.
Enhanced Default Column Value Functionality
When you add a new column to a table,that column initially has null values. You can add a column with a not null constraint only if the table has new rows or if you supply values for the new column by specifying the default clause.If you specify the default clause while adding the column, however,Oracle immediately issues an update for each row with the default value for the new column. During this row update, the database applies an exclusive lock on the table, thus limiting access to other users.
In Oracle Data base 11g,when you add a column with a default value, Oracle doesn’t immediately update the rows of the table. Let’s use a simple example to drive home the value of this important new feature.
First, let’s add a new column called BONUS to the employees table:
Before the Oracle Database 11g release,the database would let you add the column without a problem, but there was a price to pay. Since the table isn’t empty, Oracle applies an exclusive lock on the table to update all rows in the table with the default value for the new column you just added.If the table had a large number of rows, this meant that the database generated a huge amount of undo and redo logs,and in general, theperformance of the database was affected.In Oracle Database 11g, anydefault values you specify for a column you specify as not null are stored in the data dictionary instead of the table.This, of course,means the addition of a new column is almost instantaneous, thus eliminating a major hurdle for DBAs in adding new columns to tables.All the addition of the new column involves is updating the data dictionary once, with a single value denoting the default value of the new column.This is true regardless of the size of the table.In addition, you have the big advantage of not using any space for adding the default values for the new column.
Online Reorganization of Materialized Views
Until now, if a table contained materialized views, that table was ineligible for an online table reorganization, unless you were prepared to drop and re-create dependent materialized views and materialized view logs for those tables.
In Oracle Database 11g, you can perform an online reorganization of tables with materialized views and materialized view logs.You simply clone the materialized view logs on the interim table, with the procedureDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS, just as you would other objects dependent on the table being reorganized, such as triggers and indexes.After redefining a table with a materialized view log, you must make sure you completely refresh the materialized views at the end of the table reorganization, since the ROWID logs are invalidated during the table reorganization.
Minimal Invalidation of Dependent Objects
In previous versions of the Oracle data base,during an online redefinition, the database automatically invalidated all dependent views and PL/SQLpackages.In Oracle Database 11g, all dependent objects aren’t automatically invalidated any longer.The database marks as invalid only those dependent views, synonyms, andother objects that are logically affected.If an object that’s referenced has identical column names and types following the online redefinition, its status continues to remain valid.Previously,for example,if you dropped a column from a table that you wereredefining online,all procedures and views that depended on the table were invalidated,even when it was not logically required.Under the new concept of a fine-grained dependency management, object dependencies are tracked only at the level of an element within each unit.Now, Oracle invalidates only the subset of proce dures and views that actually use the dropped column.Note that the new object-invalidating behavior doesn't affect triggers, because all triggers on a table that’s being redefined online are invalidated as before.
Oracle Database 11g uses more precise dependency metadata than in previous releases, called fine-grained dependencies.In earlier releases, object dependency metadata was recorded with the precision of the entire object; for example,view V depends on table T.If the view V depended on just the first three columns of the table T and you added a new column to table T, the view would be invalidated, even though the new column didn’t logically affect it.In Oracle Database 11g,the view V will not be invalidated when a change in an object doesn’t directly affect the view.For example,a column addition such as the one explained here won’t result in the invalidation of the view.
Oracle also uses similar fine-grained dependency management for PL/SQL objects such as procedures and functions.In current releases, if you add a new element or change an element in a PL/SQL package, you invalidate all the procedures in that package. In Oracle Database 11g, a new program element invalidates other proce dures or functions only if that object has a specific dependency on the changed or new element.
Fine-grained dependency management leads to an overall reduction in the invalidation of objects following changes to objects, and you don’t need to configure anything to avail of this new feature.You’ll have enhanced application availability especially when you’re upgrading applications.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.