9i 新增功能，相对Release 9.0.1 Oracle9i Release 2 (9.2) New Features in the SQL Reference1、SQL Reference：The following built-in conditions are new to this release:EQUALS_PATH UNDER_PATH The following built-in expression is enhanced in this release:The syntax for type constructor expressions now allows creation of new user-defined constructors (see "Type Constructor Expressions"). The following built-in functions are new to this release:DEPTH EXTRACTVALUE PATH UPDATEXML XMLAGG XMLCONCAT XMLCOLATTVAL XMLELEMENT XMLFOREST XMLSEQUENCE XMLTRANSFORM The following privileges are new or enhanced in this release:DEBUG CONNECT SESSION system privilege (new) DEBUG ANY PROCEDURE system privilege (new) DEBUG object privilege (new) EXECUTE object privilege on procedures, packages, libraries, and object types (enhanced) GRANT ANY OBJECT PRIVILEGE system privilege The following top-level SQL statements are new or enhanced in this release:ALTER DATABASE has new syntax for managing standby databases, for managed standby recovery, and for logical standby. ALTER OPERATOR lets you recompile an existing user-defined operator. ALTER TABLE contains new clauses that let you rename a column or a constraint. CREATE DATABASE: Contains two new clauses for assigning passwords to the SYS and SYSTEM users Lets you create a locally managed SYSTEM tablespace CREATE SYNONYM now allows creation of synonyms for object types. CREATE TABLE: Allows creation of a default list partition to capture rows that do not fall within any of the other list partitions Allows creation of range-list composite-partitioned tables Contains syntax for creating a table of type XMLType and for creating range-list composite-partitioned tables Allows data compression of data in table and partition segments CREATE TYPE allows creation of object types with of NCHAR, NVARCHAR2, and NCLOB attributes. CREATE VIEW now contains syntax for creating an XML view by transforming a table of type XMLType. SELECT provides syntax for "flashback queries", which let you query data at a specified system change number or time in the past. 2、PL/SQL Insert/update/select of entire PL/SQL records You can now insert into or update a SQL table by specifying a PL/SQL record variable, rather than specifying each record attribute separately. You can also select entire rows into a PL/SQL table of records, rather than using a separate PL/SQL table for each SQL column.Associative arrays You can create collections that are indexed by VARCHAR2 values, providing features similar to hash tables in Perl and other languages.See Also: "Understanding Associative Arrays (Index-By Tables)" User-defined constructors You can now override the system default constructor for an object type with your own function.See Also: "Defining Constructors" Enhancements to UTL_FILE package UTL_FILE contains several new functions that let you perform general file-management operations from PL/SQL.See Also: Oracle9i Supplied PL/SQL Packages and Types Reference TREAT function for object types You can dynamically choose the level of type inheritance to use when calling object methods. That is, you can reference an object type that inherits from several levels of parent types, and call a method from a specific parent type. This function is similar to the SQL function of the same name.See Also: Oracle9i SQL Reference Better linking in online documentation Many of the cross-references from this book to other books have been made more specific, so that they link to a particular place within another book rather than to the table of contents. Because this is an ongoing project, not all links are improved in this edition. If you are reading a printed copy of this book, you can find the online equivalent at http://tahiti.oracle.com/, with full search capability.
这里只列举了sql和plsql的新特性，其他的还有很多，楼主去官网下载个9.2的联机帮助文档，搜索what's new 就知道9.0.1和9.2的区别了
Oracle9i Release 2 (9.2) New Features in the SQL Reference1、SQL Reference：The following built-in conditions are new to this release:EQUALS_PATH
UNDER_PATH The following built-in expression is enhanced in this release:The syntax for type constructor expressions now allows creation of new user-defined constructors (see "Type Constructor Expressions").
The following built-in functions are new to this release:DEPTH
EXTRACTVALUE
PATH
UPDATEXML
XMLAGG
XMLCONCAT
XMLCOLATTVAL
XMLELEMENT
XMLFOREST
XMLSEQUENCE
XMLTRANSFORM The following privileges are new or enhanced in this release:DEBUG CONNECT SESSION system privilege (new)
DEBUG ANY PROCEDURE system privilege (new)
DEBUG object privilege (new)
EXECUTE object privilege on procedures, packages, libraries, and object types (enhanced)
GRANT ANY OBJECT PRIVILEGE system privilege
The following top-level SQL statements are new or enhanced in this release:ALTER DATABASE has new syntax for managing standby databases, for managed standby recovery, and for logical standby.
ALTER OPERATOR lets you recompile an existing user-defined operator.
ALTER TABLE contains new clauses that let you rename a column or a constraint.
CREATE DATABASE:
Contains two new clauses for assigning passwords to the SYS and SYSTEM users
Lets you create a locally managed SYSTEM tablespace
CREATE SYNONYM now allows creation of synonyms for object types.
CREATE TABLE:
Allows creation of a default list partition to capture rows that do not fall within any of the other list partitions
Allows creation of range-list composite-partitioned tables
Contains syntax for creating a table of type XMLType and for creating range-list composite-partitioned tables
Allows data compression of data in table and partition segments
CREATE TYPE allows creation of object types with of NCHAR, NVARCHAR2, and NCLOB attributes.
CREATE VIEW now contains syntax for creating an XML view by transforming a table of type XMLType.
SELECT provides syntax for "flashback queries", which let you query data at a specified system change number or time in the past. 2、PL/SQL
Insert/update/select of entire PL/SQL records You can now insert into or update a SQL table by specifying a PL/SQL record variable, rather than specifying each record attribute separately. You can also select entire rows into a PL/SQL table of records, rather than using a separate PL/SQL table for each SQL column.Associative arrays You can create collections that are indexed by VARCHAR2 values, providing features similar to hash tables in Perl and other languages.See Also:
"Understanding Associative Arrays (Index-By Tables)"
User-defined constructors You can now override the system default constructor for an object type with your own function.See Also:
"Defining Constructors"
Enhancements to UTL_FILE package UTL_FILE contains several new functions that let you perform general file-management operations from PL/SQL.See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference
TREAT function for object types You can dynamically choose the level of type inheritance to use when calling object methods. That is, you can reference an object type that inherits from several levels of parent types, and call a method from a specific parent type. This function is similar to the SQL function of the same name.See Also:
Oracle9i SQL Reference
Better linking in online documentation Many of the cross-references from this book to other books have been made more specific, so that they link to a particular place within another book rather than to the table of contents. Because this is an ongoing project, not all links are improved in this edition. If you are reading a printed copy of this book, you can find the online equivalent at http://tahiti.oracle.com/, with full search capability.
oracle9i：用initsid.ora来作为启动文件
oracle9i2：第一次用initsid.ora（pfile目录下）作为启动文件，以后都用spfile.ora（用pfile创建）来作为启动文件，支持在线修改系统一些参数，不必关闭数据库也可以修改这些参数