Oracle8 SQL Reference Release 8.0 A58225-01

Library Product Index

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

Literals

Text

Integer

Number

Datatypes

Nulls

Pseudocolumns

Comments

Database Objects

Schema Object Names and Qualifiers

Referring to Schema Objects and Parts

3 Operators, Functions, Expressions, Conditions

Operators

SQL Functions

User Functions

Format Models

Expressions

Conditions

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

Examples

ALTER FUNCTION

Recompiling Standalone Functions

ALTER INDEX

Examples

ALTER PACKAGE

Recompiling Stored Packages

ALTER PROCEDURE

Recompiling Stored Procedures

ALTER PROFILE

Using Password History

Examples

ALTER RESOURCE COST

Altering Resource Costs

ALTER ROLE

Changing Authorizations

ALTER ROLLBACK SEGMENT

Altering Rollback Segments

ALTER SEQUENCE

Examples

ALTER SESSION

Enabling and Disabling the SQL Trace Facility

Using NLS Parameters

Changing the Optimization Approach and Mode

FIPS Flagging

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

Parallel DML

ALTER SNAPSHOT

Examples

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

Adding Columns

Modifying Column Definitions

Index-Organized Tables

LOB Columns

Nested Table Columns

REFs

Modifying Table Partitions

ALTER TABLESPACE

Using ALTER TABLESPACE

ALTER TRIGGER

Invalid Triggers

Enabling and Disabling Triggers

ALTER TYPE

Restriction

ALTER USER

Establishing Default Roles

Changing Authentication Methods

ALTER VIEW

Recompiling Views

ANALYZE

Restrictions

Collecting Statistics

Clusters

Deleting Statistics

Validating Structures

Listing Chained Rows

ARCHIVE LOG clause

Restrictions

AUDIT (SQL Statements)

Auditing

Statement Options for Database Objects

Statement Options for Commands

Shortcuts for System Privileges and Statement Options

AUDIT (Schema Objects)

Object Options

Default Auditing

COMMENT

Using Comments

COMMIT

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

About Clusters

Cluster Keys

Types of Clusters

Cluster Size

Adding Tables to a Cluster

CREATE CONTROLFILE

Re-creating Control Files

CREATE DATABASE

Examples

CREATE DATABASE LINK

Creating Database Links

Current-User Database Links

Examples

CREATE DIRECTORY

Directory Objects

CREATE FUNCTION

Examples

CREATE INDEX

Creating Indexes

Index Columns

Multiple Indexes Per Table

The NOSORT Option

NOLOGGING

Nulls

Creating Cluster Indexes

Creating Partitioned Indexes

Creating Bitmap Indexes

Creating Indexes on Nested Table Columns

CREATE LIBRARY

Examples

CREATE PACKAGE

Packages

CREATE PACKAGE BODY

Examples

CREATE PROCEDURE

Using Procedures

CREATE PROFILE

Using Profiles

Fractions in Dates

The DEFAULT Profile

CREATE ROLE

Using Roles

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

Using Synonyms

Scope of Synonyms

CREATE TABLE

Examples

LOB Column Example

Index-Organized Tables

Partitioned Tables

Object Tables

Nested Table Storage

REFs

CREATE TABLESPACE

About Tablespaces

CREATE TRIGGER

Using Triggers

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

Constructors

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

Using Views

The View Query

Join Views

Partition Views

Examples

Object Views

DEALLOCATE UNUSED clause

Deallocating Unused Space

DELETE

Using DELETE

Deleting from a Single Partition

The RETURNING Clause

DISABLE clause

Using the DISABLE Clause

DROP clause

Removing Integrity Constraints

DROP CLUSTER

Restrictions

DROP DATABASE LINK

Restrictions

Example

DROP DIRECTORY

Dropping a Directory

DROP FUNCTION

Dropping Functions

DROP INDEX

Dropping an Index

DROP LIBRARY

Example

DROP PACKAGE

Dropping a Package

DROP PROCEDURE

Dropping a Procedure

DROP PROFILE

Dropping a Profile

DROP ROLE

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

DROP TABLE

Dropping Tables

DROP TABLESPACE

Dropping Tablespaces

DROP TRIGGER

Dropping Triggers

DROP TYPE

Dropping Types

DROP TYPE BODY

Dropping Type Bodies

DROP USER

Dropping Users and Their Objects

DROP VIEW

Dropping Views

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

Filespec

Examples

GRANT (System Privileges and Roles)

Granting System Privileges and Roles

Granting the ADMIN OPTION

Other Authorization Methods

Examples

GRANT (Object Privileges)

Database Object Privileges

Synonym Privileges

Directory Privileges

Examples

INSERT

The VALUES Clause and Subqueries

Parallel DML

Inserting Into Views

The RETURNING Clause

Examples

LOCK TABLE

Locking Tables

NOAUDIT (SQL Statements)

Stopping Auditing

NOAUDIT (Schema Objects)

Examples

PARALLEL clause

Using the PARALLEL Clause

Nonpartitioned Tables and Indexes

Partitioned Tables and Indexes

Examples

RECOVER clause

Examples

RENAME

Renaming Objects

Restrictions

REVOKE (System Privileges and Roles)

Revoking Privileges

Revoking Roles

Limitations

Examples

REVOKE (Schema Object Privileges)

Revoking Object Privileges

Using FORCE

Revoking Multiple Identical Grants

Cascading Revokes

Examples

ROLLBACK

Rolling Back Transactions

Distributed Transactions

SAVEPOINT

Creating Savepoints

SELECT

Creating Simple Queries

Hierarchical Queries

GROUP BY Clause

HAVING Clause

UNION, UNION ALL, INTERSECT, and MINUS

ORDER BY Clause

FOR UPDATE Clause

Joins

SET CONSTRAINT(S)

Examples

SET ROLE

Privilege Domains

Examples

SET TRANSACTION

Establishing Read-Only Transactions

Assigning Transactions to Rollback Segments

STORAGE clause

Specifying Storage Parameters

Rollback Segments and MAXEXTENTS UNLIMITED

Examples

Subqueries

Using Subqueries

Using Flattened Subqueries

Correlated Subqueries

Selecting from the DUAL Table

Using Sequences

Distributed Queries

TRUNCATE

Truncating Tables and Clusters

Restrictions

Examples

UPDATE

Updating Views

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

Next

Copyright © 1997 Oracle Corporation. All Rights Reserved.

Library Product Index