Overview on some of the new features in Oracle Database 10g

Oracle has introduced several important features with its Database 10g version. Some of them described below include Flashback enhancements, PL/SQL Enhancements, SQL Enhancements, Automatic storage management, Performance and tuning features, Security and Auditing enhancements, Job Scheduler, Data Pump export/import utility, Backup and Recovery Enhancements and other miscellaneous enhancements.

Oracle Database supports a variety of tables. They are the relational tables, object relational tables, Index organized tables, External tables, Partitioned tables, Materialized views, Temporary tables, clustered tables and Dropped tables.

The Dropped tables has been introduced as of Oracle Database 10g where tables that are dropped can be recovered using the flashback table to before drop command. As of Oracle Database10g, dropped tables retain their space and can be accessed by means of the recycle bin. To remove the table from the recycle bin, the purge clause has to be added to drop table command. The TIMESTAMP_TO_SCN (SCN is the system change number) and SCN_TO_TIMESTAMP have been added to SQL and PL/SQL to make flashback operations simpler.

In Oracle 10g, the process of PL/SQL native compilation has been simplified as compiled shared libraries can now be stored in the database and extracted when necessary. They need not be manually maintained and are part of Real Application Cluster (RAC) configuration. Oracle10g introduces support for FORALL syntax for non consecutive indexes in collections datatype. Also the new IEEE BINARY_FLOAT and BINARY_DOUBLE are very efficient for floating point computations.

Nested tables in Pl/SQL support lots more operations. Collections can be assigned to one another and compared easily with enhanced operators and set operations. Warnings that are encountered while compiling can be produced by setting the PLSQL_WARNINGS parameter at either instance or session level. Oracle 10g supports regular expressions in SQL and PL/SQL where regular expression rather than literals can be used as search strings.

The MERGE statements include a number of amendments that increases its flexibility. It’s MATCHED and NOT MATCHED clauses are made optional, conditional inserts and updates made possible and it also has an optional DELETE WHERE clause.

The SQL * Plus enhancements in Oracle Database 10g includes support for white spaces in Windows Path and File names, APPEND, CREATE and REPLACE extensions to SPOOL and SAVE , the SHOW RECYCLEBIN option etc.

The UTL_COMPRESS facility package gives an Application Programming Interface facility to allow compression and decompression of binary data. The UTL_MAIL package provides a simple API to allow email to be sent from PL/SQL without knowledge required for the underlying protocol.

The Automated storage management (ASM) facility introduced as of Oracle Database 10g automates the arrangement of datafiles and other operating system files by distributing them across all accessible disks. It thus simplifies database administration. Oracle Database 10g includes automatic performance diagnostics and tuning features too.

These are the Automatic Optimizer Statistics Collection, Automatic Workload Repository (AWR) which is an extended version of the STATSPACK repository, Automatic Database Diagnostic Monitoring (ADDM) which is a diagnostics and tuning tool that uses information stored in AWR and automatic SQL Tuning Advisor that enables SQL tuning .The Automatic Shared Memory Management puts Oracle in be in charge of of allocating memory within the system global area (SGA).

In Oracle Databse10g features pertaining to Virtual Private Databases (VPD) enhancements, Fine Grained Auditing (FGA) which now includes support for DML statements apart from queries and Data encryption wherein the DBMS_CRYPTO package is easy to use and contains cryptographic algorithms are some features that play an important role to enhance the database security.

Oracle Database 10g has a extensive scheduler (DBMS_SCHEDLER) which enables robust task management. It comprises several components and extends the functionality provided earlier by the DBMS_JOB package.

Oracle Data Pump feature in Oracle Database 10g is a faster and more flexible option to the Export and Import utilities that existed in previous versions of Oracle. It extends the basic functionalities by providing a PL/SQL API and support for external tables.

Oracle also has several new features in the 10g release that increase the manageability of Recovery manager (RMAN ) and resolves performance and functionality issues faced with earlier releases. Some of these features include the ability to span resetlogs operations during recoveries, use the restore database preview command to preview the backup needed for recovery, ability of providing faster incremental backups, merged backups and compressed backups.

Thus Oracle Database 10g release has many new features that can benefit the application. Some of these features may contain only diminutive changes but still have dramatic impact on the application or even programming approach. It is necessary to review the latest features whenever possible to determine how best the application can benefit in terms of functionality and performance.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |