Topic Summary

• Deprecated and Desupported Features and the Replacement
  • Statistics: What's happened to ANALYZE?
  • Init.ora changes - No more DB_BLOCK_BUFFERS?  Let's use DB_CACHE_SIZE instead! More.
  • Tuning:  Use STATSPACK instead of bstat/estat
  • No more CONNECT INTERNAL, finally?
  • Goodbye Server Manager
  • More....
• Undo Tablespaces
  • Eliminate Rollback Segment management with Automatic UNDO Management! You will implement AUM on your classroom test database and give it a bit of a workout.
• Using Server Parameter Files
  • Learn to implement and manage binary, server-side parameter files.
  • The server parameter file (SPFILE) allows you to store and manage your initialization parameters persistently in a server side binary file. It is similar to the parameter file from earlier versions of Oracle in that it is used to set the parameters for an instance.  It is different in that it is a binary file and it is always stored on the server itself....
  • You will create a server parameter file, implement it and also try the persistent parameter change feature, SCOPE=BOTH.
• SGA Management
  • Oracle9i allows you to dynamically change several areas of the SGA; the database buffer cache, memory sub-caches, and the shared pool.   The overall size of the SGA is now governed by the SGA_MAX_SIZE parameter....
  • Oracle9i supports multiple database block sizes. There are two types of block sizes, standard and non-standard....
  • In lab, we will create a tablespace with a non-standard blocksize.
• Resumable Space Management
  • Restart long-running jobs after space-related errors.  With RSM, the transaction that has encountered the space allocation error is suspended for a period of time - allowing you to fix the suspend condition and eventually allowing the transaction to resume. This is a very valuable enhancement that could result in a huge amount of saved time especially in systems where there are large data loads or large transactions coupled with vague data requirements....
  • Of course, we will give it a try!
• New Online Operations
  • DBMS_REDEFINITION and index REBUILD ONLINE
  • In Oracle9i you are now able to perform table redefinitions online without limiting DML access to the object being redefined. In earlier releases of Oracle the redefinition operations were available, however, when it came to the actual data and moving it to the redefined object, you needed exclusive access to the original data in order to get a consistent image of the data. By using the online table redefinition feature, the original table is still accessible to DML for most of the time that the redefinition of the table is running....
• LogMiner
  • LogMiner is a new PL/SQL-based utility introduced with version 8.1 (8i).  It allows DBA's to use the REDO logs to see changes to the database.  Both the REDO SQL (the statement to reapply the change) and the UNDO SQL (the statement to undo the change) are available.   Beginning in Oracle9i, LogMiner Viewer is available. LogMiner Viewer is an easy to use GUI interface that gives you the capability to start / stop LogMiner and to create LogMiner reports....
• Real Application Clusters Concepts
  • Oracle Parallel Server Enhanced! Cache Fusion technology binds together the buffer caches of multiple nodes. Write/write contention is addressed by sending blocks to requesting instance over high speed interconnect. Find out what all the fuss is about.
• Backup and Recovery Enhancements - RMAN
  • Recovery Manager is now a fairly robust tool for performing server-based backup and recovery operations. What's new? How to get started? This lesson will teach you - and give you a chance to setup and work with RMAN.
• Partitioning Features
  • The technique used to divide and conquer large objects has been enhanced. List partitioning is just one of the new partitioning features.
• Indexing Enhancements
  • Bitmap join indexes and skip scan are two of the most visible new features. Here is your chance to test them out.
• Performance and Tuning Enhancements
  • We have got roughly 25 new performance and tuning related features to show you. There are advisories to help size the buffer cache, shared pool and PGA to dynamic sampling, cursor sharing and table compression, just to mention a few. There is a lot to see and do here.
• Security
  • Fine Grained Access Control (a.k.a. Virtual Private Database) has been enhanced with a GUI tool, Oracle Policy Manager, Global Application Contexts and more..
  • Fine Grained Auditing allows you to audit select statements based on access to specified columns. This is done through audit policies that define the criteria by which a specific column should be audited....
  • DBA can now grant/revoke privileges on another schema's objects.
  • SYS and SYSTEM passwords are set by the DBA at database creation time.
  • Enhanced key generation for encryption (FIPS-140 certified). You will have an opportunity to encrypt and decrypt data using the new random key generator.
• Flashback Query
  • So, you want to see your data as it was an hour ago? With flashback query, that is no problem. Call DBMS_FLASHBACK or, in Release 2, use the AS OF clause in your query.
• External Tables
  • This feature has at least two faces. One is simply a method of easily querying (yes, with SQL) files stored outside the database. Another is a more convenient way to use SQL*Loader. You will get to use it in workshop.
• New Datatypes
  • Datetime data contains fractional seconds and timestamp data. Find out how to use TIMESTAMP and TIMESTAMP WITH TIME ZONE. And what are these INTERVAL types? Find out in this lesson.
  • XML DB, a first look.
  • XMLType scratches the surface of a group of features collectively known as "XML DB". You will get a first look here.
• SQL Enhancements
  • There is a lot to show you. This includes the new MERGE Statement (often called an "Upsert" operation) and multi-Table INSERT, which, by the way, can be used on External Tables! There is also ANSI Compliant Joins, CONNECT BY extensions, scrollable cursors and much more.
• New PL/SQL features
  • Doing any Extraction, Transformation and Loading (ETL)? Pipelined Table Functions can make this a lot faster. There is much more including:
    • > CASE Statement
    • >ZAssociative Arrays
    • > Record-Level DML Support
    • > Object Inheritance support
    • > Multi-Level Collections
    • > Select Rows into PL/SQL Tables
    • > Native Compilation
• Oracle Managed Files
  • Oracle Managed Files (OMF) gives the database the ability to "manage" database files, including datafiles, online redo log files, and control files.  Oracle will create, name, and locate physical files for you and will also delete them from the operating system automatically when you drop objects the file is associated with.