Entry Topic
Oracle7 Server SQL Language Quick Reference

Oracle7™ SQL Language Quick Reference

SQL Language Quick Reference


This help file created via:

 Version 1.0m

© 1994 Oracle Corporation Inc.

Authored by KM .


Part# A12712-1


Oracle7ä SQL Language Quick Reference
Oracle7™ SQL Language Quick Reference

Part No. A12712-1

August 10, 1993

Oracle7 Server SQL Language Quick Reference

Part No. A12712-1

Copyright  1993 Oracle Corporation

Contributing Author:  Eric  Armstrong

This software/documentation contains proprietary information of Oracle Corporation; it is provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law.  Reverse engineering of the software is prohibited.

If this software/documentation is delivered to a U.S. Government Agency of the Department of Defense, then it is delivered with Restricted Rights and the following legend is applicable:

Restricted Rights Legend  Use, duplication, or disclosure by the Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of DFARS 252.227-7013, Rights in Technical Data and Computer Software (October 1988). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA  94065.

If this software/documentation is delivered to a U.S. Government Agency not within the Department of Defense, then it is delivered with "Restricted Rights," as defined in FAR 52.227-14, Rights in Data - General, including Alternate III (June 1987).

The information in this document is subject to change without notice.   If you find any problems in the documentation, please report them to us in writing.   Oracle Corporation does not warrant that this document is error-free.

Oracle, SQL*Loader, and SQL*Net are registered trademarks of Oracle Corporation.  PL/SQL is a trademark of Oracle Corporation.


_______________________________________________________________


SQL Language Quick Reference

This Reference briefly describes elements of SQL and the ORACLE7 data dictionary.  For details on SQL, refer to the SQL Language Reference Manual. For information on the data dictionary see the ORACLE7 Server Administrator's Guide or the ORACLE7 Server Application Developer's Guide. For information on Trusted ORACLE, see the Trusted ORACLEL7 Server Administrator's Guide.

________________________________________

Index of Major Sections

Functions	 3	Privileges	 15	Comments	 63

Format Models	 8	Expressions and 	 19	Special Tables	 64

Pseudocolumns	 12	Conditions	Data Dictionary Views	 65

Datatypes	 12	Operators	 22	Dynamic Tables	 71

Names and Reserved 	 13	SQL Commands	 24

Words	Embedded SQL	 64

________________________________________

Index of Commands

ALTER CLUSTER	 24	COMMENT	 39	CREATE VIEW	 54

ALTER DATABASE	 24	COMMIT	 39	DELETE	 54

ALTER FUNCTION	 27	CREATE CLUSTER	 39	DROP	 55

ALTER INDEX	 27	CREATE CONTROLFILE	 40	EXPLAIN PLAN	 57

ALTER PACKAGE	 27	CREATE DATABASE	 41	GRANT (System Privi	 57

ALTER PROCEDURE	 27	CREATE DATABASE 	 41	leges and Roles)

ALTER PROFILE	 27	LINK	GRANT (Object 	 57

ALTER RESOURCE  	 28	CREATE FUNCTION	 42	Privileges)

COST	CREATE INDEX	 42	INSERT	 58

ALTER ROLE	 28	CREATE PACKAGE	 43	LOCK TABLE	 58

ALTER ROLLBACK 	 28	CREATE PACKAGE 	 43	NOAUDIT (SQL 	 58

SEGMENT	BODY	Statements)

ALTER SEQUENCE	 29	CREATE PROCEDURE	 43	NOAUDIT (Schema 	 59

ALTER SESSION	 29	CREATE PROFILE	 44	Objects)

ALTER SNAPSHOT	 30	CREATE ROLE	 44	RENAME	 59

ALTER SNAPSHOT LOG	 31	CRATE ROLLBACK 	 45	REVOKE (System 	 59

ALTER SYSTEM	 31	SEGMENT	Privileges and Roles)

ALTER TABLE	 32	CREATE SCHEMA	 45	REVOKE (Object 	 59

ALTER TABLESPACE	 34	CREATE SEQUENCE	 45	Privileges)

ALTER TRIGGER	 34	CREATE SNAPSHOT	 46	ROLLBACK	 60

ALTER USER	 34	CREATE SNAPSHOT 	 47	SAVEPOINT	 60

ALTER VIEW	 35	LOG	SELECT	 60

ANALYZE	 35	CREATE SYNONYM	 47	SET ROLE	 62

AUDIT (SQL Statements)	 36	CREATE TABLE	 48	SET TRANSACTION	 62

AUDIT (Schema Objects)	 38	CREATE TABLESPACE	 52	TRUNCATE	 63

CREATE TRIGGER	 53	UPDATE	 63

CREATE USER	 53

________________________________________

Parameters mentioned in this Reference

Parameter	Explanation

c_alias	Column alias. Another name for a column specified in SELECT statement.

char	Column constant in single quotes or expression of datatype CHAR or VAR

CHAR2.

column_element	Column definition. For the format of a column element refer to the CREATE

TABLE statement.

condition	Expression that logically evaluates to TRUE, FALSE, or UNKNOWN.

constraint	The name of an integrity constraint, stored in the data dictionary.

dblink	Database link.

expr	Any expression.

location	Operating-system-dependent specification of a file's location. Usually either a

device or a directory.

integer	Number constant or expression of datatype NUMBER. Must evaluate to an inte

ger.

m,n	Any number constant or expression of datatype NUMBER.

'nls_sort'	An expression of the form 'NLS_SORT = name', where name is either BINARY or

the name of a linguistic sort sequence

subquery	Query expression in another statement, such as CREATE TABLE---AS subquery

rowid	Expression of datatype ROWID>

t_alias	Table alias. Another name for a table specified in a SELECT statement

scn	System Change Number. A number that uniquely identifies a transaction.

sid,serial#	Session ID and serial number. A combination that uniquely identified a session.

________________________________________

Functions

Single-Row Number Functions

Functions	Value Returned

ABS(n)	Absolute value of n.

CEIL(n)	Smallest integer greater than or equal to n.99

COS(n)	Cosine of n in radians.

COSH(n)	Hyperbolic cosine of n in radians..

EXP(n)	e raised to the nth power.

FLOOR(n)	Largest integer equal to or less than n.

LN(n)	Natural logarithm of n, where n > 0

LOG(m,n)	Logarithm, base m, of n.

MOD(m,n)	Remainder of m divided by n.

POWER(m,n)	<MI%-2>m raised to the nth power.<%0>

ROUND(n[,m])	n rounded to m decimal places; m defaults to 0.

SIGN(n)	If n<0, -1; if n=0, 0; if n>0, 1.

SIN(n)	Sine of n in radians.

SINH(n)	Hyperbolic sine of n in radians.

SQRT(n)	Square root of n; if n<0, NULL.

TAN(n)	Tangent of n in radians.

TANH(n)	Hyperbolic tangent of n in radians.

TRUNC(n[,m])	<MI%-2>n truncated to m decimal places; m defaults to 0.<%0>

Single-Row Character Functions

Single row character functions that return character values:

Function	Value Returned

CHR(n)	CharacDate of the first day of the week named by

char that is equal to or later than the date d.

CONCAT(char1,char2)	Concatenates char1 and char2.

INITCAP(char)	char, with first letter of each word capitalized.

LOWER(char)	char, with all letters lowercase.

LPAD(char1,n[,char2])	char1, left-padded to length n with the sequence of

characters in char2;  char2 defaults to ''. A multi-

byte character that does not fit is replaced with a

blank.

LTRIM(char[,set])	char, with initial characters removed up to the first

character not in set. set defaults to ' '.

NLS_INITCAP(n [, nls_sort])	char, with first letter of each word capitalized.

nls_sort defines handling of language-specific capi

talization.

NLS_LOWER(n [, nls_sort])	char, with all letters lowercase. nls_sort defines

handling of language-specific capitalization.

NLS_UPPER(n [, nls_sort])	char, with all letters uppercase. nls_sort defines

handling of language-specific capitalization.

REPLACE(char,search_string               	char, with every occurrence of search_string re

[,replacement_string])	placed by replacement_string. If you do not specify

replacement_string, ORACLE removes all occur

rences of search_string.

RPAD(char1,n[,char2])	char1, right-padded to length n with the sequence

of characters in char2;  char2 defaults to a blank. A

multi-byte character that does not fit is replaced

with a blank.

RTRIM(char[,set])	char, with final characters removed after the  last

character not in set. set defaults to ' '.

SOUNDEX(char)	A char value representing the sound of the word(s)

in char.

SUBSTR(char,m[,n])	A substring of char, beginning at character m, n

characters long (if n is omitted, to end of char).

SUBSTRB(char,m[,n])	A substring of char, beginning at byte m, n bytes

long (if n is omitted, to end of char).

TRANSLATE(char,from,to)	char, translated from the character set from to the

character set to.

UPPER(char)	char, with all letters uppercase.

Single row character functions that return character values:

Function	Value Returned

ASCII(char)	Returns a decimal number equivalent to the value

of the first character of char in the database charac

ter set. (An ASCII value on ASCII systems.) Returns

value of first byte in a multi-byte character.

INSTR(char1,char2[,n[,m]])	Position of the mth occurrence of char2 in char1, be

ginning search at character position n. m and n de

fault to 1. Position is relative to the first character of

char1, even if n>1.

INSTRB(char1,char2[,n[,m]])	Position of the mth occurrence of char2 in char1, be

ginning search at byte position n. m and n default to

1. Position is relative to the first byte of char1, even

if n>1.

LENGTH(char)	Length of char in characters

LENGTHB(char)	Length of char in bytes

NLSSORT(char)	The NLS-equivalent value used when sorting char.

Group Functions

Function	Value Returned

AVG([DISTINCT|ALL]n)	Average value of n, ignoring nulls.

COUNT([ALL]*)	Number of rows retunred by a query or subquery.

COUNT([DISTINCT|ALL]expr})	Number of rows where expr is not null.

MAX([DISTINCT|ALL]expr)	Maximum value of expr.

MIN([DISTINCT|ALL]expr)	Min imum value of expr.

STDDEV([DISTINCT|ALL]n)	Standard deviation of n, ignoring nuyll values.

SUM([DISTINCT|ALL]n)	Sum values of n.

VARIANCE([DISTINCT|ALL]n)	Variance of n, ignoring null values.

Date Functions

Function	Value Returned

ADD_MONTHS(d,n)	Date d plus n months.

LAST_DAY(d)	Date of last day of month containing d.

MONTHS_BETWEEN(d,e)	<%-2>Number of months by which e precedes

d.<%0>

NEW_TIME(d,a,b)	Date and time in time zone b when date and time

in time zone a are d. a and b are CHAR values

identifying time zones.

NEXT_DAY(d,char)	Date of the first day of the week named by char

that is equal to or later than the date d.

SYSDATE	Current date and time.

Date Truncation and Rounding

Function	Value Returned

ROUND(d[,fmt])	d rounded as specified by rounding unit fmt.

TRUNC(d[,fmt])	d truncated as specified by fmt.

Format Models for ROUND and TRUNC

This table list the date format elements used to control truncation and rounding. If no format element is specified, fmt defaults to 'DD'.

Format Element	Value Returned

CC or SCC	First day of the century

YYYY or SYYYY	First day of the year (rounds up on July 1)

YYY or YY or Y

Y,YYY or YEAR or SYEAR

Q	First day of the quarter (rounds up on 16th day

of 2nd month of the quarter)

MONTH or MON or MM or RM	Same day of the month (rounds up on 16th day)

WW or IW	Same day of the week as jan 1st of that year

W	Same day of the week as the 1st of the month

DDD or DD or J	Day

DAY or DY or D	First day of the week

HH or HH12 or HH24	Hour

MI	Minute

Conversion Functions

Function	Value Returned

CHARTORWID(char)	char, converted from a CHAR value to a RO

WID value.

CONVERT(char,dest_char_set	CHAR, converted from source_char_set.

 [,source_char_set])	representation to dest_char_set representation.

HEXTORAW(char)	char, converted from a hexadecimal number

to a binary RAW value.

RAWTHOHEX(raw)	raw, converted from a binary value of data

type RAW to a dezadecimal numver of data

type CHAR.

ROWIDTOCHAR9rowid)	rowid, converted from a ROWID value to a

CHAR value.

TO_CHAR(expr[,fmt 	expr, converted from a NUMBER or DATE

[,'nls_num_fmt ]])	value to a CHAR value in the format specified

by fmt. If you omit fmt, ORACLE converts

DATE values to default date format and

NUMVWER value3s to CHAR values exactly

wide ehnought to hold all significant digits.

nls_num_fmt sets the international numver-

format specifications. In Trusted ORACLE,

converst values of type MLS or MLS_LABEL

to type VARCHAR2.

TO_DATE(char[,fmt 	char, converted from a CHAR value in the for

[,'nls_lang']])	mat fmt to a DATE value. If  you omit fmt,

char must be in default date format. nls_lang

specifies the language4 used for day and

month.

TO_MULTI_BYTE(char)	char, with all single-byte characters tha have a

multi-byte equivalent converted to their mul

ti-byte form.

TO_NUMBER(char 	char, which is a character value containing a

[,fmt [, 'nls_lang']])	number in the format specified by the optional

fmt, converted to a NUMBER value. nls_lang

specifies the language used for numeric char

acters and currency symbols.

TO_SINGLE_BYTE(char)	char, with all multi-byte characters that have

a single-byte equivalent converted to their

single-byte form.

Other Functions

Function	Value Returned

DECODE(expr,search1,return1,        	If expr equals any search, returns the following re

[search2,return2,]...[default])	turn; if not, returns default.

DUMP(expr[,display_format             	expr in ORACLE internal format.

[,start_position[,length]]])

GREATEST(expr[,expr]...)	expr with the greatest value.

LEAST(expr[,expr]...)	expr with the least value.

NVL(expr1,expr2)	expr2, if expr1 is null; otherwise returns expr1.

UID	<%-2>Number that uniquely identifies the current

user.<%0>

USER	Name of the current user.

USERENV('option')	Information about the current session. Specify op

tion in single quotes. Options: ENTRYID, SESSIO

NID, TERMINAL, and LANGUAGE.

VSIZE(expr)	The number of bytes in ORACLE's internal repre

sentation of expr.

SYSDATE	Current system time/date.

________________________________________

Format Models

Date Format Models for TO_CHAR and TO_DATE

This table lists the date format elements. You can use any combinationof these elements as the fmt argument of the TO_CHAR or TO_DATE functions.

 fmt defaults to the default DATE format, 'DD-MON-YY'.

Format Element	Value Returned

SCC or CC	Century; 'S' prefixes BC date with '-'.

YYYY or SYYYY	Year; 'S' prefixes BC date with '-'.

YYY or YY or Y	Last 3,2, or 1 digit(s) of year. Century defaults to current.

IYYY	4-digit ISO standard year.

IYY or IY or I	Last 3,2, or 1 digit(s) of ISO year.

Y,YYY	Year with comma in this position.

SYEAR or YEAR	Year spelled out; 'S' prefixes BC date with '-'.

RR	Last 2 digits of year, for years in next century.

BC or AD	BC/AD indicator.

B.C. or A.D.	BC/AD indicator with periods.

Q	Quarter of year (1,2,3,4; JAN-MAR=1)

MM	Month of year (01-12; JAN=01)

RM	Roman Numeral month (I..XII;JAN=I).

MONTH	Month name, padded with blanks to 9 characters.

MON	Name of month, abbreviated (JAN,FEB,etc.).

WW or W	Week of year (1052) or month (1-5)

IW	ISO standard week of the year (1..21 or 1..53).

DDD or DD or D	Day of year (1-366) or month (1-31) or week (1-7).

DAY	Name of day, blank-padded to 9 characters.

DY	Name of day, 3-lettter abbreviation.

J	Julian day (days since December 31, 4713 BC).

AM or PM	Meridian indicator.

A.M. or P.M.	Meridian indicator with periods.

HH or HH12	Hour of the day (1-12).

HH24	Hour of day (0-23).

MI	Minute (0-59).

SS or SSSSS	Second (0-59) or seconds past midnight (0-86399).

-/,.;:	Punctuation is reproduced in the result.

"...text..."	QUoted string is reporduced in the result.

Date Format Prefixes and Suffixes

You can add this prefix to date format elements:

FM	"Fill mode." Suppresses blank padding when pre

fixed to MONTH or DAY. FM is a toggle. Use FM

twice to reenable blank padding.

FX	"Format exact." Specifies exact matching for the

character argument and date format model of a

TO_DATE function

You can add this suffixes to date format elements:

TH	Ordinal number ("DDTH" for "4TH").

SP	Spelled-out number ("DDSP" for "FOUR").

SPTH and THSP	Spelled-out ordinal number ("DDSPTH" for

"FOURTH"). The following strings specify output

in uppercase, initial caps, or lowercase:

Date Format Case Control

The following strings output in uppercase, initial caps, or lowercase:

Uppercase	Initial Caps	Lowercase

DAY	Day	day

DY	Dy	dy

MONTH	Month	month

MON	Mon	mon

YEAR	Year	year

AM	Am	am

PM	Pm	pm

A.M., A.m.	am

p.m., p.M.	pm

When prefixes or suffices are added to a date format, the case (uppercase, initial caps, or lowercase) is determined by the format element, not by the prefix or suffix. For example, 'ddTH' produces "04th", not "04TH".

Number Format Models for TO_CHAR

This table lists the number format elements. You can use a combinationof these elements as the fmt argument of the TO_CHAR function.

Format

Element	Example	Function

9	9999	Number of "9"s determines length of returned character.

0	'0999'	Prefixes value with leading zeroes.

$	$9999	Prefixes value with dollar sign.

B	B9999	Returns zero value as blank, instead of "0".

MI	9999MI	Returns "-" after negative values.

S	S9999	Returns "+" for positive values and "-" for negative values.

PR	9999PR	Returns negative values in <angle brackets>.

D	99D99	Returns the decimal character.

G	9G999	Returns the group separator.

C	C999	Returns the international currency symbol.

L	L999	Returns the local currency symbol.

, 	9,999	Returns a comma in this position.

. 	99.99	Returns a period in this position.

V	999V99	Multiplies value by 10n, where nis the number of "9"s after the "V".

EEEE	9.999EEEE	Returns value in scientific notation. fmt must contain exactly for

"E"s.

RN or rn	RN	upper- or lower-case Roman numerals (numbers in range 1..3999).

DATE	'DATE`	Returns value converted from Julian date to 'MM/DD/YY' date

format.

NLS Parameters

Since the parameters are specified inside a quoted string, the parametervalues must be specified with two single quotes, instead of one.

nls_lang ::=

nls_sort ::=

________________________________________

Pseudo-Columns

Pseudo-columns are similar to table columns. You can query any of these pseudo-columns, but you cannot change their values with DML statements:

Column Name	Value

sequence.CURRVAL	Current value of sequence for the current session. (sequence.NEXTVAL

must be referenced first.)

sequence.NEXTVAL	Next value of sequence for the current session.

table.LEVEL	1 for a root node, 2 for a child of a root, etc. Used in the SELECT

statement during tree-structured queries.

[table.]ROWID	Value that uniquely identifies a single row among other rows in the

table. ROWID values are of datatype ROWID, not NUMBER or

CHAR.

ROWNUM	Position of a single row among other rows selected by a query.

ORACLE selects rows in an arbitrary order and evaluates ROWNUM

before sorting rows for an ORDER BY clause.

________________________________________

Datatypes

ORACLE accepts the following datatypes:

CHAR(size)	Fixed length character string of size characters. Default 1. Maximum

255.

CHARACTER(size)	Same as CHAR

DATE	Dates ranging from January 1, 4712 BC to December 31, 4712 AD.

LONG	Variable length character string of up to 2 gigabytes.

LONG RAW	Binary data of up to 2 gigabytes, or 2!!super!!31 - 1.

NUMBER(p,s)	Number having precision p and scale s. p ranges from 1 to 38. Default

is 38. s ranges from -84 to 127.

RAW(size)	Binary data of up to size bytes. Maximum 2000 bytes. Size must be spe

cified.

ROWID	Values from the pseudo-column ROWID.

VARCHAR2(size)	Variable length character string having maximum length of size. Maxi

mum 2,000. De fault 1.

VARCHAR(size)	Same as VARCHAR2.

________________________________________

Names and Reserved Words

Names

Names of database objects

•	are between 1 and 30 characters long, except for database names, which can be no longer than 8 characters

•	do not contain quotation marks

•	do not duplicate the name of another object

Names that are always enclosed in double quotation marks can ignore the following rules. Otherwise, names

•	begin with a letter A-Z

•	contain only the characters A-Z, 0-9, $, #, and _

•	do not duplicate a SQL reserved word

Only names that are enclosed in double quotes are case sensitive. All other names are case insensitive.

SQL Reserved Words

SQL reserved words have special meanings in SQL statements. You maynot use these words as names for database objects:  (Words marked with * are also reserved PL/SQL.)

ACCESS*  	DEFAULT* 	INTEGER  	OPTION* 	START*

ADD*  	DELETE* 	INTERSECT*	OR*  	SUCCESSFUL

ALL*  	DESC* 	INTO*  	ORDER* 	SYNONYM

ALTER*  	DISTINCT* 	IS*  	PCTFREE*	SYSDATE

AND*  	DROP* 	LEVEL*  	PRIOR* 	TABLE*

ANY*  	ELSE* 	LIKE*  	PRIVILEGES 	THEN*

AS*  	EXCLUSIVE 	LOCK  	PUBLIC* 	TO*

ASC*  	EXISTS* 	LONG  	RAW  	TRIGGER

AUDIT  	FILE 	MAXEXTENTS	RENAME* 	UID

BETWEEN*  	FLOAT 	MINUS*  	RESOURCE*	UNION*

BY*  	FOR* 	MODE  	REVOKE 	UNIQUE*

CHAR*  	FROM* 	MODIFY	ROW 	UPDATE*

CHECK*  	GRANT* 	NOAUDIT  	ROWID 	USER

CLUSTER*  	GROUP* 	NOCOMPRESS* 	ROWLABEL 	VALIDATE

COLUMN  	HAVING* 	NOT*  	ROWNUM* 	VALUES*

COMMENT 	IDENTIFIED* 	NOWAIT  	ROWS  	VARCHAR*

COMPRESS*  	IMMEDIATE 	NULL*  	SELECT*	VARCHAR2*

CONNECT*	IN* 	NUMBER*  	SESSION 	VIEW*

CREATE*  	INCREMENT 	OF*  	SET*  	WHENEVER

CURRENT*  	INDEX* 	OFFLINE	SHARE  	WHERE*

DATE*  	INITIAL	ON*	SIZE* 	WITH*

DECIMAL	INSERT	ONLINE	SMALLINT

PL/SQL Reserved Words

ABORT	CREATE	GOTO	PACKAGE	SUM

ACCEPT	CURRENT	GRANT	PARTITION`	TABAUTH

ACCESS	CURSOR	GROUP	PCTFREE	TABLE

ADD	DATABASE	HAVING	PRAGMA	TABLES

ALL	DATA_BASE	IDENTIFIED	PRIOR	TASK

ALTER	DATE	IF	PRIVATE	TERMINATE

AND	DBA	IN	PROCEDURE	THEN

ANY	DEBUGOFF	INDEX	PUBLIC	TO

ARRAY	DEBUGON	INDEXES	RAISE	TRUE

AS	DECLARE	INDICATOR	RANGE	TYPE

ASC	DEFAULT	INSERT	RECORD	UNION

ASSERT	DEFINITION	INTERSECT	RELEASE	UNIQUE

ASSIGN	DELAY	INTO	REM	UPDATE

AT	DELETE	IS	RENAME	USE

AUTHORIZA	DELTA	LEVEL	RESOURCE	VALUES

TION	DESC	LIKE	RETURN	VARCHAR

AVG	DIGITS	LIMITED	REVERSE	VARCHAR2

BEGIN	DISPOSE	LOOP	REVOKE	VARIANCE

BETWEEN	DISTINCT	MAX	ROLLBACK	VIEW

BODY 	DO 	MIN	ROWNUM	VIEWS

BOOLEAN 	DROP 	MINUS	ROWTYPE	WHEN

BY 	ELSE 	MOD	RUN	WHERE

CASE 	ELSIF 	NEW	SAVEPOINT	WHILE

CHAR	END 	NOCOMPRESS	WSCHEMA	WITH

CHAR_BASE	ENTRY 	NOT	SELECT	WORK

CHECK 	EXCEPTION	NULL	SEPARATE	XOR

CLOSE 	EXCEP	NUMBER	SET

CLUSTER 	TION_INIT	NUMBER_BASE	SIZE

CLUSTERS 	EXISTS 	OR	SPACE

COLAUTH	EXIT 	ON	SQL

COLUMNS	FALSE	OPEN	SQLCODE

COMMIT 	FETCH 	OPTION	SQLERRM

COMPRESS	FOR 	OR	START

CONNECT	FORM 	ORDER	STATEMENT

CONSTANT	FROM	OTHERS	STDDEV

COUNT 	FUNCTION	OUT	SUBTYPE

CRASH	GENERIC

________________________________________

Privileges

System Privileges

These privileges apply to a whole class of objects.

ANALYZE ANY	Analyze any table, index, or cluster in any schema.

AUDIT ANY	Audit any object in any schema.

TRUNCATE ANY	Truncate any table or cluster in any schema.

CREATE CLUSTER	Create a cluster in own schema.

ALTER ANY CLUSTER	Alter any cluster in any schema.

CREATE ANT CLUSTER	Create a cluster in own schema.

DROP ANY CLUSTER	Drop any cluster in any schema.

ALTER DATABASE	Alter the database.

CREATE DATABASE LINK	Create private database links in own schema.

CREATE PUBLIC DATABASE 	Create public database links.

LINK

DROP PUBLIC DATABASE 	Drop public database links.

LINK

CREATE INDEX	Create index in own schema on any table in the same schema.

ALTER ANY INDEX	Alter any index in any schema.

CREATE ANY INDEX	Create an index in any schedma on any table in the same sche

ma.

DROP ANY INDEX	Drop any index in any schema.

CREATE PROCEDURE	Create stored procedures, functions, and p[ackages in own

schema.

ALTER ANY PROCEDURE	Alter any stored procedure, function, or package in any sche

ma.

CREATE ANY PROCEDURE	Create store procedures, functions, and packages in any sche

ma.

DROP ANY PROCEDURE	Drop stored procedures, functions, and packages in any sche

ma.

EXECUTE ANY PROCEDURE	Execute any stored procedure, function, or package, or refer

ence amy public package variable in any schema.

GRANT ANY PRIVILEGE	Grant system privileges even if not currently owned.

ALTER PROFILE	Alter any profile in the database.

CEATE PROFILE	Create profiles

DROP PROFILE	Drop any profile in the database.

ALTER RESOURCE COST	Set costs for sesssion resources

CREATE ROLE	Create roles.

ALTER ANY ROLE	Alter any role in the database.

DROP ANY ROLE	Drop any role in the database.

GRANT ANY ROLE	Grant any role in the database.

ALTER ROLLBACK SEGMENT	Alter rollback segments.

CREATE ROLLBACK SEGMENT	Create rollback segments.

DROP ROLLBACK SEGMENT	Drop rollback segments.

ALTER SESSION	Trace facility, national language characteristics, close a data

base link.

CREATE SESSION	Connects the database.

RESTRICTED SESSION	Logon after the database is started using STARTUP RE

STRICT.

CREATE SEQUENCE	Create a sequence in own schema.

ALTER ANY SEQUENCE	Alter any sequence in any schema.

CREATE ANY SEQUENCE	Create a sequence in any shcema.

DROP ANY SEQUENCE	Drop any sequence in any schema.

SELECT ANY SEQUENCE	Reference any sequence in any schema.

CREATE SNAPSHOT	Create snapshots in any schema. Requires CREAE ANY

TABLE, as well.

ALTER ANY SNAPSHOT	Alter any snapshot in the schema.

CREATE ANY SNAPSHOT	Create cnapshots in any schema. Requires CREATE ANY

TABLE, as well.

DROP ANY SNAPSHOT	Drop any snapshot in any schema.

CREATE SYNONYM	Create a synonym in won schema.

CREATE ANY SYNONYM	Create a synonym in any schema.

DROP ANY SYNONYM	Drop any synonym in any schema, except public synonyms.

CREATE PUBLIC SYNONYM	Create public synonyms.

DROP PUBLIC SYNONYM	Drop public synonyms.

AUDIT SYSTEM	Audit system events.

ALTER SYSTEM	resource limits, server and dispatcher processes, change and

archive redo log files, distriburted recovery, checkpoint, verify

file access.

CREATE TABLE	Create tables in own schema. Requires UNLIMITED TABLE

SPACE, or a quota on a tablespace.

ALTER ANY TABLE	Alter any table in any schema.

BACKUP ANY TABLE	Backup any table in any schema with ALTER TABLE com

mand.

COMMENT ANY TABLE	Comment on any table, view, or column in any schema.

CREATE ANY TABLE	Create a table in any schema.

DELETE ANY TABLE	Delete rows from any table, view, or snapshot in any schema.

DROP ANY TABLE	Drop any table in any schema.

INSERT ANY TABLE	insert into any table, view, or snapshot in any schema.

LOCK ANY TABLE	Lock any table in any schema.

SELECT ANY TABLE	Query any table, view, or snapshot in any schema.

UPDATE ANY TABLE	Update rows in any table, view, or snapshot in any schema.

ALTER TABLESPACE	Alter tablespaces.

CREATE TABLESPACE	Create tablespaces.

DROP TABLESPACE	Drop tablespaces.

MANAGE TABLESPACE	Tablespace online and offline. Tablespace backups.

UNLIMITED TABLESPACE	Use an unlimited amount of any tablespace.

FORCE TRANSACTION	Force commit or rollback of own distributed transaction in the

local database.

FORCE ANY TRANSACTION	Force commit or rollback of any distributed transaction in the

local database.

CREATE TRIGGER	Create triggers in own schema.

ALTER ANY TRIGGER	Enable, disable, or compile trigger in any schema.

CREATE ANY TRIGGER	Create a trigger in any schema associated with any table in

any schema.

DROP ANY TRIGGER	Drop any trigger in any schema.

ALTER USER	Alter other users: password, authentication method, quotas,

tablespaces, assign a profile & default roles

BECOME USER	Become another user.

CREATE USER	Create users, assign quotas on any tablespace, set default and

temporary tablespaces, assign a profile.

DROP USER	Drop another user.

CREATE VIEW	Create a view in own schema

CREATE ANY VIEW	Create a view in any schema.

DROP ANY VIEW	Drop any view in any schema.

Object Privileges

These privileges apply to specific objects.

Object Privilege	Explanation

ALL	All of the object privileges that can be applied.

ALL PRIVILEGES	Same as ALL.

ALTER	Change definition.

DELETE	Delete rows.

EXECUTE	Execute the object, reference its variables.

INDEX	Create an index on the table.

INSERT	Insert rows.

REFERENCES	Create a constraint that refers to the table. Not granted to roles.

SELECT	Query rows.

UPDATE	Change rows.

Procedure

Functions

Object Privilege	Table	Views	Sequences	Packages	Snaphots

ALTER	/	/

DELETE	/	/

EXECUTE	/

INDEX	/

INSERT	/	/

REFERENCES	/	/

SELECT	/	/	/	/

UPDATE	/	/

________________________________________

Expressions and Conditions

The following diagrams show the possible expressions and conditions denoted by "expr" and "condition" in the SQL command syntax diagrams.

________________________________________

Operators and Operator Precedence

Arithmetic Operators

Operator	Function

+- (one operand)	Denotes a positive or negative expression.

*/	Multiplication and division.

+- (two operands)	Addition and subtraction.

Logical Operators

Operator	Function

||	Concatenates character values.

Logical Operators

Operator	Function

NOT	reverses a logical expression`s result

AND	Combines two or more logical expresseions: TRUE if all are

true; otherwise FALSE.

OR	Combines two or more logical exprtession: TRUE if any are

true; otherwise FALSE.

Comparison Operators

Operator	Function

=	Tests for equaulty.

!= ^=  = <>	Tests for inequality.

>  >=  < <=	Greater than, greater than or equal to, less than, less than or

equal to.

IN	Equal to any memvber of a set or subquery. ALso equivalent

to '!=ANY'.

NOT IN	Not equal to any memver of a set or subquery. Also equivalent

to '!=ALL'.

ANY,SOME	True if the condition is satisfied by one or more values con

tained in a list of expressions or returned by a subquery.

ALL	True if the condition is satisfied by all values contaiend in a

list of expressions or returned by a subquery.

[NOT]between x and y	[Not] greater than or equal to x and less than or equal to y.

[NOT] EXISTS	TRUE if a subquery returns [does not return] at least one row.

[NOT] LIKE p [ESCAPE'c']	Matches [does not match] a specified pattern. '%' matches any

sequence of characters; '_' matches any single character. The

ESCAPE character preceding '%' or '_' causes themn to be

treated literaly. Matches multiple bytes for a multi-byte char

acter.

IS [NOT] NULL	Is [not] null.

Set Operators used in SELECT Statements

Operator	Function

UNION	Combines two qureies; returns all distinct rows returned by either

individual query.

UNION ALL	Combines two queries; returns all rows returned by either query,

including duplicates.

INTERSECT	Combines two queries; returns all distinct rows returned by both

individual queries.

MINUS	Combines two queries; returns all distinct rows returned by the 1st

but not by the 2nd.

(+)	Indicates that the preceding column is an outer join column.

*	Wildcard operator used instead of column names to select all columns

from a table or view.

PRIOR	Used in a tree-structured query to define a parent-child relationship

between nodes in a tree-structured query. See SELECT.

ALL	Retains duplicate rows in a query (ALL is the default, as compared to

DISTINCT).

DISTINCT	Eliminates duplicate rows from the result of a query.

Operator Precedence

When evaluating an expression, ORACLE evaluates operators with greater precedence first. Operators on the same line have the same precedence.

Note: Parentheses() override normal precedence. ORACLE evaluates operators inside parentheses before those outside. Wihtout parentheses, operators at the same precedence are evaluated from left to right.

SQL Operator Precedence

+ - unary arithemetic operators	PRIOR operator

*/ arithmetic operators

+ - binary arithemetic operators	|| character operator

All comparison operators

NOT logical operator

AND logical operator

OR logical operator

Arithmetic Operator Precedence

+ - unary arithmetic operators

* / arithmetic operators

+ - binary arithmetic operators

________________________________________

SQL Commands

ALTER CLUSTER

Redefines storage allocations for an existing cluster.

ALTER DATABASE

Changes the operation of the database with respect to redo log files and database files. Backs up control files and archives redo log files. Mounts, dismounts, opens and closes a database. Performs media recovery.

ALTER FUNCTION

Recompiles a standalone function.

ALTER INDEX

Redefines storage allocations for an existing index.

ALTER PACKAGE

Recompiles the specification or body of a stored package.

ALTER PROCEDURE

Recompiles a stand-alone stored procedure.

ALTER PROFILE

Add, modify, or remove a resource limit in a profile.

ALTER RESOURCE COST

Specify a formula to calculate the total resource cost for a session.

ALTER ROLE

Changes the authorization needed to enable a role.

ALTER ROLLBACK SEGMENT

Redefines storage allocations for an existing rollback_segment,

ALTER SEQUENCE

Redefines an existing sequence.

ALTER SESSION

Modifies the current user session by enabling and disabling the SQL trace facility, changing national language characteristics, or closing a remote database link.

ALTER SNAPSHOT

Change storage characteristics or redefine the refresh time and mode for a snapshot.

ALTER SNAPSHOT LOG

Change storage characteristics or remove rows from a snapshot log.

ALTER SYSTEM

Perform a specialized system function.

ALTER TABLE

Redefines the columns, constraints, or storage allocations of an existing table.

ALTER TABLESPACE

Redefines an existing tablespace by adding or renaming a database file, or by redefining default storage allocations. Also changes the state of a tablespace by moving the tablespace online or offline, or by <%-3>signifying that a backup of the database files has begun or completed

ALTER TRIGGER

Enable, disable, or recompile a database trigger.

ALTER USER

Changes the password, default tablespace, or temporary tablespace of an existing user.

ALTER VIEW

Recompiles a database view, affecting dependencies.

ANALYZE

Validates the structure of an index, table, or cluster, or collects performance statistics for them.

AUDIT (Form I)

Enables auditing of specific SQL statements or types of objects.

AUDIT (Schema objects)

Enables auditing of access to specific database objects. The DEFAULT option enables auditing of all (specified) objects that are subsequently created. Auditable objects include tables, views, sequences, functions, procedures, packages, and snapshots.

Procedure

Functions

Object Privilege	Table	Views	Sequences	Packages	Snaphots

ALTER	/	/

AUDIT	/	/	/	/

COMMENT	/	/

DELETE	/	/

EXECUTE	/

GRANT	/	/	/	/

INDEX	/

INSERT	/	/

LOCK	/	/

RENAME	/	/	/

SELECT	/	/	/	/

UPDATE	/	/

SELECT	/	/	/	/

UPDATE	/	/

COMMENT

Adds a comment about a table, view, or column to the data dictionary.

COMMIT

Saves the changes in the current transaction to the database. Also erases the transaction's savepoints and releases the transaction's locks.

CREATE CLUSTER

Creates a new cluster and specifies its cluster key columns.

CREATE CONTROL FILE

Recreates the database control file.

CREATE DATABASE

Creates a new database setting the maximum numbers of instances, database files, and redo log files; specifying database files and redo log files; and choosing the mode for the redo log.

CREATE DATABASE LINK

Creates a link from the local database to a user on a remote database specified by a connect_string.

CREATE FUNCTION

Creates a stored function.

CREATE INDEX

Creates a new index on the specified columns in a table or cluster.

CREATE PACKAGE

Creates a stored package specification.

CREATE PACKAGE BODY

Creates the body of a stored package.

CREATE PROCEDURE

Creates a stored procedure.

CREATE PROFILE

Creates a user profile and specifies its resource limits.

CREATE ROLE

Creates a security role.

CREATE ROLLBACK SEGMENT

Creates a new rollback_segment.

CREATE SCHEMA

Issues multiple CREATE TABLE, CREATE VIEW, and GRANT commands in a single transaction.

CREATE SEQUENCE

Creates a new sequence suitable for generation of primary keys.

CREATE SNAPSHOT

Creates a snapshot of a database from one or more master tables.

CREATE SNAPSHOT LOG

Creates a snapshot log identifying changes made to the master table.

CREATE SYNONYM

Creates a new synonym for a table, view, sequence, packaged procedure, top-level function or procedure (one that is not defined inside a package), or for another synonym.

CREATE TABLE

Creates a new table, defining its columns, constraints, and storage allocations.<%-5>

PCT_USED is not allowed when the storage parameters are specified as part of an INDEX clause or in a CREATE INDEX statement.

Storage Clause Notes:

•	OPTIMAL is only allowed for rollback segments.

•	FREELIST GROUPS is only allowed for tables, clusters, and indexes.

•	FREELISTS is only allowed for tables and clusters.

CREATE TABLESPACE

Creates a new area in the database for storage of tables, indexes, rollback segments, and temporary segments. Specifies the database files, the default storage allocations, and whether the tablespace is initially online or offline.

CREATE TRIGGER

Creates a database trigger.

CREATE USER

Defines a database user.

CREATE VIEW

Creates a new view of one or more tables and/or other views.

DELETE

Removes rows from a table or view that meet the WHERE condition.  Removes all rows if no WHERE clause is specified.

DROP

This command removes objects from the database. The appropriate privileges are required for each object. For example, removing a PUBLIC database link requires the DROP PUBLIC DATABASE LINK privilege.

EXPLAIN PLAN

Describes each step of the execution plan for a SQL statement and optionally places the description in a plan table.

GRANT (System Privileges and Roles)

Gives system privileges to users and other roles.

GRANT (Object Privileges)

Gives privileges for a particular object (a table, view, synonym, package, procedure, etc.) to users and to roles.

INSERT

Adds new rows to a table or view.

LOCK TABLE

Overrides default lock mode and reserves access to a table for the current transaction. Permits or restricts access by other users.

NOAUDIT (SQL Statements)

Disables auditing of specific SQL statements, as established by Form I of the AUDIT command.

NOAUDIT (Form II)

Disables auditing of specific database objects, as established by Form II of the AUDIT command.

RENAME

Renames a table, view, or synonym from old to new.

REVOKE (Form I)

Revokes system privileges and roles from users and from roles. Reverses the action of the GRANT (System Privileges and Roles) command.

REVOKE (Object Privileges)

Revokes object privileges from users and from roles. Reverses the action of the GRANT (Object Privileges) command.

ROLLBACK

Undoes all changes made since the savepoint. Undoes all changes in the current transaction if no savepoint is specified.

SAVEPOINT

Identifies a savepoint in the current transaction to which changes can be rolled back.

SELECT

Queries one or more tables or views. Returns rows and columns of data. May be used as statement or as a subquery in another statement.

SET ROLE

Enables a specific role for a session and disables all other roles for a user. Along with SET TRANSACTION, must be at the start of the transaction.

SET TRANSACTION

Enforces read consistency at the transaction level, or specifies the rollback segment to be used for the transaction. Along with SET ROLE, must be at the start of the transaction.

TRUNCATE

Removes all rows from a table or cluster.

UPDATE

Changes the values of columns in rows that meet the WHERE condition in a table or view. Changes all rows if no WHERE condition is specified.

________________________________________

Comments

Comments set off by /* and */ may appear anywhere within a SQL statement:

ALTER USER SCOTT /* This is a comment */

 IDENTIFIED BY TIGER;

ANSI-standard comments may also be used.  All characters from a double-hyphen to the end of the line are ignored.

ALTER USER SCOTT -- This comment goes to the end of the line

 IDENTIFIED BY TIGER;

________________________________________

Embedded SQL Commands

You can use the ORACLE precompilers to embed SQL statements into an application program. For the syntax of embedded SQL statements, refer to the SQL Language Reference Manual, Part No. 778-70.

________________________________________

Special Tables

CHAINED_ROWS Table

Lists chained rows in a table or cluster named inthe ANALYZE command.

Column	Datatype

OWNER_NAME	VARCHAR2(30)

TABLE_NAME	VARCHAR2(30)

CLUSTER_NAME	VARCHAR2(30)

HEAD_ROWID	ROWID

TIMESTAMP	DATE

EXCEPTIONS Table

Used to identify rows that violate integrity constraintswhen constraints are enabled.

Column	Datatype

ROWID	ROWID

OWNER	VARCHAR2(30)

TABLE_NAME	VARCHAR2(30)

CONSTRAINT	VARCHAR2(30)

PLAN_TABLE

This table may be filled in with the EXPLAIN PLANcommand, to get a description of the execution plan for a SQL statement. To interpret the plan, see the ORACLE RDBMS Performance Tuning Guide.

Column	Datatype

STATEMENT_ID	VARCHAR2(30)

TIMESTAMP	DATE

REMARKS	VARCHAR2(80)

OPERATION	VARCHAR2(30)

OPTIONS	VARCHAR(230)

OBJECT_NODE	VARCHAR2(30)

OBJECT_OWNER	VARCHAR2(30)

OBJECT_NAME	VARCHAR(30)

OBJECT_INSTANCE	NUMBER

OBJECT_TYPE	VARCHAR2(30)

SEARCH_COLUMNS	NUMBER

ID	NUMBER

PARENT_ID	NUMBER

POSITION	NUMBER

OTHER	LONG

________________________________________

Data Dictionary Views

The Data Dictionary contains information about database objects, users, and events. You can access this information through these views of the data dictionary:

View	Description

ALL_CATALOG	All tables, views, synonyms, and sequences accessible to the

user.

ALL_COL_COMMENTS	Comments on columns of accessible tables and views.

ALL_COL_PRIVS	Synonym for COLUM N_PRIVILEGES.

ALL_COL_PRIVS_MADE	Grants on columns for which the user or PUBLIC is the

grantee.

AL_COL_PRIVS_RECD	Grants on columns for which the user or PUBLIC is the

grantee.

ALL_CONSTRAINTS	Constraint definitions on accessible tables.

ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions.

ALL_DB_LINKS	Database links accessible to the user.

ALL_DEF_AUDIT_OPTS	Auditing options for newly created objects.

ALL_DEPENDENCIES	Dependencies between objects.

ALL_ERRORS	Current errors on all stored objects in the database.

ALL_INDEXES	Description of indexes on tables accessible to the user.

ALL_IND_COLUMNS	Columns of the indexes on accessible tables.

ALL_LABELS	Trusted ORACLE view that lists system labels.

ALL_COUNTED_DBS	Trusted ORACLE view that lists mounted databases.

ALL_OBJECTS	Objects accessible to the user.

ALL_SEQUENCES	Description of sequences accessible to the user.

ALL_SNAPSHOTS	All snapshots accessible to the user.

ALL_SOURCE	Text source of all stored objects in the database.

ALL_SYNONYMS	All synonyms accessible to the user.

ALL_TABLES	Description of tables accessible to the user.

ALL_TAB_COLUMNS	Columns of all tables, vies, and clusters.

ALL_TAB_COMMENTS	Comments on tables and views accessible o the user.

ALL_TAB_PRIVS	Synonym for TABLE_PRIVILEGES.

ALL_TAB_PRIVS_MADE	User's grants and grants on user`s objects.

ALL_TAB_PRIVS_RECD	Grants on objects for which the user or PUBLIC is the grantee.

ALL_TRIGGERS	Triggers accessible to the current user.

ALL_USERS	Information about all users of the database.

ALL_VIEWS	Text of views accessible to the user.

AUDIT_ACTIONS	Description table for audit trail action type codes.

CAT	Synonym for USER_CATALOG

CLU	Synonym for USER_CLUSTERS.

CODE_PIECES	Used for building the _OBJECT_SIZE views.

CODE_SIZE	Used for building the _OBJECT_SIZE views.

COLS	Synonym for USER_TAB_COLUMNS.

COLUMN_PRIVILEGES	Grants on columns for which the user is the grantor, grantee,

or owner, or PUBLIC is the grantee.

DBA_2PC_NEIGHBORS	Information about incoming and outgoing connections for

pending transactions.

DBA_2PC_PENDING	Information about failed distributed transactions in

"PREPARED" state.

DBA_AUDIT_EXISTS	AUdit trail entries created by the AUDIT EXISTS command.

DBA_AUDIT_OBJECT	Synonym for USER_AUDIT_OBJECT.

DBA_AUDIT_SESSION	Synonym for USER_AUDIT_SESSION.

DBA_AUDIT_STATEMENT	Synonym for USER_AUDIT_STATEMENT.

DBA_AUDIT_TRAIL	Synonym for USER_AUDIT_TRAIL.

DBA_BLOCKERS	Sessions holding a lock someone wants.

DBA_CATALOG	All database tables, views, synonyms, and sequences.

DBA_CLUSTERS	Description of all clusters in the database.

DBA_CLU_COLUMN	Mapping of table columns to cluster columns.

DBA_COL_COMMENTS	COmments on columns of all tables and views.

DBA_COL_PRIVS	All grants on columns in the database.

DBA_CONSTRAINTS	Constraint definitions on accessible tables.

DBA_CONS_COLUMNS	Information about accessible columns in constraint definitions.

DBA_DATA_FILES	Information about database files.

DBA_DB_LINKS	All database links in the database.

DBA_DDL_LOCKS	All DDL locks held and requested.

DBA_DEPENDENCIES	Dependencies to and from all objects.

DBA_DML_LOCKS	All DML locks held and requested.

DBA_ERRORS	Current errors on all stored objects in the database.

DBA_EXP_FILES	Description of export files.

DBA_EXP_OBJECTS	Objects that have been incrementally exported.

DBA_EXP_VERSION	Version number of the last export session.

DBA_EXTENTS	Extents of all segments in the database.

DBA_FREE_SPACE	Free extents in all tablespace.

DBA_INDEXES	Description for all indexes in the database.

DBA_IND_COLUMNS	Columns on indexes on all tables and clusters.

DBA_LOCKS	All locks held and requested.

DBA_OBJECTS	All objects in the database.

DBA_OBJECT_SIZE	All PL/SQL objects.

DBA_OBJ_AUDIT_OPTS	AUditing options for all tables and views.

DBA_PRIV_AUDIT_OPTS	Auditing options for privileges.

DBA_PROFILES	Profiles assigned to each user.

DBA_ROLE_PRIVS	Description of roles granted to users and to roles.

DBA_ROLES	All roles that exist in the database.

DBA_ROLLBACK_SEGS	Description of roles granted to users and to roles.

DBA_SEGMENTS	Storage allocated for all database segments.

DBA_SEQUENCES	Description of all sequences in the database.

DBA_SNAPSHOTS	All snapshots in the database.

DBA_SNAPSHOT_LOGS	All snapshot logs in the database.

DBA_SOURCE	Text source of all stored objects in the database.

DBA_STMT_AUDIT_OPTS	Describes current system auditing options across the system

and by user

DBA_SYNONYMS	All synonyms in the database.

DBA_SYS_PRIVS	description of system privileges granted to users and to roles.

DBA_TABLES	Description of all tables in the database.

DBA_TABLESPACES	Description of all tablespace in the database.

DBA_TAB_COLUMNS	Columns of all tables, views, and clusters.

DBA_TAB_COMMENTS	Comments on all tables and views in the database.

DBA_TAB_PRIVS	All grants on objects in the database.

DBA_TRIGGERS	Description of all triggers in the database.

DBA_TS_QUOTAS	Tablespace quotas for all users.

DBA_USERS	Information about all users of the database.

DBA_VIEWS	Text of all views in the database.

DBA_WAITERS	Sessions waiting for a lock.

DBMS_ALERT_INFO	Table of registered alerts.

DBMS_LOCK_ALLOCATED	Table of user allocated locks.

DEPTREE	Object dependency tree.

DICT	Synonym for DICTIONARY.

DICTIONARY	Description of data dictionary tables and views.

DICT_COLUMNS	Description of columns in data dictionary tables and views.

ERROR_COLUMNS	Description of columns in data dictionary tables and views.

GLOBAL_NAME	Database`s global name.

IDEPTREE	Indented object dependency tree.

IND	Synonym for USER_INDEXES.

INDEX_HISTOGRAM	Index statistics from the ANALYZE INDEX VALIDATE

STRUCTURE command.

INDEX_STATS	SQL* Loader direct load view.

LOADER_COL_INFO	SQL* Loader direct load view.

LOADER_CONSTRAINT_INFO	SQL* Loader direct load view.

LOADER_INDCOL_INFO	SQL* Loader direct load view.

LOADER_IND_INFO	SQL* Loader direct load view.

LOADER_PARAM_INFO	SQL* Loader direct load view.

LOADER-TAB_INFO	SQL* Loader direct load view.

LOADER_TRIGGER-INFO	SQL* Loader direct load view.

OBJ	Synonym for USER_OBJECTS.

PARSED_PIECES	Used for building the _OBJECT_SIZE views.

PARSED_SIZE	Used for building the _OBJECT_SIZE views.

PUBLIC_DEPENDENCY	Dependencies to and from objects.

RESOURCE_COST	Cost for each resource.

ROLE_ROLE_PRIVS	Information about roles granted to other roles.

ROLE_SYS_PRIVS	Information about system privileges granted to roles.

ROLE_TAB_PRIVS	Information about table privileges granted to roles.

SEQ	Synonym for USER_SEQUENCES.

SESSION_PRIVS	Privileges which are currently available to the user.

SESSION_ROLES	Roles which the user currently has enabled.

SOURCE_SIZE	Used for building the _OBJECT_SIZE views.

STMT_AUDIT_OPTION_MAP	Description table for auditing option type codes.

SYN	Synonym for USER_SYNONYMS.

SYSTEM_PRIVILEGE_MAP	Description table for system privilege codes.

TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, or

owner, or PUBLIC is the grantee.

TABLE_PRIVILEGE_MAP	Description table for object privilege codes.

TABS	Synonym for USER_TABLES.

USER_AUDIT_OBJECT	Audit trail records for statement concerning objects.

USER_AUDIT_SESSION	All audit trail records concerning connections and

disconnections for the user.

USER_AUDIT_STATEMENT	Audit trail entries for the following statements: grant, revoke,

audit, noaudit, and ALTER SYSTEM

USER_AUDIT_TRAIL	Audit trail entries relevant to the user.

USER_CATALOG	Tables, views, synonyms, and sequences owned by the user.

USER_CLUSTERS	Description of user`s own clusters.

USER_CLU_COLUMNS	Mapping of table columns to cluster columns.

USER_COL_COMMENTS	Comments on columns of user's tables or views.

USER_COL_PRIVS	Grants on columns for which the user is the owner, grantor, or

grantee.

USER_COL_PRIVS_MADE	All grants on columns of objects owned by the user.

USER_COL_PRIVS_RECD	Grants on columns for which the user is the grantee.

USER_CONSTRAINTS	Constraint definitions on user's tables.

USER_CONS_COLUMNS	Information about columns in constraint definitions owned by

the user.

USER_DB_LINKS	Database links owned by the user.

USER_DEPENDENCIES	Dependencies to and from a user`s objects.

USER_ERRORS	Current errors on all stored objects in the database.

USER_EXTENTS	Extents of the segments owned by the user.

USER_FREE_SPACE	Free extents in tablespace accessible to the user.

USER_INDEXES	Description of the user's own indexes.

USER_IND_COLUMNS	Columns of the user's indexes or on user's tables.

USER_OBJECTS	Objects owned by the user.

USER_OBJECT_SIZE	User`s PL/SQL objects.

USER_OBJ_AUDIT_OPTS	Auditing options for user's own tables and views.

USER_RESOURCE_LIMITS	Displays the resource limits for the current user.

USER_ROLE__PRIVS	Roles granted to the user.

USER_SEGMENTS	Storage allocation for all database segments.

USER_SEQUENCES	Description of the user's own sequences.

USER_SNAPSHOTS	Snapshots the user can view.

USER_SNAPSHOT_LOGS	All snapshot logs owned by the user.

USER_SOURCE	Text source of all stored objects in the database.

USER_SYNONYMS	The user's private synonyms.

USER_SYS_PRIVS	System privileges granted to the user.

USER_TABLES	Description of the user's own tables.

USER_TABLESPACES	Description of accessible tablespaces.

USER_TAB-COLUMNS	Columns of user's tables, views, and clusters.

USER_TAB_COMMENTS	Comments on the tables and views owned by the user.

USER_TAB_PRIVS	Grants on objects for which the user is the owner, grantor, or

grantee.

USER_TAB_PRIVS_MADE	All grants on objects owned by the user.

USER_TAB_PRIVS_RECD	Grants on objects for which the user is the grantee.

USER_TRIGGERS	Description of the user's triggers.

USER_TS_QUOTAS	Tablespace quotas for the user.

USER_USERS	Information about the current user.

USER_VIEWS	Text of views owned by the user.

________________________________________

Dynamic Performance Tables

The system performance tables are accessible to the user SYS.

Table	Description

V$ACCESS	Describes the owners of tables.

V$ARCHIVE	information on archive logs.

V$BACKUP	Backup status of online data files.

V$BGPROCESS	Describes the background processes.

V$CIRCUITS	information on virtual circuits.

V$DATABASE	Database information from the control file.

V$DATAFILE	Data file information from the control file.

V$DBFILE	information about each database file in the database.

V$DB_OBJECT_CACHE	Shows objects cached in the library cache.

V$DISPATCHERS	Information on the dispatcher processes.

V$ENABLEDPRIVS	information about enabled privileges.

V$FILESTAT	information about file read/write statistics.

V$INSTANCE	State of the current instance.

V$LATCH	Information about each type of latch. (The rows of this table

and the rows. of V$LATCHNAME correspond one-to-one.)

V$LATCHHOLDER	Information about the current latch holders.

V$LATCHNAME	The decoded latch names for the latches shown in table

V$LATCH. (The rows of this tale and the rows of V$LATCH

correspond one-to-one.)

V$LIBRARYCACHE	Statistics on library cache management.

V$LICENSE	Information on license limits.

V$LOADSTAT	SQL* Loader direct statistics.

V$LOADSTAT	SQL* Loader direct statistics.

V$LOCK	Information about locks and resources.

V$LOG	Log information from the control file.

V$LOGHIST	Log history information from the control file.

V$LOG_HISTORY	Archived log names.

V$LOGFILE	Information about redo log files.

V$NLS_PARAMETERS	Current values of NLS parameters.

V$OPEN_CURSOR	Cursors that each session has open and parsed.

V$PARAMETER	Information about current parameter values.

V$PROCESS	Information about currently active processes.

V$QUEUES	Information on multi-thread message queues.

V$RECOVERY_LOG	Archived logs needed for media recovery.

V$RECOVER_FILE	Status of files needing media recovery.

V$REQDIST	Status of files needing media recovery.

V$RESOURCE	Histogram of lock request times.

V$ROLLNAME	Information about resources.

V$ROLLSTAT	Names of rollback segments (Although this table has no join

key with V$ROLLSTAT, the rows on the two tables

correspond one-to-one.)

V$ROWCACHE	Statistics for data dictionary activity: each row contains

statistics for one data dictionary cache.

V$SECONDARY	Trusted ORACLE view of secondary mounted databases.

V$SESSION	Session information for each current session.

V$SESSTAT	For each current session, the current statistics values.

V$SESSION_WAIT	Wait status of active sessions.

V$SESS_IO	User session I/O statistics.

V$SGA	Information about the System Global Area.

V$SGASTAT	Detailed statistics on the SGA.

V$SHARED_SERVERS	Information on shared server processes.

V$SQLAREA	Statistics on the shared cursor cache.

V$SQLTEXT	Full text of cached SQL statements.

V$STATNAME	Decoded statistic names for the statistics.

V$SYSLABEL	Trusted ORACLE view of system labels.

V$SYSSTAT	The current system-wide value for each statistic in table

V$SESSTAT.

V$THREAD	Thread information from the control file.

V$TIMER	Current time in hundredths of seconds.

V$TRANSACTION	Information about transactions.

V$TYPE_SIZE	Sizes of database components.

V$VERSION	Versions of core library components.

V$WAISTAT	Block contention statistics.

________________________________________

Reader's Comment Form

ORACLE7 Server SQL language Quick Reference

-Part Np. 5421-70-1292

December 1993

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this publication. Your input is an important part of the information used for revision.

•	Did you find any erros?

•	In the information clearly presented?

•	Do you need more information? If so, where?

•	Are the examples correnct? Do you need more examples?

•	What features did you like most about this manual?

If you find any errors or have any other suggestions for improvement, please indicate the topic, chapter and page number below:

Please mail or FAX your comments to:

ORACLE7 Server Documentaiton Manager

Oracle Corporation

500 Oracle Parkway

Redwood Shores, CA 94065

(415) 506-7000

FAX: (415) 506-7200

If you would like a reply, please give your name, address, and telephone number below:

Thank you for helping us improve our documentation.