Oracle7 Server SQL Reference

APPENDIX A. 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:

acos

asin

atan

atan2

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 - 255.

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;

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

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

ORDER BY clause

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

reserved words

Oracle datatypes

commands

SQL functions

format elements

operators

comments

namespaces

system privileges

optional components of Oracle7

compatibility modes

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).