Oracle9i New Features for Oracle8i DBAs
Roger Schrag, Database Specialists, Inc.

Introduction

This paper offers a brief overview of the new features in the Oracle9i database of most interest to database administrators. It is written for DBAs experienced with Oracle8i who are looking for a quick-read roadmap to what has changed and what is new in Oracle9i. For each item we'll just look at a few of the highlights so that we can keep this paper short. This should be enough to whet your appetite and give you an idea of which features and areas you want to learn more about.

In the first section we'll cover some of the fundamental changes in the Oracle9i database that a DBA with Oracle8i experience really needs to know about before working with Oracle9i. In the second section we'll look at many of the new features Oracle9i has to offer. The third section is a brief list of Oracle documentation that talks about Oracle9i changes and new features in greater detail.

The Oracle9i database has an amazing amount of new functionality. There are literally hundreds of new features--too many to list in a paper that claims to be a quick-read. In order to deliver the most value in the shortest amount of time, I've tried to keep this paper short, limiting coverage to the most significant changes and new features.

Changes In Oracle9i To Be Aware Of

• CONNECT INTERNAL and Server Manager are gone. Oracle has been saying for a long time that these would be desupported someday, and now they have.
  • Use CONNECT / AS SYSDBA or CONNECT username/password AS SYSDBA instead of CONNECT INTERNAL.
  • Use SQL*Plus to startup and shutdown Oracle from the command line instead of Server Manager.
• The Database Creation Assistant is more secure about passwords when a new database is created.
  • Most schemas start out locked.
  • A Password Management button allows you to set passwords and unlock accounts.
• You can no longer connect as SYS unless you connect AS SYSDBA.
• Security in newly created databases is expected to get tighter in future releases.
  • The SYSTEM schema might be eliminated altogether in a future release.
  • All initial schemas might start out locked in a future release.
• A "server parameter file" or SPFILE can be used to hold instance parameters instead of an init.ora file.
  • The SPFILE is a binary file; you can read it but cannot edit it. (An embedded checksum makes sure you don't make any manual changes!)
  • The SPFILE always resides on the server where the database is located. This is a nice feature in that the same SPFILE will get used whether you start the database locally or from a remote machine.
  • In 9i RAC systems, one SPFILE holds settings for all instances.
  • You can still use an init.ora file instead of an SPFILE, but you should specify PFILE= when starting the instance with an init.ora file. Without PFILE= in the STARTUP command Oracle will look for an SPFILE first, and will only use the init.ora file if it cannot find the SPFILE.
  • The ALTER SYSTEM command can be used to change dynamic parameters in memory or update the SPFILE or both. Static parameters can be updated in the SPFILE with the ALTER SYSTEM command. This is a key benefit of the SPFILE over the init.ora file.
  • You can create an SPFILE from an init.ora file and vice versa with the CREATE SPFILE and CREATE PFILE commands.
• "System managed undo" or SMU can be used instead of rollback segments.
  • With SMU you designate a tablespace as an undo tablespace and Oracle does all of the management.
  • Set undo_management = AUTO to use SMU. You'll need to set undo_tablespace to specify which tablespace to use for undo, and you might want to set undo_retention to the number of seconds Oracle should try to wait before overwriting committed undo information. (The default retention is 900 seconds.)
  • Set undo_suppress_errors = TRUE to cause Oracle to ignore statements like SET TRANSACTION USE ROLLBACK SEGMENT when SMU is being used.
  • Set undo_management = MANUAL (or don't set it at all) in order to use conventional rollback segments.
  • The entire database runs in SMU mode or manual mode, but not both at the same time. You must restart the instance to switch from one to the other.
  • When you create a new database, it will use SMU out of the box instead of rollback segments.
  • Releases 9.0.1.0 and 9.0.1.1 have serious SMU bugs. Users have had their databases corrupted by these bugs. Release 9.0.1.2 was supposed to have fixed these problems.
• Oracle9i "seems like Oracle 8.2" when it comes to migrations (in the words of Michael Alt).
  • You go from Oracle8i to Oracle9i by running catalog upgrade scripts or using the Oracle Data Migration Assistant.
  • You cannot run the mig utility to go from Oracle8i to Oracle9i.
  • The move from Oracle8i to Oracle9i only changes the data dictionary. Data blocks are reformatted as they are used.
  • You can go from Oracle 7.3.4 to Oracle9i in one step with the Oracle Data Migration Assistant or mig.
  • The move to Oracle9i can take several hours because the entire JVM is rebuilt.

Oracle9i New Features In A Nutshell

• Databases can now have multiple block sizes.
  • Every database has a "standard" block size specified by db_block_size.
  • The SYSTEM and temporary tablespaces use the standard block size.
  • Application tablespaces can use other block sizes.
  • All partitions of a table or index must use the same block size.
  • The SGA has a separate buffer cache for each block size.
  • One nice use for this feature is transporting tablespaces between databases that use different block sizes.
• You can have Oracle self-tune PGA memory usage instead of setting sort_area_size, hash_area_size, bitmap_merge_area_size, and create_bitmap_area_size manually.
  • Set pga_aggregate_target to the total amount of physical memory available for use by all dedicated server processes.
  • Oracle will then self-tune the *_area_size parameters for all dedicated server connections.
  • You can still set the *_area_size parameters manually and omit pga_aggregate_target for manual tuning.
• Most parts of the SGA can be dynamically resized and have default sizes.
  • Set the sga_max_size parameter to specify the largest the SGA is allowed to be. This is static. If you don't set sga_max_size, it will default to the initial size of the SGA (meaning that you can dynamically shrink the SGA but not grow it).
  • Set db_cache_size to the size in bytes of the buffer cache for the standard block size. This replaces db_block_buffers, and will default to a setting based on sga_max_size if not specified.
  • Set db_Nk_cache_size to specify the sizes of the buffer caches for alternate block sizes.
  • db_cache_size, db_Nk_cache_size, shared_pool_size, and large_pool_size are all dynamic parameters, meaning you can alter their settings without shutting down the instance.
  • You can still set db_block_buffers and omit sga_max_size, but the SGA and buffer cache size will be static.
• You can have Oracle estimate what cache hit ratios would be like if buffer caches were larger or smaller.
  • Set db_cache_advice to ON. This is a dynamic parameter, so you don't have to bounce the instance to turn this feature on and off.
  • Query v$db_cache_advice for the desired buffer pool and block size.
  • Oracle maintains physical I/O estimates for 20 cache sizes ranging from 10% to 200% of current size.
  • This mechanism is supposed to be more accurate and use less overhead than the v$current_bucket view provided back in Oracle8.
• Statements that run into space allocation problems can be suspended while you fix the problem.
  • Use ALTER SESSION ENABLE RESUMABLE TIMEOUT N to tell Oracle to wait N seconds when a space allocation problem occurs. The default timeout period is one hour.
  • When a problem occurs, you can fix it (add another datafile, etc.) in another session.
  • Oracle checks periodically to see if the problem has been resolved, and resumes the operation if possible.
  • If the timeout period elapses and the problem has not been resolved, Oracle fails the operation in the usual way. This usually means rolling back the work done thus far.
  • You can create an AFTER SUSPEND trigger to cause Oracle to take action automatically when an operation gets suspended.
• The dbms_redefinition package lets you make substantial schema changes while users are querying and updating tables-without data loss.
  • You can add indexes, rename columns, change data types, move tables to new tablespaces, add constraints, convert conventional tables to IOTs or partitioned tables, and on and on and on.
  • Oracle creates a materialized view log on the table to log updates, then copies all rows to an interim table and swaps the names of the two tables in the data dictionary.
  • Users have full query and write access to the production table while the redefinition is taking place, except for a split second at the very end when the table names are swapped in the data dictionary.
• You can now specify a default temporary tablespace for the database. If you don't specify a temporary tablespace when creating a new user, they will be assigned to the database default instead of SYSTEM.
• When you drop a tablespace, you can tell Oracle to remove the data files with the new INCLUDING CONTENTS AND DATAFILES clause.
• Oracle can now manage the datafiles for you.
  • Oracle can name, size, create, and delete the datafiles used in the database.
  • You just need to specify the directory in which the files should reside. (You can only specify one directory in Oracle9i Release 1; all datafiles will be located in the same directory.)
  • Oracle won't necessarily use the friendliest filenames for the files it creates, similar to declaring a primary key without specifying a name for the index.
• Data Guard has been enhanced and made easier to use.
  • Data Guard is bundled into the Oracle9i database, whereas it was an add-on in Oracle8i.
  • You can now manage Data Guard from OEM. It is supposed to be easier to use.
  • You can configure Oracle to write redo entries synchronously to the standby database so that Data Guard can ensure zero data loss when failing over to the standby. However, there are serious performance penalties in doing this, and perhaps also availability issues if the network connection to the standy database gets disrupted.
• Oracle9i Release 2 introduces the concept of a "logical standby database" (as opposed to a "physical standby"). With a logical standby database, Oracle applies the SQL to the standby instead of the block changes. This allows the standby database to be open read-only while changes are applied. It also allows the standby database to be a subset of the primary database.
• The v$sql_plan view shows the actual execution plans of SQL statements in the shared pool. You can join v$sql_plan to v$sql_workarea to see memory usage of each operation in the execution plan of a SQL statement.
• The cost-based optimizer can "peek" at the values of bind variables when choosing an execution plan.
  • When a statement is first parsed, the cost-based optimizer takes the values of bind variables into consideration.
  • This helps the optimizer determine the selectivity of predicates in a WHERE clause. Bind variable values can have a significant impact when data is not evenly distributed.
  • The optimizer only peeks at the bind variable values when the statement is first parsed; subsequent executions of the same statement will use the same execution plan even if the bind variables have different values and a different selectivity.
• You can now quiesce the instance (prevent any new transactions from starting) with the ALTER SYSTEM QUIESCE RESTRICTED command. However, transactions already in progress will be allowed to continue; you'll need to wait for these transactions to complete before the instance will be trully quiesced.
• A new command called MERGE allows you to update a row in a table if it exists and insert it if it does not exist, all in one operation. This command doesn't let you do anything you couldn't do before, but now you can replace a PL/SQL loop with a single SQL statement.
  • You specify a query, a target table, a join condition, and two column mappings.
  • For all rows retrieved by the query, a join is attempted to the target table.
  • If the join succeeds, the update column mapping is used to update columns in the target table.
  • If the join fails, the insert column mapping is used to insert a new row in the target table.
• SQL now supports the CASE function specified in the ANSI SQL standard. This just seems to be a verbose implementation of the DECODE function. However, in some situations the CASE syntax will be more readable than a comparable DECODE function.
• SQL now supports join syntax as specified in the ANSI SQL standard.
  • You can express joins the traditional Oracle way (using (+) for outer joins) or the ANSI standard way.
  • Oracle recommends that you use the ANSI standard syntax.
  • When using ANSI syntax, Oracle now supports full outer joins.
• Tables can now be partitioned by list of values (in addition to range and hash).
  • For example, you can store customer calls from the "west" region in one partition, customer calls from the "north" and "south" regions in another partition, and so on.
  • Starting in Oracle9i Release 2 you can use composite partitioning on both list of values and ranges.
• The ALTER INDEX MONITORING USAGE command tells Oracle to track if an index has been used or not.
  • Query v$object_usage to see if the index has been used and during what time period it was monitored.
  • v$object_usage will show you just whether the index has been used or not; it will not show you how many times the index was used or when it was last used.
• PL/SQL stored procedures can now be natively compiled.
  • Set plsql_compiler_flags to NATIVE and set other parameters to indicate the location of your C compiler, make utility, make file, and shared libraries target directory.
  • When plsql_compiler_flags = NATIVE, PL/SQL code is converted to C and then compiled into a shared library that can be called directly by the oracle executable.
  • You can mix interpretted and natively compiled PL/SQL program units in the same database. However, if a package specification is natively compiled, then its body must be natively compiled as well..
  • When you natively compile a PL/SQL program unit, Oracle marks this in the data dictionary so that if the program unit becomes invalidated and required re-compilation, Oracle will again compile the program unit for native execution.
  • You are allowed to natively compile PL/SQL packages supplied by Oracle.
• PL/SQL has enhancements for returning row sets and pipelining.
  • A PL/SQL function whose return value is a set of rows may be specified in the FROM clause of a query.
  • A pipelining mechanism has been provided so that row output from a function can be passed directly into the next operator in the pipeline instead of collecting all output in a temporary segment before sending to the next operator. This feature can be used to improve parallelism in processing.
• PL/SQL now uses the SQL engine's parser when evaluating SQL embedded inside a PL/SQL block. (The PL/SQL engine used to have its own SQL parser which caused problems when a new feature was added to the SQL engine but not the PL/SQL engine.)
• There is a new data type called TIMESTAMP.
  • Timestamps can include timezone information, and can automatically adjust for daylight savings time.
  • Timestamps can hold fractional seconds.
• You can now specify string lengths in characters instead of bytes.
  • Set nls_length_semantics to CHAR or BYTE to indicate what you mean when you specify the length of a VARCHAR2 column.
  • nls_length_semantics setting is relevant only when you create a table or add a column to a table.
  • You can force the semantics one way or the other with expressions such as VARCHAR2(10 CHAR) or VARCHAR2(20 BYTE).
  • Character length semantics are relevant when working with multi-byte character sets such as unicode.
• LOBs are now easier to manage.
  • You can apply character functions to CLOBs as if they were VARCHAR2 data.
  • The OCI API now allows you to manipulate LOBs very much like LONGs and LONG RAWs. This should help ease the transition of existing systems from LONGs to LOBs.
  • You can convert a LONG column to a CLOB with a single ALTER TABLE command.
  • Oracle seems to be looking to desupport LONGs in a future release.
• The heavily-touted "flashback query" feature lets you see data as it existed in the past.
  • Flashback query lets you see what data looked like at a previous point in time, even if the data has subsequently been updated and the updates have been committed.
  • Flashback query uses undo information in SMU to reconstruct what the data used to look like. But if the undo has been overwritten, you cannot flash back.
  • Running a flashback query to repair a user error is kludgey in Oracle9i Release 1 because you must call the dbms_flashback package to enable the feature, then open a cursor on the old version of the data, and then call dbms_flashback again to turn off the feature before you execute DML to repair the data.
  • You can flash back to a specific SCN or time. But times will round off to five-minute intervals.
  • Flashback query looks much better in Oracle9i Release 2 where you can use the AS OF clause in the FROM clause of a query instead of calling dbms_flashback. You can also use INSERT..SELECT statements to repair data using the flashback, and you can join current tables to flashed-back tables.
• You can now access data in flat files from SQL as if it were stored in a database table. This could be very useful for accessing transient data external to the database without having to load the data into the database.
  • You create a table in the database with the ORGANIZATION EXTERNAL clause. In this clause you specify the location of the flat file and how data in the file maps to columns in the table. (This looks a bit like a SQL*Loader control file.)
  • Whenever you access the table in a query, Oracle will perform a "full table scan" by reading the flat file.
  • You can join external tables to conventional tables in a query.
  • You cannot create indexes on external tables, nor can you insert, update, or delete rows.
• A new type of index called a "bitmap join index" lets you index the join condition between multiple tables. This speeds up queries that join the tables because the index readily identifies matching rows between the tables.
• Oracle9i includes many new capabilities and performance enhancements with respect to Java. If I knew more about Java, I'd mention some of the Java highlights here.

Helpful References

• Oracle9i Database New Features (more of a marketing piece than a technical manual)
• Oracle9i SQL Reference (includes a helpful section on Oracle9i new features)
• Oracle9i PL/SQL User's Guide and Reference (includes a helpful section on Oracle9i new features)
• Oracle9i Database Administrator's Guide (detailed reference once you have a specific topic in mind)
• Oracle9i Database Concepts (detailed reference once you have a specific topic in mind)

About the Author

Roger Schrag has been an Oracle DBA and application architect for over twelve years. He started out at Oracle Corporation on the Oracle Financials development team and moved into the roles of production DBA and database architect at various companies in the San Francisco Bay Area. Roger is a frequent speaker at Oracle World and the IOUG Live! conferences. He is also vice-president of the Northern California Oracle Users Group. In 1995, Roger founded Database Specialists, Inc., (http://www.dbspecialists.com) a consulting firm specializing in business solutions based on Oracle technology. In addition to consulting, the company offers flexible solutions including part-time DBA support and Database Rx (http://www.dbspecialists.com/database_rx.html), a web-based monitoring and alert notification service for Oracle databases. In 2001, the San Francisco Business Times named Database Specialists one of the Top 150 Fastest-Growing Private Companies in the Bay Area.

Database Specialists, Inc. http://www.dbspecialists.com
© Copyright 2002