position

                   orders rows based on their value for the expression

                   in this position of the select list.

            ASC

            DESC

                   specifies either ascending or descending order.  ASC

                   is the default.



FOR UPDATE

    locks the selected rows.



NOWAIT

    returns control to you if the SELECT statement attempts to lock a

    row that is locked by another user.  If you omit this clause, ORACLE

    waits until the row is available and then returns the results of the

    SELECT statement.



PREREQUISITES:

    For you to select data from a table or snapshot, the table or

    snapshot must be in your own schema or you must have SELECT

    privilege on the table or snapshot.



    For you to select rows from the base tables of a view, the owner of

    the schema containing the view must have SELECT privilege on the

    base tables.  Also, if the view is in a schema other than your own,

    you must have SELECT privilege on the view.



    The SELECT ANY TABLE system privilege also allows you to select data

    from any table or any snapshot or any view"s base table.



    If you are using Trusted ORACLE in DBMS MAC mode, your DBMS label

    must dominate the creation label of each queried table, view, or

    snapshot or you must have READUP system privileges.



SEE:

    DELETE, UPDATE

    CREATE TABLE command



PURPOSE:

    To create a table, the basic structure to hold user data, specifying

    this information:



    * column definitions

    * integrity constraints

    * the table"s tablespace

    * storage characteristics

    * an optional cluster

    * data from an arbitrary query



SYNTAX:



CREATE TABLE [schema.]table

     ( { column datatype [DEFAULT expr] [column_constraint] ...

       | table_constraint}

    [, { column datatype [DEFAULT expr] [column_constraint] ...

       | table_constraint} ]...)

    [ [PCTFREE  integer] [PCTUSED  integer]

      [INITRANS integer] [MAXTRANS integer]

      [TABLESPACE tablespace]

      [STORAGE storage_clause]

    | [CLUSTER cluster (column [, column]...)] ]

    [ ENABLE   enable_clause

    | DISABLE disable_clause ] ...

    [AS subquery]



where:



schema

    is the schema to contain the table.  If you omit schema, ORACLE

    creates the table in your own schema.



table

    is the name of the table to be created.



column

    specifies the name of a column of the table.  The number of columns

    in a table can range from 1 to 254.



datatype

    is the datatype of a column.



DEFAULT

    specifies a value to be assigned to the column if a subsequent

    INSERT statement omits a value for the column.  The datatype of the

    expression must match the datatype of the column.  A DEFAULT

    expression cannot contain references to other columns, the

    pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants

    that are not fully specified.



column_constraint

    defines an integrity constraint as part of the column definition.



table_constraint

    defines an integrity constraint as part of the table definition.



PCTFREE

    specifies the percentage of space in each of the table"s data blocks

    reserved for future updates to the table"s rows.  The value of

    PCTFREE must be a positive integer from 1 to 99.  A value of 0

    allows the entire block to be filled by inserts of new rows.  The

    default value is 10.  This value reserves 10% of each block for

    updates to existing rows and allows inserts of new rows to fill a

    maximum of 90% of each block.



    PCTFREE has the same function in the commands that create and alter

    clusters, indexes, snapshots, and snapshot logs.  The combination of

    PCTFREE and PCTUSED determines whether inserted rows will go into

    existing data blocks or into new blocks.



PCTUSED

    specifies the minimum percentage of used space that ORACLE maintains

    for each data block of the table.  A block becomes a candidate for

    row insertion when its used space falls below PCTUSED.  PCTUSED is

    specified as a positive integer from 1 to 99 and defaults to 40.



    PCTUSED has the same function in the commands that create and alter

    clusters, snapshots, and snapshot logs.



    The sum of PCTFREE and PCTUSED must be less than 100.  You can use

    PCTFREE and PCTUSED together use space within a table more

    efficiently.



INITRANS

    specifies the initial number of transaction entries allocated within

    each data block allocated to the table.  This value can range from 1

    to 255 and defaults to 1.  In general, you should not change the

    INITRANS value from its default.



    Each transaction that updates a block requires a transaction entry

    in the block.  The size of a transaction entry depends on your

    operating system.



    This parameter ensures that a minimum number of concurrent

    transactions can update the block and helps avoid the overhead of

    dynamically allocating a transaction entry.



    The INITRANS parameter serves the same purpose in clusters, indexes,

    snapshots, and snapshot logs as in tables.  The minimum and default

    INITRANS value for a cluster or index is 2, rather than 1.



MAXTRANS

    specifies the maximum number of concurrent transactions that can

    update a data block allocated to the table.  This limit does not

    apply to queries.  This value can range from 1 to 255 and the

    default is a function of the data block size.  You should not change

    the MAXTRANS value from its default.



    If the number concurrent transactions updating a block exceeds the

    INITRANS value, ORACLE dynamically allocates transaction entries in

    the block until either the MAXTRANS value is exceeded or the block

    has no more free space.



    The MAXTRANS parameter serves the same purpose in clusters,

    snapshots, and snapshot logs as in tables.



TABLESPACE

    specifies the tablespace in which ORACLE creates the table.  If you

    omit this option, then ORACLE creates the table in the default

    tablespace of the owner of the schema containing the table.



STORAGE

    specifies the storage characteristics for the table.  This clause

    has performance ramifications for large tables.  Storage should be

    allocated to minimize dynamic allocation of additional space.



CLUSTER

    specifies that the table is to be part of the cluster.  The columns

    listed in this clause are the table columns that correspond to the

    cluster"s columns.  Generally, the cluster columns of a table are

    the column or columns that comprise its primary key or a portion of

    its primary key.



    Specify one column from the table for each column in the cluster

    key.  The columns are matched by position, not by name.  Since a

    clustered table uses the cluster"s space allocation, do not use the

    PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameters, the TABLESPACE

    option, or the STORAGE clause in conjunction with the CLUSTER

    option.



ENABLE

    enables an integrity constraint.



DISABLE

    disables an integrity constraint.



    Constraints specified in the ENABLE and DISABLE clauses of a CREATE

    TABLE statement must be defined in the statement.  You can also

    enable and disable constraints with the ENABLE and DISABLE keywords

    of the CONSTRAINT clause.  If you define a constraint but do not

    explicitly enable or disable it, ORACLE enables it by default.



    You cannot use the ENABLE and DISABLE clauses in a CREATE TABLE

    statement to enable and disable triggers.



AS subquery

    inserts the rows returned by the subquery into the table upon its

    creation.



    If you include this clause, the column definitions can only specify

    column names, default values, and integrity constraints, not

    datatypes.  ORACLE derives column datatypes and lengths from the

    subquery.  ORACLE also automatically defines NOT NULL constraints on

    columns in the new table if they existed on the corresponding

    columns of the selected table and the subquery does not modify the

    column value with a SQL function or operator.  A CREATE TABLE

    statement cannot contain both the AS clause and a referential

    integrity constraint definition.



    The number of columns must equal the number of expressions in the

    subquery.  If all expressions in the subquery are columns, you can

    omit the columns from the table definition entirely.  In this case,

    the names of the columns of table are the same as the columns in the

    subquery.



PREREQUISITES:

    To create a table in your own schema, you must have CREATE TABLE

    system privilege.  To create a table in another user"s schema, you

    must have CREATE ANY TABLE system privilege.  Also, the owner of the

    schema to contain the table must have either space quota on the

    tablespace to contain the table or UNLIMITED TABLESPACE system

    privilege.



SEE:

    ALTER TABLE, CONSTRAINT, CREATE CLUSTER, CREATE INDEX, CREATE

    TABLESPACE, DISABLE, DROP TABLE, ENABLE, STORAGE

    CONSTRAINT clause



PURPOSE:

    To define an integrity constraint.  An integrity constraint is a

    rule that restricts the values for one or more columns in a table.



SYNTAX:



Column constraint:



[CONSTRAINT constraint]

{ [NOT] NULL

| {UNIQUE | PRIMARY KEY}

|  REFERENCES [schema.]table [(column)]

        [ON DELETE CASCADE]

|  CHECK (condition) }

{ [ USING INDEX [PCTFREE integer]

                [INITRANS integer] [MAXTRANS integer]

                [TABLESPACE tablespace]

                [STORAGE storage_clause] ]

  [ EXCEPTIONS INTO [schema.]table

| DISABLE }



Table constraint:



[CONSTRAINT constraint]

{ {UNIQUE | PRIMARY KEY} (column [,column] ...)

|  FOREIGN KEY (column [,column] ...)

         REFERENCES [schema.]table [(column [,column] ...)]

        [ON DELETE CASCADE]

| CHECK (condition) }

{ [ USING INDEX [PCTFREE integer]

                [INITRANS integer] [MAXTRANS integer]

                [TABLESPACE tablespace]

                [STORAGE storage_clause] ]

  [ EXCEPTIONS INTO [schema.]table[@dblink]

| DISABLE }



where:



CONSTRAINT

    identifies the integrity constraint by the name constraint.  ORACLE

    stores this name in the data dictionary along with the definition of

    the integrity constraint.  If you omit this identifier, ORACLE

    generates a name with this form:

            SYS_Cn

    where

            n

                   is an integer that makes the name unique

                   within the database.



    For the names and definitions of integrity constraints, query the

    data dictionary.



NULL

    specifies that a column can contain null values.



NOT NULL

    specifies that a column cannot contain null values.



    If you do not specify NULL or NOT NULL in a column definition, NULL

    is the default.



UNIQUE

    designates a column or combination of columns as a unique key.



PRIMARY KEY

    designates a column or combination of columns as the table"s primary

    key.



FOREIGN KEY

    designates a column or combination of columns as the foreign key in

    a referential integrity constraint.



REFERENCES

    identifies the primary or unique key that is referenced by a foreign

    key in a referential integrity constraint.



ON DELETE CASCADE

    specifies that ORACLE maintains referential integrity by

    automatically removing dependent foreign key values if you remove a

    referenced primary or unique key value.



CHECK

    specifies a condition that each row in the table must satisfy.



USING INDEX

    specifies parameters for the index ORACLE uses to enforce a UNIQUE

    or PRIMARY KEY constraint.  The name of the index is the same as the

    name of the constraint.  You can choose the values of the INITRANS,

    MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters for the index.

    For information on these parameters, see the CREATE TABLE command.



    Only use this clause when enabling UNIQUE and PRIMARY KEY

    constraints.



EXCEPTIONS INTO

    identifies a table into which ORACLE places information about rows

    that violate an enabled integrity constraint.  This table must exist

    before you use this option.  If you omit schema, ORACLE assumes the

    exception table is in your own schema.  The exception table must be

    on your local database.



DISABLE

    disables the integrity constraint.  If an integrity constraint is

    disabled, ORACLE does not enforce it.



    If you do not specify this option, ORACLE automatically enables the

    integrity constraint.



    You can also enable and disable integrity constraints with the

    ENABLE and DISABLE clauses of the CREATE TABLE and ALTER TABLE

    commands.



PREREQUISITES:

    CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE

    commands.  To define an integrity constraint, you must have the

    privileges necessary to issue one of these commands.  See the CREATE

    TABLE and ALTER TABLE commands.



    Defining a constraint may also require additional privileges or

    preconditions that depend on the type of constraint.



SEE:

    ALTER TABLE, CREATE TABLE, DISABLE, ENABLE



Example:  To define the table staff, as user scott, you could enter:



               CREATE TABLE staff (

                  empno    NUMBER       NOT NULL   PRIMARY KEY,

                  ename    CHAR(20)     NOT NULL   CHECK (ename = UPPER),

                  job      CHAR(10),

                  mgr      NUMBER       REFERENCES scott.staff(empno),

                  hiredate DATE         CHECK (hiredate >= SYSDATE - 7),

                  sal      NUMBER(10,2) CHECK (sal > 800),

                  comm     NUMBER(9,2)  DEFAULT NULL,

                  deptno   NOT NULL     REFERENCES scott.dept(deptno)

               )

               PCTFREE 5 PCTUSED 75