Oracle8 SQL Reference
Release 8.0
A58225-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

History of SQL

SQL Standards

Embedded SQL

Lexical Conventions

Tools Support

2 Elements of Oracle8 SQL

Text

Nulls

Pseudocolumns

Database Objects

Schema Object Names and Qualifiers

Referring to Schema Objects and Parts

3 Operators, Functions, Expressions, Conditions

SQL Functions

User Functions

Format Models

Expressions

4 Commands

Summary of SQL Commands

Data Definition Language (DDL) Commands

Data Manipulation Language (DML) Commands

Transaction Control Commands

Session Control Commands

System Control Command

Embedded SQL Commands

ALTER CLUSTER

Altering Clusters

ALTER DATABASE

ALTER FUNCTION

Recompiling Standalone Functions

ALTER INDEX

ALTER PACKAGE

Recompiling Stored Packages

ALTER PROCEDURE

Recompiling Stored Procedures

ALTER PROFILE

Using Password History

ALTER RESOURCE COST

Altering Resource Costs

Changing Authorizations

ALTER ROLLBACK SEGMENT

Altering Rollback Segments

ALTER SEQUENCE

ALTER SESSION

Enabling and Disabling the SQL Trace Facility

Using NLS Parameters

Changing the Optimization Approach and Mode

Caching Session Cursors

Accessing the Database as if Connected to Another Instance in a Parallel Server

Closing Database Links

Forcing In-Doubt Distributed Transactions

Transaction Control in Procedures and Stored Functions

ALTER SNAPSHOT

Specifying Rollback Segments

Primary Key Snapshots

Partitioned Snapshots

ALTER SNAPSHOT LOG

Modifying Physical Attributes

Adding Primary Key, ROWID, and Filter Columns

Partitioned Snapshot Logs

ALTER SYSTEM

Restricting Logons

Clearing the Shared Pool

Performing a Checkpoint

Checking Datafiles

Using Resource Limits

Global Name Resolution

Managing Processes for the Multithreaded Server

Using Licensing Limits

Switching Redo Log File Groups

Enabling and Disabling Distributed Recovery

Terminating a Session

Disconnecting a Session

ALTER TABLE

Modifying Column Definitions

Index-Organized Tables

Nested Table Columns

Modifying Table Partitions

ALTER TABLESPACE

Using ALTER TABLESPACE

ALTER TRIGGER

Invalid Triggers

Enabling and Disabling Triggers

Establishing Default Roles

Changing Authentication Methods

Recompiling Views

Collecting Statistics

Deleting Statistics

Validating Structures

Listing Chained Rows

ARCHIVE LOG clause

AUDIT (SQL Statements)

Statement Options for Database Objects

Statement Options for Commands

Shortcuts for System Privileges and Statement Options

AUDIT (Schema Objects)

Default Auditing

About Transactions

Ending Transactions

CONSTRAINT clause

Defining Integrity Constraints

NOT NULL Constraints

UNIQUE Constraints

PRIMARY KEY Constraints

Referential Integrity Constraints

CHECK Constraints

DEFERRABLE Constraints

Enabling and Disabling Constraints

CREATE CLUSTER

Types of Clusters

Adding Tables to a Cluster

CREATE CONTROLFILE

Re-creating Control Files

CREATE DATABASE

CREATE DATABASE LINK

Creating Database Links

Current-User Database Links

CREATE DIRECTORY

Directory Objects

CREATE FUNCTION

CREATE INDEX

Creating Indexes

Multiple Indexes Per Table

The NOSORT Option

Creating Cluster Indexes

Creating Partitioned Indexes

Creating Bitmap Indexes

Creating Indexes on Nested Table Columns

CREATE LIBRARY

CREATE PACKAGE

CREATE PACKAGE BODY

CREATE PROCEDURE

Using Procedures

CREATE PROFILE

Fractions in Dates

The DEFAULT Profile

CREATE ROLE

Roles Predefined by Oracle

CREATE ROLLBACK SEGMENT

Rollback Segments and Tablespaces

CREATE SCHEMA

Creating Schemas

CREATE SEQUENCE

Using Sequences

Sequence Defaults

Incrementing Sequence Values

Caching Sequence Numbers

Accessing Sequence Values

CREATE SNAPSHOT

About Snapshots

Types of Snapshots

Refreshing Snapshots

Specifying Rollback Segments

Specifying Primary-Key or ROWID Snapshots

Partitioned Snapshots

CREATE SNAPSHOT LOG

Using Snapshot Logs

Recording Primary Keys, ROWIDs, and Filter Columns

CREATE SYNONYM

Scope of Synonyms

CREATE TABLE

LOB Column Example

Index-Organized Tables

Partitioned Tables

Nested Table Storage

CREATE TABLESPACE

About Tablespaces

CREATE TRIGGER

Conditional Predicates

Parts of a Trigger

Types of Triggers

Snapshot Log Triggers

INSTEAD OF Triggers

User-Defined Types, LOB, and REF Columns

CREATE TYPE

Incomplete Object Types

CREATE TYPE BODY

TYPE and TYPE BODY

CREATE USER

Verifying Users Through Your Operating System

Verifying Users Through the Network

Establishing Tablespace Quotas for Users

Granting Privileges to a User

CREATE VIEW

Partition Views

DEALLOCATE UNUSED clause

Deallocating Unused Space

Deleting from a Single Partition

The RETURNING Clause

DISABLE clause

Using the DISABLE Clause

DROP clause

Removing Integrity Constraints

DROP CLUSTER

DROP DATABASE LINK

DROP DIRECTORY

Dropping a Directory

DROP FUNCTION

Dropping Functions

Dropping an Index

DROP LIBRARY

DROP PACKAGE

Dropping a Package

DROP PROCEDURE

Dropping a Procedure

DROP PROFILE

Dropping a Profile

Dropping a Role

DROP ROLLBACK SEGMENT

Dropping Rollback Segments

DROP SEQUENCE

Dropping Sequences

DROP SNAPSHOT

Dropping Snapshots

DROP SNAPSHOT LOG

Dropping Snapshot Logs

DROP SYNONYM

Dropping Synonyms

Dropping Tables

DROP TABLESPACE

Dropping Tablespaces

DROP TRIGGER

Dropping Triggers

DROP TYPE BODY

Dropping Type Bodies

Dropping Users and Their Objects

ENABLE clause

Enabling and Disabling Constraints

How Oracle Validates Integrity Constraints

How to Identify Exceptions

Enabling Triggers

EXPLAIN PLAN

Using EXPLAIN PLAN

EXPLAIN PLAN and Partitioned Tables

EXPLAIN PLAN and Parallel DML

GRANT (System Privileges and Roles)

Granting System Privileges and Roles

Granting the ADMIN OPTION

Other Authorization Methods

GRANT (Object Privileges)

Database Object Privileges

Synonym Privileges

Directory Privileges

The VALUES Clause and Subqueries

Inserting Into Views

The RETURNING Clause

NOAUDIT (SQL Statements)

Stopping Auditing

NOAUDIT (Schema Objects)

PARALLEL clause

Using the PARALLEL Clause

Nonpartitioned Tables and Indexes

Partitioned Tables and Indexes

RECOVER clause

Renaming Objects

REVOKE (System Privileges and Roles)

Revoking Privileges

REVOKE (Schema Object Privileges)

Revoking Object Privileges

Revoking Multiple Identical Grants

Cascading Revokes

Rolling Back Transactions

Distributed Transactions

Creating Savepoints

Creating Simple Queries

Hierarchical Queries

GROUP BY Clause

UNION, UNION ALL, INTERSECT, and MINUS

ORDER BY Clause

FOR UPDATE Clause

SET CONSTRAINT(S)

Privilege Domains

SET TRANSACTION

Establishing Read-Only Transactions

Assigning Transactions to Rollback Segments

STORAGE clause

Specifying Storage Parameters

Rollback Segments and MAXEXTENTS UNLIMITED

Using Subqueries

Using Flattened Subqueries

Correlated Subqueries

Selecting from the DUAL Table

Using Sequences

Distributed Queries

Truncating Tables and Clusters

Updating Partitioned Tables

Correlated Update

The RETURNING Clause

A Syntax Diagrams

B Oracle and Standard SQL

Conformance with Standard SQL

Extensions to Standard SQL

C Oracle Reserved Words and Keywords

Index

Copyright © 1997 Oracle Corporation.
All Rights Reserved.