Oracle8 SQL Reference
Release 8.0

A58225-01

Library

Product

Index

Next

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
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Index