Oracle7 Server SQL Reference Manual Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index


Go to previous file in sequence Go to next file in sequence

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:

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:

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

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

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:

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:

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

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:

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:

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.


Go to previous file in sequence Go to next file in sequence
Prev Next		 Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.		 Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index