Oracle 10g New Features
Not to be outdone by previous releases, Oracle
Database 10g offers a large number of new features. In this section
we will summarize a number of the new features in Oracle Database
The SYSAUX Tablespace
The SYSAUX tablespace is a secondary
tablespace for storage of a number of database components that were
previously stored in the SYSTEM tablespace. It is created as a
locally managed tablespace using automatic segment space management.
Many Oracle features require storage (such as
the RMAN recovery catalog, Ultra Search, Data Mining, XDP, and
OLAP), and often these are stored in disparate tablespaces. This
increases the management responsibility of the Remote DBA. The SYSAUX
tablespace consolidates these tablespaces into one location, which
becomes the default tablespace for these Oracle features.
When you upgrade to Oracle Database 10g, you
will have to create a SYSAUX tablespace. When you create a new
Oracle database, Oracle creates the SYSAUX tablespace for you by
default. Oracle provides a new view, V$SYSAUX_OCCUPANTS, to
manage occupants in the SYSAUX tablespace. This view allows you to
monitor the space usage of occupant application objects in the
SYSAUX tablespace, as shown in this example:
Automated Storage Management
Oracle Database 10G introduces
Automated Storage Management (ASM), a service that provides
management of disk drives. ASM can be used on a variety of
configurations, including Oracle9i RAC installations. ASM is
an alternative to the use of raw or cooked file systems.
ASM offers a number of features, including:
Simplified daily administration
The performance of raw disk I/O for all ASM files
Compatibility with any type of disk configuration, be
it JBOD or complex SAN
Use of a specific file-naming convention to name
files, enforcing an enterprise-wide file-naming convention
Prevention of the accidental deletion of files, since
there is no file system interface and ASM is solely responsible for
Load balancing of data across all ASM managed disk
drives, which helps improve performance by removing disk hot spots
Dynamic load balancing of disks as usage patterns
change and when additional disks are added or removed
Ability to mirror data on different disks to provide
Support of vendor-supplied storage offerings and
Enhanced scalability over other disk-management
Note: Iíve seen a few bugs associated with
ASM. Iíd move to it carefully.
Oracle Database 10G offers some new
features to help you collect database statistics. These new features
include automated collection of statistics, collection of data
dictionary statistics, new behaviors associated with the
dbms_stats package, and new features related to monitoring
tables in the database.
Oracle Database 10G is a big departure
from previous releases of Oracle insofar as Oracle recommends
that you analyze the data dictionary. You can collect these
statistics by using either the dbms_stats.gather_schema_stats
or dbms_stats.gather_database_stats Oracle-supplied
Oracle Database 10G also offers two
new procedures in the dbms_stats Oracle-supplied package. The
dbms_stats.gather_dictionary_stats procedure facilitates
analysis of the data dictionary. The
dbms_stats.delete_dictionary_stats procedure allows you to
remove data dictionary stats.
New Table-Monitoring Behaviors
Oracle Database 10G enables global
table monitoring by default. This feature is controlled via the
statistics_level parameter (statistics_level was
available in Oracle9i). When statistics_level is set
to TYPICAL (which is the default setting) or ALL, then global
monitoring is enabled. When the statistics_level parameter is
set to BASIC, global monitoring is disabled.
Flushing the Buffer Cache
Oracle Database 10G now allows you to
flush the database buffer cache with the alter system command
using the flush buffer_cache parameter.
Oracle Database 10G offers a brand new
job-scheduling facility, known as The Scheduler. The Scheduler is
controlled via the new Oracle Database 10G supplied package
dbms_scheduler, which replaces the dbms_job package.
The new scheduler offers much added functionality over the
dbms_job package. The Scheduler enables you to execute a variety
of stored code (such as PL/SQL), a native binary executable, and
User-Configurable Default Tablespaces
Oracle offers user-configurable default
tablespaces in Oracle Database 10G. Once you configure a
default user tablespace, all new users will be assigned to that
tablespace rather than the SYSTEM tablespace.
Tablespace Groups and Multiple Default Temporary
Oracle Database 10G now allows you to
define tablespace groups, which are logical groupings of
tablespaces. This further allows you to assign temporary tablespaces
to those groups, and then assign this tablespace group as the
default temporary tablespace for the database. In essence,
tablespace groups allow you to combine temporary tablespaces into
one tablespace pool that is available for use to the database.
You have been asking for it, I have been
asking for it, and now itís here! Oracle Database 10G
includes the ability to rename tablespaces. You use the alter
tablespace command with the rename to parameter.
The drop database command can be used
to drop your database. Oracle will drop the database, deleting all
control files and all datafiles listed in the control file. If you
are using a SPFILE, then Oracle will remove it as well. Only a user
with SYSRemote DBA privileges can issue the statement and the database must
be mounted (not open) in exclusive and restricted mode.
If you use LOBs in your database (NCLOB,
BLOB, or CLOB), then you will be happy to know that the limits on
LOBs have been increased in Oracle Database 10G. The new
maximum limits are calculated at (4GB Ė 1 byte) * (the database
block size). Thus, if the database block size is 8KB, there is
essentially a 32GB limitation on LOBs in that database. Note that
Bfiles are limited to 4GB in size.
Shrinking and Compacting Segments Online
Oracle Database 10G now allows you to
manually shrink the space in a table, and by default compacting the
segment and adjusting the high water mark all at the same time. This
all can occur online, and the recovered space will be released to
the database after the operation is complete. You can optionally
compact the segment and then shrink it, in two separate alter
operations. Most Oracle segment types can be shrunk and/or compacted
including tables (partitioned or not), IOTís, and indexes.
ADDM/Oracle Database Control/AWR and the
Oracle Database 10g has a totally new
management/performance tuning infrastructure. The AWR (Advanced
Workload Repository) is like statspack on steroids. It collects
database performance statistics automatically, out of the box. The
Automatic Database Diagnostic Monitor uses AWR and other database
features to monitor the database and provide reporting and error
notifications to the Remote DBA. The Advisors assist you in making
performance related database changes.
Oracle Database Control is the newest
incarnation of Oracle Enterprise Manager, and is by far the best.
Automatic SGA Tuning
Oracle is making new strides to make managing an
Oracle database instance easier. Oracle now allows you to set one
parameter, sga_target, and the server itself will manage the
primary SGA memory component allocations.
Sorted Hash Clusters
Oracle10i offers a sorted hash cluster, which
ensures rows will be stored within the hash cluster in the order
defined when you created the hash cluster. This can eliminate the
overhead associated with sort operations that might otherwise be
required since data will be returned in a guaranteed order.
Virtual Private Database New Features
Oracle Database 10g includes improvements to
Oracleís Virtual Private Database (VPD). New features include the
- Column level privacy
- VPD customization
- Shared policies
- Support for Parallel Query
Auditing the SYS User
Oracle Database 10g adds a new parameter,
AUDIT_SYS_OPERATIONS, that allows users connected as SYS to be fully
audited. When AUDIT_SYS_OPERATIONS is set to TRUE (FALSE is the
default) then audit records for SYS will be written to an operating
system file rather than the Remote DBA_AUDIT_TRAIL table (or SYS.AUD$).
General Database Recovery Improvements
Several changes have been made in Oracle10g
that relate to backup and recovery. These include:
Easier recovery though the resetlogs command.
The new alter database begin backup command.
Changes to the alter database end backup
Changes to the Alter Database Archivelog Command
When you issue the alter database archivelog
command in Oracle10g, archiving will be started by default. Thus,
you donít need to use the log archive start command.
Oracle10g comes with a just a ton of
improvements (I like that word, plethora!) for RMAN. These include
The Flash Recovery Area
Using the catalog and uncatalog commands
Dropping a database in RMAN
Unregistering a database in RMAN
Making and Using RMAN backup copies
Configuring default disk backup types.
Changes to incremental backups.
Recovering datafiles not backed up.
Changes in error reporting.
Compressing RMAN Backups
RMAN Related TSPITR changes.