Oracle

Oracle Database 11g: The Top New Features for DBAs and Developers
by Arup Nanda Oracle ACE Director

In this multipart series, learn how important new features such as Database Replay, Flashback Data Archive, and SecureFiles work via simple, actionable how-to’s and sample code.
Change, although constantly present, is seldom risk-free. Even if the change is relatively minor (creating an indexfor example), your goal is probably to predict its precise impact as accurately as possible and then take appropriate action Many new change assurance (or “Real Application Testing, as Oracle calls it) features in Oracle Database ” 11g bring that dream closer to reality. The Database Replay tool, for example, allows you to capture production database workload and replay it in a test (or even thesame) database to assess the impact of change. Or consider SQL Performance Analyzer, which predicts the performance impact of changes to SQL before they are made. In my opinion, this Real Application Testing functionality alone justifies the upgrade. Overall, Oracle Database 11g makes database infrastructure far more efficient, resilient, and manageable. For example, very compelling new features inthe realm of partitioning ease the design and management of partitioned tables immensely. In this series (as in the previous series focusing on Oracle Database 10g), you will learn how these new features work via simple, actionable how-to’s and sample code. Enjoy the series, and the release!

Table of Contents

001

Database Replay Explore Database Replay, the new tool that captures SQLstatements and lets you replay them at will. Transaction Management Get an introduction to Flashback Data Archive and explore Enterprise Manager’s LogMiner interface. SQL Plan Management Use bind variables that pick the right plan every time and ensure a new execution plan is perfect before it’s used. SQL Access Advisor Get advice about optimal table design based on actual use of the table, not justdata. RMAN Explore Data Recovery Advisor, do parallel backup of the same file, and create and manage virtual catalogs. Automatic Storage Management Learn about new SYSASM role, variable extent sizes, and other ASM improvements. Caching and Pooling Explore SQL Result Cache, PL/SQL Function Cache, and Database Resident Connection Pooling. SecureFiles Explore next-generation LOBs: LOB encryption,compression, deduplication, and asynchronicity. Data Guard Query the physical standby database in real time without shutting down recovery, just for starters. Data Warehousing and OLAP Get a tour of new features in these areas, including Cube Organized MVs. Partitioning Learn about Referential, Internal, and Virtual Column partitioning; new sub-partitioning options; and more.

135

SchemaManagement Add columns with a default value easily and explore invisible indexes, virtual columns, and read only tables. SQL Performance Analyzer Accurately assess the impact of rewriting of SQL statements and get suggested improvements. PL/SQL: Efficient Coding Triggers that fire several times at different events and ability to force triggers of the same type to follow a sequence are some new gems.Security Learn about Tablespace Encryption, case-sensitive passwords, data masking, and other features. Manageability Explore automatic memory management, multicolumn statistics, online patching, and more features. SQL Operations: Pivot and Unpivot Present information in a spreadsheet-type crosstab report from any relational table using simple SQL, and store any data from a crosstab table to arelational table. Resiliency Explore Automatic Health Monitor, Automatic Diagnostic Repository, and other new resiliency features. PL/SQL Performance Explore in-lining of code, “real” native compilation, PLS timer, use of simple integer, and more. And Don’t Forget... COPY command, Export/Imports, Data Pump improvements, and more.

014

146

024

155

037

165

048

179

063

195...