René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

Oracle 10g

As of the time of this writing, I know only little about Oracle 10g's new features, but I intend to put them here as soon as I know more.
The G stands for Grid Computing. A common missconception seems to be that grid is just the new name for RAC (having improved RAC). This is not the case. 10g comes with both RAC and grid. One will be able to install 10g with RAC only or with grid only, without either and with both.
There is a profound difference between grid and RAC.
10g is said to have 149 new features. Possibly, the most popular will be the model clause and the transportable tablespaces.
The SQL*PLUS copy command will be deprecated.
SQL Model Clause. This will enhance SQL for calculations. SQL result sets can be treated like multidimensional arrays.
HTML DB (previously known as Project Marvel) will be a RAD environment for web-based applications.
PHP will be supported
SQL: Regular Expressions (finally), native numbers (based on IEEE 754), enhancements for LOBs, enhancements for collections.
It should be noted, however, that regular expressions were available through the owa_pattern package.
Data Pump replaces EXP and IMP. It provides high speed, parallel, bulk data and metadata movement of Oracle database contents across platforms and database versions. If a data pump job is started and fails for any reason before it has finished, it can be restarted at a later time.
ASM: automatic storage management.
Flashback database: old database block images are stored in a Flash Recovery Area which allow fast rollbacks of database (as no online redo logs are required.)
Flash Back Database makes it also possible to correct user errors: undropping tables.
Automatic Shared Memory Management is another self-management enhancement to Oracle. It includes a new parameter: sga_target.
A myriad of advisors:
  • SQL tuning advisor
    See also dbms_sqltune
  • SQL access advisor
  • Memory advisors
  • Segment advisor
    hanling space-related questions (predictions of table/index sizes, analzying growth trends)
  • Undo advisor
    Helping to estimate the size for undo.
The 'help' of the advisors is exposed through dba_advisor_findings, dba_advisor_recommendations, dba_advisor_rationale and many more.
An alert infrastructure complements these advisors. DBA's will be notified when a problem occurs and a recommondation for corrective actions will be delivered.
ADDM: Automatic database diagnostic monitor. ADDM enables Oracle to diagnose its own performance problems.
For example, ADDM identifies the most resource intensive SQL statements and passes that statement to the SQL tuning advisor.
AWR: automatic workload repository. AWR periodically gathers and stores system activity and workload data which is then analysed by ADDM.
Every layer of Oracle is now equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored.
trcsess is a tool that gathers SQL from sessions in a shared server environment.
statspack will be somewhat obsolete with 10g although it is still around. It's functionality is "taken over" by the Oracle kernel and the results can be made visible with enterprise manager (EM).

Misc changes

dbms_job is going to be replaced by dbms_scheduler.
Calendar Expressions (through / within???) dbms_scheduler : Yearly, Monthly, Weekly, Daily, Hourly, Minutely, Secondely.
New pl/sql data types: binary_float and binary_double (IEEE).
Default User tablespace. A default user tablespace allows to define a tablespace that will be the default tablespaces for users created afterwards. Those users will then not have the system tablespace as their default tablespace.
This concept is similar to that of default temporary tablespace in 9i.
Renaming tablespaces (even read only ones).
Larger LOBS: (4GB - 1) * default block size.
Bigfile tablespaces : create bigfile tablespace beeeg_ts data file '/o1/dat/beeeg.dbf' size 2T.
The sysaux tablespace.
Transportable tablespaces can now operate across platforms with convert tablespace.
The RBO is definitely dead and CBO is the only optimizer.
Three new operators enhance hierarchical queries: connect_by_root, connect_by_isleaf and connect_by_iscycle.
A new background process: MMON.
v$session now exposing the information of v$session_wait (event, p1, p2, p3)
spool now with three options: create, replace and append.
The sqlprompt can dynamically be set at runtime.
glogin.sql and login.sql executed also on connect.
Case and accent insensitive queries if parameter nls_comp = ansi and nls_sort = generic_m_ai. (See also On case insensitive queries in 10g.)
The GUI version of windows SQL*PLUS FINALLY removed.

Basic Init Parameters

Developing Oracle 10g

According to Andy Mendelsohn (senior vice president in Oracle's Database and Application Server Technologies group), one of the goals for 10g was to deliver a complete, integrated stack of software so as to make it possible for Oracle users to not have to depend anymore on third party software.
This lead to twelve (eleven??) development focus areas for 10g:
  • Application Development
  • Business Intelligence and Data Warehousing Clustering
  • Content Management
  • High Availability
  • Information Integration
  • Life Sciences
  • Location Services
  • Performance and Scalability
  • Security and Directory
  • Server Manageability
  • Windows


My special thanks go to Tanel Poder (of who has supplied me with a good bunch of new features and has allowed me to put it here.