Differences From Previous Versions
This appendix lists differences between the current and previous releases of Oracle.
Differences Between Oracle7 Release 7.2 and Release 7.3
New SQL Functions
The following transcendental functions are new:
ALTER CLUSTER DEALLOCATE UNUSED
You can deallocate unused space from a cluster and save specified kilobytes for future use. For example,
ALTER CLUSTER detroit.sales
DEALLOCATE USUSED KEEP 100K;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS <filename>
You can specify the controlfile for a standby database. For example,
ALTER DATABASE stocks CREATE STANDBY CONTROLFILE AS controlfile;
ALTER DATABASE MOUNT STANDBY DATABASE [EXCLUSIVE, PARALLEL]
You can mount a database or its corresponding standby database as either EXCLUSIVE or as PARALLEL. For example,
ALTER DATABASE stocks MOUNT STANDBY DATABASE EXCLUSIVE;
ALTER DATABASE stocks MOUNT STANDBY DATABASE PARALLEL;
ALTER DATABASE RECOVER STANDBY DATABASE
You can recover the standby database. For example,
ALTER DATABASE stocks RECOVER STANDBY DATABASE;
ALTER DATABASE ACTIVATE STANDBY DATABASE
You can activate a standby database. For example,
ALTER DATABASE stocks ACTIVATE STANDBY DATABASE;
ALTER INDEX ALLOCATE EXTENT
You can allocate an extent to an index after creation.
ALTER INDEX DEALLOCATE UNUSED
You can deallocate unused space from an index and save specified kilobytes for future use. For example,
ALTER INDEX april.sales
DEALLOCATE USUSED KEEP 100K;
ALTER INDEX REBUILD
You can use an existing index as the data source of a fast re-create index. This changes the index's storage characteristics. ALTER INDEX has the new parameter, REBUILD. For example,
ALTER INDEX salesmen REBUILD UNRECOVERABLE TABLESPACE detroit;
ALTER SESSION HASH_JOIN_
ENABLED
You can use hash-join to improve the performance of join operations. There are three new parameters:
HASH_JOIN_ENABLED turns the feature on or off.
- HASH_AREA_SIZE specifies the maximum amount of memory in bytes to be used for the hash join. If not specified, hash join uses twice the SORT_AREA_SIZE value.
- HASH_MULTIBLOCK_IO_COUNT determines how many blocks hash join should read and write at once. If not specified, hash join uses the value for DB_FILE_MULTIBLOCK_READ_COUNT.
- REMOTE_DEPENDENCIES_MODE specifies how the session handles dependencies of remote stored procedures, by TIMESTAMP or by SIGNATURE..
For example,
ALTER SESSION HASH_JOIN_ENABLED = TRUE;
ALTER SESSION HASH_AREA_SIZE = 1000K;
ALTER SESSION HASH_MULTIBLOCK_IO_COUNT = 50;
ALTER SESSION SET
You can change dynamic initialization parameters while an instance is running. ALTER SESSION changes the parameter for the duration of the session, or until you re-execute ALTER SESSION. For example,
ALTER SESSION SET COMMIT_POINT_STRENGTH 100;
ALTER SYSTEM SET REMOTE_
DEPENDENCIES_
MODE
You can alter the system so that timestamp mismatches are now ignored if the user requests that invalidation be based on signatures, rather than by a timestamp with the REMOTE_DEPENDENCIES_MODE parameter.
ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = SIGNATURE;
ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = TIMESTAMP;
ALTER SYSTEM SET
You can change global value of a dynamic initialization parameter. New sessions use the changed value. For example,
ALTER SYSTEM SET COMMIT_POINT_STRENGTH=100;
ALTER TABLE DEALLOCATE UNUSED
You can release unused space from a segment and return it to the database system. For example,
ALTER TABLE emp DEALLOATE UNUSED KEEP 100K;
ALTER TABLESPACE COALESCE
You can improve performance by coalescing available free space (extents) in the tablespace into larger, contiguous extents on a per file basis. For example,
ALTER TABLESPACE inventory COALESCE;
ALTER TABLESPACE [PERMANENT, TEMPORARY]
You can alter a tablespace to be a permanent or temporary to use with multiple sort operations.
ALTER TABLESPACE inventory PERMANENT;
ALTER TABLESPACE inventory TEMPORARY;
For more information, see CREATE TABLESPACE [PERMANENT, TEMPORARY] on 4 - 254.
ALTER TRIGGER trigger_name COMPILE
You can now create a compiled trigger that is stored in pcode form, thereby eliminating the need for recompilation during loads and removing RPI calls at execution time. COMPILE and DEBUG are new parameters of the ALTER TRIGGER command. Because triggers now have remote dependencies, they can become invalid if a depended-on object changes. The COMPILE option allows a user to manually recompile an invalid trigger object.
ALTER TRIGGER reorder COMPILE;
ALTER TRIGGER trigger_name DEBUG
The DEBUG option allows PL/SQL information to be generated during trigger recompilation.
ALTER TRIGGER reorder DEBUG;
ANALYZE TABLE
You can create histograms on columns that have highly-skewed database distribution and are frequently used in WHERE clauses of queries. You create a histogram with the ANALYZE TABLE command. For example
ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS salary SIZE 50;
The SIZE keyword states the maximum number of buckets for the histogram.
CREATE TABLESPACE [PERMANENT, TEMPORARY]
You can create a permanent or temporary tablespace to use with multiple sort operations.
A tablespace can be defined as temporary during creation, or it can be made temporary later. The CREATE TABLESPACE command is expanded to include the TEMPORARY and PERMANENT options:
Specifying TEMPORARY defines the tablespace as a temporary tablespace. All sorts in a temporary tablespace share a single sort segment and allocate space using the sort segment table. However, no permanent objects can be stored in the temporary tablespace.
Specifying PERMANENT allows the permanent objects to be stored in the tablespace. However, if this tablespace is used for sorting, no caching is done, so sort performance may suffer. For example,
CREATE TABLESPACE inventory PERMANENT;
CREATE TABLESPACE inventory TEMPORARY;
CREATE TRIGGER trigger_name COMPILE
You can now create a compiled trigger that is stored in pcode form, thereby eliminating the need for recompilation during loads and removing RPI calls at execution time.
ALTER TRIGGER reorder COMPILE;
SET TRANSACTION READ ONLY
In previous releases, you could use the SET TRANSACTION READ ONLY command to design application transactions that had a consistent view of their data during query-only application transactions. The new isolation level provided by serializable transaction isolation preserves the transaction-consistent view of data that is provided by SET TRANSACTION READ ONLY. Serializable transaction isolation now allows transactions to execute DML statements and to see their own changes while shielding them from visibility of other transactions' changes-either in-flight or committed.
SET TRANSACTION ISOLATION_LEVEL SERIALIZABLE;
or
SET TRANSACTION ISOLATION_LEVEL READ COMMITTED;
The SQL command syntax for the ALTER SESSION command is extended as follows:
ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE
or
ALTER SESSION SET ISOLATION_LEVEL=READ COMMITTED
STORAGE Clause
In Release 7.2 and earlier releases of Oracle7, the number of extents that could be allocated to a single segment was limited by the database block size. The entire extent map had to fit within half of the segment header block. For a 2 Kb block, the maximum number of extents per segment was 121.
The following are changes in space management:
- MAXEXTENTS is no longer limited by the number of extents that fit into a single database block.
- A new keyword, UNLIMITED, is now supported as a valid value for MAXEXTENTS. For example,
CREATE TABLESPACE emp MAXEXTENTS UNLIMITED;
Differences Between Oracle7 Release 7.1 and Release 7.2
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
It is now possible to write SQL commands to the database's trace file that can be used to re-create the database. For example:
ALTER DATABASE BACKUP CONTROLFILE
TO TRACE
NORESETLOGS ;
ALTER DATABASE CLEAR LOGFILE
It is now possible to reinitialize redo log files during recovery. For example:
ALTER DATABASE CLEAR UNARCHIVED
LOGFILE 'somefile'
UNRECOVERABLE DATAFILE;
ALTER DATABASE DATAFILE datafile END BACKUP
It is now possible to avoid unnecessary media recovery (when the database was closed without finishing an online backup) using the following command:
ALTER DATABASE DATAFILE 'file' END BACKUP;
ALTER DATABASE DATAFILE datafile RESIZE
It is now possible to dynamically change the size of a datafile. For example:
ALTER DATABASE DATAFILE 'file' RESIZE 10M ;
ALTER ROLLBACK SEGMENT SHRINK
It is now possible to shrink a rollback segment to an optimum size using the following command:
ALTER ROLLBACK SEGMENT name SHRINK TO size ;
ALTER SESSION
SET INSTANCE
In a parallel server environment while connected to one instance it is now possible to mimic that the session is connected to another instance. For example:
ALTER SESSION SET INSTANCE = 3;
ALTER SESSION
SET NLS_CALENDAR
It is now possible to redefine the language calendar for a session. For example:
ALTER SESSION SET NLS_CALENDAR = gregorian;
ALTER TABLE
... DISABLE TABLE LOCK
It is now possible to allow or disallow users to use a table lock using the following commands:
ALTER TABLE table_name DISABLE TABLE LOCK;
ALTER TABLE table_name ENABLE TABLE LOCK;
ALTER TABLESPACE ... ADD DATAFILE ... AUTOEXTEND
It is now possible for datafiles to be automatically extended when more space is required. For example:
ALTER TABLESPACE temp ADD DATAFILE 'file' AUTOEXTEND ON;
This feature is of most use in a parallel server environment where a table lock can affect system performance.
CREATE CLUSTER ... HASH IS
It is now possible to use your own PL/SQL functions to calculate the hash key. For example:
CREATE CLUSTER cloudy (deptno number(2))
HASHKEY 20 HASH IS my_hash(deptno);
CREATE DATABASE DATAFILE datafile AUTOEXTEND
It is now possible to create a database with datafiles that will be automatically extended when more space is required. For example:
CREATE DATABASE
DATAFILE 'file' 10M AUTOEXTEND ON;
CREATE INDEX ... UNRECOVERABLE
It is now possible to create an index quickly in ARCHIVELOG mode by avoiding the overhead required to save recovery information. For example:
CREATE INDEX tmp_idx
ON emp(ename)
UNRECOVERABLE;
CREATE TABLE ... UNRECOVERABLE
It is now possible to create a table quickly in ARCHIVELOG mode by avoiding the overhead required to save recovery information. For example:
CREATE TABLE quick_emp
UNRECOVERABLE
AS SELECT * FROM emp WHERE deptno = 10;
CREATE TABLESPACE DATAFILE datafile AUTOEXTEND
It is now possible to create a tablespace with datafiles that will be automatically extended when more space is required. For example:
CREATE TABLESPACE DATAFILE 'file' SIZE 10M AUTOEXTEND ON;
expr
It is now possible to use a user defined PL/SQL function in the same manner as a SQL expression. For example:
SELECT my_fun(ename) FROM emp;
INSERT INTO subquery
It is now possible to use a subquery in the INTO clause of an insert statement similar to how views are used. For example:
INSERT INTO (SELECT * FROM dept)
VALUES (50, 'DEVELOPMENT', 'BELMONT');
SELECT FROM subquery
It is now possible to use a subquery in the FROM clause of a select statement similar to how views are used. For example:
SELECT *
FROM (SELECT * FROM dept) a,
emp b
WHERE a.deptno = b.deptno
TO_CHAR
A number format model using '9's now returns a zero for the value zero. For example:
SELECT TO_CHAR(0,'999') num FROM DUAL;
NUM
----
0
UPDATE subquery
It is now possible to use a subquery in an update statement similar to how views are used. For example:
UPDATE (SELECT * FROM dept)
SET deptno = 50
WHERE deptno = 60
Differences Between Oracle7, Release 7.0 and Release 7.1
ALTER CLUSTER
This command has a PARALLEL clause and a CACHE clause to support the parallel query option.
ALTER DATABASE
This command has a RESET COMPATIBILITY option for compatibility control.
You must have ALTER DATABASE system privilege and your instance must have the database open for you to issue this command.
The RECOVER option of this command has changed to include a PARALLEL clause for use with the parallel recovery feature.
ALTER SESSION
This command has a new SET FLAGGER option to support flagging of SQL extensions that go beyond the SQL92 standard for SQL. The SET FLAGGER option has four additional options: entry, intermediate, full, and off.
This command also has a new option for closing cached cursors used by PL/SQL. Using the ALTER SESSION command with this option overrides the initialization parameter CLOSE_CACHED_OPEN_CURSORS for your current session.
This command also has a new option for specifying the size of the session cursor cache. The syntax is:
ALTER SESSION SET SESSION_CACHED_CURSORS = integer
The integer specified can be any positive integer, but the maximum value is operating-system dependent.
ALTER TABLE
This command has a PARALLEL clause and a CACHE clause to support the parallel query option.
ALTER TABLESPACE
This command has READ ONLY and READ WRITE options to support read-only tablespaces.
This command has BEGIN BACKUP and END BACKUP options to support the parallel server option.
CREATE CLUSTER
This command has a PARALLEL clause and a CACHE clause to support the parallel query option.
CREATE INDEX
This command has a PARALLEL clause to support the parallel query option.
CREATE TABLE
This command has a PARALLEL clause and a CACHE clause to support the parallel query option.
SELECT
There is new syntax and functionality in the following parts of the SELECT command:
SELECT List
Column aliases in the SELECT list can optionally be separated from their expressions by the new AS keyword, as in this example:
SELECT empno, ename AS name
FROM emp
ORDER BY Clause
The ORDER BY clause can now reference column expression aliases defined in the SELECT list. These column expression aliases effectively rename the SELECT list items for the duration of the expression.
Differences Between Oracle Version 6 and Oracle7, Release 7.0
This section indicates differences between Oracle Version 6 and Oracle7, Release 7.0, and contains the following sections:
- terminology introduced in release 7.0
- optional components of Oracle7
Terminology Introduced in Release 7.0
Some new terms have been introduced in Oracle7 that describe features of Oracle Version 6. These are new terms that better explain old concepts:
initialization parameters The term initialization parameter now describes parameters that you use to specify configuration settings when starting an instance.
In Version 6 manuals, these parameters were commonly called INIT.ORA parameters.
schema
The term schema now describes the collection of objects owned by a user. Every user owns a schema in which objects can be created. The name of that schema is the same as the name of the user. The name of an object can be qualified by the schema in which the object exists. For example, the table EMP in the schema of the user SCOTT can be identified by SCOTT.EMP.
In Version 6 manuals, there was no distinction between a user and the collection of objects owned by the user. The name of an object could be qualified with the name of the user who owned it.
server processes
The term server process now describes a process that handles requests from user processes. A server process can be either dedicated to one user process or shared among many user processes, depending on the configuration of your instance.
In Version 6 manuals, these processes were called shadow processes.
Session Control commands
The term Session Control commands now describes a category of SQL commands that manage the properties of a session. This category includes the ALTER SESSION command (described in Version 6 manuals as a Data Definition Language command) and the new SET ROLE command.
system change number (SCN)
The term system change number now describes values that identify committed transactions.
In Version 6 manuals, these values were called system commit numbers. The new term is still abbreviated SCN.
System Control commands
The term System Control commands now describes a category of SQL commands that manage the properties of your Oracle instance. This category includes the new ALTER SYSTEM command.
Transaction Control commands
The term Transaction Control commands now describes a category of SQL commands that manage changes made by Data Manipulation Language commands. This category includes the COMMIT, ROLLBACK, and SAVEPOINT commands (described in Version 6 as Data Manipulation Language commands) and the SET TRANSACTION command (described in Version 6 manuals as a Data Definition Language command).
Reserved Words
This section lists changes to the SQL reserved words in Oracle7:
- new reserved words in Oracle7
- previously reserved words now obsolete
A complete list of all the SQL reserved words for Oracle7, begins .
New Reserved Words
Oracle7 has new SQL reserved words:
ROWLABEL This reserved word is the name of a column automatically created by Trusted Oracle7 for all tables in the database. This column holds the label for each row in the table. For more information on ROWLABEL, see Trusted Oracle7 Server Administrator's Guide.
In the standard Oracle7 Server, ROWLABEL is also a reserved word and always evaluates to null.
VARCHAR2
This reserved word is a datatype for variable length character strings. For more information on this datatype, see the section "Oracle Datatypes" beginning and the section "Character Datatypes" .
Do not use these words to name objects or their parts in Oracle7.
Obsolete Reserved Words
Previous versions of Oracle contained SQL reserved words that are no longer reserved in Oracle7:
You can use these words as names of schema objects or object parts in Oracle7.
Oracle Datatypes
Oracle7 has new datatypes and changes to existing datatypes. This section discusses how Oracle7 treats these types of data:
Numeric Datatypes
Oracle7 returns an error if a numeric expression evaluates to a value greater than or equal to 10126 or less than or equal to -10126. Oracle Version 6 returned a tilde (~) for a value outside these limits.
Character Datatypes
This section discusses the differences in Oracle Version 6 and Oracle7 character datatypes. For information on upgrading to Oracle7 with respect to these differences, see Oracle7 Server Migration.
In Oracle Version 6
Oracle Version 6 supported one datatype for character strings:
CHAR Values of this datatype were variable length character strings of maximum length 255 characters. Oracle Version 6 compared CHAR values using non-padded comparison semantics.
Oracle Version 6 also supported these synonyms for the CHAR datatype:
In Oracle7
Oracle7 supports two datatypes for character strings:
CHAR Values of this datatype are fixed length character strings of maximum length 255 characters. Oracle7 compares CHAR values using blank-padded comparison semantics. Note that the Oracle7 CHAR datatype is not equivalent to the Oracle Version 6 CHAR datatype.
VARCHAR2
Values of this datatype are variable length character strings of maximum length 2000. Oracle7 compares VARCHAR2 values using non-padded comparison semantics. The VARCHAR2 datatype is equivalent to the Oracle Version 6 CHAR datatype except for the difference in maximum lengths.
Attention: Oracle Version 6 only had the CHAR datatype available. In Version 6, VARCHAR and VARCHAR2 were synonyms for CHAR. Thus, the default datatype of character strings was CHAR. In Oracle7, the default character type is VARCHAR2.
Oracle7 also supports these synonyms for the CHAR and VARCHAR2 datatypes:
CHARACTER This datatype is synonymous with the Oracle7 CHAR datatype.
VARCHAR
This datatype is currently synonymous with the VARCHAR2 datatype. However, Oracle Corporation recommends that you use VARCHAR2 rather than VARCHAR. In a future version of Oracle, VARCHAR may be a separate datatype used for variable length character strings compared with different comparison semantics.
For complete information on the Oracle7 datatypes, including the differences between blank-padded and non-padded comparison semantics, see the sections, "Character Datatypes," , and "Datatype Comparison Rules," .
LONG Datatype
The LONG datatype has new properties and fewer restrictions:
- The maximum length a LONG value is now 2 gigabytes, or 231 - 1 bytes, increased from 65,535 bytes.
- You can now use a distributed query to select a LONG column from a remote table or view.
For more information on the LONG datatype, see the section "LONG Datatype" .
Label Data
Labels are used by the Trusted Oracle7 to mediate access to information. The new MLSLABEL datatype is used to store representations of labels. For more information on these datatypes, see Trusted Oracle7 Server Administrator's Guide.
New Commands
These commands are new to the SQL language for Oracle7.
CREATE FUNCTION
| These commands have been
|
ALTER FUNCTION
| added for stored functions.
|
DROP FUNCTION
|
|
CREATE PACKAGE
| These commands have been
|
CREATE PACKAGE BODY
| added for stored packages.
|
ALTER PACKAGE
|
|
DROP PACKAGE
|
|
CREATE PROCEDURE
| These commands have been
|
ALTER PROCEDURE
| added for stored procedures.
|
DROP PROCEDURE
|
|
CREATE TRIGGER
| These commands have been
|
ALTER TRIGGER
| added for database triggers.
|
DROP TRIGGER
|
|
ALTER VIEW
| This command has been added to recompile views.
|
CREATE PROFILE
| These commands have been
|
ALTER PROFILE
| added for resource limits.
|
DROP PROFILE
|
|
ALTER RESOURCE COST
|
|
CREATE ROLE
| These commands have been
|
ALTER ROLE
| added for security.
|
DROP ROLE
|
|
SET ROLE
|
|
CREATE USER
|
|
DROP USER
|
|
CREATE SNAPSHOT
| These commands have been
|
ALTER SNAPSHOT
| added for. snapshots.
|
DROP SNAPSHOT
|
|
CREATE SNAPSHOT LOG
|
|
ALTER SNAPSHOT LOG
|
|
DROP SNAPSHOT LOG
|
|
ALTER SYSTEM
| This command has been added to
|
| perform various specialized
|
| operations on an instance.
|
ANALYZE
| This command has been added to
|
| collect statistics for cost-based
|
| optimization.
|
CREATE CONTROLFILE
| This command has been added
|
| for recovery.
|
CREATE SCHEMA
| This command has been added to
|
| added to issue multiple Data
|
| Definition Language statements
|
| in the same transaction.
|
TRUNCATE
| This command has been added to
|
| added to quickly remove all rows
|
| from a table or cluster.
|
For complete information on each of these commands, see Chapter 4 "Commands" of this manual.
For a list of new embedded SQL commands for Oracle7, see Programmer's Guide to the Oracle Precompilers.
Existing Commands with New Functionality
These commands were part of the SQL language for Oracle Version 6, but they have new syntax or functionality in Oracle7. For complete information on these commands, see the section describing the command in Chapter 4 of this manual. For a list of embedded SQL commands with new syntax or functionality for Oracle7, see Programmer's Guide to the Oracle Precompilers.
ALTER CLUSTER
This command has a new ALLOCATE EXTENT clause for dynamic free space management.
The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and generated an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle generates an error immediately.
For complete information on this parameter, see the section describing the STORAGE clause .
ALTER DATABASE
This command now allows you to specify multiple copies of redo log files and has new clauses to manipulate multiple copies of redo log files:
This command also has these new clauses for managing multiple redo log files for multiple instances of the Oracle7 Parallel Server in parallel mode:
The ADD LOGFILE clause of this command also has a new THREAD parameter for this purpose.
This command also has a new PARALLEL option that replaces the SHARED option from Oracle Version 6.
This command also has the new BACKUP CONTROLFILE, CREATE DATAFILE, and RECOVER clauses for backup and recovery.
This command also has the new RENAME GLOBAL_NAME to change the database's global name.
This command also has a new SET clause to change the MAC mode or to establish the labels DBHIGH and DBLOW with Trusted Oracle7. For more information on this clause, see Trusted Oracle7 Server Administrator's Guide.
The CLOSE and DISMOUNT options of this command that were supported in previous versions are no longer supported. You should use the Server Manager SHUTDOWN command instead. For information on this command, see Oracle Server Manager User's Guide.
ALTER INDEX
The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and generated an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle generates an error immediately.
For complete information on this parameter, see the section describing the STORAGE clause .
ALTER ROLLBACK SEGMENT
You need no longer specify the PUBLIC keyword to alter a public rollback segment, although Oracle still accepts this keyword for backward compatibility.
The STORAGE clause of this command has new syntax and functionality. For a summary of these changes, see the CREATE ROLLBACK SEGMENT command later in this list.
ALTER SESSION
This command has new parameters for National Language Support:
The equal sign (=) following the SQL_TRACE parameter is optional. Equal signs following all other parameters are mandatory.
This command also has a new GLOBAL_NAMES parameter to enable and disable global name resolution for remote objects. For more information on global name resolution, see Chapter "Database Administration" of Oracle7 Server Distributed Systems, Volume I.
This command also has a new LABEL parameter to change your DBMS session label and to change your default label format with Trusted Oracle7. For more information on this command, see Trusted Oracle7 Server Administrator's Guide.
This command also has a new OPTIMIZER_GOAL parameter to change:
- the optimization approach between the rule-based approach and the cost-based approach
- the goal of the cost-based approach between best throughput and best response time
In future versions of Oracle, the rule-based approach will not be available and this parameter will only specify the goal of the cost-based approach.
This command also has a new CLOSE DATABASE LINK clause to explicitly close an open database link.
This command also has a new ADVISE clause for sending advice for forcing in-doubt distributed transactions to remote databases.
This command also has a new COMMIT IN PROCEDURE clause for permitting or prohibiting COMMIT and ROLLBACK commands in procedures and stored functions.
ALTER TABLE
This command has a new ALLOCATE EXTENT clause for dynamic free space management.
The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and generated an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle generates an error immediately.
For complete information on this parameter, see the section describing the STORAGE clause .
This command also has these new clauses to enable and disable integrity constraints and database triggers:
The CONSTRAINT clause of the ALTER TABLE command also has new syntax and functionality. For a summary of these changes, see the CREATE TABLE command later in this list.
DEFAULT values for columns were not enforced by Oracle Version 6. Oracle7 does enforce them. Oracle7 also ensures that a column is long enough to hold its DEFAULT value.
This command also has a new DROP clause for dropping integrity constraints.
For information on the ENABLE, DISABLE, CONSTRAINT, and DROP clauses, see the sections describing them in Chapter 4 "Commands" of this manual.
ALTER TABLESPACE
This command has a new OFFLINE TEMPORARY option. Also, the ONLINE option generates an error message if the tablespace requires media recovery, rather than performing the media recovery transparently.
The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and returned an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle returns an error message immediately.
For information on this parameter, see the section describing the STORAGE clause .
ALTER USER
This command has new clauses to assign tablespaces, profiles, and default roles to users:
AUDIT (SQL Statements)
This form of the AUDIT command has many new system auditing options to support auditing of system operations with finer granularity.
AUDIT (Schema Objects)
This form of the AUDIT command has new object auditing options to support auditing of stored procedures, functions, and packages.
COMMIT
This command has new clauses for managing distributed transactions:
CREATE CLUSTER
This command has these new parameters to create hash clusters:
The STORAGE clause of this command has new syntax and functionality:
- The maximum value of the MAXEXTENTS parameter of the STORAGE varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and returns an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle returns an error message immediately.
- This clause has these new parameters for managing free space:
For complete information on these parameters, see the section describing the STORAGE clause .
CREATE DATABASE
This command now allows you to specify redo log file groups containing multiple copies. This command also has these new parameters:
MAXLOGMEMBERS This parameter specifies the maximum number of members in a single redo log file group.
MAXLOGHISTORY
This parameter specifies the maximum number of archived redo log file groups for automatic media recovery of the Oracle7 Parallel Server.
CHARACTER SET
This parameter specifies the database character set.
CREATE DATABASE LINK
The name of a database link must correspond to the name and domain of the remote database to which it connects. For more information on naming and referring to database links, see the section "Referring to Objects in Remote Databases" .
The USING clause of this command is now optional. This clause specifies the connect string to a remote database.
The USING clause also supports the specification of a secondary database for a read-only mount with Trusted Oracle7. For information on using this command with read-only mounts, see Trusted Oracle7 Server Administrator's Guide.
When you issue a SQL statement that contains a database link, Oracle must determine both of these things before connecting to the remote database:
- a username and password (specified by the CONNECT TO clause of a CREATE DATABASE LINK statement)
- a database string (specified by the USING clause of a CREATE DATABASE LINK statement)
Oracle finds these things by first searching for private database links in your own schema with the same name as the database link in the statement, and then, if necessary, searching for a public database link with the same name.
Oracle always determines the username and password from the first matching database link (either private or public). If the first matching database link has an associated username and password, Oracle uses it. If it does not have an associated username and password, Oracle uses your current username and password.
If the first matching database link has an associated database string, Oracle uses it. If not, Oracle searches for the next matching (public) database link. If there is no matching database link, or if no matching link has an associated database string, Oracle returns an error message.
CREATE INDEX
Enforcing uniqueness among column values is now performed by integrity constraints. Oracle Corporation recommends that you use UNIQUE integrity constraints rather than unique indexes. Unique indexes may not be supported in future versions of Oracle.
The STORAGE clause of this command has new syntax and functionality:
- The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and returned an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle returns an error message immediately.
- This clause has the new FREELISTS parameter for managing free space.
For complete information on these parameters, see the section describing the STORAGE clause .
CREATE ROLLBACK SEGMENT
This command has these changes to the STORAGE clause parameters:
- The PCTINCREASE parameter can no longer be specified for rollback segments. Rollback segments automatically have a PCTINCREASE value of 0.
- The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and returned an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle returns an error message immediately.
For complete information on these parameters, see the section describing the STORAGE clause .
CREATE TABLE
This command has these new clauses to enable and disable integrity constraints and triggers:
The CONSTRAINT clause of the CREATE TABLE command has new syntax and functionality:
- The optional CONSTRAINT identifier must appear at the beginning of the CONSTRAINT clause in Oracle7, rather than at the end as in Oracle Version 6.
- The new ON DELETE CASCADE option allows deletions of referenced key values from the parent table that have dependent rows in the child table and causes Oracle to delete the dependent rows to maintain referential integrity.
- The new DISABLE option allows you to disable an integrity constraint upon creation.
- The new USING INDEX option allows you to specify parameter values and storage characteristics for the index that Oracle7 uses to enforce a UNIQUE or PRIMARY KEY constraint.
- The new EXCEPTIONS INTO clause allows you to identify existing rows that violate a constraint.
Furthermore, Oracle Version 6 only enforced NOT NULL constraints. Oracle7 enforces all types of integrity constraints.
DEFAULT values for columns were not enforced by Oracle Version 6. Oracle7 does enforce them. Oracle7 also ensures columns are long enough to hold their DEFAULT values.
The STORAGE clause of this command has new syntax and functionality:
- The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and generated an error only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle generates an error immediately.
- This clause has these new parameters for managing free space:
For complete information on the ENABLE, DISABLE, CONSTRAINT, and STORAGE clauses, see the sections describing them in Chapter 4 "Commands" of this manual.
CREATE TABLESPACE
The STORAGE clause of this command has new syntax and functionality:
- The maximum value of the MAXEXTENTS parameter of the STORAGE clause varies depending on your data block size:
- In Oracle Version 6, if you specified a value that exceeded the maximum, Oracle stored the specified value in the data dictionary and returned an error message only if there is an attempt to allocate more extents than the maximum MAXEXTENTS value.
- In Oracle7, if you specify a value greater than the maximum, Oracle returns an error message immediately.
- This clause has these new parameters for managing free space:
For complete information on these parameters, see the section describing the STORAGE clause .
CREATE VIEW
This command has these new options:
OR REPLACE This option allows you to redefine a view without dropping and recreating it and regranting object privileges previously granted on it.
FORCE
This option allows you to create a view even if the tables, views, and snapshots that it queries do not exist.
NOFORCE
This option prevents you from creating a view if the tables, views, and snapshots that it queries do not exist. This is the default option and is equivalent to the behavior of Version 6.
The authorization of this command is slightly different in Oracle7 than in Oracle Version 6. In Oracle Version 6, a user granted the DBA system privilege could create a view based on any table in any schema. In Oracle7, a user granted the predefined DBA role can only create a view if the owner of the schema to contain the view is granted privileges to select, insert, update, or delete rows from the base table. These privileges must be granted directly, rather than through roles.
DELETE
This command now allows you to delete rows from a remote table or view using a database link.
DROP CLUSTER
This command has a new CASCADE CONSTRAINTS option to allow you to drop referential integrity constraints from tables outside the dropped cluster that refer to primary and unique keys in the tables of the cluster.
DROP ROLLBACK SEGMENT
You need no longer specify the PUBLIC keyword to drop a public rollback segment, although Oracle7 still accepts this keyword for backward compatibility.
DROP TABLE
This command has a new CASCADE CONSTRAINTS option to allow you to drop referential integrity constraints that refer to primary and unique keys in a dropped table.
EXPLAIN PLAN
The INTO clause of this command can now contain a remote table qualified by a database link.
The SQL statement in the FOR clause can now contain bind variables. Oracle assumes these bind variables are of datatype VARCHAR2.
GRANT (System Privileges and Roles)
In Oracle7, this form of the GRANT command is the same as Form I in Oracle Version 6. It also has many new system privileges to support security management with finer granularity. This form of the GRANT command can also administer roles.
In Oracle Version 6, the GRANT command (Form I) was also used to create users and change passwords. In Oracle7, you can use the CREATE USER and ALTER USER commands to perform these tasks. Oracle Corporation recommends that you use the CREATE USER and ALTER USER commands rather than the GRANT command. Using the GRANT command for these purposes may not be supported in future versions of Oracle. For information on using the GRANT command for these purposes, see the SQL Language Reference Manual for Oracle Version 6.
In Oracle Version 6, the GRANT command (Form II) gave users access to tablespaces. In Oracle7, you can only perform this task with the new TABLESPACE clause of the CREATE USER and ALTER USER commands.
GRANT (Object Privileges)
In Oracle7, this form of the GRANT command is the same as Form III in Oracle Version 6. This form of the command grants privileges on specific objects. In Oracle7, this form has new object privileges for security management of stored procedures, functions, and packages.
INSERT
This command now allows you to insert rows into a remote table or view using a database link.
LOCK TABLE
This command now allows you to lock a remote table or view using a database link.
NOAUDIT
Changes to the NOAUDIT command correspond directly to the changes to the AUDIT command listed earlier in this section.
REVOKE
Changes to the REVOKE command correspond directly to the changes to the GRANT command listed earlier in this section.
ROLLBACK
This command has a new FORCE clause for managing distributed transactions.
SELECT
Oracle7 places fewer restrictions on distributed queries than Oracle Version 6. For complete information on distributed queries, see the section, "Distributed Queries," .
In Oracle Version 6, you could specify a column of a remote table in the select list using this syntax:
table@dblink.column
Since Oracle7 interprets all characters following @ to be the complete name of a database link, you cannot use this syntax in Oracle7. For example, you can issue this query in Oracle Version 6, but not in Oracle7:
SELECT emp@boston.ename
FROM emp@boston
Oracle7 interprets 'boston.ename' to be the complete name of a database link. In Oracle7, you can instead issue one of these equivalent queries also accepted by Oracle Version 6:
SELECT e.ename
FROM emp@boston e
SELECT ename
FROM emp@boston
You can also issue this equivalent query that was not acceptable in Oracle Version 6:
SELECT emp.ename@boston
FROM emp@boston
Also, in Oracle Version 6, you could qualify a table.column expression with a schema in the select list regardless of whether the table was qualified with a schema in the FROM clause. In Oracle7, you can only qualify a table.column expression with a schema if the table is qualified with a schema in the FROM clause. For example, you could issue this query in Oracle Version 6, but not in Oracle7:
SELECT scott.emp.ename
FROM emp
Oracle7 places more restrictions on the WHERE clause conditions of SELECT statements that perform outer joins:
- The OR logical operator cannot combine two conditions if either contains the outer join operator (+). Also, a condition cannot use the IN logical operator to compare a column marked with the (+) operator to another expression. If you have applications that issue queries with such conditions, replace them with equivalent queries that use the UNION or UNION ALL set operators instead.
- If a condition compares a column marked with the (+) operator to a subquery, Oracle7 returns an error message. Oracle Version 6 ignored the (+) operator in such conditions. If you have applications that issue queries with such conditions, remove the (+) operator from them and they will behave in Oracle7 as they did in Oracle Version 6.
SET TRANSACTION
This command has these new options:
READ WRITE This option establishes the current transaction as a read-write transaction in which data can be both queried and modified, as opposed to a read-only transaction in which data can only be queried and not modified. Oracle establishes a read-write transaction by default if you do not issue a SET TRANSACTION statement.
USE ROLLBACK SEGMENT
This option allows you to assign your current transaction to a specific rollback segment.
UPDATE
This command now allows you to update values in remote tables and views using a database link.
VALIDATE INDEX
Validating indexes is now also performed by the new ANALYZE command. Oracle Corporation recommends that you use the ANALYZE command rather than the VALIDATE INDEX command. The VALIDATE INDEX command may not be supported in future versions of Oracle. For information on the VALIDATE INDEX command, see the SQL Language Reference Manual for Oracle Version 6.
SQL Functions
This section lists:
- new SQL functions added for Oracle7
- existing SQL functions with new functionality
New SQL Functions
These new SQL functions have been added for Oracle7:
These new SQL functions have been added for Trusted Oracle7:
Existing SQL Functions with New Functionality
These functions have been enhanced for Oracle7:
- In Trusted Oracle7, the TO_CHAR function converts values with the datatypes MLSLABEL or RAW MLSLABEL to values with the datatype VARCHAR2.
For complete information on these functions, see the section "Functions" .
Format Models
These new number format elements have been added to SQL for Oracle7:
These new date format elements have been added to SQL for Oracle7:
If you used National Language Support in Oracle Version 6, the WW date format element may behave differently in Oracle7. In Version 6, depending on the territory component of the value of the LANGAUGE initialization parameter, WW returned a week number based on either the ISO standard or the number of days from January 1. In Oracle7, WW always returns a week number based on the number of days from January 1, regardless of the value of the NLS_TERRITORY initialization parameter, and the new IW date format element returns the ISO standard week number. If your Version 6 application used WW to return the ISO standard week number, replace WW with IW.
Oracle7 also has a new format model modifier FX and new functionality for the FM format model modifier. For information on format models, see the section "Format Models" .
Operators
This section describes:
- existing operators with changes in functionality
New Operators
These new operators have been added to SQL for Oracle7:
SOME This new comparison operator is synonymous with the ANY comparison operator.
UNION ALL
This new set operator combines two queries and returns all rows returned by either query, including all duplicate rows. The UNION ALL operator is similar to the UNION operator, except the UNION operator returns only one copy of duplicate rows.
Existing Operators with Functional Changes
The functionality of these existing operators has changed for Oracle7:
- Do not use consecutive minus signs with no separation in arithmetic expressions to indicate double negation or the subtraction of a negative value. The characters -- are used to begin comments within SQL statements. If you have applications that issue SQL statements with such arithmetic expressions, separate the minus signs with a space or a parenthesis.
LIKE
The LIKE operator accepts the new ESCAPE option, which allows you to use the characters % and _ literally, rather than as special pattern matching characters, within a pattern.
(+)
The outer join operator is subject to new restrictions listed in the section describing the SELECT command earlier in this chapter.
Comments
Oracle7 supports comments within SQL statements beginning with -- as well as comments beginning with /*. For more information on comments within SQL statements, see the section "Comments" beginning .
Namespaces
This section describes:
- changes to namespaces for schema objects
- changes to namespaces for other objects
Changes to Namespaces for Schema Objects
Figure A - 1 shows the namespaces for schema objects in Oracle Version 6:
Figure A - 1. Namespaces for Schema Objects in Oracle Version 6
For Oracle7, changes have been made to these namespaces:
- Stand-alone procedures, stand-alone stored functions, packages, and snapshots have been added to the namespace containing tables.
- Indexes have been moved from the namespace containing tables to a new namespace.
- Clusters have been moved from the namespace containing tables to a new namespace.
- Database triggers have been added in a new namespace.
These changes are shown in bold in Figure A - 2.
Figure A - 2. Changes in Namespaces for Schema Objects for Oracle7
Changes to Namespaces for Other Objects
Figure A - 3 shows the namespaces for other objects in Oracle Version 6:
Figure A - 3. Namespaces for Other Objects in Oracle Version 6
For Oracle7, changes have been made to these namespaces:
- Roles have been added to the namespace containing users.
- Profiles have been added to a new namespace.
These changes are shown in bold in Figure A - 4.
Figure A - 4. Changes in Namespaces for Other Objects in Oracle7
Changes to the Optional Components of Oracle
This section discusses the differences in the optional components between Oracle Version 6 and Oracle7.
With Oracle Version 6, the transaction processing option was available. This option included these features:
With Oracle7, the transaction processing option is obsolete. However, these options are available:
procedural option This option includes PL/SQL and allows you to use anonymous PL/SQL blocks, stored procedures, stored functions, stored packages, and database triggers.
distributed option
This option allows you to issue Data Manipulation Language (DELETE, EXPLAIN PLAN, LOCK TABLE, INSERT, and UPDATE) statements that modify data on remote databases.
Parallel Server option
This option allows multiple Oracle instances to mount an Oracle7 database in parallel mode. This functionality was also available in Oracle Version 6.2.
To use snapshots, you must have both the procedural option and the distributed option. All other features of Oracle7 (including row-level locking) are available in all installations and do not require one of these options.
Compatibility Modes
The compatibility mode controls Oracle7's behavior in a few areas for which there are minor differences between Oracle Version 6 and Oracle7. Oracle7 can operate in these compatibility modes:
V7 compatibility mode In this mode, Oracle interprets SQL exactly as described in this manual.
V6 compatibility mode
In this mode, Oracle interprets SQL as described in this manual, with some exceptions for compatibility with Oracle Version 6.
Table 4 - 14 describes the differences between V6 and V7 compatibility modes:
V6 Compatibility Mode
| V7 Compatibility Mode
|
If you define a column of datatype CHAR, Oracle creates the column with the Oracle7 VARCHAR2 datatype, which is equivalent to the Oracle Version 6 CHAR datatype. The column is a variable-length character string with non-padded comparison semantics and a maximum length of 2000 bytes.
| If you define a column of datatype CHAR, Oracle creates the column with the Oracle7 CHAR datatype, which is not equivalent to the Oracle Version 6 CHAR datatype. The column is fixed-length character string with blank-padded comparison semantics and a maximum length of 255 bytes.
|
The optimal CONSTRAINT identifier can only appear at the end of a CONSTRAINT clause.
| The optional CONSTRAINT identifier can only appear at the beginning of a CONSTRAINT clause.
|
By default, PRIMARY KEY, UNIQUE, referential integrity, and CHECK constraints are disabled upon creation. NOT NULL constraints are enabled upon creation by default.
| By default, all integrity constraints are enabled upon creation.
|
If you specify a PCTINCREASE value for a rollback segment, Oracle ignores this value and uses a value of 0.
| If you specify a PCTINCREASE value for a rollback segment, Oracle returns an error.
|
If you specify a MAXEXTENTS value that exceeds the maximum possible value based on the data block size, Oracle ignores the specified value and uses the maximum possible value.
| If you specify a MAXEXTENTS value that exceeds the maximum possible value based on the data block size, Oracle returns an error.
|
Table 4 - 14. Differences Between V6 and V7 Compatibility Modes
There are additional differences between the V6 and V7 compatibility modes that are specific to the Oracle Precompilers and the Oracle Call Interfaces (OCIs). For information on these differences, see Programmer's Guide to the Oracle Precompilers and Programmer's Guide to the Oracle Call Interface.
Migrating to Oracle7
You may want to establish V6 compatibility mode when you initially upgrade to Oracle7 in order ease the migration of your existing Oracle Version 6 applications. Establishing V6 compatibility mode reduces (but does not eliminate) the number of changes you may have to make to your applications before running them on Oracle7. Note that there is some SQL syntax supported by Oracle Version 6 that is not supported by Oracle7 in either V6 or V7 compatibility mode. If you have existing applications that you have run on Oracle Version 6, see Oracle7 Server Migration for a list of the changes that you must make to these applications before running them on Oracle7.
You should eventually upgrade your applications so that they can be run in V7 compatibility mode, rather than V6 compatibility mode.
Establishing and Switching Between Compatibility Modes
By default, all sessions on Oracle7 initially run in V7 compatibility mode. Some Oracle application tools allow you to establish and switch between compatibility modes for your sessions. For information on how to establish and switch between compatibility modes, see the manual for the specific tool. For example, to find out how to switch between compatibility modes with SQL*Plus, see SQL*Plus User's Guide and Reference.