Oracle Database 10g: The Top 20 Features for DBAs

Over the last 27 years, Oracle has made tremendous improvements in its core database product. Now, that product is not only the world's most reliable and performant database, but also part of a complete software infrastructure for enterprise computing. With each new release comes a sometimes dizzying display of new capabilities and features, sometimes leaving developers, IT managers, and even seasoned DBAs wondering which new features will benefit them most.
With the introduction of Oracle Database 10g, DBAs will have in their hands one of the most profound new releases ever from Oracle. So, DBAs who take the time to understand the proper application of new Oracle technology to their everyday jobs will enjoy many time-saving, and ultimately, money-saving new capabilities.
Oracle Database 10g offers many new tools that help DBAs work more efficiently (and perhaps more enjoyably), freeing them for more strategic, creative endeavors—not to mention their nights and weekends. Oracle Database 10g really is that big of a deal for DBAs.
Over the new 20 weeks, I will help you through the ins and outs of this powerful new release by presenting what I consider to be the top 20 new Oracle Database 10g features for database administration tasks. This list ranges from the rudimentary, such as setting a default tablespace for creating users, to the advanced, such as the new Automatic Storage Management feature.
In this series, I will provide brief, focused analyses of these interesting new tools and techniques. The goal is to outline the functions and benefits of the feature so that you can put it into action in your environment as quickly as possible.
I welcome your thoughts, comments, and questions about this series. Enjoy!

Schedule
Lesson 1—Flashback Versions Query
Lesson 2—Rollback Monitoring
Lesson 3—Tablespace Management
Lesson 4—Oracle Data Pump
Lesson 5—Flashback Table
Lesson 6—Automatic Workload Repository
Lesson 7—SQL*Plus Rel 10.1
Lesson 8—Automatic Storage Management
Lesson 9—RMAN
Lesson 10—Auditing
Lesson 11—Wait Interface
Lesson 12—Materialized Views
Lesson 13—Enterprise Manager 10g
Lesson 14—Virtual Private Database
Lesson 15—Automatic Segment Management
Lesson 16—Transportable Tablespaces
Lesson 17—Automatic Shared Memory Management
Lesson 18—ADDM and SQL Tuning Advisor
Lesson 19—Scheduler
Lesson 20—Best of the Rest

ORA-30393: A query block in the statement cannot be rewritten

ORA-30393: A query block in the statement cannot be rewritten


There may be situations where you want to stop the query from executing if it did not rewrite.Oracle Database 10g provides a new hint called REWRITE_OR_ERROR. This is a query block-level hint. When the Query cannot be rewritten, the query will throw error and stop.The error is ORA-30393: A query block in the statement cannot be rewrittenREWRITE_ON_ERROR hint is good for when the failed rewrite may cause unexpected lengthy execution. It stop and report error instead

Typically, when the rewrite failed and query stop. You should use EXPLAIN_REWRITE procedure of DBMS_MVIEW PL/SQL package to troubleshoot why the failed mview rewriteExplain the MVIEWSQL> execute dbms_mview.EXPLAIN_REWRITE -( query => 'SELECT s.prod_id -, sum(s.quantity_sold) -FROM sales s -GROUP BY s.prod_id' -, mv => 'SH.SALES_PROD' -, statement_id => 'EXPLAIN_REWRITE demo' -);Check the Explain resultSQL> SELECT messageFROM rewrite_tableWHERE statement_id = 'EXPLAIN_REWRITE demo';