7
SQL Statements (continued)

ALTER TABLE

Syntax

column_constraint, table_constraint, column_ref_constraint, table_ref_constraint, constraint_state: See the "constraint_clause".

LOB_storage_clause::=

LOB_parameters::=

storage_clause: See "storage_clause".

partition_LOB_storage_clause::=

modify_column_options::=

move_table_clause::=

segment_attributes_clause::=

physical_attributes_clause::=

index_organized_table_clause::=

compression_clause::=

index_organized_overflow_clause::=

modify_collection_retrieval_clause::=

storage_clauses::=

modify_LOB_storage_clause::=

modify_LOB_storage_parameters::=

allocate_extent_clause::=

deallocate_unused_clause::=

varray_storage_clause::=

modify_varray_storage_clause::=

nested_table_storage_clause::=

object_properties::=

physical_properties::=

drop_constraint_clause::=

drop_column_clause::=

records_per_block_clause::=

alter_overflow_clause::=

overflow_clause::=

add_overflow_clause::=

partitioning_clauses::=

modify_default_attributes_clause::=

modify_partition_clause::=

partition_attributes::=

add_subpartition_clause::=

subpartition_description::=

modify_subpartition_clause::=

move_partition_clause::=

partition_description::=

partition_level_subpartitioning::=

partitioning_storage_clause::=

move_subpartition_clause::=

add_range_partition_clause::=

add_hash_partition_clause::=

coalesce_partition_clause::=

drop_partition_clause::=

rename_partition/ subpartition_clause::=

truncate_partition_clause/truncate_partition_clause::=

split_partition_clause::=

merge_partitions_clause::=

exchange_partition_clause/exchange_partition_clause::=

row_movement_clause::=

parallel_clause::=

enable_disable_clause::=

using_index_clause::=

Purpose

To alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition.

Prerequisites

The table must be in your own schema, or you must have ALTER privilege on the table, or you must have ALTER ANY TABLE system privilege. For some operations you may also need the CREATE ANY INDEX privilege.

In addition, if you are not the owner of the table, you need the DROP ANY TABLE privilege in order to use the drop_partition_clause or truncate_partition_clause.

You must also have space quota in the tablespace in which space is to be acquired in order to use the add_partition_clause, modify_partition_clause, move_partition_clause, and split_partition_clause.

To enable a UNIQUE or PRIMARY KEY constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle creates an index on the columns of the unique or primary key in the schema containing the table. See "CREATE INDEX".

To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege.

To use an object type in a column definition when modifying a table, either that object must belong to the same schema as the table being altered, or you must have either the EXECUTE ANY TYPE system privilege or the EXECUTE schema object privilege for the object type.

Keywords and Parameters

Examples

Nested Table Example

The following statement modifies the storage characteristics of a nested table column PROJECTS in table EMP so that when queried it returns actual values instead of locators:

ALTER TABLE emp MODIFY NESTED TABLE projects RETURN AS VALUE;

PARALLEL Example

The following statement specifies parallel processing for queries to the EMP table:

ALTER TABLE emp
   PARALLEL;

ENABLE VALIDATE Example

The following statement places in ENABLE VALIDATE state an integrity constraint named FK_DEPTNO in the EMP table:

ALTER TABLE emp
   ENABLE VALIDATE CONSTRAINT fk_deptno
   EXCEPTIONS INTO except_table;

Each row of the EMP table must satisfy the constraint for Oracle to enable the constraint. If any row violates the constraint, the constraint remains disabled. Oracle lists any exceptions in the table EXCEPT_TABLE. You can also identify the exceptions in the EMP table with the following statement:

SELECT emp.*
   FROM emp e, except_table ex
   WHERE e.row_id = ex.row_id
      AND ex.table_name = 'EMP'
      AND ex.constraint = 'FK_DEPTNO';

ENABLE NOVALIDATE Example

The following statement tries to place in ENABLE NOVALIDATE state two constraints on the EMP table:

ALTER TABLE emp
   ENABLE NOVALIDATE UNIQUE (ename)
   ENABLE NOVALIDATE CONSTRAINT nn_ename;

This statement has two ENABLE clauses:

The first places a unique constraint on the ENAME column in ENABLE NOVALIDATE state.
The second places the constraint named NN_ENAME in ENABLE NOVALIDATE state.

In this case, Oracle enables the constraints only if both are satisfied by each row in the table. If any row violates either constraint, Oracle returns an error and both constraints remain disabled.

DISABLE Example

Consider a referential integrity constraint involving a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table. The foreign key references a unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table. The following statement disables the unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table:

ALTER TABLE customers
   DISABLE UNIQUE (areaco, phoneno) CASCADE;

The unique key in the CUSTOMERS table is referenced by the foreign key in the PHONE_CALLS table, so you must use the CASCADE clause to disable the unique key. This clause disables the foreign key as well.

CHECK Example

The following statement defines and disables a CHECK constraint on the EMP table:

ALTER TABLE emp
   ADD (CONSTRAINT check_comp CHECK (sal + comm <= 5000) )
   DISABLE CONSTRAINT check_comp;

The constraint CHECK_COMP ensures that no employee's total compensation exceeds $5000. The constraint is disabled, so you can increase an employee's compensation above this limit.

Triggers Example

The following statement enables all triggers associated with the EMP table:

ALTER TABLE emp
   ENABLE ALL TRIGGERS;

DEALLOCATE UNUSED Example

The following statement frees all unused space for reuse in table EMP, where the high water mark is above MINEXTENTS:

ALTER TABLE emp
    DEALLOCATE UNUSED;

DROP COLUMN Example

This statement illustrates the drop_column_clause with CASCADE CONSTRAINTS. Assume table T1 is created as follows:

CREATE TABLE t1 (
   pk NUMBER PRIMARY KEY,
   fk NUMBER,
   c1 NUMBER,
   c2 NUMBER,
   CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1,
   CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0),
   CONSTRAINT ck2 CHECK (c2 > 0)
);

An error will be returned for the following statements:

ALTER TABLE t1 DROP (pk); -- pk is a parent key
ALTER TABLE t1 DROP (c1);  -- c1 is referenced by multicolumn
                           constraint ck1

Submitting the following statement drops column PK, the primary key constraint, the foreign key constraint, RI, and the check constraint, CK1:

ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

If all columns referenced by the constraints defined on the dropped columns are also dropped, then CASCADE CONSTRAINTS is not required. For example, assuming that no other referential constraints from other tables refer to column PK, then it is valid to submit the following statement without the CASCADE CONSTRAINTS clause:

ALTER TABLE t1 DROP (pk, fk, c1);

Index-Organized Table Examples

This statement modifies the INITRANS parameter for the index segment of index-organized table DOCINDEX:

ALTER TABLE docindex INITRANS 4;

The following statement adds an overflow data segment to index-organized table DOCINDEX:

ALTER TABLE docindex ADD OVERFLOW;

This statement modifies the INITRANS parameter for the overflow data segment of index-organized table DOCINDEX:

ALTER TABLE docindex OVERFLOW INITRANS 4;

ADD PARTITION Example

The following statement adds a partition P3 and specifies storage characteristics for three of the table's LOB columns (B, C, and D):

ALTER TABLE pt ADD PARTITION p3 VALUES LESS THAN (30)
   LOB (b, d) STORE AS (TABLESPACE tsz)
   LOB (c) STORE AS mylobseg;

The LOB data and LOB index segments for columns B and D in partition P3 will reside in tablespace TSZ. The remaining attributes for these LOB columns will be inherited first from the table-level defaults, and then from the tablespace defaults.

The LOB data segments for column C will reside in the MYLOBSEG segment, and will inherit all other attributes from the table-level defaults and then from the tablespace defaults.

SPLIT PARTITION Example

The following statement splits partition P3 into partitions P3_1 and P3_2:

ALTER TABLE pt SPLIT PARTITION p3 AT VALUES LESS THAN (25)
   INTO (PARTITION p3_1 TABLESPACE ts4
            LOB (b,d) STORE AS (TABLESPACE tsz),
         PARTITION p3_2 (TABLESPACE ts5)
            LOB (c) STORE AS (TABLESPACE ts5);

In partition P3_1, Oracle creates the LOB segments for columns B and D in tablespace TSZ. In partition P3_2, Oracle creates the LOB segments for column C in tablespace TS5. The LOB segments for columns B and D in partition P3_2 and those for column C in partition P3_1 remain in original tablespace for the original partition P3. However, Oracle creates new segments for all the LOB data and LOB index segments, even though they are not moved to a new tablespace.

User-Defined Object Identifier Example

The following statements create an object type, a corresponding object table with a primary-key-based object identifier, and a table having a user-defined REF column:

CREATE TYPE emp_t AS OBJECT (empno NUMBER, address CHAR(30));

CREATE TABLE emp OF emp_t (
   empno PRIMARY KEY)
   OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TABLE dept (dno NUMBER, mgr_ref REF emp_t SCOPE is emp);

The next statements add a constraint and a user-defined REF column, both of which reference table EMP:

ALTER TABLE dept ADD CONSTRAINT mgr_cons FOREIGN_KEY (mgr_ref)
   REFERENCES emp;
ALTER TABLE dept ADD sr_mgr REF emp_t REFERENCES emp;

Add Column Example

The following statement adds a column named THRIFTPLAN of datatype NUMBER with a maximum of seven digits and two decimal places and a column named LOANCODE of datatype CHAR with a size of one and a NOT NULL integrity constraint:

ALTER TABLE emp 
   ADD (thriftplan NUMBER(7,2),
        loancode CHAR(1) NOT NULL);

Modify Column Examples

The following statement increases the size of the THRIFTPLAN column to nine digits:

ALTER TABLE emp
   MODIFY (thriftplan NUMBER(9,2));

Because the MODIFY clause contains only one column definition, the parentheses around the definition are optional.

The following statement changes the values of the PCTFREE and PCTUSED parameters for the EMP table to 30 and 60, respectively:

ALTER TABLE emp 
    PCTFREE 30
    PCTUSED 60;

ALLOCATE EXTENT Example

The following statement allocates an extent of 5 kilobytes for the EMP table and makes it available to instance 4:

ALTER TABLE emp
  ALLOCATE EXTENT (SIZE 5K INSTANCE 4);

Because this statement omits the DATAFILE parameter, Oracle allocates the extent in one of the datafiles belonging to the tablespace containing the table.

DEFAULT Examples

This statement modifies the BAL column of the ACCOUNTS table so that it has a default value of 0:

ALTER TABLE accounts
  MODIFY (bal DEFAULT 0);

If you subsequently add a new row to the ACCOUNTS table and do not specify a value for the BAL column, the value of the BAL column is automatically 0:

INSERT INTO accounts(accno, accname)
  VALUES (accseq.nextval, 'LEWIS'); 

SELECT * 
    FROM accounts
    WHERE accname = 'LEWIS'; 

ACCNO  ACCNAME BAL 
------ ------- ---
815234  LEWIS     0

To discontinue previously specified default values, so that they are no longer automatically inserted into newly added rows, replace the values with nulls, as shown in this statement:

ALTER TABLE accounts
   MODIFY (bal DEFAULT NULL);

The MODIFY clause need only specify the column name and the modified part of the definition, rather than the entire column definition. This statement has no effect on any existing values in existing rows.

Drop Constraint Examples

The following statement drops the primary key of the DEPT table:

ALTER TABLE dept 
    DROP PRIMARY KEY CASCADE;

If you know that the name of the PRIMARY KEY constraint is PK_DEPT, you could also drop it with the following statement:

ALTER TABLE dept
    DROP CONSTRAINT pk_dept CASCADE;

The CASCADE clause drops any foreign keys that reference the primary key.

The following statement drops the unique key on the DNAME column of the DEPT table:

ALTER TABLE dept 
    DROP UNIQUE (dname);

The DROP clause in this statement omits the CASCADE clause. Because of this omission, Oracle does not drop the unique key if any foreign key references it.

LOB Examples

The following statement adds CLOB column RESUME to the EMPLOYEE table and specifies LOB storage characteristics for the new column:

ALTER TABLE employee ADD (resume CLOB)
  LOB (resume) STORE AS resume_seg (TABLESPACE resume_ts);

To modify the LOB column RESUME to use caching, enter the following statement:

ALTER TABLE employee MODIFY LOB (resume) (CACHE);

Nested Table Examples

The following statement adds the nested table column SKILLS to the EMPLOYEE table:

ALTER TABLE employee ADD (skills skill_table_type)
    NESTED TABLE skills STORE AS nested_skill_table;

You can also modify a nested table's storage characteristics. Use the name of the storage table specified in the nested_table_storage_clause to make the modification. You cannot query or perform DML statements on the storage table. Use the storage table only to modify the nested table column storage characteristics.

The following statement creates table VETSERVICE with nested table column CLIENT and storage table CLIENT_TAB. Nested table VETSERVICE is modified to specify constraints:

CREATE TYPE pet_table AS OBJECT
   (pet_name VARCHAR2(10), pet_dob DATE);

CREATE TABLE vetservice (vet_name VARCHAR2(30),
                         client   pet_table)
  NESTED TABLE client STORE AS client_tab;

ALTER TABLE client_tab ADD UNIQUE (ssn);

The following statement adds a UNIQUE constraint to nested table NESTED_SKILL_TABLE:

ALTER TABLE nested_skill_table ADD UNIQUE (a);

The following statement alters the storage table for a nested table of REF values to specify that the REF is scoped:

CREATE TYPE emp_t AS OBJECT (eno number, ename char(31)); 
CREATE TYPE emps_t AS TABLE OF REF emp_t; 
CREATE TABLE emptab OF emp_t; 
CREATE TABLE dept (dno NUMBER, employees emps_t) 
   NESTED TABLE employees STORE AS deptemps; 
ALTER TABLE deptemps ADD (SCOPE FOR (column_value) IS emptab);

Similarly, to specify storing the REF with rowid:

ALTER TABLE deptemps ADD (REF(column_value) WITH ROWID);

In order to execute these ALTER TABLE statements successfully, the storage table DEPTEMPS must be empty. Also, because the nested table is defined as a table of scalars (REFs), Oracle implicitly provides the column name COLUMN_VALUE for the storage table.

For more information about nested table storage see "CREATE TABLE". For more information about nested tables, see Oracle8i Application Developer's Guide - Fundamentals.

REF Examples

In the following statement an object type DEPT_T has been previously defined. Now, create table EMP as follows:

CREATE TABLE emp 
   (name VARCHAR(100), 
    salary NUMBER,
    dept REF dept_t);

An object table DEPARTMENTS is created as:

CREATE TABLE departments OF dept_t;

The DEPT column can store references to objects of DEPT_T stored in any table. If you would like to restrict the references to point only to objects stored in the DEPARTMENTS table, you could do so by adding a scope constraint on the DEPT column as follows:

ALTER TABLE emp 
    ADD (SCOPE FOR (dept) IS departments);

The above ALTER TABLE statement will succeed only if the EMP table is empty.

If you want the REF values in the DEPT column of EMP to also store the rowids, issue the following statement:

ALTER TABLE emp 
   ADD (REF(dept) WITH ROWID);

Add Partition Example

The following statement adds partition JAN99 to tablespace TSX:

ALTER TABLE sales 
  ADD PARTITION jan99 VALUES LESS THAN( '970201' ) 
  TABLESPACE tsx;

Drop Partition Example

The following statement drops partition DEC98:

ALTER TABLE sales DROP PARTITION dec98;

Exchange Partition Example

The following statement converts partition FEB97 to table SALES_FEB97 without exchanging local index partitions with corresponding indexes on SALES_FEB97 and without verifying that data in SALES_FEB97 falls within the bounds of partition FEB97:

ALTER TABLE sales 
   EXCHANGE PARTITION feb97 WITH TABLE sales_feb97 
   WITHOUT VALIDATION;

Modify Partition Examples

The following statement marks all the local index partitions corresponding to the NOV96 partition of the SALES table UNUSABLE:

ALTER TABLE sales MODIFY PARTITION nov96 
   UNUSABLE LOCAL INDEXES;

The following statement rebuilds all the local index partitions that were marked UNUSABLE:

ALTER TABLE sales MODIFY PARTITION jan97
   REBUILD UNUSABLE LOCAL INDEXES;

The following statement changes MAXEXTENTS and logging attribute for partition BRANCH_NY:

ALTER TABLE branch MODIFY PARTITION branch_ny  
   STORAGE (MAXEXTENTS 75) LOGGING;

Move Partition Example

The following statement moves partition DEPOT2 to tablespace TS094:

ALTER TABLE parts 
  MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;

Rename Partition Examples

The following statement renames a table:

ALTER TABLE emp RENAME TO employee;

In the following statement, partition EMP3 is renamed:

ALTER TABLE employee RENAME PARTITION emp3 TO employee3;

Split Partition Example

The following statement splits the old partition DEPOT4, creating two new partitions, naming one DEPOT9 and reusing the name of the old partition for the other:

ALTER TABLE parts
   SPLIT PARTITION depot4 AT ( '40-001' )
   INTO ( PARTITION depot4 TABLESPACE ts009 STORAGE (MINEXTENTS 2),
         PARTITION depot9 TABLESPACE ts010 )
   PARALLEL (10);

Truncate Partition Example

The following statement deletes all the data in the SYS_P017 partition and deallocates the freed space:

ALTER TABLE deliveries
   TRUNCATE PARTITION sys_p017 DROP STORAGE;

Additional Examples

For examples of defining integrity constraints with the ALTER TABLE statement, see the "constraint_clause".

For examples of changing the value of a table's storage parameters, see the "storage_clause".

ALTER TABLESPACE

Syntax

datafile/tempfile_clauses::=

filespec: See "filespec".

autoextend_clause::=

maxsize_clause::=

storage_clause: See "storage_clause".

Purpose

To alter an existing tablespace or one or more of its datafiles or tempfiles.

For information on creating a tablespace, see "CREATE TABLESPACE".

Prerequisites

If you have ALTER TABLESPACE system privilege, you can perform any of this statement's operations. If you have MANAGE TABLESPACE system privilege, you can only perform the following operations:

take the tablespace online or offline
begin or end a backup
make the tablespace read-only or read-write

Before you can make a tablespace read-only, the following conditions must be met:

The tablespace must be online.
The tablespace must not contain any active rollback segments. For this reason, the SYSTEM tablespace can never be made read-only, because it contains the SYSTEM rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, Oracle recommends that you drop the rollback segments before you make a tablespace read-only.
The tablespace must not be involved in an open backup, because the end of a backup updates the header file of all datafiles in the tablespace.

Performing this function in restricted mode may help you meet these restrictions, because only users with RESTRICTED SESSION system privilege can be logged on.

Keywords and Parameters

tablespace

is the name of the tablespace to be altered.

LOGGING | NOLOGGING

specifies the default logging attribute of all tables, indexes, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.

When an existing tablespace logging attribute is changed by an ALTER TABLESPACE statement, all tables, indexes, and partitions created after the statement will have the new default logging attribute (which you can still subsequently override). The logging attributes of existing objects are not changed.

Only the following operations support NOLOGGING mode:

DML: direct-load INSERT (serial or parallel); Direct Loader (SQL*Loader)

DDL: CREATE TABLE... AS SELECT, CREATE INDEX, ALTER INDEX... REBUILD, ALTER INDEX... REBUILD PARTITION, ALTER INDEX... SPLIT PARTITION, ALTER TABLE... SPLIT PARTITION, ALTER TABLE... MOVE PARTITION.

In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose the object, it is important to take a backup after the NOLOGGING operation.

datafile/tempfile_clauses

adds or modifies a datafile or tempfile.

ADD DATAFILE | TEMPFILE

Adds to the tablespace a datafile or tempfile specified by filespec (see "filespec").
You can add a datafile or tempfile to a locally managed tablespace that is online or to a dictionary managed tablespace that is online or offline. Be sure the file is not in use by another database.

Note: As the syntax shows, you cannot combine an ADD clause with any other clauses in the same ALTER TABLESPACE statement. In addition, for a locally managed temporary tablespace, you cannot specify any of the other clauses for this tablespace at any time.

RENAME DATAFILE

renames one or more of the tablespace's datafiles. Take the tablespace offline before renaming the datafile. Each 'filename' must fully specify a datafile using the conventions for filenames on your operating system.

This clause merely associates the tablespace with the new file rather than the old one. This clause does not actually change the name of the operating system file. You must change the name of the file through your operating system.

autoextend_clause

enables or disables the autoextending of the size of the datafile in the tablespace.

OFF

disables autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND statements.

ON

enables autoextend.

NEXT

specifies the size in bytes of the next increment of disk space to be allocated automatically to the datafile when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is one data block.

maxsize_clause

specifies maximum disk space allowed for automatic extension of the datafile.

UNLIMITED sets no limit on allocating disk space to the datafile.

DEFAULT storage_clause

specifies the new default storage parameters for objects subsequently created in the tablespace. For a dictionary-managed temporary table, Oracle considers only the NEXT parameter of the storage_clause. See the "storage_clause".
Restriction: You cannot specify this clause for a locally managed tablespace.

MINIMUM EXTENT integer

controls free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, integer. This clause is not relevant for a dictionary-managed temporary tablespace. For more information about using MINIMUM EXTENT to control space fragmentation, see Oracle8i Administrator's Guide.
Restriction: You cannot specify this clause for a locally managed tablespace.

ONLINE

brings the tablespace online.

OFFLINE

takes the tablespace offline and prevents further access to its segments.

NORMAL

flushes all blocks in all datafiles in the tablespace out of the SGA. You need not perform media recovery on this tablespace before bringing it back online. This is the default.

TEMPORARY

performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Any offline files may require media recovery before you bring the tablespace back online.

IMMEDIATE

does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.

FOR RECOVER

takes the production database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information see Oracle8i Backup and Recovery Guide.

Suggestion: Before taking a tablespace offline for a long time, you may want to alter the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace. When the tablespace is offline, these users cannot allocate space for objects or sort areas in the tablespace. For more information, see "ALTER USER".

BEGIN BACKUP

signifies that an open backup is to be performed on the datafiles that make up this tablespace. This clause does not prevent users from accessing the tablespace. You must use this clause before beginning an open backup. You cannot use this clause on a read-only tablespace.

Note: While the backup is in progress, you cannot take the tablespace offline normally, shut down the instance, or begin another backup of the tablespace.

END BACKUP

signifies that an open backup of the tablespace is complete. Use this clause as soon as possible after completing an open backup. You cannot use this clause on a read-only tablespace.

If you forget to indicate the end of an online tablespace backup, and an instance failure or SHUTDOWN ABORT occurs, Oracle assumes that media recovery (possibly requiring archived redo log) is necessary at the next instance start up. To restart the database without media recovery, see Oracle8i Administrator's Guide.

READ ONLY

signifies that no further write operations are allowed on the tablespace. (This clause waits for all existing transactions either to commit or roll back before taking effect.) The tablespace becomes read only.
Once a tablespace is read only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL statement ALTER DATABASE ... RENAME. See "ALTER DATABASE". For more information on read-only tablespaces, see Oracle8i Concepts.

READ WRITE

signifies that write operations are allowed on a previously read-only tablespace.

PERMANENT

specifies that the tablespace is to be converted from a temporary to a permanent one. A permanent tablespace is one in which permanent database objects can be stored. This is the default when a tablespace is created.

TEMPORARY

specifies that the tablespace is to be converted from a permanent to a temporary one. A temporary tablespace is one in which no permanent database objects can be stored. Objects in a temporary tablespace persist only for the duration of the session.

COALESCE

for each datafile in the tablespace, coalesces all contiguous free extents into larger contiguous extents.
Restriction: COALESCE cannot be specified with any other statement clause.

Examples

Backup Examples

The following statement signals to the database that a backup is about to begin:

ALTER TABLESPACE accounting 
    BEGIN BACKUP;

The following statement signals to the database that the backup is finished:

ALTER TABLESPACE accounting 
    END BACKUP;

Moving and Renaming Example

This example moves and renames a datafile associated with the ACCOUNTING tablespace from 'DISKA:PAY1.DAT' to 'DISKB:RECEIVE1.DAT':

Take the tablespace offline using an ALTER TABLESPACE statement with the OFFLINE clause:
```
ALTER TABLESPACE accounting OFFLINE NORMAL; 
```
Copy the file from 'DISKA:PAY1.DAT' to 'DISKB:RECEIVE1.DAT' using your operating system's commands.

Rename the datafile using the ALTER TABLESPACE statement with the RENAME DATAFILE clause:

ALTER TABLESPACE accounting
  RENAME DATAFILE 'diska:pay1.dbf'
  TO     'diskb:receive1.dbf';

Bring the tablespace back online using an ALTER TABLESPACE statement with the ONLINE clause:
```
ALTER TABLESPACE accounting ONLINE; 
```

Adding a Datafile Example

The following statement adds a datafile to the tablespace and changes the default logging attribute to NOLOGGING. When more space is needed, new extents of size 10 kilobytes will be added up to a maximum of 100 kilobytes:

ALTER TABLESPACE accounting NOLOGGING
    ADD DATAFILE 'disk3:pay3.dbf'
    SIZE 50K
    AUTOEXTEND ON
    NEXT 10K
    MAXSIZE 100K;

Altering a tablespace logging attribute has no affect on the logging attributes of the existing schema objects within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.

Changing Extent Allocation Example

The following statement changes the allocation of every extent of TABSPACE_ST to a multiple of 128K:

ALTER TABLESPACE tabspace_st MINIMUM EXTENT 128K;

ALTER TRIGGER

Syntax

Purpose

To enable, disable, or compile a database trigger. For information on creating a trigger, see "CREATE TRIGGER". For information on dropping a trigger, see "DROP TRIGGER".

Note:
This statement does not change the declaration or definition of an existing trigger. To redeclare or redefine a trigger, use the CREATE TRIGGER statement with OR REPLACE.

Prerequisites

The trigger must be in your own schema or you must have ALTER ANY TRIGGER system privilege.

In addition, to alter a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege. For more information on triggers based on DATABASE, see "CREATE TRIGGER".

Keywords and Parameters

schema

is the schema containing the trigger. If you omit schema, Oracle assumes the trigger is in your own schema.

trigger

is the name of the trigger to be altered.

ENABLE

enables the trigger. You can also use the ENABLE ALL TRIGGERS clause of ALTER TABLE to enable all triggers associated with a table. See "ALTER TABLE".

DISABLE

disables the trigger. You can also use the DISABLE ALL TRIGGERS clause of ALTER TABLE to disable all triggers associated with a table. See "ALTER TABLE".

COMPILE

explicitly compiles the trigger, whether it is valid or invalid. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

Oracle first recompiles objects upon which the trigger depends, if any of these objects are invalid. If Oracle recompiles the trigger successfully, the trigger becomes valid.
If recompiling the trigger results in compilation errors, then Oracle returns an error and the trigger remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS. For information on debugging procedures, see Oracle8i Application Developer's Guide - Fundamentals. For information on how Oracle maintains dependencies among schema objects, including remote objects, see Oracle8i Concepts.

DEBUG

instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. This clause can be used for normal triggers and for instead-of triggers.

Examples

Consider a trigger named REORDER created on the INVENTORY table. The trigger is fired whenever an UPDATE statement reduces the number of a particular part on hand below the part's reorder point. The trigger inserts into a table of pending orders a row that contains the part number, a reorder quantity, and the current date.

When this trigger is created, Oracle enables it automatically. You can subsequently disable the trigger with the following statement:

ALTER TRIGGER reorder DISABLE;

When the trigger is disabled, Oracle does not fire the trigger when an UPDATE statement causes the part's inventory to fall below its reorder point.

After disabling the trigger, you can subsequently enable it with the following statement:

ALTER TRIGGER reorder ENABLE;

After you reenable the trigger, Oracle fires the trigger whenever a part's inventory falls below its reorder point as a result of an UPDATE statement. It is possible that a part's inventory falls below its reorder point while the trigger was disabled. In that case, when you reenable the trigger, Oracle does not automatically fire the trigger for this part until another transaction further reduces the inventory.

ALTER TYPE

Syntax

element_list::=

pragma_clause::=

Purpose

To recompile the specification and/or body, or to change the specification of an object type by adding new object member subprogram specifications.

You cannot change the existing properties (attributes, member subprograms, map or order functions) of an object type, but you can add new member subprogram specifications.

Prerequisites

The object type must be in your own schema and you must have CREATE TYPE or CREATE ANY TYPE system privilege, or you must have ALTER ANY TYPE system privileges.

Keywords and Parameters

schema

is the schema that contains the type. If you omit schema, Oracle assumes the type is in your current schema.

type

is the name of an object type, a nested table type, or a rowid type.

COMPILE

compiles the object type specification and body. This is the default if neither SPECIFICATION nor BODY is specified.
If recompiling the type results in compilation errors, then Oracle returns an error and the type remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.

SPECIFICATION

compiles only the object type specification.

BODY

compiles only the object type body.

DEBUG

instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.

REPLACE AS OBJECT

adds new member subprogram specifications. This clause is valid only for object types, not for nested table or varray types.

attribute

is an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object.

MEMBER | STATIC

specifies a function or procedure subprogram associated with the object type which is referenced as an attribute. For a description of the difference between member and static methods, and for examples, see "CREATE TYPE". For information about overloading subprogram names within a package, see the PL/SQL User's Guide and Reference.
You must specify a corresponding method body in the object type body for each procedure or function specification. See "CREATE TYPE BODY".

procedure_spec

is the specification of a procedure subprogram.

function_spec

is the specification of a function subprogram.

pragma_clause

is a complier directive that denies member functions read/write access to database tables, packaged variables, or both, and thereby helps to avoid side effects. For more information, see Oracle8i Application Developer's Guide - Fundamentals.

method

is the name of the MEMBER function or procedure to which the pragma is being applied.

DEFAULT

specifies that the pragma should be applied to all methods in the type for which a pragma has not been explicitly specified.

WNDS

specifies the constraint writes no database state (does not modify database tables).

WNPS

specifies the constraint writes no package state (does not modify packaged variables).

RNDS

specifies the constraint reads no database state (does not query database tables).

RNPS

specifies the constraint reads no package state (does not reference package variables).

TRUST

specifies that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true.

MAP|ORDER MEMBER function_spec

MAP

specifies a member function (MAP method) that returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and induces an ordering of object instances by mapping them to values of a predefined scalar type. Oracle uses the ordering for comparison operators and ORDER BY clauses.

If the argument to the map method is null, the map method returns null and the method is not invoked.

An object specification can contain only one map method, which must be a function. The result type must be a predefined SQL scalar type, and the map function can have no arguments other than the implicit SELF argument.

Note: If type_name will be referenced in queries involving sorts (through ORDER BY, GROUP BY, DISTINCT, or UNION clauses) or joins, and you want those queries to be parallelized, you must specify a MAP member function.

ORDER

specifies a member function (ORDER method) that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative, zero, or positive integer. The negative, zero, or positive indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.

If either argument to the order method is null, the order method returns null and the method is not invoked.
When instances of the same object type definition are compared in an ORDER BY clause, the order method function is invoked.

An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.

You can declare either a MAP method or an ORDER method, but not both. If you declare either method, you can compare object instances in SQL.

If you do not declare either method, you can compare object instances only for equality or inequality. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal. No comparison method needs to be specified to determine the equality of two object types. For more information about object value comparisons, see "Object Values".

Examples

Adding a Member Function

In the following example, member function QTR is added to the type definition of DATA_T.

CREATE TYPE data_t AS OBJECT 
   ( year NUMBER, 
     MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER 
   ); 
 
   CREATE TYPE BODY data_t IS   
      MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS 
         BEGIN 
             RETURN (year + invent);
         END; 
      END; 
 
   ALTER TYPE data_t REPLACE AS OBJECT 
   ( year NUMBER, 
     MEMBER FUNCTION  prod(invent NUMBER) RETURN NUMBER, 
     MEMBER FUNCTION  qtr(der_qtr DATE) RETURN CHAR 
   ); 
 
   CREATE OR REPLACE TYPE BODY data_t IS   
      MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS 
      MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS 
         BEGIN 
            RETURN (year + invent);
         END; 
         BEGIN 
            RETURN 'FIRST'; 
         END;
      END;

Recompiling a Type

The following example creates and then recompiles type LOAN_T:

CREATE TYPE loan_t AS OBJECT
  ( loan_num        NUMBER,
    interest_rate   FLOAT,
    amount          FLOAT,
    start_date      DATE,
    end_date        DATE );

ALTER TYPE loan_t COMPILE;

Recompiling a Type Body

The following example compiles the type body of LINK2.

CREATE TYPE link1 AS OBJECT
  (a NUMBER); 

CREATE TYPE link2 AS OBJECT
  (a NUMBER, 
   b link1, 
   MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER); 

CREATE TYPE BODY link2 AS
   MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER IS t13 link1; 
      BEGIN t13 := link1(13); 
         dbms_output.put_line(t13.a);
         RETURN 5; 
      END; 
   END; 

CREATE TYPE link3 AS OBJECT (a link2); 
CREATE TYPE link4 AS OBJECT (a link3); 
CREATE TYPE link5 AS OBJECT (a link4); 
ALTER TYPE link2 COMPILE BODY;

Recompiling a Type Specification

The following example compiles the type specification of LINK2.

CREATE TYPE link1 AS OBJECT
  (a NUMBER); 

CREATE TYPE link2 AS OBJECT
  (a NUMBER, 
   b link1, 
   MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER); 

CREATE TYPE BODY link2 AS
    MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER IS t14 link1;
      BEGIN t14 := link1(14); 
         dbms_output.put_line(t14.a);
         RETURN 5; 
      END; 
    END; 

CREATE TYPE link3 AS OBJECT (a link2); 
CREATE TYPE link4 AS OBJECT (a link3); 
CREATE TYPE link5 AS OBJECT (a link4); 
ALTER TYPE link2 COMPILE SPECIFICATION;

ALTER USER

Syntax

proxy_clause::=

Purpose

To change the authentication or database resource characteristics of a database user.

To permit a proxy server to connect as a client without authentication.

Note:
ALTER USER syntax does not accept the old password. Therefore it neither authenticates using the old password nor checks the new password against the old before setting the new password. If these checks against the old password are important, use the OCIPasswordChange() call instead of ALTER USER. For more information, see Oracle Call Interface Programmer's Guide.

Prerequisites

You must have the ALTER USER system privilege. However, you can change your own password without this privilege.

Keywords and Parameters

The keywords and parameters shown below are unique to ALTER USER or have different functionality than they have in CREATE USER. All the remaining keywords and parameters in the ALTER USER statement have the same meaning as in the CREATE USER statement. For information on these keywords and parameters, see "CREATE USER".

To assign limits on database resources to a user, see "CREATE PROFILE".

IDENTIFIED GLOBALLY AS

indicates that a user must be authenticated by way of an LDAP V3 compliant directory service such as Oracle Internet Directory. (See also "CREATE USER".)
You can change a user's access verification method to IDENTIFIED GLOBALLY AS 'external_name' only if all external roles granted directly to the user are revoked.
You can change a user created as IDENTIFIED GLOBALLY AS 'external_name' to IDENTIFIED BY password or IDENTIFIED EXTERNALLY.

DEFAULT ROLE

can contain only roles that have been granted directly to the user with a GRANT statement. You cannot use the DEFAULT ROLE clause to enable:

roles not granted to the user

roles granted through other roles

roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

Oracle enables default roles at logon without requiring the user to specify their passwords. For more information on roles, see "CREATE ROLE".

proxy_clause

controls the ability of a proxy (an application or application server) to connect as the specified user and to activate all, some, or none of the user's roles. For more information on proxies and their use of the database, see Oracle8i Concepts.

GRANT

allows the connection.

REVOKE

prohibits the connection.

proxy

identifies the proxy connecting to Oracle.

WITH ROLE

specifies the roles that the application is permitted to activate after it connects as the user. If you do not include this clause, Oracle activates all roles granted to the specified user automatically.

role_name

permits the proxy to connect as the specified user and to activate only the roles that are specified by role_name.

ALL EXCEPT role_name

permits the proxy to connect as the specified user and to activate all roles associated with that user except those specified by role_name.

NONE

permits the proxy to connect as the specified user, but prohibits the proxy from activating any of that user's roles after connecting.

Examples

General Examples

The following statement changes the user SCOTT's password to LION and default tablespace to the tablespace TSTEST:

ALTER USER scott 
    IDENTIFIED BY lion
    DEFAULT TABLESPACE tstest;

The following statement assigns the CLERK profile to SCOTT:

ALTER USER scott 
    PROFILE clerk;

In subsequent sessions, SCOTT is restricted by limits in the CLERK profile.

The following statement makes all roles granted directly to SCOTT default roles, except the AGENT role:

ALTER USER scott 
    DEFAULT ROLE ALL EXCEPT agent;

At the beginning of SCOTT's next session, Oracle enables all roles granted directly to SCOTT except the AGENT role.

Authentication Examples

The following statement changes user TOM's authentication mechanism:

ALTER USER tom IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';

The following statement causes user FRED's password to expire:

ALTER USER fred PASSWORD EXPIRE;

If you cause a database user's password to expire with PASSWORD EXPIRE, the user (or the DBA) must change the password before attempting to log in to the database following the expiration. However, tools such as SQL*Plus allow you to change the password on the first attempted login following the expiration.

Proxy Examples

The following statement permits the proxy user APPSERVER1 to connect as the user JANE. It also allows APPSERVER1 to activate the role INVENTORY:

ALTER USER jane GRANT CONNECT THROUGH appserver1 WITH ROLE inventory;

The following statement takes away the right of proxy user APPSERVER1 to connect as the user JANE:

ALTER USER jane REVOKE CONNECT THROUGH appserver1;

ALTER VIEW

Syntax

Purpose

To explicitly recompile a view that is invalid. Explicit recompilation allows you to locate recompilation errors before run time. You may want to recompile a view explicitly after altering one of its base tables to ensure that the alteration does not affect the view or other objects that depend on it.

When you issue an ALTER VIEW statement, Oracle recompiles the view regardless of whether it is valid or invalid. Oracle also invalidates any local objects that depend on the view. For more about dependencies among schema objects, see Oracle8i Concepts.

Notes:

This statement does not change the definition of an existing view. To redefine a view, you must use CREATE VIEW with OR REPLACE. See "CREATE VIEW".

If you alter a view that is referenced by one or more materialized views, those materialized views are invalidated. Invalid materialized views cannot be used by query rewrite and cannot be refreshed. To revalidate an invalid materialized view, see "ALTER MATERIALIZED VIEW / SNAPSHOT". For information on materialized views in general, see Oracle8i Tuning.

Prerequisites

The view must be in your own schema or you must have ALTER ANY TABLE system privilege.

Keywords and Parameters

schema

is the schema containing the view. If you omit schema, Oracle assumes the view is in your own schema.

view

is the name of the view to be recompiled.

COMPILE

causes Oracle to recompile the view. The COMPILE keyword is required.

Example

To recompile the view CUSTOMER_VIEW, issue the following statement:

ALTER VIEW customer_view
    COMPILE;

If Oracle encounters no compilation errors while recompiling CUSTOMER_VIEW, CUSTOMER_VIEW becomes valid. If recompiling results in compilation errors, Oracle returns an error and CUSTOMER_VIEW remains invalid.

Oracle also invalidates all dependent objects. These objects include any procedures, functions, package bodies, and views that reference CUSTOMER_VIEW. If you subsequently reference one of these objects without first explicitly recompiling it, Oracle recompiles it implicitly at run time.

ANALYZE

Syntax

for_clause::=

Purpose

To collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.

To validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

To identify migrated and chained rows of a table or cluster.

Prerequisites

The schema object to be analyzed must be in your own schema or you must have the ANALYZE ANY system privilege.

If you want to list chained rows of a table or cluster into a list table, the list table must be in your own schema, or you must have INSERT privilege on the list table, or you must have INSERT ANY TABLE system privilege.

If you want to validate a partitioned table, you must have INSERT privilege on the table into which you list analyzed rowids, or you must have INSERT ANY TABLE system privilege.

Keywords and Parameters

Examples

Analyzing a Cluster

The following statement estimates statistics for the CUST_HISTORY table and all of its indexes:

ANALYZE TABLE cust_history
   ESTIMATE STATISTICS;

Deleting Statistics

The following statement deletes statistics about the CUST_HISTORY table and all its indexes from the data dictionary:

ANALYZE TABLE cust_history
   DELETE STATISTICS;

Histogram Examples

The following statement creates a 10-band histogram on the SAL column of the EMP table:

ANALYZE TABLE emp 
   COMPUTE STATISTICS FOR COLUMNS sal SIZE 10;

You can also collect histograms for a single partition of a table. The following statement analyzes the EMP table partition P1:

ANALYZE TABLE emp PARTITION (p1) COMPUTE STATISTICS;

Index Example

The following statement validates the structure of the index PARTS_INDEX:

ANALYZE INDEX parts_index VALIDATE STRUCTURE;

Table Examples

The following statement analyzes the EMP table and all of its indexes:

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;

For a table, the VALIDATE REF UPDATE clause verifies the REFs in the specified table, checks the rowid portion of each REF, and then compares it with the true rowid. If the result is an incorrect rowid, the REF is updated so that the rowid portion is correct.

The following statement validates the REFs in the EMP table:

ANALYZE TABLE emp 
   VALIDATE REF UPDATE;

Cluster Example

The following statement analyzes the ORDER_CUSTS cluster, all of its tables, and all of their indexes, including the cluster index:

ANALYZE CLUSTER order_custs
    VALIDATE STRUCTURE CASCADE;

Chained Rows Example

The following statement collects information about all the chained rows of the table ORDER_HIST:

ANALYZE TABLE order_hist
    LIST CHAINED ROWS INTO cr;

The preceding statement places the information into the table CR. You can then examine the rows with this query:

SELECT * 
    FROM cr; 

OWNER_NAME  TABLE_NAME  CLUSTER_NAME  HEAD_ROWID         TIMESTAMP
----------  ----------  ------------  ------------------ ---------
SCOTT       ORDER_HIST                AAAAZzAABAAABrXAAA 15-MAR-96

COMPUTE Example

The following statement calculates statistics for a scalar object attribute:

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS addr.street;

ASSOCIATE STATISTICS

Syntax

column_association::=

function_association::=

using_clause::=

default_cost_clause::=

default_selectivity_clause::=

Purpose

To associate a statistics type (or default statistics) containing functions relevant to statistics collection, selectivity, or cost with one or more columns, standalone functions, packages, types, domain indexes, or indextypes.

For a listing of all current statistics type associations, refer to the USER_ASSOCIATIONS table. If you analyze the object with which you are associating statistics, you can also view the associations in the USER_USTATS table. For information on the order of precedence with which ANALYZE uses associations, see "ANALYZE".

Prerequisites

To issue this statement, you must have the appropriate privileges to alter the base object (table, function, package, type, domain index, or indextype). In addition, unless you are associating only default statistics, you must have execute privilege on the statistics type. The statistics type must already have been defined. For information on defining types, see "CREATE TYPE".

Keywords and Parameters

column_association

specifies a list of one or more table columns. If you do not specify schema, Oracle assumes the table is in your own schema.

function_association

specifies a list of one or more standalone functions, packages, user-defined datatypes, domain indexes, or indextypes. If you do not specify schema, Oracle assumes the object is in your own schema.

FUNCTIONS refers only to standalone functions, not to method types or to built-in functions.

TYPES refers only to user-defined types, not to internal SQL datatypes.

Restriction: You cannot specify an object for which you have already defined an association. You must first disassociate the statistics from this object. See "DISASSOCIATE STATISTICS".

using_clause

specifies the statistics type being associated with columns, functions, packages, types, domain indexes, or indextypes. The statistics_type must already have been created.

default_cost_clause

specifies default costs for standalone functions, packages, types, domain indexes, or indextypes. If you specify this clause, you must include one number each for CPU cost, I/O cost, and network cost, in that order. Each cost is for a single execution of the function or method or for a single domain index access. Accepted values are integers of zero or greater.

default_selectivity_clause

specifies as a percent the default selectivity for predicates with standalone functions, types, packages, or user-defined operators. The default_selectivity must be a whole number between 0 and 100. Values outside this range are ignored.
Restriction: You cannot specify DEFAULT SELECTIVITY for domain indexes or indextypes.

Examples

Standalone Function Example

This statement creates an association for a standalone function FN and causes the optimizer to call the appropriate cost function (if present) in the statistics type STAT_FN.

ASSOCIATE STATISTICS WITH FUNCTIONS fn USING stat_fn;

Default Cost Example

This statement specifies that using the domain index T_A to implement a given predicate always has a CPU cost of 100, I/O of 5, and network cost of 0.

ASSOCIATE STATISTICS WITH INDEXES t_a DEFAULT COST (100,5,0);

The optimizer will simply use these default costs instead of calling a cost function.

AUDIT sql_statements

Syntax

Purpose

To track the occurrence of specific SQL statements in subsequent user sessions. Auditing options specified by the AUDIT sql_statements statement apply only to subsequent sessions, not to current sessions.

To choose particular schema objects for auditing, see "AUDIT schema_objects". For information on disabling auditing of SQL statements, see "NOAUDIT sql_statements".

Prerequisites

You must have AUDIT SYSTEM system privilege.

You must enable auditing by setting the initialization parameter AUDIT_TRAIL to DB. You can specify auditing options regardless of whether auditing is enabled. However, Oracle does not generate audit records until you enable auditing.

Keywords and Parameters

statement_opt

chooses specific SQL statements for auditing. For a list of these statement options and the SQL statements they audit, see Table 7-1 and Table 7-2.
For each audited operation, Oracle produces an audit record containing this information:

user performing the operation

type of operation

object involved in the operation

date and time of the operation

Oracle writes audit records to the audit trail, which is a database table containing audit records. You can review database activity by examining the audit trail through data dictionary views. For information on these views, see the Oracle8i Reference.

system_priv

chooses SQL statements that are authorized by the specified system privilege for auditing. For a list of all system privileges and the SQL statements that they authorize, see Table 7-5.
Oracle provides shortcuts for specifying groups of system privileges and statement options at once. However, Oracle encourages you to choose individual system privileges and statement options for auditing, because these shortcuts may not be supported in future versions of Oracle. The shortcuts are:

CONNECT

is equivalent to specifying the CREATE SESSION system privilege

RESOURCE

is equivalent to specifying the following system privileges:

ALTER SESSION

CREATE CLUSTER

CREATE DATABASE LINK

CREATE PROCEDURE

CREATE ROLLBACK SEGMENT

CREATE SEQUENCE

CREATE SYNONYM

CREATE TABLE

CREATE TABLESPACE

CREATE VIEW

DBA

is equivalent to the SYSTEM GRANT statement option and the following system privileges:

AUDIT SYSTEM

CREATE PUBLIC DATABASE LINK

CREATE PUBLIC SYNONYMN

CREATE ROLE

CREATE USER

ALL

is equivalent to specifying all statements options shown in Table 7-1 but not the additional statement options shown in Table 7-2.

ALL PRIVILEGES

is equivalent to specifying all system privileges.

BY user

chooses only SQL statements issued by specified users for auditing. If you omit this clause, Oracle audits all users' statements.

BY proxy

chooses for auditing only SQL statements issued by the specified proxy. For more information on proxies and their use of the database, see Oracle8i Concepts.

ON BEHALF OF

specifies the user or users on whose behalf the proxy executes the specified statement.

user specifies auditing of statements executed on behalf of a particular user.

ANY specifies auditing of statements executed on behalf of any user.

BY SESSION

causes Oracle to write a single record for all SQL statements of the same type issued in the same session.

BY ACCESS

causes Oracle to write one record for each audited statement.

If you specify statement options or system privileges that audit data definition language (DDL) statements, Oracle automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.

For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.

WHENEVER SUCCESSFUL

chooses auditing only for statements that succeed.
NOT chooses auditing only for statements that fail or result in errors.

If you omit the WHENEVER SUCCESSFUL clause, Oracle audits SQL statements regardless of success or failure.

Table 7-1 Statement Auditing Options for Database Objects

Statement Option	SQL Statements and Operations
`CLUSTER`	`CREATE CLUSTER` `AUDIT CLUSTER` `DROP CLUSTER` `TRUNCATE CLUSTER`
`CONTEXT`	`CREATE CONTEXT` `DROP CONTEXT`
`DATABASE LINK`	`CREATE DATABASE LINK` `DROP DATABASE LINK`
`DIMENSION`	`CREATE DIMENSION` `ALTER DIMENSION` `DROP DIMENSION`
`DIRECTORY`	`CREATE DIRECTORY` `DROP DIRECTORY`
`INDEX`	`CREATE INDEX` `ALTER INDEX` `DROP INDEX`
`NOT EXISTS`	All SQL statements that fail because a specified object does not exist.
`PROCEDURE`^a	`CREATE FUNCTION` `CREATE LIBRARY` `CREATE PACKAGE` `CREATE PACKAGE BODY` `CREATE PROCEDURE` `DROP FUNCTION` `DROP LIBRARY` `DROP PACKAGE` `DROP PROCEDURE`
`PROFILE`	`CREATE PROFILE` `ALTER PROFILE` `DROP PROFILE`
`PUBLIC DATABASE LINK`	`CREATE PUBLIC DATABASE LINK` `DROP PUBLIC DATABASE LINK`
`PUBLIC SYNONYM`	`CREATE PUBLIC SYNONYM` `DROP PUBLIC SYNONYM`
`ROLE`	`CREATE ROLE` `ALTER ROLE` `DROP ROLE` `SET ROLE`
`ROLLBACK STATEMENT`	`CREATE ROLLBACK SEGMENT` `ALTER ROLLBACK SEGMENT` `DROP ROLLBACK SEGMENT`
`SEQUENCE`	`CREATE SEQUENCE` `DROP SEQUENCE`
`SESSION`	`Logons`
`SYNONYM`	`CREATE SYNONYM` `DROP SYNONYM`
`SYSTEM AUDIT`	`AUDIT` sql_statements `NOAUDIT` sql_statements
`SYSTEM GRANT`	`GRANT` system_privileges_and_roles `REVOKE` system_privileges_and_roles
`TABLE`	`CREATE TABLE` `DROP TABLE` `TRUNCATE TABLE` `COMMENT ON TABLE` `DELETE [FROM]` table
`TABLESPACE`	`CREATE TABLESPACE` `ALTER TABLESPACE` `DROP TABLESPACE`
`TRIGGER`	`CREATE TRIGGER` `ALTER TRIGGER` with `ENABLE` and `DISABLE` clauses `DROP TRIGGER` `ALTER TABLE` with `ENABLE ALL TRIGGERS` clause and `DISABLE ALL TRIGGERS` clause
`TYPE`	`CREATE TYPE` `CREATE TYPE BODY` `ALTER TYPE` `DROP TYPE` `DROP TYPE BODY`
`USER`	`CREATE USER` `ALTER USER` `DROP USER`
`VIEW`	`CREATE VIEW` `DROP VIEW`
^aJava schema objects (sources, classes, and resources) are considered the same as procedures for purposes of auditing SQL statements.

Table 7-2 Additional Statement Auditing Options for SQL Statements

Statement Option	SQL Statements and Operations
`ALTER SEQUENCE`	`ALTER SEQUENCE`
`ALTER TABLE`	`ALTER TABLE`
`COMMENT TABLE`	`COMMENT ON TABLE` table, view, snapshot `COMMENT ON COLUMN` table.column, view.column, snapshot.column
`DELETE TABLE`	`DELETE FROM` table, view
`EXECUTE PROCEDURE`	`CALL` Execution of any procedure or function or access to any variable, library, or cursor inside a package.
`GRANT DIRECTORY`	`GRANT privilege ON directory` `REVOKE privilege ON directory`
`GRANT PROCEDURE`	`GRANT privilege ON procedure, function, package` `REVOKE privilege ON procedure, function, package`
`GRANT SEQUENCE`	`GRANT privilege ON sequence` `REVOKE privilege ON sequence`
`GRANT TABLE`	`GRANT privilege ON table, view, snapshot.` `REVOKE privilege ON table, view, snapshot`
`GRANT TYPE`	`GRANT privilege ON TYPE` `REVOKE privilege ON TYPE`
`INSERT TABLE`	`INSERT INTO table, view`
`LOCK TABLE`	`LOCK TABLE table, view`
`SELECT SEQUENCE`	`Any statement containing sequence.CURRVAL or sequence.NEXTVAL`
`SELECT TABLE`	`SELECT FROM table, view, snapshot`
`UPDATE TABLE`	`UPDATE table, view`

Examples

Role Examples

To choose auditing for every SQL statement that creates, alters, drops, or sets a role, regardless of whether the statement completes successfully, issue the following statement:

AUDIT ROLE;

To choose auditing for every statement that successfully creates, alters, drops, or sets a role, issue the following statement:

AUDIT ROLE
    WHENEVER SUCCESSFUL;

To choose auditing for every CREATE ROLE, ALTER ROLE, DROP ROLE, or SET ROLE statement that results in an Oracle error, issue the following statement:

AUDIT ROLE
    WHENEVER NOT SUCCESSFUL;

Query/Update Examples

To choose auditing for any statement that queries or updates any table, issue the following statement:

AUDIT SELECT TABLE, UPDATE TABLE;

To choose auditing for statements issued by the users SCOTT and BLAKE that query or update a table or view, issue the following statement:

AUDIT SELECT TABLE, UPDATE TABLE
    BY scott, blake;

Delete Example

To choose auditing for statements issued using the DELETE ANY TABLE system privilege, issue the following statement:

AUDIT DELETE ANY TABLE;

Directory Examples

To choose auditing for statements issued using the CREATE ANY DIRECTORY system privilege, issue the following statement:

AUDIT CREATE ANY DIRECTORY;

To choose auditing for CREATE DIRECTORY (and DROP DIRECTORY) statements that do not use the CREATE ANY DIRECTORY system privilege, issue the following statement:

AUDIT DIRECTORY;

AUDIT schema_objects

Syntax

Purpose

To track operations on a specific schema object. To choose particular SQL statements for auditing, see "AUDIT sql_statements".

Auditing keeps track of operations performed by database users. Auditing options established by the AUDIT schema_objects statement apply to current sessions as well as to subsequent sessions. For information on discontinuing auditing operations, see "NOAUDIT schema_objects".

Prerequisites

The object you choose for auditing must be in your own schema or you must have AUDIT ANY system privilege. In addition, if the object you choose for auditing is a directory object, even if you created it, you must have AUDIT ANY system privilege.

Keywords and Parameters

object_opt

specifies a particular operation for auditing. Table 7-3 shows each object option and the types of objects to which it applies. The name of each object option specifies a SQL statement to be audited. For example, if you choose to audit a table with the ALTER option, Oracle audits all ALTER TABLE statements issued against the table. If you choose to audit a sequence with the SELECT option, Oracle audits all statements that use any of the sequence's values.

ALL

is a shortcut equivalent to specifying all object options applicable for the type of object. You can use this shortcut rather than explicitly specifying all options for an object.

schema

is the schema containing the object chosen for auditing. If you omit schema, Oracle assumes the object is in your own schema.

object

identifies the object chosen for auditing. The object must be a table, view, sequence, stored procedure, function, package, snapshot, or library.

You can also specify a synonym for a table, view, sequence, procedure, stored function, package, or snapshot.

ON DEFAULT

establishes the specified object options as default object options for subsequently created objects. Once you have established these default auditing options, any subsequently created object is automatically audited with those options. The default auditing options for a view are always the union of the auditing options for the view's base tables.
If you change the default auditing options, the auditing options for previously created objects remain the same. You can change the auditing options for an existing object only by specifying the object in the ON clause of the AUDIT statement.

ON DIRECTORY directory_name

identifies the name of the directory chosen for auditing.

BY SESSION

causes Oracle to write a single record for all operations of the same type on the same object issued in the same session. This is the default.

BY ACCESS

causes Oracle to write one record for each audited operation.

WHENEVER SUCCESSFUL

chooses auditing only for SQL statements that complete successfully.

NOT chooses auditing only for statements that fail, or result in errors.

If you omit the WHENEVER SUCCESSFUL clause entirely, Oracle audits all SQL statements, regardless of success or failure.

Table 7-3 Object Auditing Options

Object Option	Table	View	Sequence	Procedure Function Package^a	Materialized View / Snapshot	Directory	Library	Object Type	Context
ALTER	X		X		X			X
AUDIT	X	X	X	X	X	X		X	X
COMMENT	X	X			X
DELETE	X	X			X
EXECUTE				X			X
GRANT	X	X	X	X	X	X	X	X	X
INDEX	X				X
INSERT	X	X			X
LOCK	X	X			X
READ						X
RENAME	X	X		X	X
SELECT	X	X	X		X
UPDATE	X	X			X
^a Java schema objects (sources, classes, and resources) are considered the same as procedures, functions, and packages for purposes of auditing options.

Examples

Query Examples

To choose auditing for every SQL statement that queries the EMP table in the schema SCOTT, issue the following statement:

AUDIT SELECT
    ON scott.emp;

To choose auditing for every statement that successfully queries the EMP table in the schema SCOTT, issue the following statement:

AUDIT SELECT 
    ON scott.emp
    WHENEVER SUCCESSFUL;

To choose auditing for every statement that queries the EMP table in the schema SCOTT and results in an Oracle error, issue the following statement:

AUDIT SELECT 
    ON scott.emp
    WHENEVER NOT SUCCESSFUL;

Insert/Update Example

To choose auditing for every statement that inserts or updates a row in the DEPT table in the schema BLAKE, issue the following statement:

AUDIT INSERT, UPDATE
    ON blake.dept;

ALL Example

To choose auditing for every statement that performs any operation on the ORDER sequence in the schema ADAMS, issue the following statement:

AUDIT ALL
    ON adams.order;

The above statement uses the ALL shortcut to choose auditing for the following statements that operate on the sequence:

ALTER SEQUENCE
AUDIT
GRANT
any statement that accesses the sequence's values using the pseudocolumns CURRVAL or NEXTVAL

READ Example

To choose auditing for every statement that reads files from the BFILE_DIR1 directory, issue the following statement:

AUDIT READ ON DIRECTORY bfile_dir1;

DEFAULT Example

The following statement specifies default auditing options for objects created in the future:

AUDIT ALTER, GRANT, INSERT, UPDATE, DELETE
    ON DEFAULT;

Any objects created later are automatically audited with the specified options that apply to them, provided that auditing has been enabled:

If you create a table, Oracle automatically audits any ALTER, GRANT, INSERT, UPDATE, or DELETE statements issued against the table.
If you create a view, Oracle automatically audits any GRANT, INSERT, UPDATE, or DELETE statements issued against the view.
If you create a sequence, Oracle automatically audits any ALTER or GRANT statements issued against the sequence.
If you create a procedure, package, or function, Oracle automatically audits any ALTER or GRANT statements issued against it.

Click the "Next" button to see the next statement in this chapter.

tablespace	is the name of the tablespace to be altered.
LOGGING \| NOLOGGING	specifies the default logging attribute of all tables, indexes, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.
	When an existing tablespace logging attribute is changed by an ALTER TABLESPACE statement, all tables, indexes, and partitions created after the statement will have the new default logging attribute (which you can still subsequently override). The logging attributes of existing objects are not changed.
	Only the following operations support NOLOGGING mode: DML: direct-load INSERT (serial or parallel); Direct Loader (SQL*Loader)
	DDL: CREATE TABLE... AS SELECT, CREATE INDEX, ALTER INDEX... REBUILD, ALTER INDEX... REBUILD PARTITION, ALTER INDEX... SPLIT PARTITION, ALTER TABLE... SPLIT PARTITION, ALTER TABLE... MOVE PARTITION.
	In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose the object, it is important to take a backup after the NOLOGGING operation.
*datafile/tempfile_clauses*	adds or modifies a datafile or tempfile.
	ADD DATAFILE \| TEMPFILE	Adds to the tablespace a datafile or tempfile specified by filespec (see "filespec"). You can add a datafile or tempfile to a locally managed tablespace that is online or to a dictionary managed tablespace that is online or offline. Be sure the file is not in use by another database.
	Note: As the syntax shows, you cannot combine an ADD clause with any other clauses in the same ALTER TABLESPACE statement. In addition, for a locally managed temporary tablespace, you cannot specify any of the other clauses for this tablespace at any time.
	RENAME DATAFILE	renames one or more of the tablespace's datafiles. Take the tablespace offline before renaming the datafile. Each 'filename' must fully specify a datafile using the conventions for filenames on your operating system.
		This clause merely associates the tablespace with the new file rather than the old one. This clause does not actually change the name of the operating system file. You must change the name of the file through your operating system.
*autoextend_clause*	enables or disables the autoextending of the size of the datafile in the tablespace.
	OFF	disables autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND statements.
	ON	enables autoextend.
	NEXT	specifies the size in bytes of the next increment of disk space to be allocated automatically to the datafile when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is one data block.
	maxsize_clause	specifies maximum disk space allowed for automatic extension of the datafile.
		UNLIMITED sets no limit on allocating disk space to the datafile.
DEFAULT *storage_clause*	specifies the new default storage parameters for objects subsequently created in the tablespace. For a dictionary-managed temporary table, Oracle considers only the NEXT parameter of the storage_clause. See the "storage_clause". Restriction: You cannot specify this clause for a locally managed tablespace.
MINIMUM EXTENT integer	controls free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, integer. This clause is not relevant for a dictionary-managed temporary tablespace. For more information about using MINIMUM EXTENT to control space fragmentation, see Oracle8i Administrator's Guide. Restriction: You cannot specify this clause for a locally managed tablespace.
ONLINE	brings the tablespace online.
OFFLINE	takes the tablespace offline and prevents further access to its segments.
	NORMAL	flushes all blocks in all datafiles in the tablespace out of the SGA. You need not perform media recovery on this tablespace before bringing it back online. This is the default.
	TEMPORARY	performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Any offline files may require media recovery before you bring the tablespace back online.
	IMMEDIATE	does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.
	FOR RECOVER	takes the production database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information see Oracle8i Backup and Recovery Guide.
Suggestion: Before taking a tablespace offline for a long time, you may want to alter the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace. When the tablespace is offline, these users cannot allocate space for objects or sort areas in the tablespace. For more information, see "ALTER USER".
BEGIN BACKUP	signifies that an open backup is to be performed on the datafiles that make up this tablespace. This clause does not prevent users from accessing the tablespace. You must use this clause before beginning an open backup. You cannot use this clause on a read-only tablespace.
	Note: While the backup is in progress, you cannot take the tablespace offline normally, shut down the instance, or begin another backup of the tablespace.
END BACKUP	signifies that an open backup of the tablespace is complete. Use this clause as soon as possible after completing an open backup. You cannot use this clause on a read-only tablespace.
	If you forget to indicate the end of an online tablespace backup, and an instance failure or SHUTDOWN ABORT occurs, Oracle assumes that media recovery (possibly requiring archived redo log) is necessary at the next instance start up. To restart the database without media recovery, see Oracle8i Administrator's Guide.
READ ONLY	signifies that no further write operations are allowed on the tablespace. (This clause waits for all existing transactions either to commit or roll back before taking effect.) The tablespace becomes read only. Once a tablespace is read only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL statement ALTER DATABASE ... RENAME. See "ALTER DATABASE". For more information on read-only tablespaces, see Oracle8i Concepts.
READ WRITE	signifies that write operations are allowed on a previously read-only tablespace.
PERMANENT	specifies that the tablespace is to be converted from a temporary to a permanent one. A permanent tablespace is one in which permanent database objects can be stored. This is the default when a tablespace is created.
TEMPORARY	specifies that the tablespace is to be converted from a permanent to a temporary one. A temporary tablespace is one in which no permanent database objects can be stored. Objects in a temporary tablespace persist only for the duration of the session.
COALESCE	for each datafile in the tablespace, coalesces all contiguous free extents into larger contiguous extents. Restriction: COALESCE cannot be specified with any other statement clause.

schema	is the schema containing the trigger. If you omit schema, Oracle assumes the trigger is in your own schema.
trigger	is the name of the trigger to be altered.
ENABLE	enables the trigger. You can also use the ENABLE ALL TRIGGERS clause of ALTER TABLE to enable all triggers associated with a table. See "ALTER TABLE".
DISABLE	disables the trigger. You can also use the DISABLE ALL TRIGGERS clause of ALTER TABLE to disable all triggers associated with a table. See "ALTER TABLE".
COMPILE	explicitly compiles the trigger, whether it is valid or invalid. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.
	Oracle first recompiles objects upon which the trigger depends, if any of these objects are invalid. If Oracle recompiles the trigger successfully, the trigger becomes valid. If recompiling the trigger results in compilation errors, then Oracle returns an error and the trigger remains invalid. You can see the associated compiler error messages with the SQLPlus command SHOW ERRORS. For information on debugging procedures, see Oracle8i Application Developer's Guide - Fundamentals.* For information on how Oracle maintains dependencies among schema objects, including remote objects, see Oracle8i Concepts.
	DEBUG	instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. This clause can be used for normal triggers and for instead-of triggers.

schema	is the schema that contains the type. If you omit schema, Oracle assumes the type is in your current schema.
type	is the name of an object type, a nested table type, or a rowid type.
COMPILE	compiles the object type specification and body. This is the default if neither SPECIFICATION nor BODY is specified. If recompiling the type results in compilation errors, then Oracle returns an error and the type remains invalid. You can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.
	SPECIFICATION	compiles only the object type specification.
	BODY	compiles only the object type body.
DEBUG	instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.
REPLACE AS OBJECT	adds new member subprogram specifications. This clause is valid only for object types, not for nested table or varray types.
attribute	is an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object.
MEMBER \| STATIC	specifies a function or procedure subprogram associated with the object type which is referenced as an attribute. For a description of the difference between member and static methods, and for examples, see "CREATE TYPE". For information about overloading subprogram names within a package, see the PL/SQL User's Guide and Reference. You must specify a corresponding method body in the object type body for each procedure or function specification. See "CREATE TYPE BODY".
	procedure_spec	is the specification of a procedure subprogram.
	function_spec	is the specification of a function subprogram.
*pragma_clause*	is a complier directive that denies member functions read/write access to database tables, packaged variables, or both, and thereby helps to avoid side effects. For more information, see Oracle8i Application Developer's Guide - Fundamentals.
	method	is the name of the MEMBER function or procedure to which the pragma is being applied.
	DEFAULT	specifies that the pragma should be applied to all methods in the type for which a pragma has not been explicitly specified.
	WNDS	specifies the constraint writes no database state (does not modify database tables).
	WNPS	specifies the constraint writes no package state (does not modify packaged variables).
	RNDS	specifies the constraint reads no database state (does not query database tables).
	RNPS	specifies the constraint reads no package state (does not reference package variables).
	TRUST	specifies that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true.
MAP\|ORDER MEMBER function_spec
	MAP	specifies a member function (MAP method) that returns the relative position of a given instance in the ordering of all instances of the object. A map method is called implicitly and induces an ordering of object instances by mapping them to values of a predefined scalar type. Oracle uses the ordering for comparison operators and ORDER BY clauses.
		If the argument to the map method is null, the map method returns null and the method is not invoked.
		An object specification can contain only one map method, which must be a function. The result type must be a predefined SQL scalar type, and the map function can have no arguments other than the implicit SELF argument.
		Note: If type_name will be referenced in queries involving sorts (through ORDER BY, GROUP BY, DISTINCT, or UNION clauses) or joins, and you want those queries to be parallelized, you must specify a MAP member function.
	ORDER	specifies a member function (ORDER method) that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative, zero, or positive integer. The negative, zero, or positive indicates that the implicit SELF argument is less than, equal to, or greater than the explicit argument.
		If either argument to the order method is null, the order method returns null and the method is not invoked. When instances of the same object type definition are compared in an ORDER BY clause, the order method function is invoked.
		An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.
	You can declare either a MAP method or an ORDER method, but not both. If you declare either method, you can compare object instances in SQL.
	If you do not declare either method, you can compare object instances only for equality or inequality. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal. No comparison method needs to be specified to determine the equality of two object types. For more information about object value comparisons, see "Object Values".

IDENTIFIED GLOBALLY AS	indicates that a user must be authenticated by way of an LDAP V3 compliant directory service such as Oracle Internet Directory. (See also "CREATE USER".) You can change a user's access verification method to IDENTIFIED GLOBALLY AS 'external_name' only if all external roles granted directly to the user are revoked. You can change a user created as IDENTIFIED GLOBALLY AS 'external_name' to IDENTIFIED BY password or IDENTIFIED EXTERNALLY.
DEFAULT ROLE	can contain only roles that have been granted directly to the user with a GRANT statement. You cannot use the DEFAULT ROLE clause to enable: roles not granted to the user roles granted through other roles roles managed by an external service (such as the operating system), or by the Oracle Internet Directory Oracle enables default roles at logon without requiring the user to specify their passwords. For more information on roles, see "CREATE ROLE".
*proxy_clause*	controls the ability of a proxy (an application or application server) to connect as the specified user and to activate all, some, or none of the user's roles. For more information on proxies and their use of the database, see Oracle8i Concepts.
	GRANT	allows the connection.
	REVOKE	prohibits the connection.
	proxy	identifies the proxy connecting to Oracle.
	WITH ROLE	specifies the roles that the application is permitted to activate after it connects as the user. If you do not include this clause, Oracle activates all roles granted to the specified user automatically.
	role_name	permits the proxy to connect as the specified user and to activate only the roles that are specified by role_name.
	ALL EXCEPT role_name	permits the proxy to connect as the specified user and to activate all roles associated with that user except those specified by role_name.
	NONE	permits the proxy to connect as the specified user, but prohibits the proxy from activating any of that user's roles after connecting.

schema	is the schema containing the view. If you omit schema, Oracle assumes the view is in your own schema.
view	is the name of the view to be recompiled.
COMPILE	causes Oracle to recompile the view. The COMPILE keyword is required.

schema	is the schema containing the index, table, or cluster. If you omit schema, Oracle assumes the index, table, or cluster is in your own schema.
INDEX index	identifies an index to be analyzed (if no for_clause is used).
	Oracle collects the following statistics for an index (statistics marked with an asterisk are always computed exactly): Depth of the index from its root block to its leaf blocks* Number of leaf blocks Number of distinct index values Average number of leaf blocks per index value Average number of data blocks per index value (for an index on a table) Clustering factor (how well ordered the rows are about the indexed values) Index statistics appear in the data dictionary views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES.
	For a domain index, this statement invokes the user-defined statistics collection function specified in the statistics type associated with the index (see "ASSOCIATE STATISTICS"). If no statistics type is associated with the domain index, the statistics type associated with its indextype is used. If no statistics type exists for either the index or its indextype, no user-defined statistics are collected. User-defined index statistics appear in the data dictionary views USER_USTATS, ALL_USTATS, and DBA_USTATS. Restriction: You cannot analyze a domain index that is marked LOADING or FAILED. For more information on domain indexes, see "CREATE INDEX".
TABLE table	identifies a table to be analyzed. When you collect statistics for a table, Oracle also automatically collects the statistics for each of the table's indexes and domain indexes, provided that no for_clauses are used. When you analyze a table, Oracle collects statistics about expressions occurring in any function-based indexes as well. Therefore, be sure to create function-based indexes on the table before analyzing the table. For more information about function-based indexes, see "CREATE INDEX".
	When analyzing a table, Oracle skips all domain indexes marked LOADING or FAILED. Table statistics, including the status of domain indexes, appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES.
	Oracle collects the following statistics for a table (statistics marked with an asterisk are always computed exactly): Number of rows Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) * Number of data blocks allocated to the table that have never been used * Average available free space in each data block in bytes Number of chained rows Average row length, including the row's overhead, in bytes
	Restrictions: You cannot use ANALYZE to collect statistics on data dictionary tables. You cannot use ANALYZE to collect default statistics on a temporary table. However, if you have created an association between one or more columns of a temporary table and a user-defined statistics type, you can use ANALYZE to collect the user-defined statistics on the temporary table. (The association must already exist.) For more information, see "ASSOCIATE STATISTICS". You cannot compute or estimate statistics for the following column types: REFs, varrays, nested tables, LOBs (LOBs are not analyzed, they are skipped), LONGs, or object types. However, if a statistics type is associated with such a column, user-defined statistics are collected.
PARTITION \| SUBPARTITION	specifies that statistics will be gathered for partition or subpartition. You cannot use this clause when analyzing clusters. If you specify PARTITION and table is composite-partitioned, Oracle analyzes all the subpartitions within the specified partition.
CLUSTER cluster	identifies a cluster to be analyzed. When you collect statistics for a cluster, Oracle also automatically collects the statistics for all the cluster's tables and all their indexes, including the cluster index.
	For an indexed cluster, Oracle collects the average number of data blocks taken up by a single cluster key value and all of its rows. For a hash cluster, Oracle collects the average number of data blocks taken up by a single hash key value and all of its rows. These statistics appear in the data dictionary views USER_CLUSTERS and DBA_CLUSTERS.
COMPUTE STATISTICS	computes exact statistics about the analyzed object and stores them in the data dictionary. When you analyze a table, both table and column statistics are collected.
ESTIMATE STATISTICS	estimates statistics about the analyzed object and stores them in the data dictionary.
Both computed and estimated statistics are used by the Oracle optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements. For information on how these statistics are used, see Oracle8i Tuning.
	SAMPLE integer	specifies the amount of data from the analyzed object Oracle samples to estimate statistics. If you omit this parameter, Oracle samples 1064 rows. The default sample value is adequate for tables up to a few thousand rows. If your tables are larger, specify a higher value for SAMPLE. If you specify more than half of the data, Oracle reads all the data and computes the statistics.
	ROWS	causes Oracle to sample integer rows of the table or cluster or integer entries from the index. The integer must be at least 1.
	PERCENT	causes Oracle to sample integer percent of the rows from the table or cluster or integer percent of the index entries. The integer can range from 1 to 99.
*for_clause*	specifies whether an entire table or index, or just particular columns, will be analyzed. The following clauses apply only to the ANALYZE TABLE version of this statement:
	FOR TABLE restricts the statistics collected to only table statistics rather than table and column statistics. FOR COLUMNS restricts the statistics collected to only column statistics for the specified columns and scalar object attributes, rather than for all columns and attributes. FOR ALL COLUMNS collects column statistics for all columns and scalar object attributes. FOR ALL INDEXED COLUMNS collects column statistics for all indexed columns in the table.
	Column statistics can be based on the entire column or can use a histogram by specifying SIZE (see below). For more information on histograms, see Oracle8i Tuning. See also "Histogram Examples". Oracle collects the following column statistics: - Number of distinct values in the column as a whole - Maximum and minimum values in each band
	Column statistics appear in the data dictionary views USER_TAB_COLUMNS, ALL_TAB_COLUMNS, and DBA_TAB_COLUMNS. Histograms appear in the data dictionary views USER_HISTOGRAMS, DBA_HISTOGRAMS, and ALL_HISTOGRAMS.
	Note: The MAXVALUE and MINVALUE columns of USER_, DBA_, and ALL_TAB_COLUMNS have a length of 32 bytes. If you analyze columns with a length >32 bytes, and if the columns are padded with leading blanks, Oracle may take into account only the leading blanks and return unexpected statistics.
	If a user-defined statistics type has been associated with any columns, the for_clause collects user-defined statistics using that statistics type. If no statistics type is associated with a column, Oracle checks to see if any statistics type has been associated with the type of the column, and uses that statistics type. If no statistics type has been associated with either the column or its user-defined type, no user-defined statistics are collected. User-defined column statistics appear in the data dictionary views USER_USTATS, ALL_USTATS, and DBA_USTATS.
	Note: If you want to collect statistics on both the table as a whole and on one or more columns, be sure to generate the statistics for the table first, and then for the columns. Otherwise, the table-only ANALYZE will overwrite the histograms generated by the column ANALYZE. For example, issue the following statements: `ANALYZE TABLE emp ESTIMATE STATISTICS;` `ANALYZE TABLE emp ESTIMATE STATISTICS FOR ALL COLUMNS;`
	attribute specifies the qualified column name of an item in an object.
	FOR ALL INDEXES specifies that all indexes associated with the table will be analyzed. FOR ALL LOCAL INDEXES specifies that all local index partitions are analyzed. You must specify the keyword LOCAL if the PARTITION clause and INDEX are specified. SIZE specifies the maximum number of partitions in the histogram. The default value is 75, minimum value is 1, and maximum value is 254.
DELETE STATISTICS	deletes any statistics about the analyzed object that are currently stored in the data dictionary. Use this statement when you no longer want Oracle to use the statistics. When you use this clause on a table, Oracle also automatically removes statistics for all the table's indexes. When you use this clause on a cluster, Oracle also automatically removes statistics for all the cluster's tables and all their indexes, including the cluster index.
	If user-defined column or index statistics were collected for an object, Oracle also removes the user-defined statistics by invoking the statistics deletion function specified in the statistics type that was used to collect the statistics.
VALIDATE REF UPDATE	validates the REFs in the specified table, checks the rowid portion in each REF, compares it with the true rowid, and corrects, if necessary. You can use this clause only when analyzing a table.
	SET DANGLING TO NULL	sets to NULL any REFs (whether or not scoped) in the specified table that are found to point to an invalid or nonexistent object.
		Note: If the owner of the table does not have SELECT object privilege on the referenced objects, Oracle will consider them invalid and set them to NULL. Subsequently these REFs will not be available in a query, even if it is issued by user with appropriate privileges on the objects.
VALIDATE STRUCTURE	validates the structure of the analyzed object. The statistics collected by this clause are not used by the Oracle optimizer, as are statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS clauses. For a table, Oracle verifies the integrity of each of the table's data blocks and rows. For a cluster, Oracle automatically validates the structure of the cluster's tables. For a partitioned table, Oracle also verifies that the row belongs to the correct partition. If the row does not collate correctly, the rowid is inserted into the INVALID_ROWS table. For a temporary table, Oracle validates the structure of the table and its indexes during the current session.
	For an index, Oracle verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause. Oracle stores statistics about the index in the data dictionary views INDEX_STATS and INDEX_HISTOGRAM, which are described in Oracle8i Reference.
	Validating the structure of an object prevents SELECT, INSERT, UPDATE, and DELETE statements from concurrently accessing the object. Therefore, do not use this clause on the tables, clusters, and indexes of your production applications during periods of high database activity. If Oracle encounters corruption in the structure of the object, an error message is returned to you. In this case, drop and re-create the object.
	INTO	specifies a table into which Oracle lists the rowids of the partitions whose rows do not collate correctly. If you omit schema, Oracle assumes the list is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named INVALID_ROWS. The SQL script used to create this table is UTLVALID.SQL.
	CASCADE	validates the structure of the indexes associated with the table or cluster. If you use this clause when validating a table, Oracle also validates the table's indexes. If you use this clause when validating a cluster, Oracle also validates all the clustered tables' indexes, including the cluster index.
		If you use this clause to validate an enabled (but previously disabled) function-based index, validation errors may result. In this case, you must rebuild the index.
LIST CHAINED ROWS	identifies migrated and chained rows of the analyzed table or cluster. You cannot use this clause when analyzing an index.
	INTO	specifies a table into which Oracle lists the migrated and chained rows. If you omit schema, Oracle assumes the list table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named CHAINED_ROWS. The script used to create this table is UTLCHAIN1.SQL. The list table must be on your local database.
		Note: You can use the UTLCHAIN1.SQL script with index-organized tables. You could not use earlier versions of the script for this purpose. See Oracle8i Migration for compatibility information.
	To analyze index-organized tables, you must create a separate chained-rows table for each index-organized table to accommodate the primary-key storage of index-organized tables. Use the SQL scripts DBMSIOTC.SQL and PRVTIOTC.PLB to define the BUILD_CHAIN_ROWS_TABLE procedure, and then execute this procedure to create an IOT_CHAINED_ROWS table for an index-organized table. For information on the SQL scripts, see the DBMS_IOT package in Oracle8i Supplied Packages Reference. For information on eliminating migrated and chained rows, see Oracle8i Tuning.

*column_association*	specifies a list of one or more table columns. If you do not specify schema, Oracle assumes the table is in your own schema.
*function_association*	specifies a list of one or more standalone functions, packages, user-defined datatypes, domain indexes, or indextypes. If you do not specify schema, Oracle assumes the object is in your own schema. FUNCTIONS refers only to standalone functions, not to method types or to built-in functions. TYPES refers only to user-defined types, not to internal SQL datatypes.
	Restriction: You cannot specify an object for which you have already defined an association. You must first disassociate the statistics from this object. See "DISASSOCIATE STATISTICS".
*using_clause*	specifies the statistics type being associated with columns, functions, packages, types, domain indexes, or indextypes. The statistics_type must already have been created.
*default_cost_clause*	specifies default costs for standalone functions, packages, types, domain indexes, or indextypes. If you specify this clause, you must include one number each for CPU cost, I/O cost, and network cost, in that order. Each cost is for a single execution of the function or method or for a single domain index access. Accepted values are integers of zero or greater.
*default_selectivity_clause*	specifies as a percent the default selectivity for predicates with standalone functions, types, packages, or user-defined operators. The default_selectivity must be a whole number between 0 and 100. Values outside this range are ignored. Restriction: You cannot specify DEFAULT SELECTIVITY for domain indexes or indextypes.

statement_opt	chooses specific SQL statements for auditing. For a list of these statement options and the SQL statements they audit, see Table 7-1 and Table 7-2. For each audited operation, Oracle produces an audit record containing this information: user performing the operation type of operation object involved in the operation date and time of the operation Oracle writes audit records to the audit trail, which is a database table containing audit records. You can review database activity by examining the audit trail through data dictionary views. For information on these views, see the Oracle8i Reference.
system_priv	chooses SQL statements that are authorized by the specified system privilege for auditing. For a list of all system privileges and the SQL statements that they authorize, see Table 7-5. Oracle provides shortcuts for specifying groups of system privileges and statement options at once. However, Oracle encourages you to choose individual system privileges and statement options for auditing, because these shortcuts may not be supported in future versions of Oracle. The shortcuts are:
	CONNECT	is equivalent to specifying the CREATE SESSION system privilege
	RESOURCE	is equivalent to specifying the following system privileges: ALTER SESSION CREATE CLUSTER CREATE DATABASE LINK CREATE PROCEDURE CREATE ROLLBACK SEGMENT CREATE SEQUENCE CREATE SYNONYM CREATE TABLE CREATE TABLESPACE CREATE VIEW
	DBA	is equivalent to the SYSTEM GRANT statement option and the following system privileges:
		AUDIT SYSTEM CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYMN CREATE ROLE CREATE USER
	ALL	is equivalent to specifying all statements options shown in Table 7-1 but not the additional statement options shown in Table 7-2.
	ALL PRIVILEGES	is equivalent to specifying all system privileges.
BY user	chooses only SQL statements issued by specified users for auditing. If you omit this clause, Oracle audits all users' statements.
BY proxy	chooses for auditing only SQL statements issued by the specified proxy. For more information on proxies and their use of the database, see Oracle8i Concepts.
	ON BEHALF OF	specifies the user or users on whose behalf the proxy executes the specified statement. user specifies auditing of statements executed on behalf of a particular user. ANY specifies auditing of statements executed on behalf of any user.
BY SESSION	causes Oracle to write a single record for all SQL statements of the same type issued in the same session.
BY ACCESS	causes Oracle to write one record for each audited statement.
	If you specify statement options or system privileges that audit data definition language (DDL) statements, Oracle automatically audits by access regardless of whether you specify the BY SESSION clause or BY ACCESS clause.
	For statement options and system privileges that audit SQL statements other than DDL, you can specify either BY SESSION or BY ACCESS. BY SESSION is the default.
WHENEVER SUCCESSFUL	chooses auditing only for statements that succeed. NOT chooses auditing only for statements that fail or result in errors.
	If you omit the WHENEVER SUCCESSFUL clause, Oracle audits SQL statements regardless of success or failure.

object_opt	specifies a particular operation for auditing. Table 7-3 shows each object option and the types of objects to which it applies. The name of each object option specifies a SQL statement to be audited. For example, if you choose to audit a table with the ALTER option, Oracle audits all ALTER TABLE statements issued against the table. If you choose to audit a sequence with the SELECT option, Oracle audits all statements that use any of the sequence's values.
ALL	is a shortcut equivalent to specifying all object options applicable for the type of object. You can use this shortcut rather than explicitly specifying all options for an object.
schema	is the schema containing the object chosen for auditing. If you omit schema, Oracle assumes the object is in your own schema.
object	identifies the object chosen for auditing. The object must be a table, view, sequence, stored procedure, function, package, snapshot, or library.
	You can also specify a synonym for a table, view, sequence, procedure, stored function, package, or snapshot.
ON DEFAULT	establishes the specified object options as default object options for subsequently created objects. Once you have established these default auditing options, any subsequently created object is automatically audited with those options. The default auditing options for a view are always the union of the auditing options for the view's base tables. If you change the default auditing options, the auditing options for previously created objects remain the same. You can change the auditing options for an existing object only by specifying the object in the ON clause of the AUDIT statement.
ON DIRECTORY directory_name	identifies the name of the directory chosen for auditing.
BY SESSION	causes Oracle to write a single record for all operations of the same type on the same object issued in the same session. This is the default.
BY ACCESS	causes Oracle to write one record for each audited operation.
WHENEVER SUCCESSFUL	chooses auditing only for SQL statements that complete successfully.
	NOT chooses auditing only for statements that fail, or result in errors.
	If you omit the WHENEVER SUCCESSFUL clause entirely, Oracle audits all SQL statements, regardless of success or failure.

7SQL Statements (continued)

ALTER TABLE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

Nested Table Example

PARALLEL Example

ENABLE VALIDATE Example

ENABLE NOVALIDATE Example

DISABLE Example

CHECK Example

Triggers Example

DEALLOCATE UNUSED Example

DROP COLUMN Example

Index-Organized Table Examples

ADD PARTITION Example

SPLIT PARTITION Example

User-Defined Object Identifier Example

Add Column Example

Modify Column Examples

ALLOCATE EXTENT Example

DEFAULT Examples

Drop Constraint Examples

LOB Examples

Nested Table Examples

REF Examples

Add Partition Example

Drop Partition Example

Exchange Partition Example

Modify Partition Examples

Move Partition Example

Rename Partition Examples

Split Partition Example

Truncate Partition Example

Additional Examples

ALTER TABLESPACE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

Backup Examples

Moving and Renaming Example

Adding a Datafile Example

Changing Extent Allocation Example

ALTER TRIGGER

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

ALTER TYPE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

Adding a Member Function

Recompiling a Type

Recompiling a Type Body

Recompiling a Type Specification

ALTER USER

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

General Examples

Authentication Examples

Proxy Examples

ALTER VIEW

Syntax

Purpose

Prerequisites

Keywords and Parameters

Example

ANALYZE

Syntax

7
SQL Statements (continued)