Oracle7 Server SQL Reference

Contents Index Home Previous Next

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.

Contents Index Home Previous Next