New Features of Oracle 9i
New Features of Oracle 9i
In the earlier versions:
* When you redefine a table, the table is not accessible to DML.
In this version:
* The DBMS_REDEFINITION PL/SQL package allows you to redefine tables online while maintaining the tables accessibility.
* Execute privileges on DBMS_REDEFINITION is granted to EXECUTE_CATALOG_ROLE.
* In addition to having execute privileges on DBMS_REDEFINITION, you must be granted the
following privileges:
o CREATE ANY TABLE
o ALTER ANY TABLE
o DROP ANY TABLE
o LOCK ANY TABLE
o SELECT ANY TABLE
-------------------------------------------------------
In the earlier versions:
* You cannot force the enabled online redo log threads to switch their current logs in a time-based fashion
* time lag of log updates is possible
In this version:
* Time-based means of switching the current online redo log group is supported.
* You may set the ARCHIVE_LAG_TARGET initialization parameter to limit how long the time lag can become.
-------------------------------------------------------
In the earlier versions:
* Suspending a database is difficult, if not impossible.
In this version:
* You may use the ALTER SYSTEM SUSPEND statement to suspend a database.
* All input and output to datafiles and control files will be halted, although the preexisting I/O operations are allowed to complete.
* The new database accesses will be placed in a queued state.
* You may use ALTER SYSTEM RESUME statement to resume normal database operation.
* You may suspend (and later resume) the execution of large database operations When there is space allocation failures. You can take your time to correct the problem before continuing.
* Suspension occurs when the following conditions occur:
o Out of space
o Maximum extents reached
o Space quota exceeded
* Operations that are resumable:
o Queries
o DML
o Import/Export
o DDL
-------------------------------------------------------
In the earlier versions:
* Quiesced state support is not available.
In this version:
* You may quiesce a database
* By placing the database into a quiesced state, only DBA transactions, queries, or PL/SQL statements are allowed.
* Allows you to perform administrative actions that cannot safely be done otherwise.
* You use the ALTER SYSTEM QUIESCE RESTRICTED statement to place a database into a quiesced state.
* You must be user SYS or SYSTEM.
* Users with the SYSDBA system privilege or DBA role are not allowed to issue the ALTER SYSTEM QUIESCE DATABASE statement.
-------------------------------------------------------
In the earlier versions:
* The maximum number of destinations to which you can archive the online redo log is 5.
In this version:
* The maximum number of destinations to which you can archive the online redo log is 10.
* You may use the LOG_ARCHIVE_DEST_n parameter (n is from 1 to 10) to specify from one to ten different destinations for archival.
* You may specify a maximum of two locations via the LOG_ARCHIVE_DEST parameter. You specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination.
-------------------------------------------------------
In the earlier versions:
* Free and used space within segments stored in locally managed tablespaces cannot be managed automatically.
In this version:
* Free and used space within segments stored in locally managed tablespaces can be managed automatically via the SEGMENT SPACE MANAGEMENT clause of CREATE TABLESPACE.
* You specify AUTO or MANUAL to specify the type of segment space management Oracle will use.
* The AUTO keyword tells Oracle that you want to use bitmaps instead of freelists to manage the free space within segments.
* A bitmap is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for use.
* Bitmaps allow Oracle to manage free space more automatically and in a more efficient way.
-------------------------------------------------------
In the earlier versions:
* Multiple block sizing is not supported.
In this version:
* Oracle supports multiple block sizes.
* Standard block size set by the DB_BLOCK_SIZE initialization parameter
* Standard block size used for the SYSTEM tablespace
* Additionally up to 4 nonstandard block sizes specified when creating tablespaces.
* Multiple block size support allows for the transporting of tablespaces with unlike block sizes between databases.
* Tablespaces of non-standard block sizes can be created using the CREATE TABLESPACE statement with a BLOCKSIZE clause
* non-standard block sizes can have any power-of-two value between 2K and 32K
* To use non-standard block sizes, you must configure sub-caches within the buffer cache area of the SGA memory for all of the non-standard block sizes
-------------------------------------------------------
In the earlier versions:
* The size of the buffer cache subcomponent of the System Global Area is static.
* You may not alter the size of SGA dynamically
In this version:
* The DB_BLOCK_BUFFERS initialization parameter has been replaced by a new dynamic parameter, DB_CACHE_SIZE
* user can specify the size of the buffer subcache for the standard database block size
* There can be up to four DB_nK_CACHE_SIZE initialization parameters
* You may alter the size of SGA dynamically with ALTER SYSTEM SET
-------------------------------------------------------
In the earlier versions:
* You use rollback segments to store undo.
In this version:
* You may use an undo tablespace to store undo.
* eliminates the complexities of managing rollback segment space
* you can exert control over how long undo is retained before being overwritten
* Undo records can be used to:
# Roll back transactions when a ROLLBACK statement is issued
# Recover the database
# Provide read consistency
* You cannot use both undo methods in the same database instance,
* Oracle always uses a SYSTEM rollback segment for performing system transactions.
* There is only one SYSTEM rollback segment
* SYSTEM rollback segment is created automatically at CREATE DATABASE time and is always online
* no need for you to manage by hand the SYSTEM rollback segment
-------------------------------------------------------
In the earlier versions:
* You may need to directly manage the files comprising an Oracle database.
In this version:
* You may use the following initialization parameters to specify the file system directory to be used for a particular type of file:
# DB_CREATE_FILE_DEST
# DB_CREATE_ONLINE_LOG_DEST_n
-------------------------------------------------------
In the earlier versions:
* The deletion of datafiles is rather “manual”
In this version:
* You may have Oracle automatically remove a tablespaces's when the tablespace is dropped with DROP TABLESPACE
* Any tablespace in an Oracle database, except the SYSTEM tablespace, can be dropped.
* You must have the DROP TABLESPACE system privilege to drop a tablespace.
* Once a tablespace has been dropped, the tablespace's data is not recoverable.
-------------------------------------------------------
In the earlier versions:
* External table access is complicated if not impossible
In this version:
* read-only access to data in external tables is possible
* you use the CREATE TABLE ... ORGANIZATION EXTERNAL statement to specify metadata describing the external table.
* the ORACLE_LOADER access driver provides data mapping capabilities that are a subset of the SQL*Loader control file syntax.
-------------------------------------------------------
In the earlier versions:
* initialization parameters are stored in a text initialization parameter file on a client machine.
In this version:
* you can elect to maintain initialization parameters in a server parameter file
* the file is binary
* the file is stored on the database server
* the file is persistent
-------------------------------------------------------
In the earlier versions:
* there is no clause for default temporary tablespace during database creation time
In this version:
* You may use the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement to create a default temporary tablespace at database creation time
* Users can be explicitly assigned a default temporary tablespace in the CREATE USER statement
* when no temporary tablespace is specified, the default is to use the SYSTEM tablespace
-------------------------------------------------------
In the earlier versions:
* Transaction Naming is not possible
In this version:
* you may assign a name to a transaction.
* transaction name is helpful in resolving in-doubt distributed transactions
* transaction name replaces a COMMIT COMMENT.
* you use the SET TRANSACTION ... NAME statement to name a transaction
* the view DBA_2PC_PENDING lists all the in-doubt distributed transactions
-------------------------------------------------------
In the earlier versions:
* template based database creation with the Oracle Database Configuration Assistant is not possible
In this version:
* Oracle Database Configuration Assistant provides templates for saving the definitions of the databases
* Oracle provides pre-defined templates for your use
* you can create your own templates
* When creating a database, you can initially include or later add as an option Oracle's new Sample Schemas as the basis for the examples used in Oracle documentation.
-------------------------------------------------------
In the earlier versions:
* Monitoring an index to determine if it is actively being used is difficult
In this version:
* You may add the MONITORING USAGE clause to the ALTER INDEX statement to monitor an index to determine if it is actively being used.
* To start monitoring an index's usage, use ALTER INDEX index MONITORING USAGE
* To stop monitoring an index's usage, use ALTER INDEX index NOMONITORING USAGE
* The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used.
* Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index.
-------------------------------------------------------
In the earlier versions:
* Proxy authentication and authorization features are lacking
In this version:
* You may authorize a middle-tier server to act on behalf of a client via the GRANT CONNECT THROUGH clause of the ALTER USER statement
* You may specify roles that the middle tier is permitted to activate when connecting as the client.
* You may audit operations done on behalf of a client by a middle-tier server
* You may query the PROXY_USERS data dictionary view to see which users are currently authorized to connect through a middle tier.
* You may use the REVOKE CONNECT THROUGH clause of ALTER USER to disallow a proxy connection.
-------------------------------------------------------
In the earlier versions:
* Application roles support is lacking
In this version:
* The IDENTIFIED USING package clause for the CREATE ROLE statement is available
* The INDENTIFIED USING package_name clause lets you create an application role that is enabled only by applications using an authorized package.
* Application developers no longer need to secure a role by embedding passwords inside applications.
-------------------------------------------------------
In the earlier versions:
* Audit records are stored in the audit trail
* Audit options can only be set to monitor access of objects or privileges.
In this version:
* A new PL/SQL package, DBMS_FGA is available
* It allows applications to implement fine-grained auditing of data access based on content.
* Fine-grained auditing can also be implemented in user applications via database triggers.
-------------------------------------------------------
