New features introduced by Oracle 9i

New features of Oracle 9i

Oracle 9i is having several new features and enhancements to expand the depth and usefulness of the previous release named Oracle 8i. Some of these new features are:-

• Dynamic Memory Pools - until Oracle 8i we required to stop and restart the Oracle Server after modifying the various memory pools including Shared Pool, Buffer Cache, Large Pool, etc. But, Oracle 9i reflects the changes without requiring to restart the server.
• Automatic Undo Management - Oracle 9i automates the process of Undo and now the use doesn't require to define, manage, and monitor the rollback segments. Instead the rollback segments are automatically created, monitored, sized, and deleted simply by defining the Undo tablespace.
• Auto Handling of Stabdby DB - for a completely automatic handling of standby databases.
• Flashback Query - this feature returns the results of the queries run against a database as they would have been produced at an earlier time and hence the name 'Flashback'.
• Concept of Clusters - this feature enables Oracle 9i database to be spread across multiple machines and hence resulting in a cluster of server. These servers can be used to extend the scalability and the availability of the cluster.
• XML data type introduced - this feature enables the XML documents directly into the database and thereby eliminating the need to parse the documents while storing into or retrieving from the DB.
• Partioning based on LOV - Oracle 9i introduced a new way to partition the data - by using a list of values, especially for the cases where partitioning is being done to isolate maintenance operations.
• Two-phase Rollback - Oracle 9i facilitates the database to be used soon after the roll-forward phase of the recovery gets completed and hence the use don't need to wait for the entire rollback to finish and thereby the wait time decreases.
• No Data Loss - Oracle 9i if configured for the 'ZERO Data Loss' feature will ensure that everything that is written to the local log file is also getting written to the log file of the standby DB. This makes sure that no data is lost in case of database crash as both the active DB as well as the standby DB always remain in the same state.
• TIMESTAMP Data Type family - Oracle 9i introduced this data type family to store Dates with Time up to 9 decimal places of a second. Members of this data type family are: TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND.
• Server Parameter file - Oracle 9i introduced this file, which allows to maintain the initialization parameters in a binary server parameter file (created using CREATE SPFILE statement... don't edit this file manually in a text-editor). This file is also known as SPFILE. As the name suggests that this file resides on the machine where Oracle Database Server runs. The initialization parameters stored in this file are persistent and any changes made by using ALTER SYSTEM statements remain effective only for that particular server instance and during the next server startup again the same SPFILE is read and the server is initialized using the parameteres stored in that file only.
• Label Security - Oracle 9i introduced this feature to provide a comprehensive and highly customizable solution for row level security. This feature reduces the need for relying upon manual or physical controls to protect the data by labelling data and restricting access with a high degree of granularity. For example, if the same application is being used across organizations then sensitivity labels can be used to restrict the application users to one or few of the organizations only OR may be we can use the same approach to restrict access to the data to only a subset of users within an organization.
• External Tables - these are flat files stored outside the database that are treated by Oracle as tables. Data in such tables is read-only and no indexes can be created on these tables. No DML operation can be performed on these tables, but we can use them for query, join, and sort oparations. Two new views: DBA_EXTERNAL_TABLES and DBA_EXTERNAL_LOCATIONS have been introduced to administer the external tables.
• Explicit Defaults - The DEFAULT keyword can now be used to explicitly assign the column default values in INSERT/UPDATE statements. Prior to this, we would normally skip the default column.
• Several new functions introduced - Oracle 9i added several new functions including NULLIF (expression1, expression2): returns NULL if both the expressions are equal otherwise the base expression i.e., the first expression; COALESCE (expression1, expression2, ... expressionN): returns the first non-NULL expression in the list otherwise NULL.
• MERGE statement introduced - Oracle 9i added this statement, which implements Upsert functionality. It's used for conditional INSERT/UPDATE. Read more about this statement here.

Note: This question was asked by one of our visitors, Amit. Thanks Amit for your contribution. I hope this article helps you getting the answer to your question. Keep visiting/posting!