ALTER TABLE

Purpose

To alter the definition of a table in one of the following ways:

• add a column
• add an integrity constraint
• redefine a column (datatype, size, default value)
• modify storage characteristics or other parameters
• modify the real storage attributes of a nonpartitioned table or the default attributes of a partitioned table
• enable, disable, or drop an integrity constraint or trigger
• explicitly allocate an extent
• explicitly deallocate the unused space of a table
• allow or disallow writing to a table
• modify the degree of parallelism for a table
• modify the logging attributes of a non-partitioned table, partitioned table or table partition(s)
• modify the CACHE/NOCACHE attributes
• add, modify, split, move, drop, or truncate table partitions
• rename a table or a table partition
• add or modify index-organized table characteristics
• add or modify LOB columns
• add or modify object type, nested table type, or VARRAY type column
• add integrity constraints to object type columns

Note:   Descriptions of commands and clauses preceded by  are available only if the Oracle objects option is installed on your database server.

   

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.

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.

Syntax 

add_column_options::=

column_constraint, table_constraint: See the "CONSTRAINT clause"

column_ref_clause::=

table_ref_clause::=

modify_column_options::=

physical_attributes_clause::=

storage_clause: See "STORAGE clause".

LOB_storage_clause::=

LOB_parameters::=

LOB_index_clause::=

LOB_index_parameters::=

modify_LOB_storage_clause::=

modify_LOB_index_clause::=

nested_table_storage_clause::=

drop_clause: See the "DROP clause".

allocate_extent_clause::=

deallocate_unused_clause: See the "DEALLOCATE UNUSED clause".

index_organized_table_clauses::=

partitioning_clauses::=


 

rename_partition_clause::=


 

parallel_clause: See the "PARALLEL clause".

Keywords and Parameters

Adding Columns

If you use the ADD clause to add a new column to the table, then the initial value of each row for the new column is null. You can add a column with a NOT NULL constraint only to a table that contains no rows.

If you create a view with a query that uses the asterisk (*) in the select list to select all columns from the base table and you subsequently add columns to the base table, Oracle will not automatically add the new column to the view. To add the new column to the view, you can re-create the view using the CREATE VIEW command with the OR REPLACE option.

Operations performed by the ALTER TABLE command can cause Oracle to invalidate procedures and stored functions that access the table. For information on how and when Oracle invalidates such objects, see Oracle8 Concepts.

Modifying Column Definitions

You can use the MODIFY clause to change any of the following parts of a column definition: datatype, size, default value, or NOT NULL column constraint.

The MODIFY clause need only specify the column name and the modified part of the definition, rather than the entire column definition.

Datatypes and Sizes

You can change a CHAR column to VARCHAR2 (or VARCHAR) and a VARCHAR2 (or VARCHAR) to CHAR only if the column contains nulls in all rows or if you do not attempt to change the column size. You can change any column's datatype or decrease any column's size if all rows for the column contain nulls. However, you can always increase the size of a character or raw column or the precision of a numeric column.

You cannot change a column's datatype to a LOB or REF datatype.

Default Values

A change to a column's default value only affects rows subsequently inserted into the table. Such a change does not change default values previously inserted.

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 example:

ALTER TABLE accounts
   MODIFY (bal DEFAULT NULL);

This statement has no effect on any existing values in existing rows.

Integrity Constraints

The only type of integrity constraint that you can add to an existing column using the MODIFY clause with the column constraint syntax is a NOT NULL constraint. However, you can define other types of integrity constraints (UNIQUE, PRIMARY KEY, referential integrity, and CHECK constraints) on existing columns using the ADD clause and the table constraint syntax.

You can define a NOT NULL constraint on an existing column only if the column contains no nulls.

Example I

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);

Example II

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.

Example III

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;

Example IV

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 command omits the DATAFILE parameter, Oracle allocates the extent in one of the datafiles belonging to the tablespace containing the table.

Example V

This example 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

Index-Organized Tables

Index-organized tables are special kinds of tables that keep data sorted on the primary key and are therefore best suited for primary-key-based access and manipulation.

You cannot ADD columns to an index-organized table, but you can alter the definition of an index-organized table.

Example I

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

ALTER TABLE docindex INITRANS 4;

Example II

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

ALTER TABLE docindex ADD OVERFLOW;

Example III

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

ALTER TABLE docindex OVERFLOW INITRANS 4;

LOB Columns

You can add a LOB column to a table, or modify the LOB data segment or index storage characteristics.

Example I

The following statement adds CLOB column RESUME to the EMPLOYEE table:

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

Example II

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

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

Nested Table Columns

You can add a nested table type column to a table. Specify a nested table storage clause for each column added.

Example I

The following example 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; only use the storage table to modify the nested table column storage characteristics.

Example II

The following example creates table VETSERVICE with nested table column CLIENT and storage table CLIENT_TAB. Nested table VETSERVICE is modified to specify constraints and modify a column length by altering nested storage table CLIENT_TAB:

CREATE TABLE vetservice (vet_name VARCHAR2(30),
                         client   pet_table)
  NESTED TABLE client STORE AS client_tab;
ALTER TABLE client_tab ADD UNIQUE (ssn);
ALTER TABLE client_tab MODIFY (pet_name VARCHAR2(35));

Example IV

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

ALTER TABLE nested_skill_table ADD UNIQUE (a);

For more information about nested table storage see the "CREATE TABLE". For more information about nested tables, see Oracle8 Application Developer's Guide.

Example V

The following example 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); 

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

REFs

A REF value is a reference to a row in an object table. A table can have top-level REF columns or it can have REF attributes embedded within an object column. In general, if a table has a REF column, each REF value in the column could reference a row in a different object table. A SCOPE clause restricts the scope of references to a single table.

Use the ALTER TABLE command to add new REF columns or to add REF clauses to existing REF columns. You can modify any table, including named inner nested tables (storage tables). If a REF column is created WITH ROWID or with a scope table, you cannot modify the column to drop these options. However, if a table is created without any REF clauses, you can add them later with an ALTER TABLE statement.

Note: You can add a scope clause to existing REF columns of a table only if the table is empty. The scope_table_name must be in your own schema or you must have SELECT privilege on the table, or the SELECT ANY TABLE system privilege. This privilege is needed only while altering the table with the REF column.

Example I

In the following example 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; 

If the DEPARTMENTS table contains all possible departments, the DEPT column in EMP can only refer to rows in the DEPARTMENTS table. This can be expressed as a scope clause on the DEPT column as follows:

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

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

Example II

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);

Modifying Table Partitions

You can modify a table or table partition in any of the following ways. You cannot combine partition operations with other partition operations or with operations on the base table in one ALTER TABLE statement.

ADD PARTITION

Use ALTER TABLE ADD PARTITION to add a partition to the high end of the table (after the last existing partition). If the first element of the partition bound of the high partition is MAXVALUE, you cannot add a partition to the table. You must split the high partition.

You can add a partition to a table even if one or more of the table indexes or index partitions are marked UNUSABLE.

You must use the SPLIT PARTITION clause to add a partition at the beginning or the middle of the table.

The following example adds partition JAN97 to tablespace TSX:

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

DROP PARTITION

ALTER TABLE DROP PARTITION drops a partition and its data. If you want to drop a partition but keep its data in the table, you must merge the partition into one of the adjacent partitions. For information about merging two tables partitions, see the Oracle8 Administrator's Guide.

If you drop a partition and later insert a row that would have belonged to the dropped partition, the row will be stored in the next higher partition. However, if you drop the highest partition, the insert will fail because the range of values represented by the dropped partition is no longer valid for the table.

This statement also drops the corresponding partition in each local index defined on table. The index partitions are dropped even if they are marked as unusable.

If there are global indexes defined on table, and the partition you want to drop is not empty, dropping the partition marks all the global, nonpartitioned indexes and all the partitions of global partitioned indexes as unusable.

When a table contains only one partition, you cannot drop the partition. You must drop the table.

The following example drops partition DEC95:

ALTER TABLE sales DROP PARTITION dec95;

EXCHANGE PARTITION

This form of ALTER TABLE converts a partition to a nonpartitioned table and a NONPARTITIONED table to a partition by exchanging their data segments. You must have ALTER TABLE privileges on both tables to perform this operation.

The statistics of the table and partition--including table, column, index statistics and histograms--are exchanged. The aggregate statistics of the partitioned table are recalculated.

The logging attribute of the table and partition is exchanged.

The following example 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

Use the MODIFY PARTITION options of ALTER TABLE to

• mark local index partitions corresponding to a table partition as unusable
• rebuild all the unusable local index partitions corresponding to a table partition
• modify the physical attributes of a table partition

The following example 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 example rebuilds all the local index partitions that were marked UNUSABLE:

ALTER TABLE sales MODIFY PARTITION jan97
  REBUILD UNUSABLE LOCAL INDEXES;

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

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

MOVE PARTITION

This ALTER TABLE option moves a table partition to another segment. MOVE PARTITION always drops the partition's old segment and creates a new segment, even if you do not specify a new tablespace.

If partition partition_name is not empty, MOVE PARTITION marks all corresponding local index partitions and all global nonpartitioned indexes, and all the partitions of global partitioned indexes as unusable.

ALTER TABLE MOVE PARTITION obtains its parallel attribute from the PARALLEL clause, if specified. If not specified, the default PARALLEL attributes of the table, if any, are used. If neither is specified, it performs the move without using parallelism.

The PARALLEL clause on MOVE PARTITION does not change the default PARALLEL attributes of table.

The following example moves partition DEPOT2 to tablespace TS094:

ALTER TABLE parts 
  MOVE PARTITION depot2 TABLESPACE ts094 NOLOGGING;

RENAME

Use the RENAME option of ALTER TABLE to rename a table or to rename a partition.

The following example renames a table:

ALTER TABLE emp RENAME TO employee;

In the following example, partition EMP3 is renamed:

ALTER TABLE employee RENAME PARTITION emp3 TO employee3;

SPLIT PARTITION

The SPLIT PARTITION option divides a partition into two partitions. A new segment is allocated for each partition resulting from the split. The attributes of the new partitions are inherited from the partition that was split, except for attributes whose values you explicitly override in the SPLIT clause. The segment associated with the old partition is discarded.

This statement also performs a matching split on the corresponding partition in each local index defined on table. The index partitions are split even if they are marked unusable.

With the exception of the TABLESPACE attribute, the physical attributes of the LOCAL index partition being split are used for both new index partitions. If the parent LOCAL index lacks a default TABLESPACE attribute, new LOCAL index partitions will reside in the same tablespace as the corresponding newly created partitions of the underlying table.

If you do not specify physical attributes (PCTFREE, PCTUSED, INITRANS, MAXTRANS, STORAGE) for the new partitions, the current values of the partition being split are used as the default values for both partitions.

If partition_name is not empty, SPLIT PARTITION marks all affected index partitions as unusable. This includes all global index partitions as well as the local index partitions that result from the split.

The PARALLEL clause on SPLIT PARTITION does not change the default PARALLEL attributes of table.

The following example 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 (MINEXTENTS 2),
         PARTITION depot9 TABLESPACE ts010 )
  PARALLEL ( DEGREE 10 );

TRUNCATE PARTITION

Use TRUNCATE PARTITION to remove all rows from a partition in a table. Freed space is deallocated or reused depending on whether DROP STORAGE or REUSE STORAGE is specified in the clause.

This statement truncates the corresponding partition in each local index defined on table. The local index partitions are truncated even if they are marked as unusable. The unusable local index partitions are marked valid, resetting the UNUSABLE indicator.

If any global indexes are defined on table, and the partition you want to truncate is not empty, truncating the partition marks all the global nonpartitioned indexes and all the partitions of global partitioned indexes as unusable.

If you want to truncate a partition that contains data, you must first disable any referential integrity constraints on the table. Alternatively, you can delete the rows and then truncate the partition.

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

ALTER TABLE deliveries
  TRUNCATE PARTITION sys_p017 DROP STORAGE;

For examples of defining integrity constraints with the ALTER TABLE command, see the "CONSTRAINT clause".

For examples of enabling, disabling, and dropping integrity constraints and triggers with the ALTER TABLE command, see the "ENABLE clause", the "DISABLE clause", and the "DROP clause".

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

Related Topics

"CREATE TABLE"

"CONSTRAINT clause"

"DISABLE clause"

"ENABLE clause"

"STORAGE clause"

"CREATE VIEW"

ALTER TABLESPACE

Purpose

To alter an existing tablespace in one of the following ways:

• add datafile(s)
• rename datafiles
• change default storage parameters
• take the tablespace online or offline
• begin or end a backup
• allow or disallow writing to a tablespace
• change the default logging attribute of the tablespace
• change the minimum tablespace extent length

See also "ALTER TABLESPACE".

Prerequisites

If you have ALTER TABLESPACE system privilege, you can perform any of this command'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. Performing this function in restricted mode may help you meet these restrictions, since only users with RESTRICTED SESSION system privilege can be logged on.

• The tablespace must be online.
• There must not be any active transactions in the entire database. This is necessary to ensure that no undo information needs to be applied to the tablespace.
• 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.
• The COMPATIBLE initialization parameter must be set to 7.1.0 or greater.

Syntax

filespec: See "Filespec".

storage_clause: See "STORAGE clause".

Keywords and Parameters

Using ALTER TABLESPACE

The following examples illustrate the use of the ALTER TABLESPACE COMMAND.

Example I

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

ALTER TABLESPACE accounting 
    BEGIN BACKUP;

Example II

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

ALTER TABLESPACE accounting 
    END BACKUP;

Example III

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

1. Take the tablespace offline using an ALTER TABLESPACE statement with the OFFLINE option:

ALTER TABLESPACE accounting OFFLINE NORMAL; 

2. Copy the file from 'DISKA:PAY1.DAT' to 'DISKB:RECEIVE1.DAT' using your operating system's commands.
3. Rename the datafile using the ALTER TABLESPACE command with the RENAME DATAFILE clause:

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

4. Bring the tablespace back online using an ALTER TABLESPACE statement with the ONLINE option:

ALTER TABLESPACE accounting ONLINE; 

Example IV

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'
    AUTOEXTEND ON
    NEXT 10 K
    MAXSIZE 100 K;

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.

Example V

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

ALTER TABLESPACE tabspace_st MINIMUM EXTENT 128K;

Related Topics

"CREATE TABLESPACE"

"CREATE DATABASE"

"DROP TABLESPACE"

"STORAGE clause"

"Filespec"

ALTER TRIGGER

Purpose

To enable, disable, or compile a database trigger.

Prerequisites

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

Syntax

Keywords and Parameters

Invalid Triggers

You can use the ALTER TRIGGER command to explicitly recompile a trigger that is invalid. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

When you issue an ALTER TRIGGER statement, Oracle recompiles the trigger regardless of whether it is valid or invalid.

When you recompile a trigger, 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 then debug triggers using the predefined package DBMS_OUTPUT. For information on debugging procedures, see Oracle8 Application Developer's Guide. For information on how Oracle maintains dependencies among schema objects, including remote objects, see Oracle8 Concepts.

Enabling and Disabling Triggers

A database trigger is always either enabled or disabled. If a trigger is enabled, Oracle fires the trigger when a triggering statement is issued. If the trigger is disabled, Oracle does not fire the trigger when a triggering statement is issued.

When you create a trigger, Oracle enables it automatically. You can use the ENABLE and DISABLE options of the ALTER TRIGGER command to enable and disable a trigger.

You can also use the ENABLE and DISABLE clauses of the ALTER TABLE command to enable and disable all triggers associated with a table.

Example

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. Note that a part's inventory may have fallen below its reorder point while the trigger was disabled. When you reenable the trigger, Oracle does not automatically fire the trigger for this part until another transaction further reduces the inventory.

Related Topics

"CREATE TRIGGER"

"DROP TRIGGER"

"DISABLE clause"

"ENABLE clause"

"ALTER TABLE"

ALTER TYPE

Purpose

To recompile the specification and/or body, or to change the specification of an object type by adding new object 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.

Syntax

Keywords and Parameters

Restriction

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.

Example I

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 
        BEGIN 
           RETURN (year + invent); 
        END; 
      MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS 
        BEGIN 
           RETURN 'FIRST'; 
        END; 
      END;

Example II

The following example recompiles type LOAN_T:

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

ALTER TYPE loan_t COMPILE;

Example III

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;

Example IV

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;

Related Topics

"CREATE TYPE"

"CREATE TYPE BODY"

PL/SQL User's Guide and Reference

Oracle8 Application Developer's Guide

ALTER USER

Purpose

To change any of the following characteristics of a database user:

• authentication mechanism of the user
• password
• default tablespace for object creation
• tablespace for temporary segments created for the user
• tablespace access and tablespace quotas
• limits on database resources
• default roles

Prerequisites

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

Syntax

Keywords and Parameters

The keywords and parameters in the ALTER USER command all have the same meaning as in the CREATE USER command. For information on these keywords and parameters, see "CREATE USER".

For more information on default roles, see "Establishing Default Roles". For more information on security domains, see "Changing Authentication Methods".

Establishing Default Roles

The DEFAULT ROLE clause can only contain 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 Security Service Certification Authority

Note that Oracle enables default roles at logon without requiring the user to specify their passwords.

Example I

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;

Example II

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.

Example III

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.

Changing Authentication Methods

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.

Example I

The following example changes user TOM's authentication mechanism:

ALTER USER tom IDENTIFIED GLOBALLY AS 'CN=tom';

Example II

The following example 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 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.

Related Topics

"CREATE PROFILE"

"CREATE ROLE"

"CREATE USER"

"CREATE TABLESPACE"

ALTER VIEW

Purpose

To recompile a view or an object view. See also "Recompiling Views".

Prerequisites

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

Syntax

Keywords and Parameters

Recompiling Views

You can use the ALTER VIEW command to explicitly recompile a view that is invalid. Explicit recompilation allows you to locate recompilation errors before run time. You may want to explicitly recompile a view 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 Oracle8 Concepts.

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.

Related Topics

"CREATE VIEW"

ANALYZE

Purpose

To perform one of the following functions on an index or index partition, table or table partition, index-organized table, or cluster:

• collect statistics about the schema object used by the optimizer and store them in the data dictionary
• delete statistics about the schema object from the data dictionary
• validate the structure of the schema object
• identify migrated and chained rows of the table or cluster
• collect statistics on scalar object attributes
• validate and update object references (REFs)

Note:   Descriptions of commands and clauses preceded by  are only available if the Oracle objects option is installed on your database server.

   

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.

See also "Restrictions".

Syntax

Keywords and Parameters

Restrictions

Do not use ANALYZE to collect statistics on data dictionary tables.

You cannot compute or estimate statistics for the following column types:

• REFs
• VARRAYs
• nested tables
• LOBs (LOBs are not analyzed, they are skipped)
• LONGs
• object types

Collecting Statistics

You can collect statistics about the physical storage characteristics and data distribution of an index, table, column, or cluster and store them in the data dictionary. For computing or estimating statistics:

• Computation always provides exact values, but can take longer than estimation.
• Estimation is often much faster than computation and the results are usually nearly exact.

Use estimation, rather than computation, unless you feel you need exact values. Some statistics are always computed exactly, regardless of whether you specify computation or estimation. If you choose estimation and the time saved by estimating a statistic is negligible, Oracle computes the statistic exactly.

If the data dictionary already contains statistics for the analyzed object, Oracle updates the existing statistics with the new ones.

Example I

The following statement calculates statistics for a scalar object attribute:

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMNS addr.street;

The 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 Oracle8 Tuning.

The following sections list the statistics for that are collected for indexes, tables, columns, and clusters. The statistics marked with asterisks (*) are always computed exactly.

Indexes

For an index, Oracle collects the following statistics:

• 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.

Tables

For a table, Oracle collects the following statistics:

• number of rows
• number of data blocks currently containing data *
• 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

Table statistics appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES.

Columns

Column statistics can be based on the entire column or can use a histogram. A histogram partitions the values in the column into bands, so that all column values in a band fall within the same range. In some cases, it is useful to see how many values fall in various ranges. Oracle's histograms are height balanced as opposed to width balanced. This means that the column values are divided into bands so that each band contains approximately the same number of values. The useful information the histogram provides, then, is where in the range of values the endpoints fall. Width-balanced histograms, in contrast, divide the data into a number of ranges, all of which are the same size, and then count the number of values falling into each range.

Oracle collects the following column statistics:

• number of distinct values in the column as a whole
• maximum and minimum values in each band

When to Use Histograms

For uniformly distributed data, the cost-based approach makes fairly accurate guesses at the cost of executing a particular statement. For non-uniformly distributed data, Oracle allows you to store histograms describing the data distribution of a particular column. These histograms are stored in the dictionary and can be used by the cost-based optimizer.

Histograms are persistent objects, so there is a maintenance and space cost for using them. You should compute histograms only for columns that you know have highly skewed data distribution. Also, be aware that histograms, as well as all optimizer statistics, are static. If the data distribution of a column changes frequently, you must reissue the ANALYZE command to recompute the histogram for that column.

Histograms are not useful for columns with the following characteristics:

• all predicates on the column use bind variables
• the column data is uniformly distributed
• the column is not used in WHERE clauses of queries
• the column is unique and is used only with equality predicates

Create histograms on columns that are frequently used in WHERE clauses of queries and have a highly skewed data distribution. You create a histogram by using the ANALYZE TABLE command. For example, if you want to create a 10-band histogram on the SAL column of the EMP table, issue the following statement:

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;

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.

Clusters

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 clusters, 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.

Example II

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

ANALYZE TABLE cust_history
   ESTIMATE STATISTICS;

Deleting Statistics

With the DELETE STATISTICS option of the ANALYZE command, you can remove existing statistics about an object from the data dictionary. You may want to remove statistics if you no longer want the Oracle optimizer to use them.

When you use the DELETE STATISTICS option on a table, Oracle also automatically removes statistics for all the table's indexes. When you use the DELETE STATISTICS option on a cluster, Oracle also automatically removes statistics for all the cluster's tables and all their indexes, including the cluster index.

Example

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

ANALYZE TABLE cust_history
   DELETE STATISTICS;

Validating Structures

With the VALIDATE STRUCTURE option of the ANALYZE command, you can verify the integrity of the structure of an index, table, or cluster. If Oracle successfully validates the structure, a message confirming its validation is returned to you. 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.

Validating the structure of a object prevents SELECT, INSERT, UPDATE, and DELETE statements from concurrently accessing the object. Therefore, do not use this option on the tables, clusters, and indexes of your production applications during periods of high database activity.

Indexes

For an index, the VALIDATE STRUCTURE option verifies the integrity of each data block in the index and checks for block corruption. Note that this option 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 option.

When you use the VALIDATE STRUCTURE option on an index, Oracle also collects statistics about the index and stores them in the data dictionary view INDEX_STATS. Oracle overwrites any existing statistics about previously validated indexes. At any time, INDEX_STATS can contain only one row describing only one index. The INDEX_STATS view is described in the Oracle8 Reference.

The statistics collected by this option are not used by the Oracle optimizer. Do not confuse these statistics with the statistics collected by the COMPUTE STATISTICS and ESTIMATE STATISTICS options.

Example I

The following statement validates the structure of the index PARTS_INDEX:

ANALYZE INDEX parts_index
  VALIDATE STRUCTURE;

Tables

For a table, the VALIDATE STRUCTURE option verifies the integrity of each of the table's data blocks and rows. You can use the CASCADE option to also validate the structure of all indexes on the table as well and to perform cross-referencing between the table and each of its indexes. For each index, the cross-referencing involves the following validations:

• Each value of the table's indexed column must match the indexed column value of an index entry. The matching index entry must also identify the row in the table by the correct ROWID.
• Each entry in the index must identify a row in the table. The indexed column value in the index entry must match that of the identified row.

Example II

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 option 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.

Example III

The following statement validates the REFs in the EMP table:

ANALYZE TABLE emp VALIDATE REF UPDATE;

Clusters

For a cluster, the VALIDATE STRUCTURE option verifies the integrity of each row in the cluster and automatically validates the structure of each of the cluster's tables. You can use the CASCADE option to also validate the structure of all indexes on the cluster's tables as well, including the cluster index.

Example IV

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;

Partitioned Tables

There is no rule-based optimizer for partitioned tables, so it is important to analyze partitioned tables and indexes regularly.

For a partitioned table, the VALIDATE STRUCTURE option verifies each row in the partition to verify whether the column values of the partitioning columns collate less than the partition bound of that partition and greater than the partition bound of the previous partition (except the first partition). If the row does not collate correctly, the ROWID is inserted into the INVALID_ROWS table.

Listing Chained Rows

With the LIST option of the ANALYZE command, you can collect information about the migrated and chained rows in a table or cluster. A migrated row is one that has been moved from one data block to another. For example, Oracle migrates a row in a cluster if its cluster key value is updated. A chained row is one that is contained in more than one data block. For example, Oracle chains a row of a table or cluster if the row is too long to fit in a single data block. Migrated and chained rows may cause excessive I/O. You may want to identify such rows to eliminate them. For information on eliminating migrated and chained rows, see Oracle8 Tuning.

You can use the INTO clause to specify an output table into which Oracle places this information. The definition of a sample output table CHAINED_ROWS is provided in a SQL script available on your distribution media. Your list table must have the same column names, types, and sizes as the CHAINED_ROWS table. On many operating systems, the name of this script is UTLCHAIN.SQL. The actual name and location of this script depends on your operating system.

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

Related Topics

Oracle8 Tuning

ARCHIVE LOG clause

Purpose

To manually archive redo log file groups or to enable or disable automatic archiving. See also "Restrictions".

Prerequisites

The ARCHIVE LOG clause must appear in an ALTER SYSTEM command. You must have the privileges necessary to issue this statement. For information on these privileges, see "ALTER SYSTEM".

You must also have the OSDBA or OSOPER role enabled.

You can use most of the options of this clause when your instance has the database mounted, open or closed. Options that require your instance to have the database open are noted.

Syntax

Keywords and Parameters

Restrictions

You must archive redo log file groups in the order in which they are filled. If you specify a redo log file group for archiving with the LOGFILE parameter, and earlier redo log file groups are not yet archived, Oracle returns an error. If you specify a redo log file group for archiving with the CHANGE parameter or CURRENT option, and earlier redo log file groups are not yet archived, Oracle archives all unarchived groups up to and including the specified group.

You can also manually archive redo log file groups with the ARCHIVE LOG Server Manager command. For information on this command, see the Oracle Server Manager User's Guide.

You can also choose to have Oracle archive redo log files groups automatically. For information on automatic archiving, see Oracle8 Administrator's Guide. Note that you can always manually archive redo log file groups regardless of whether automatic archiving is enabled.

Example I

The following statement manually archives the redo log file group with the log sequence number 4 in thread number 3:

ALTER SYSTEM ARCHIVE LOG THREAD 3 SEQUENCE 4;

Example II

The following statement manually archives the redo log file group containing the redo log entry with the SCN 9356083:

ALTER SYSTEM ARCHIVE LOG CHANGE 9356083;

Example III

The following statement manually archives the redo log file group containing a member named 'DISKL:LOG6.LOG' to an archived redo log file in the location 'DISKA:[ARCH$]':

ALTER SYSTEM ARCHIVE LOG 
    LOGFILE 'diskl:log6.log' 
    TO 'diska:[arch$]';

Related Topics

"ALTER SYSTEM"

AUDIT (SQL Statements)

Purpose

To choose specific SQL statements for auditing in subsequent user sessions. To choose particular schema objects for auditing, see "AUDIT (Schema Objects)". See also "Auditing".

Prerequisites

You must have AUDIT SYSTEM system privilege.

Syntax

Keywords and Parameters

Auditing

Auditing keeps track of operations performed by database users. 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. The audit trail is a database table that contains audit records. You can review database activity by examining the audit trail through data dictionary views. For information on these views, see the Oracle8 Reference.

To generate audit records, you must perform the following steps:

Enable auditing

You must enable auditing by setting the initialization parameter AUDIT_TRAIL = DB.

Specify auditing options

To specify auditing options, you must use the AUDIT command. Auditing options choose which SQL commands, operations, database objects, and users Oracle audits. After you specify auditing options, they appear in the data dictionary. For more information on data dictionary views containing auditing options see the Oracle8 Reference.

You can specify auditing options regardless of whether auditing is enabled. However, Oracle does not generate audit records until you enable auditing.

Auditing options specified by the AUDIT command (SQL Statements) apply only to subsequent sessions, rather than to current sessions.

Statement Options for Database Objects

The following table lists the statement options relating to database objects and the statements that they audit.

Table 4-6 Statement Auditing Options for Database Objects

CLUSTER

CREATE CLUSTER
AUDIT CLUSTER
DROP CLUSTER
TRUNCATE CLUSTER

DATABASE 
LINK

CREATE DATABASE LINK
DROP DATABASE LINK

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

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

TABLESPACE

CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE

TRIGGER

CREATE TRIGGER
ALTER TRIGGER 
   with ENABLE and DISABLE options
DROP TRIGGER
ALTER TABLE
   with ENABLE ALL TRIGGERS
   and DISABLE ALL TRIGGERS clauses

  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

Statement Options for Commands

The following table lists additional statement options related to commands and the SQL statements and operations that they audit.

Table 4-7 Statement Auditing Options for Commands

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

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

SELECT TABLE

SELECT FROM table, view, snapshot

UPDATE TABLE

UPDATE table, view

Example I

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;

Example II

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;

Example III

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

AUDIT DELETE ANY TABLE;

Example IV

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

AUDIT CREATE ANY DIRECTORY;

Example V

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;

Shortcuts for System Privileges and Statement Options

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 follows:

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 SYNONYM  CREATE ROLE  CREATE USER
ALL	equivalent to specifying all statement options shown in Table 4-6, but not the additional statement options shown in Table 4-7.
ALL PRIVILEGES	is equivalent to specifying all system privileges.

 

Related Topics

"AUDIT (Schema Objects)"

"NOAUDIT (Schema Objects)"

AUDIT (Schema Objects)

Purpose

To choose a specific schema object for auditing. To choose particular SQL commands for auditing, see "AUDIT (SQL Statements)".

Auditing keeps track of operations performed by database users. For a brief conceptual overview of auditing, including how to enable auditing, see the "AUDIT (SQL Statements)". Note that auditing options established by the AUDIT command (Schema Objects) apply to current sessions as well as to subsequent sessions.

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.

Syntax

Keywords and Parameters

Object Options

The following table shows the object options you can choose for each type of object.

Table 4-8 Object Auditing Options

The name of each object option specifies a command 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.

Shortcuts for Object Options

Oracle provides a shortcut for specifying object auditing options:

ALL	is 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.

 

Default Auditing

You can use the DEFAULT option of the AUDIT command to specify auditing options for objects that have not yet been created. Once you have established these default auditing options, any subsequently created object is automatically audited with those options. Note that 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 command.

Example I

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;

Example II

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;

Example III

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 short cut 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

Example IV

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

AUDIT READ ON DIRECTORY bfile_dir1;

Example V

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, INSERT, UPDATE, or DELETE statements issued against the table.
• If you create a view, Oracle automatically audits any INSERT, UPDATE, or DELETE statements issued against the view.
• If you create a sequence, Oracle automatically audits any ALTER statements issued against the sequence.
• If you create a procedure, package, or function, Oracle automatically audits any ALTER statements issued against it.

Related Topics

"AUDIT (SQL Statements)"

"NOAUDIT (Schema Objects)"

---

Prev   Next

Copyright © 1997 Oracle Corporation.  All Rights Reserved.

Library   Product   Contents   Index