4
Commands

This chapter describes, in alphabetical order, Oracle SQL commands and clauses.

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

The description of each command or clause contains the following sections:

Purpose	describes the basic uses of the command.
Prerequisites	lists privileges you must have and steps that you must take before using the command. In addition to the prerequisites listed, most commands also require that the database be opened by your instance, unless otherwise noted.
Syntax	shows the keywords and parameters that make up the command.
Keywords and Parameters	describes the purpose of each keyword and parameter. The conventions for keywords and parameters used in this chapter are explained in the Preface of this reference.
	Usage notes: Optional sections following "Keywords and Parameters" provide examples and discuss how and when to use the command.
Related Topics	lists related commands, clauses, and sections of this and other manuals.

---

Summary of SQL Commands

The tables in the following sections provide a functional summary of SQL commands and are divided into these categories:

• Data Definition Language (DDL) Commands
• Data Manipulation Language (DML) Commands
• Transaction Control Commands
• Session Control Commands
• System Control Command

Data Definition Language (DDL) Commands

Data definition language (DDL) commands enable you to perform these tasks:

• create, alter, and drop schema objects
• grant and revoke privileges and roles
• analyze information on a table, index, or cluster
• establish auditing options
• add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the object being acted upon. For example, an ALTER TABLE command fails if another user has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not required exclusive access to the object being acted upon. For example, you can analyze a table while other users are updating the table.

Oracle implicitly commits the current transaction before and after every DDL statement.

Many DDL statements may cause Oracle to recompile or reauthorize schema objects. For information on how Oracle recompiles and reauthorizes schema objects and the circumstances under which a DDL statement would cause this, see Oracle8 Concepts.

DDL commands are not directly supported by PL/SQL, but may be available using packaged procedures supplied by Oracle corporation. For more information, see PL/SQL User's Guide and Reference.

Table 4-1 lists the DDL commands.

Table 4-1 Data Definition Language Commands

Command	Purpose
ALTER CLUSTER	Change the storage characteristics of a cluster. Allocate an extent for a cluster.
ALTER DATABASE	Open/mount the database. Convert an Oracle7 data dictionary when migrating to Oracle8. Prepare to downgrade to an earlier release of Oracle. Choose ARCHIVELOG/NOARCHIVELOG mode. Perform media recovery. Add/drop/clear redo log file groups members. Rename a datafile/redo log file member. Back up the current control file. Back up SQL commands (that can be used to re-create the database) to the trace file. Create a new datafile. Resize one or more datafiles. Create a new datafile in place of an old one for recovery purposes. Enable/disable autoextending the size of datafiles. Take a datafile online/offline. Enable/disable a thread of redo log file groups. Change the database's global name.
ALTER FUNCTION	Recompile a stored function.
ALTER INDEX	Redefine an index's future storage allocation.
ALTER PACKAGE	Recompile a stored package.
ALTER PROCEDURE	Recompile a stored procedure.
ALTER PROFILE	Add or remove a resource limit to or from a profile.
ALTER RESOURCE COST	Specify a formula to calculate the total cost of resources used by a session.
ALTER ROLE	Change the authorization needed to access a role.
ALTER ROLLBACK SEGMENT	Change a rollback segment's storage characteristics. Bring a rollback segment online/offline. Shrink a rollback segment to an optimal or given size.
ALTER SEQUENCE	Redefine value generation for a sequence.
ALTER SNAPSHOT	Change a snapshot's storage characteristics, automatic refresh time, or automatic refresh mode.
ALTER SHAPSHOT LOG	Change a snapshot log's storage characteristics.
ALTER TABLE	Add a column/integrity constraint to a table. Redefine a column, to change a table's storage characteristics. Enable/disable/drop an integrity constraint. Enable/disable table locks on a table. Enable/disable all triggers on a table. Allocate an extent for the table. Allow/disallow writing to a table. Modify the degree of parallelism for a table.
ALTER TABLESPACE	Add/rename datafiles. Change storage characteristics. Take a tablespace online/offline. Begin/end a backup. Allow/disallow writing to a tablespace.
ALTER TRIGGER	Enable/disable a database trigger.
ALTER TYPE	Change a user-defined type.
ALTER USER	Change a user's password, default tablespace, temporary tablespace, tablespace quotas, profile, or default roles.
ALTER VIEW	Recompile a view.
ANALYZE	Collect performance statistics, validate structure, or identify chained rows for a table, cluster, or index.
AUDIT	Choose auditing for specified SQL commands or operations on schema objects.
COMMENT	Add a comment about a table, view, snapshot, or column to the data dictionary.
CREATE CLUSTER	Create a cluster that can contain one or more tables.
CREATE CONTROLFILE	Re-create a control file.
CREATE DATABASE	Create a database.
CREATE DATABASE LINK	Create a link to a remote database.
CREATE DIRECTORY	Create a directory database object for administering access to and use of BFILEs stored outside the database.
CREATE FUNCTION	Create a stored function.
CREATE INDEX	Create an index for a table or cluster.
CREATE LIBRARY	Create a library from which SQL and PL/SQL can call external third-generation language (3GL) functions and procedures.
CREATE PACKAGE	Create the specification of a stored package.
CREATE PACKAGE BODY	Create the body of a stored package
CREATE PROCEDURE	Create a stored procedure.
CREATE PROFILE	Create a profile and specify its resource limits.
CREATE ROLE	Create a role.
CREATE ROLLBACK SEGMENT	Create a rollback segment.
CREATE SCHEMA	Issue multiple CREATE TABLE, CREATE VIEW, and GRANT statements in a single transaction.
CREATE SEQUENCE	Create a sequence for generating sequential values.
CREATE SHAPSHOT	Create a snapshot of data from one or more remote master tables.
CREATE SNAPSHOT LOG	Create a snapshot log containing changes made to the master table of a snapshot.
CREATE SYNONYM	Create a synonym for a schema object.
CREATE TABLE	Create a table, defining its columns, integrity constraints, and storage allocation.
CREATE TABLESPACE	Create a place in the database for storage of schema objects, rollback segments, and temporary segments, naming the datafiles that make up the tablespace.
CREATE TRIGGER	Create a database trigger.
CREATE TYPE	Create an object type, named varying array (VARRAY), nested table type, or an incomplete object type.
CREATE USER	Create a database user.
CREATE VIEW	Define a view of one or more tables or views.
DROP CLUSTER	Remove a cluster from the database.
DROP DATABASE LINK	Remove a database link.
DROP DIRECTORY	Remove a directory from the database.
DROP FUNCTION	Remove a stored function from the database.
DROP INDEX	Remove an index from the database.
DROP LIBRARY	Remove a library object from the database.
DROP PACKAGE	Remove a stored package from the database.
DROP PROCEDURE	Remove a stored procedure from the database.
DROP PROFILE	Remove a profile from the database.
DROP ROLE	Remove a role from the database.
DROP ROLLBACK SEGMENT	Remove a rollback segment from the database.
DROP SEQUENCE	Remove a sequence from the database.
DROP SNAPSHOT	Remove a snapshot from the database.
DROP SNAPSHOT LOG	Remove a snapshot log from the database.
DROP SYNONYM	Remove a synonym from the database.
DROP TABLE	Remove a table from the database.
DROP TABLESPACE	Remove a tablespace from the database.
DROP TRIGGER	Remove a trigger from the database.
DROP TYPE	Remove a user-defined type from the database.
DROP USER	Remove a user and the objects in the user's schema from the database.
DROP VIEW	Remove a view from the database.
GRANT	Grant system privileges, roles, and object privileges to users and roles.
NOAUDIT	Disable auditing by reversing, partially or completely, the effect of a prior AUDIT statement.
RENAME	Change the name of a schema object.
REVOKE	Revoke system privileges, roles, and object privileges from users and roles.
TRUNCATE	Remove all rows from a table or cluster and free the space that the rows used.

Data Manipulation Language (DML) Commands

Data manipulation language (DML) commands query and manipulate data in existing schema objects. These commands do not implicitly commit the current transaction.

Table 4-2 Data Manipulation Language Commands

Command	Purpose
DELETE	Remove rows from a table.
EXPLAIN PLAN	Return the execution plan for a SQL statement.
INSERT	Add new rows to a table.
LOCK TABLE	Lock a table or view, limiting access to it by other users.
SELECT	Select data in rows and columns from one or more tables.
UPDATE	Change data in a table.

All DML commands except the EXPLAIN PLAN command are supported in PL/SQL.

Transaction Control Commands

Transaction control commands manage changes made by DML commands.

Table 4-3 Transaction Control Commands

Command	Purpose
COMMIT	Make permanent the changes made by statements issued since the beginning of the current transaction.
ROLLBACK	Undo all changes since the beginning of a transaction or since a savepoint.
SAVEPOINT	Establish a point back to which you may roll.
SET TRANSACTION	Establish properties for the current transaction.

All transaction control commands except certain forms of the COMMIT and ROLLBACK commands are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK.

Session Control Commands

Session control commands dynamically manage the properties of a user session. These commands do not implicitly commit the current transaction.

PL/SQL does not support session control commands.

Table 4-4 Session Control Commands

Command	Purpose
ALTER SESSION	Enable/disable the SQL trace facility. Enable/disable global name resolution. Change the values of the session's NLS parameters. In a parallel server, indicate that the session must access database files as if the session were connected to another instance. Close a database link. Send advice to remote databases for forcing an in-doubt distributed transaction. Permit or prohibit procedures and stored procedures from issuing COMMIT and ROLLBACK statements. Change the goal of the cost-based optimization approach.
SET ROLE	Enable/disable roles for the current session.

System Control Command

The single system control command dynamically manages the properties of an Oracle instance. This command does not implicitly commit the current transaction.

ALTER SYSTEM is not supported in PL/SQL.

Table 4-5 System Control Command

Command	Purpose
ALTER SYSTEM	Alter the Oracle instance by performing a specialized function.

Embedded SQL Commands

Embedded SQL commands place DDL, DML, and transaction control statements within a procedural language program. Embedded SQL is supported by the Oracle precompilers and is documented in the following books:

• Pro*COBOL Precompiler Programmer's Guide
• Pro*C/C++ Precompiler Programmer's Guide
• SQL*Module for Ada Programmer's Guide

---

ALTER CLUSTER

Purpose

Redefines storage and parallelism characteristics of a cluster. See also "Altering Clusters".

Prerequisites

The cluster must be in your own schema or you must have ALTER ANY CLUSTER system privilege.

Syntax

deallocate_unused_clause: See the DEALLOCATE UNUSED clause.

parallel_clause: See the PARALLEL clause.

Keywords and Parameters

schema	is the schema containing the cluster. If you omit schema, Oracle assumes the cluster is in your own schema.
cluster	is the name of the cluster to be altered.
physical_attributes_clause	changes the values of the PCTUSED, PCTFREE, INITRANS, and MAXTRANS parameters of the cluster. See CREATE CLUSTER.
	storage_clause	changes the storage characteristics for the cluster. See the STORAGE clause.
SIZE	determines how many cluster keys will be stored in data blocks allocated to the cluster. You can change the SIZE parameter only for an indexed cluster, not for a hash cluster. For a description of the SIZE parameter, see CREATE CLUSTER.
allocate_extent_clause	explicitly allocates a new extent for the cluster.
	SIZE	specifies the size of the extent in bytes. Use K or M to specify the extent size in kilobytes or megabytes. If you omit this parameter, Oracle determines the size based on the values of the cluster's STORAGE parameters.
	DATAFILE	specifies one of the datafiles in the cluster's tablespace to contain the new extent. If you omit this parameter, Oracle chooses the datafile.
	INSTANCE	makes the new extent available to the specified instance. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER. If you omit this parameter, the extent is available to all instances. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode. Explicitly allocating an extent with this clause does not cause Oracle to evaluate the cluster's storage parameters and determine a new size for the next extent to be allocated. You can allocate a new extent only for an indexed cluster, not a hash cluster.
deallocate_unused_clause	explicitly deallocates unused space at the end of the cluster and makes the freed space available for other segments. Only unused space above the high-water mark can be freed. If KEEP is omitted, all unused space is freed. For syntax and complete information, see the DEALLOCATE UNUSED clause.
	KEEP	specifies the number of bytes above the high-water mark that the cluster will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent.
parallel_clause	specifies the degree of parallelism for creating the cluster and the default degree of parallelism for queries on the cluster once created. For syntax and complete information, see the PARALLEL clause.

Altering Clusters

You can perform these tasks with the ALTER CLUSTER command:

• change the MAXTRANS parameter value for data blocks in the cluster
• change the SIZE, PCTUSED, PCTFREE, and INITRANS parameter values for future data blocks in the cluster
• change future storage characteristics with the STORAGE characteristics NEXT, PCTINCREASE, and MAXEXTENTS
• explicitly allocate an extent
• explicitly deallocate space from unused extents

You cannot perform these tasks with the ALTER CLUSTER command:

• change the number or the name of columns in the cluster key
• change the values of the STORAGE parameters INITIAL and MINEXTENTS
• change the tablespace in which the cluster is stored
• remove tables from a cluster (see DROP CLUSTER and DROP TABLE)

Example I

The following statement alters the CUSTOMER cluster in the schema SCOTT:

ALTER CLUSTER scott.customer 
    SIZE 512 
    STORAGE (MAXEXTENTS 25); 

Oracle allocates 512 bytes for each cluster key value. Assuming a data block size of 2 kilobytes, future data blocks within this cluster contain 4 cluster keys per data block, or 2 kilobytes divided by 512 bytes.

The cluster can have a maximum of 25 extents.

Example II

The following statement deallocates unused space from CUSTOMER cluster, keeping 30 kilobytes of unused space for future use:

ALTER CLUSTER scott.customer 
    DEALLOCATE UNUSED KEEP 30 K;

Related Topics

CREATE CLUSTER

CREATE TABLE

DEALLOCATE UNUSED clause

DROP CLUSTER

DROP TABLE

STORAGE clause

PARALLEL clause

---

ALTER DATABASE

Purpose

To alter an existing database in one of these ways:

• mount the database, clone, or standby database
• convert an Oracle7 data dictionary when migrating to Oracle8
• open the database
• choose ARCHIVELOG or NOARCHIVELOG mode for redo log file groups
• perform media recovery
• add or drop a redo log file group or a member of a redo log file group
• clear and initialize an online redo log file
• rename a redo log file member or a datafile
• back up the current control file
• back up SQL commands (that can be used to re-create the database) to the database's trace file
• take a datafile online or offline
• enable or disable a thread of redo log file groups
• change the database's global name
• prepare to downgrade to an earlier release of Oracle
• resize one or more datafiles
• create a new datafile in place of an old one for recovery purposes
• enable or disable the autoextending of the size of datafiles

For illustrations of some of these purposes, see "Examples".

Prerequisites

You must have ALTER DATABASE system privilege.

Syntax

logfile_descriptor::=

autoextend_clause::=

recover_clause: See the RECOVER clause.

Keywords and Parameters

database	identifies the database to be altered. The database name can contain only ASCII characters. If you omit database, Oracle alters the database identified by the value of the initialization parameter DB_NAME. You can alter only the database whose control files are specified by the initialization parameter CONTROL_FILES. Note that the database identifier is not related to the Net8 database specification.
You can use the following options only when the database is not mounted by your instance:
MOUNT	mounts the database.
	STANDBY DATABASE	mounts the standby database. For more information, see the Oracle8 Administrator's Guide.
	CLONE DATABASE	mounts the clone database. For more information, see the Oracle8 Backup and Recovery Guide.
CONVERT	completes the conversion of the Oracle7 data dictionary. After you use this option, the Oracle7 data dictionary no longer exists in the Oracle database. Use this option only when you are migrating to Oracle8. For more information on using this option, see Oracle8 Migration.
OPEN	opens the database, making it available for normal use. You must mount the database before you can open it. You cannot open a standby database that has not been activated.
	RESETLOGS	resets the current log sequence number to 1 and discards any redo information that was not applied during recovery, ensuring that it will never be applied. This effectively discards all changes that are in the redo log, but not in the database. You must use this option to open the database after performing media recovery with an incomplete recovery using the RECOVER UNTIL clause (see RECOVER clause) or with a backup control file. After opening the database with this option, you should perform a complete database backup.
	NORESETLOGS	leaves the log sequence number and redo log files in their current state.
	You can specify the above options only after performing incomplete media recovery or complete media recovery with a backup control file. In any other case, Oracle uses the NORESETLOGS automatically.
ACTIVATE STANDBY DATABASE	changes the state of a standby database to an active database. For more information, see Oracle8 Administrator's Guide..
Use the following options only if your instance has the database mounted in parallel server disabled mode, but not open:
ARCHIVELOG	establishes ARCHIVELOG mode for redo log file groups. In this mode, the contents of a redo log file group must be archived before the group can be reused. This option prepares for the possibility of media recovery. You can use this option only after shutting down your instance normally or immediately with no errors and then restarting it, mounting the database in parallel server disabled mode.
NOARCHIVELOG	establishes NOARCHIVELOG mode for redo log files. In this mode, the contents of a redo log file group need not be archived so that the group can be reused. This mode does not prepare for recovery after media failure.
You can use any of the following options when your instance has the database mounted, open or closed, and the files involved are not in use:
recover_clause	performs media recovery. For syntax and more information, see the RECOVER clause. You recover the entire database only when the database is closed. You can recover tablespaces or datafiles when the database is open or closed, provided the tablespaces or datafiles to be recovered are offline. You cannot perform media recovery if you are connected to Oracle through the multithreaded server architecture. You can also perform media recovery with the Server Manager recovery dialog box.
ADD LOGFILE	adds one or more redo log file groups to the specified thread, making them available to the instance assigned the thread.
	THREAD	is required only if you are using Oracle with the Parallel Server option in parallel mode. If you omit the THREAD parameter, the redo log file group is added to the thread assigned to your instance.
	GROUP	uniquely identifies the redo log file group among all groups in all threads and can range from 1 to the MAXLOGFILES value. You cannot add multiple redo log file groups having the same GROUP value. If you omit this parameter, Oracle generates its value automatically. You can examine the GROUP value for a redo log file group through the dynamic performance view V$LOG.
	filespec	Each filespec specifies a redo log file group containing one or more members, or copies. See the syntax description of filespec in "Filespec".
ADD LOGFILE MEMBER	adds new members to existing redo log file groups. Each new member is specified by 'filename'. If the file already exists, it must be the same size as the other group members, and you must specify the REUSE option. If the file does not exist, Oracle creates a file of the correct size. You cannot add a member to a group if all of the group's members have been lost through media failure.
	You can specify an existing redo log file group in one of these ways:
	GROUP	Specify the value of the GROUP parameter that identifies the redo log file group.
	list of filenames	List all members of the redo log file group. You must fully specify each filename according to the conventions of your operating system.
DROP LOGFILE	drops all members of a redo log file group. You can specify a redo log file group in the same manner as the ADD LOGFILE MEMBER clause. You cannot drop a redo log file group if it needs archiving or is the currently active group; nor can you drop a redo log file group if doing so would cause the redo thread to contain less than two redo log file groups.
DROP LOGFILE MEMBER	drops one or more redo log file members. Each 'filename' must fully specify a member using the conventions for filenames on your operating system.
	You cannot use this clause to drop all members of a redo log file group that contains valid data. To perform this operation, use the DROP LOGFILE clause.
CLEAR LOGFILE	reinitializes an online redo log, optionally without archiving the redo log. CLEAR LOGFILE is similar to adding and dropping a redo log, except that the command may be issued even if there are only two logs for the thread and also may be issued for the current redo log of a closed thread.
	UNARCHIVED	You must specify UNARCHIVED if you want to reuse a redo log that was not archived.
		WARNING: Specifying UNARCHIVED makes backups unusable if the redo log is needed for recovery.
	You cannot use CLEAR LOGFILE to clear a log needed for media recovery. If it is necessary to clear a log containing redo after the database checkpoint, you must first perform incomplete media recovery. The current redo log of an open thread can be cleared. The current log of a closed thread can be cleared by switching logs in the closed thread.
	If the CLEAR LOGFILE command is interrupted by a system or instance failure, then the database may hang. If so, this command must be reissued once the database is restarted. If the failure occurred because of I/O errors accessing one member of a log group, then that member can be dropped and other members added.
	UNRECOVERABLE DATAFILE	You must specify UNRECOVERABLE DATAFILE if the database has a datafile that is offline (not for drop) and if the unarchived log to be cleared is needed to recover the datafile before bringing it back online. In this case, you must drop the datafile and the entire tablespace once the CLEAR LOGFILE command completes.
RENAME FILE	renames datafiles or redo log file members. This clause renames only files in the control file; it does not actually rename them on your operating system. You must specify each filename using the conventions for filenames on your operating system.
CREATE STANDBY CONTROLFILE	creates a control file to be used to maintain a standby database. For more information, see Oracle8 Administrator's Guide.
BACKUP CONTROLFILE	backs up the current control file.
	TO 'filename'	specifies the file to which the control file is backed up. You must fully specify the filename using the conventions for your operating system. If the specified file already exists, you must specify the REUSE option.
	TO TRACE	writes SQL statements to the database's trace file rather than making a physical backup of the control file. The SQL commands can be used to start up the database, re-create the control file, and recover and open the database appropriately, based on the created control file.
		You can copy the commands from the trace file into a script file, edit the commands as necessary, and use the database if all copies of the control file are lost (or to change the size of the control file).
	RESETLOGS	specifies that the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN RESETLOGS.
	NORESETLOGS	specifies that the SQL statement written to the trace file for starting the database is ALTER DATABASE OPEN NORESETLOGS.
RENAME GLOBAL_NAME	changes the global name of the database. The database is the new database name and can be as long as eight bytes. The optional domain specifies where the database is effectively located in the network hierarchy. Note: Renaming your database does not change global references to your database from existing database links, synonyms, and stored procedures and functions on remote databases. Changing such references is the responsibility of the administrator of the remote databases.
	For more information on global names, see Oracle8 Distributed Database Systems.
RESET COMPATIBILITY	marks the database to be reset to an earlier version of Oracle when the database is next restarted.
	Note: RESET COMPATIBILITY works only if you have successfully disabled Oracle features that affect backward compatibility. For more information on downgrading to an earlier version of Oracle, see Oracle8 Migration.
You can use the following options only when your instance has the database open:
ENABLE THREAD	in a parallel server, enables the specified thread of redo log file groups. The thread must have at least two redo log file groups before you can enable it.
	PUBLIC	makes the enabled thread available to any instance that does not explicitly request a specific thread with the initialization parameter THREAD. If you omit the PUBLIC option, the thread is available only to the instance that explicitly requests it with the initialization parameter THREAD.
DISABLE THREAD	disables the specified thread, making it unavailable to all instances. You cannot disable a thread if an instance using it has the database mounted.
You can use any of the following options when your instance has the database mounted, open or closed, and the files involved are not in use:
CREATE DATAFILE	creates a new empty datafile in place of an old one. You can use this option to re-create a datafile that was lost with no backup. The 'filename' must identify a file that is or was once part of the database. The filespec specifies the name and size of the new datafile. If you omit the AS clause, Oracle creates the new file with the name and size as the file specified by 'filename'.
	During recovery, all archived redo logs written to since the original datafile was created must be applied to the new, empty version of the lost datafile.
	Oracle creates the new file in the same state as the old file when it was created. You must perform media recovery on the new file to return it to the state of the old file at the time it was lost.
	You cannot create a new file based on the first datafile of the SYSTEM tablespace.
DATAFILE	affects your database files as follows:
	ONLINE	brings the datafile online.
	OFFLINE	takes the datafile offline. If the database is open, you must perform media recovery on the datafile before bringing it back online, because a checkpoint is not performed on the datafile before it is taken offline.
		DROP takes a datafile offline when the database is in NOARCHIVELOG mode.
	RESIZE	attempts to change the size of the datafile to the specified absolute size in bytes. You can also use K or M to specify this size in kilobytes or megabytes. There is no default, so you must specify a size.
	autoextend_clause	enables or disables the automatic extension of a datafile. If you do not specify this clause, datafiles are not automatically extended.
		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 DATABASE AUTOEXTEND commands.
		ON enables autoextend.
		NEXT specifies the size in bytes of the next increment of disk space to be automatically allocated to the datafile when more extents are required. You can also use K or M to specify this size in kilobytes or megabytes. The default is one data block.
		MAXSIZE specifies the maximum disk space allowed for automatic extension of the datafile.
		UNLIMITED sets no limit on allocating disk space to the datafile.
	END BACKUP	avoids media recovery on database startup after an online tablespace backup was interrupted by a system failure or instance failure or SHUTDOWN ABORT.
	WARNING: Do not use ALTER TABLESPACE ... END BACKUP if you have restored any of the files affected from a backup. Media recovery is fully described in the Oracle8 Backup and Recovery Guide andOracle8 Administrator's Guide..

Examples

For more information on using the ALTER DATABASE command for database maintenance, see the Oracle8 Administrator's Guide.

Example I

The following statement adds a redo log file group with two members and identifies it with a GROUP parameter value of 3:

ALTER DATABASE stocks
  ADD LOGFILE GROUP 3 
    ('diska:log3.log' ,  
     'diskb:log3.log') SIZE 50K; 

Example II

The following statement adds a member to the redo log file group added in the previous example:

ALTER DATABASE stocks  
   ADD LOGFILE MEMBER 'diskc:log3.log'  
   TO GROUP 3; 

Example III

The following statement drops the redo log file member added in the previous example:

ALTER DATABASE stocks  
    DROP LOGFILE MEMBER 'diskc:log3.log'; 

Example IV

The following statement renames a redo log file member:

ALTER DATABASE stocks  
    RENAME FILE 'diskb:log3.log' TO 'diskd:log3.log'; 

The above statement only changes the member of the redo log group from one file to another. The statement does not actually change the name of the file 'DISKB:LOG3.LOG' to 'DISKD:LOG3.LOG'. You must perform this operation through your operating system.

Example V

The following statement drops all members of the redo log file group 3:

ALTER DATABASE stocks DROP LOGFILE GROUP 3; 

Example VI

The following statement adds a redo log file group containing three members to thread 5 and assigns it a GROUP parameter value of 4:

ALTER DATABASE stocks 
    ADD LOGFILE THREAD 5 GROUP 4  
        ('diska:log4.log', 
        'diskb:log4:log', 
        'diskc:log4.log' ); 

Example VII

The following statement disables thread 5 in a parallel server:

ALTER DATABASE stocks  
    DISABLE THREAD 5; 

Example VIII

The following statement enables thread 5 in a parallel server, making it available to any Oracle instance that does not explicitly request a specific thread:

ALTER DATABASE stocks  
    ENABLE PUBLIC THREAD 5; 

Example IX

The following statement creates a new datafile 'DISK1:DB1.DAT' based on the file 'DISK2:DB1.DAT':

ALTER DATABASE 
    CREATE DATAFILE 'disk1:db1.dat' AS 'disk2:db1.dat'; 

Example XI

The following statement changes the global name of the database and includes both the database name and domain:

ALTER DATABASE  
    RENAME GLOBAL_NAME TO sales.australia.acme.com; 

Example XII

The following statement attempts to change the size of datafile 'DISK1:DB1.DAT':

ALTER DATABASE  
    DATAFILE 'disk1:db1.dat' RESIZE 10 M;

For examples of performing media recovery, see Oracle8 Administrator's Guide and Oracle8 Backup and Recovery Guide.

Example XIII

The following statement clears a log file:

ALTER DATABASE  
    CLEAR LOGFILE 'disk3:log.dbf';

Related Topics

CREATE DATABASE

RECOVER clause

"Filespec"

---

ALTER FUNCTION

Purpose

To recompile a standalone stored function. See also "Recompiling Standalone Functions".

Prerequisites

The function must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.

Syntax

Keywords and Parameters

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

Recompiling Standalone Functions

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

The ALTER FUNCTION command is similar to ALTER PROCEDURE. For information on how Oracle recompiles functions and procedures, see Oracle8 Concepts.

Note: This command does not change the declaration or definition of an existing function. To redeclare or redefine a function, use the CREATE FUNCTION command with the OR REPLACE option; see CREATE FUNCTION.

Example

To explicitly recompile the function GET_BAL owned by the user MERRIWEATHER, issue the following statement:

ALTER FUNCTION merriweather.get_bal
COMPILE; 

If Oracle encounters no compilation errors while recompiling GET_BAL, GET_BAL becomes valid. Oracle can subsequently execute it without recompiling it at run time. If recompiling GET_BAL results in compilation errors, Oracle returns an error message and GET_BAL remains invalid.

Oracle also invalidates all objects that depend upon GET_BAL. If you subsequently reference one of these objects without explicitly recompiling it first, Oracle recompiles it implicitly at run time.

Related Topics

ALTER PROCEDURE

CREATE FUNCTION

---

ALTER INDEX

Purpose

Use ALTER INDEX to:

• change storage allocation for, rebuild, or rename an index
• rename, split, remove, mark as unusable, rebuild, or modify physical or logging attributes of a partition of a partitioned index
• modify the physical, parallel, or logging attributes of a nonpartitioned index
• modify the default physical, parallel, or logging attributes of index partitions
• rebuild an index to store the bytes of the index block in reverse order
• modify a nested table index

For illustrations of some of these purposes, see "Examples".

Prerequisites

The index must be in your own schema or you must have ALTER ANY INDEX system privilege.

Schema object privileges are granted on the parent index, not on individual index partitions. The following index partition operations require tablespace quota:

• modify
• rebuild
• split

Syntax

parallel_clause: See PARALLEL clause.

storage_clause: See STORAGE clause.

deallocate_unused_clause: See DEALLOCATE UNUSED clause.

Keywords and Parameters

schema	is the schema containing the index. If you omit schema, Oracle assumes the index is in your own schema.
index	is the name of the index to be altered.
	The following operations can be performed only on partitioned indexes: drop partition split partition rename partition rebuild partition modify partition Of these, drop partition and split partition can be performed only on global indexes.
REBUILD	re-creates an existing index.
	parallel_clause	specifies that rebuilding the index, or some queries against the index or the index partition, are performed either in serial or parallel execution. For information about the syntax of this option and this clause, see the PARALLEL clause. For more information about parallelized operations see Oracle8 Parallel Server Concepts and Administration.
	LOGGING/NOLOGGING	specifies whether ALTER INDEX...REBUILD (and ALTER INDEX...SPLIT) operations will be logged.
	REVERSE	stores the bytes of the index block in reverse order, excluding the ROWID when the index is rebuilt.
	NOREVERSE	stores the bytes of the index block without reversing the order when the index is rebuilt. Rebuilding a REVERSE index without the NOREVERSE keyword produces a rebuilt, reverse keyed index.
	index_physical_attributes_clause	changes the values of the PCTFREE, INITRANS, and MAXTRANS parameters for a nonpartitioned index, index partition, or all partitions of a partitioned index, or default values of these parameters for a partitioned index. See these parameters in CREATE TABLE. Note: You cannot change the value of the PCTFREE parameter for the index as a whole (ALTER INDEX) or to modify a partition (ALTER INDEX ... MODIFY PARTITION). You can change it in all other forms of the ALTER INDEX command.
	storage_clause	changes the storage parameters for a nonpartitioned index, index partition, or all partitions of a partitioned index, or default values of these parameters for a partitioned index. See the STORAGE clause.
	TABLESPACE	specifies the tablespace where the rebuilt index or index partition will be stored. The default is the default tablespace of the user issuing the command.
deallocate_unused_clause	explicitly deallocates unused space at the end of the index and make the freed space available for other segments. Only unused space above the high-water mark can be freed. If KEEP is omitted, all unused space is freed. See the DEALLOCATE UNUSED clause.
	KEEP	specifies the number of bytes above the high-water mark that the index will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent.
allocate_extent_clause	explicitly allocates a new extent for the index.
	SIZE	specifies the size of the extent in bytes. Use K or M to specify the extent size in kilobytes or megabytes. If you omit this parameter, Oracle determines the size based on the values of the index's STORAGE parameters.
	DATAFILE	specifies one of the data files in the index's tablespace to contain the new extent. If you omit this parameter, Oracle chooses the data file.
	INSTANCE	makes the new extent available to the specified instance. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER. If you omit this parameter, the extent is available to all instances. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.
	Explicitly allocating an extent with this clause does affect the size for the next extent to be allocated as specified by the NEXT and PCTINCREASE storage parameters.
LOGGING/NOLOGGING	LOGGING/NOLOGGING specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned index, index partition, or all partitions of a partitioned index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file.
	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 this index, you must take a backup after the operation in NOLOGGING mode.
	If the database is run in ARCHIVELOG mode, media recovery from a backup taken before the an operation in LOGGING mode will re-create the index. However, media recovery from a backup taken before an operation in NOLOGGING mode will not re-create the index.
	An index segment can have logging attributes different from those of the base table and different from those of other index segments for the same base table.
	For more information about the LOGGING option and parallel DML, see Oracle8 Concepts and the Oracle8 Parallel Server Concepts and Administration.
	Note: The LOGGING/NOLOGGING keywords replace the RECOVERABLE/UNRECOVERABLE option. That option is still available as a valid keyword in Oracle8 when altering or rebuilding nonpartitioned indexes, but its use is not recommended.
RENAME TO	renames index to new_index_name. The new_index_name is a single identifier and does not include the schema name.
MODIFY DEFAULT ATTRIBUTES	is a valid option only for a partitioned index. Use this option to specify new values for the default attributes of a partitioned index.
	TABLESPACE	specifies the tablespace where the default tablespace of a partitioned index will be stored. The default is the default tablespace of the user issuing the command.
	LOGGING/NOLOGGING	specifies the default logging attribute of a partitioned index.
Note: You can combine several operations on the base index into one ALTER INDEX statement (except RENAME and REBUILD), but you cannot combine partition operations with other partition operations or with operations on the base index.
MODIFY PARTITION	modifies the real physical attributes, logging option, or storage characteristics of index partition partition_name; partition_name is the name of the index partition to be altered. It must be a partition in index.
UNUSABLE	marks the index or index partition(s) as unusable. An unusable index must be rebuilt, or dropped and re-created, before it can be used. While one partition is marked unusable, the other partitions of the index are still valid; you can execute statements that require the index if the statements do not access the unusable partition. You can also split or rename the unusable partition before rebuilding it.
RENAME PARTITION	renames index partition_name to new_partition_name.
DROP PARTITION	removes a partition and the data in it from a partitioned global index. Dropping a partition of a global index marks the index's next partition as unusable. You cannot drop the highest partition of a global index.
split_partition_clause	splits a global partitioned index into two partitions, adding a new partition to the index. Splitting a partition marked as unusable results in two partitions, both marked as unusable. You must rebuild the partitions before you can use them.
	Splitting a usable partition results in two partitions populated with index data, both marked as usable.
	AT (value_list)	specifies the new noninclusive upper bound for split_partition_1. The value_list must compare less than the presplit partition bound for partition_name_old and greater than the partition bound for the next lowest partition (if there is one).
	INTO	describes the two partitions resulting from the split.
	partition_description, partition_description	specifies the names and physical attributes of the two partitions resulting from the split.
REBUILD PARTITION	rebuilds one partition of an index. You can also use this option to move an index partition to another tablespace or to change a create-time physical attribute. For more information about partition maintenance operations, see the Oracle8 Administrator's Guide.

Examples

Example I

This statement alters SCOTT'S CUSTOMER index so that future data blocks within this index use 5 initial transaction entries and an incremental extent of 100 kilobytes:

ALTER INDEX scott.customer  
    INITRANS 5  
    STORAGE (NEXT 100K); 

Example II

The following example drops index partition IX_ANTARTICA:

ALTER INDEX sales_area_ix
  DROP PARTITION ix_antarctica;

Example III

This statement alters the real attributes of every partition of local partitioned index SALES_IX3. New partitions added in the future will use 5 initial transaction entries and an incremental extent of 100 K:

ALTER INDEX sales_ix3 INITRANS 5 STORAGE ( NEXT 100K );

Example III(a)

This statement alters the default attributes of local partitioned index SALES_IX3. New partitions added in the future will use 5 initial transaction entries and an incremental extent of 100 K:

ALTER INDEX sales_ix3 
  MODIFY DEFAULT ATTRIBUTES INITRANS 5 STORAGE ( NEXT 100K );

Example IV

The following statement marks the IDX_ACCTNO index as UNUSABLE:

ALTER INDEX idx_acctno UNUSABLE;

Example V

The following statement changes the maximum number of extents for partition BRIX_NY:

ALTER INDEX branch_ix MODIFY PARTITION brix_ny  
  STORAGE( MAXEXTENTS 30 ) LOGGING;

Example VI

The following example marks partition IDX_FEB96 of index IDX_ACCTNO as UNUSABLE:

ALTER INDEX idx_acctno MODIFY PARTITION idx_feb96 UNUSABLE;

Example VII

The following statement sets the parallel attributes for index ARTIST_IX:

ALTER INDEX artist_ix PARALLEL (DEGREE 4, INSTANCES 3);

Example VIII

The following statement sets the parallel attributes for index ARTIST_IX so that scans on the index will not be parallelized:

ALTER INDEX artist_ix NOPARALLEL;

Example IX

The following statement rebuilds partition P063 in index ARTIST_IX. The rebuilding of the index partition will not be logged:

ALTER INDEX artist_ix 
  REBUILD PARTITION p063 NOLOGGING;

Example X

The following example renames an index:

ALTER INDEX emp_ix1 RENAME TO employee_ix1;

Example XI

The following example renames an index partition:

ALTER INDEX employee_ix2 RENAME PARTITION emp_ix2_p3 
  TO employee_ix2_p3;

Example XII

The following example splits partition PARTNUM_IX_P6 in partitioned index PARTNUM_IX into PARTNUM_IX_P5 and PARTNUM_IX_P6:

ALTER INDEX partnum_ix
  SPLIT PARTITION partnum_ix_p6 AT ( 5001 )
  INTO ( PARTITION partnum_ix_p5 TABLESPACE ts017 LOGGING, 
         PARTITION partnum_ix_p6 TABLESPACE ts004 );

Note that the second partition retains the name of the old partition.

Example XIII

The following statement rebuilds index EMP_IX so that the bytes of the index block are stored in REVERSE order:

ALTER INDEX emp_ix REBUILD REVERSE;

Related Topics

CREATE INDEX

CREATE TABLE

PARALLEL clause

STORAGE clause

DEALLOCATE UNUSED clause

---

ALTER PACKAGE

Purpose

To recompile a stored package. See also "Recompiling Stored Packages".

Prerequisites

The package must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.

Syntax

Keywords and Parameters

schema	is the schema containing the package. If you omit schema, Oracle assumes the package is in your own schema.
package	is the name of the package to be recompiled.
COMPILE	recompiles the package specification or body. The COMPILE keyword is required.
PACKAGE	recompiles the package body and specification.
BODY	recompiles only the package body.
	The default option is PACKAGE.

Recompiling Stored Packages

You can use the ALTER PACKAGE command to explicitly recompile either a package specification and body or only a package body. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

Because all objects in a package are stored as a unit, the ALTER PACKAGE command recompiles all package objects together. You cannot use the ALTER PROCEDURE command or ALTER FUNCTION command to individually recompile a procedure or function that is part of a package.

Note: This command does not change the declaration or definition of an existing package. To redeclare or redefine a package, use the CREATE PACKAGE or the CREATE PACKAGE BODY command with the OR REPLACE option.

Recompiling Package Specifications

You might want to recompile a package specification to check for compilation errors after modifying the specification. When you issue an ALTER PACKAGE statement with the COMPILE PACKAGE option, Oracle recompiles the package specification and body regardless of whether it is invalid. When you recompile a package specification, Oracle invalidates any local objects that depend on the specification, such as procedures that call procedures or functions in the package. Note that the body of a package also depends on its specification. If you subsequently reference one of these dependent objects without first explicitly recompiling it, Oracle recompiles it implicitly at run time.

Recompiling Package Bodies

You might want to recompile a package body after modifying it. When you issue an ALTER PACKAGE statement with the COMPILE BODY option, Oracle recompiles the package body regardless of whether it is invalid. When you recompile a package body, Oracle first recompiles the objects on which the body depends, if any of those objects are invalid. If Oracle recompiles the body successfully, the body becomes valid. If recompiling the body results in compilation errors, Oracle returns an error and the body remains invalid. You can then debug the body using the predefined package DBMS_OUTPUT. Note that recompiling a package body does not invalidate objects that depend upon the package specification.

For more information on debugging packages, see Oracle8 Application Developer's Guide. For information on how Oracle maintains dependencies among schema objects, including remote objects, see Oracle8 Concepts.

Example I

This statement explicitly recompiles the specification and body of the ACCOUNTING package in the schema BLAIR:

ALTER PACKAGE blair.accounting
COMPILE PACKAGE; 

If Oracle encounters no compilation errors while recompiling the ACCOUNTING specification and body, ACCOUNTING becomes valid. BLAIR can subsequently call or reference all package objects declared in the specification of ACCOUNTING without run-time recompilation. If recompiling ACCOUNTING results in compilation errors, Oracle returns an error message and ACCOUNTING remains invalid.

Oracle also invalidates all objects that depend upon ACCOUNTING. If you subsequently reference one of these objects without explicitly recompiling it first, Oracle recompiles it implicitly at run time.

Example II

To recompile the body of the ACCOUNTING package in the schema BLAIR, issue the following statement:

ALTER PACKAGE blair.accounting 
COMPILE BODY; 

If Oracle encounters no compilation errors while recompiling the package body, the body becomes valid. BLAIR can subsequently call or reference all package objects declared in the specification of ACCOUNTING without run-time recompilation. If recompiling the body results in compilation errors, Oracle returns an error message and the body remains invalid.

Because this statement recompiles the body and not the specification of ACCOUNTING, Oracle does not invalidate dependent objects.

Related Topics

CREATE PACKAGE

CREATE PACKAGE BODY

---

ALTER PROCEDURE

Purpose

To recompile a stand-alone stored procedure. See also "Recompiling Stored Procedures".

Prerequisites

The procedure must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.

Syntax

Keywords and Parameters

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

Recompiling Stored Procedures

The ALTER PROCEDURE command is quite similar to the ALTER FUNCTION command. The following discussion of explicitly recompiling procedures also applies to functions.

You can use the ALTER PROCEDURE command to explicitly recompile a procedure 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 PROCEDURE statement, Oracle recompiles the procedure regardless of whether it is valid or invalid.

You can use the ALTER PROCEDURE command only to recompile a standalone procedure. To recompile a procedure that is part of a package, recompile the entire package using the ALTER PACKAGE command.

When you recompile a procedure, Oracle first recompiles objects upon which the procedure depends, if any of those objects are invalid. Oracle also invalidates any local objects that depend upon the procedure, such as procedures that call the recompiled procedure or package bodies that define procedures that call the recompiled procedure. If Oracle recompiles the procedure successfully, the procedure becomes valid. If recompiling the procedure results in compilation errors, then Oracle returns an error and the procedure remains invalid. You can then debug procedures 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.

Note: This command does not change the declaration or definition of an existing procedure. To redeclare or redefine a procedure, use the CREATE PROCEDURE command with the OR REPLACE option.

Example

To explicitly recompile the procedure CLOSE_ACCT owned by the user HENRY, issue the following statement:

ALTER PROCEDURE henry.close_acct
COMPILE; 

If Oracle encounters no compilation errors while recompiling CLOSE_ACCT, CLOSE_ACCT becomes valid. Oracle can subsequently execute it without recompiling it at run time. If recompiling CLOSE_ACCT results in compilation errors, Oracle returns an error and CLOSE_ACCT remains invalid.

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

Related Topics

ALTER FUNCTION

ALTER PACKAGE

CREATE PROCEDURE

---

ALTER PROFILE

Purpose

To add, modify, or remove a resource limit or password management in a profile. See also "Examples".

Prerequisites

You must have ALTER PROFILE system privilege to change profile resource limits. To modify password limits and protection, you must have ALTER PROFILE and ALTER USER system privileges. See also "Using Password History".

Syntax

Keywords and Parameters

profile	is the name of the profile to be altered.
integer	defines a new limit for a resource in this profile.
	For information on parameter resource limits for ALTER PROFILE, see CREATE PROFILE.
Note: You cannot remove a limit from the DEFAULT profile. You can use fractions of days for all parameters with days as units. Fractions are expressed as x/y. For example, 1 hour is 1/24 and 1 minute is 1/1440.

Using Password History

Changes made to a profile with an ALTER PROFILE statement affect users only in their subsequent sessions, not in their current sessions.

The following restrictions apply when specifying password history parameters:

• If PASSWORD_REUSE_TIME is set to an integer value, PASSWORD_REUSE_MAX must be set to UNLIMITED. If PASSWORD_REUSE_MAX is set to an integer value, PASSWORD_REUSE_TIME must be set to UNLIMITED.
• If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to UNLIMITED, then Oracle uses neither of these password resources.
• If PASSWORD_REUSE_MAX is set to DEFAULT and PASSWORD_REUSE_TIME is set to UNLIMITED, then Oracle uses the PASSWORD_REUSE_MAX value defined in the default profile.
• If PASSWORD_REUSE_TIME is set to DEFAULT and PASSWORD_REUSE_MAX is set to UNLIMITED, then Oracle uses the PASSWORD_REUSE_TIME value defined in the default profile.
• If both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX are set to DEFAULT, then Oracle uses whichever value is defined in the default profile.

Examples

Example I

The following example makes a password unavailable for reuse for 90 days:

ALTER PROFILE prof 
LIMIT PASSWORD_REUSE_TIME 90 
PASSWORD_REUSE_MAX UNLIMITED;

Example II

The following statement defaults the PASSWORD_REUSE_TIME value to its defined value in the DEFAULT profile:

ALTER PROFILE prof 
LIMIT PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX UNLIMITED;

Example III

The following example alters profile PROF with FAILED_LOGIN_ATTEMPTS set to 5 and PASSWORD_LOCK_TIME set to 1:

ALTER PROFILE prof LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1;

This command causes PROF's account to become locked for 1 day after 5 unsuccessful login attempts.

Example IV

The following example modifies profile PROF's PASSWORD_LIFE_TIME to 60 days and PASSWORD_GRACE_TIME to 10 days:

ALTER PROFILE prof LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10;

Example V

This statement defines a new limit of 5 concurrent sessions for the ENGINEER profile:

ALTER PROFILE engineer LIMIT SESSIONS_PER_USER  5; 

If the ENGINEER profile does not currently define a limit for SESSIONS_PER_USER, the above statement adds the limit of 5 to the profile. If the profile already defines a limit, the above statement redefines it to 5. Any user assigned the ENGINEER profile is subsequently limited to 5 concurrent sessions.

Example VI

This statement defines unlimited idle time for the ENGINEER profile:

ALTER PROFILE engineer LIMIT IDLE_TIME UNLIMITED; 

Any user assigned the ENGINEER profile is subsequently permitted unlimited idle time.

Example VII

This statement removes the IDLE_TIME limit from the ENGINEER profile:

ALTER PROFILE engineer LIMIT IDLE_TIME DEFAULT;

Any user assigned the ENGINEER profile is subject in their subsequent sessions to the IDLE_TIME limit defined in the DEFAULT profile.

Example VIII

This statement defines a limit of 2 minutes of idle time for the DEFAULT profile:

ALTER PROFILE default LIMIT IDLE_TIME  2; 

This IDLE_TIME limit applies to these users:

• users who are not explicitly assigned any profile
• users who are explicitly assigned a profile that does not define an IDLE_TIME limit

Related Topics

CREATE PROFILE

---

ALTER RESOURCE COST

Purpose

To specify a formula to calculate the total resource cost used in a session. For any session, this cost is limited by the value of the COMPOSITE_LIMIT parameter in the user's profile. See also "Altering Resource Costs".

Prerequisites

You must have ALTER RESOURCE COST system privilege.

Syntax

Keywords and Parameters

CPU_PER_SESSION	is the amount of CPU time used by a session measured in hundredth of seconds.
CONNECT_TIME	is the amount of CPU time used by a session measured in hundredths of seconds.
CPU_PER_SESSION	is the elapsed time of a session measured in minutes.
LOGICAL_READS_PER_SESSION	is the number of data blocks read during a session including blocks read from both memory and disk.
PRIVATE_SGA	The number of bytes of private space in the system global area (SGA) used by a session. This limit only applies if you are using the multithreaded server architecture and allocating private space in the SGA for your session.
integer	is the weight of each resource.

Altering Resource Costs

The ALTER RESOURCE COST command specifies the formula by which Oracle calculates the total resource cost used in a session. Oracle calculates the total resource cost by multiplying the amount of each resource used in the session by the resource's weight and summing the products for all four resources. Both the products and the total cost are expressed in units called service units.

Although Oracle monitors the use of other resources, only these four can contribute to the total resource cost for a session. For information on all resources, see CREATE PROFILE.

The weight that you assign to each resource determines how much the use of that resource contributes to the total resource cost. Using a resource with a lower weight contributes less to the cost than using a resource with a higher weight. If you do not assign a weight to a resource, the weight defaults to 0 and use of the resource subsequently does not contribute to the cost. The weights you assign apply to all subsequent sessions in the database.

Once you have specified a formula for the total resource cost, you can limit this cost for a session with the COMPOSITE_LIMIT parameter of the CREATE PROFILE command. If a session's cost exceeds the limit, Oracle aborts the session and returns an error. For information on establishing resource limits, see CREATE PROFILE. If you use the ALTER RESOURCE COST command to change the weight assigned to each resource, Oracle uses these new weights to calculate the total resource cost for all current and subsequent sessions.

Example I

The following statement assigns weights to the resources CPU_PER_SESSION and CONNECT_TIME:

ALTER RESOURCE COST 
CPU_PER_SESSION 100
CONNECT_TIME      1; 

The weights establish this cost formula for a session:

T = (100 * CPU) + CON

where:

T	is the total resource cost for the session expressed in service units.
CPU	is the CPU time used by the session measured in hundredths of seconds.
CON	is the elapsed time of a session measured in minutes.

Because the above statement assigns no weight to the resources LOGICAL_READS_PER_SESSION and PRIVATE_SGA, these resources do not appear in the formula.

If a user is assigned a profile with a COMPOSITE_LIMIT value of 500, a session exceeds this limit whenever T exceeds 500. For example, a session using 0.04 seconds of CPU time and 101 minutes of elapsed time exceeds the limit. A session 0.0301 seconds of CPU time and 200 minutes of elapsed time also exceeds the limit.

You can subsequently change the weights with another ALTER RESOURCE statement:

ALTER RESOURCE COST 
LOGICAL_READS_PER_SESSION 2
CONNECT_TIME 0; 

These new weights establish a new cost formula:

T = (100 * CPU) + (2 * LOG)

where:

T CPU	are the same as in the previous formula.
LOG	is the number of data blocks read during the session.

This ALTER RESOURCE COST statement changes the formula in these ways:

• Because the statement omits a weight for the CPU_PER_SESSION resource and the resource was already assigned a weight, the resource remains in the formula with its original weight.
• Because the statement assigns a weight to the LOGICAL_READS_PER_SESSION resource, this resource now appears in the formula.
• Because the statement assigns a weight of 0 to the CONNECT_TIME resource, this resource no longer appears in the formula.
• Because the statement omits a weight for the PRIVATE_SGA resource and the resource was not already assigned a weight, the resource still does not appear in the formula.

Related Topics

CREATE PROFILE

---

ALTER ROLE

Purpose

To change the authorization needed to enable a role. See also "Changing Authorizations".

Prerequisites

You must either have been granted the role with the ADMIN OPTION or have ALTER ANY ROLE system privilege.

Syntax

Keywords and Parameters

The keywords and parameters in the ALTER ROLE command all have the same meaning as in the CREATE ROLE command; see CREATE ROLE.

Changing Authorizations

Before you alter a role to IDENTIFIED GLOBALLY, you must:

• revoke all grants of roles identified externally to the role and
• revoke the grant of the role from all users, roles, and PUBLIC.

The one exception to this rule is that you should not revoke the role from the user who is currently altering the role.

If a user with ALTER ANY ROLE changes a role that is IDENTIFIED GLOBALLY to any of the following, then Oracle grants the role with the ADMIN OPTION:

• to IDENTIFIED BY password
• to IDENTIFIED EXTERNALLY
• to NOT IDENTIFIED

Example I

The following example changes the role ANALYST to IDENTIFIED GLOBALLY:

ALTER ROLE analyst IDENTIFIED GLOBALLY;

Example II

This statement changes the password on the TELLER role to LETTER:

ALTER ROLE teller 
IDENTIFIED BY letter; 

Users granted the TELLER role must subsequently enter the new password "letter" to enable the role.

Related Topics

CREATE ROLE

SET ROLE

---

ALTER ROLLBACK SEGMENT

Purpose

To alter a rollback segment by

• bringing it online
• taking it offline
• changing its storage characteristics
• shrinking it to an optimal or given size

For more information, see "Altering Rollback Segments".

Prerequisites

You must have ALTER ROLLBACK SEGMENT system privilege.

Syntax

storage_clause: See STORAGE clause.

Keywords and Parameters

rollback_segment	specifies the name of an existing rollback segment.
ONLINE	brings the rollback segment online.
OFFLINE	takes the rollback segment offline.
storage_clause	changes the rollback segment's storage characteristics. See the STORAGE clause for syntax and additional information.
SHRINK	attempts to shrink the rollback segment to an optimal or given size.

Altering Rollback Segments

When you create a rollback segment, it is initially offline. An offline rollback segment is not available for transactions.

The ONLINE option brings the rollback segment online, making it available for transactions by your instance. You can also bring a rollback segment online when you start your instance with the initialization parameter ROLLBACK_SEGMENTS.

The OFFLINE option takes the rollback segment offline. If the rollback segment does not contain information necessary to roll back any active transactions, Oracle takes it offline immediately. If the rollback segment does contain information for active transactions, Oracle makes the rollback segment unavailable for future transactions and takes it offline after all the active transactions are committed or rolled back. Once the rollback segment is offline, it can be brought online by any instance.

You cannot take the SYSTEM rollback segment offline.

You can tell whether a rollback segment is online or offline by querying the data dictionary view DBA_ROLLBACK_SEGS. Online rollback segments are indicated by a STATUS value of IN_USE. Offline rollback segments are indicated by a STATUS value of AVAILABLE.

For more information on making rollback segments available and unavailable, see Oracle8 Administrator's Guide.

The STORAGE clause of the ALTER ROLLBACK SEGMENT command affects future space allocation in the rollback segment. You cannot change the values of the INITIAL and MINEXTENTS for an existing rollback segment.

The SHRINK clause of the ALTER ROLLBACK SEGMENT command initiates an attempt to reduce the specified rollback segment to an optimum size. If size is not specified, then the size defaults to the OPTIMAL value of the STORAGE clause of the CREATE ROLLBACK SEGMENT command that created the rollback segment. If the OPTIMAL value was not specified, then the size defaults to the MINEXTENTS value of the STORAGE clause of the CREATE ROLLBACK SEGMENT command. The specified size in a SHRINK clause is valid for the execution of the command; thereafter, OPTIMAL reverts to the OPTIMAL value of the CREATE ROLLBACK SEGMENT command. Regardless of whether a size is specified or not, the rollback segment cannot shrink to less than two extents.

You can query the DBA_ROLLBACK_SEGS view to determine the actual size of a rollback segment after attempting to shrink a rollback segment.

For a parallel server, you can shrink only rollback segments that are online to your instance.

The SHRINK option is an attempt to shrink the size of the rollback segment; the success and amount of shrinkage depends on the following:

• available free space in the rollback segment
• how active transactions are holding space in the rollback segment

Example I

This statement brings the rollback segment RSONE online:

ALTER ROLLBACK SEGMENT rsone ONLINE; 

Example II

This statement changes the STORAGE parameters for RSONE:

ALTER ROLLBACK SEGMENT rsone 
STORAGE (NEXT 1000 MAXEXTENTS 20); 

Example III

This statement attempts to resize a rollback segment to an optimum size of 100 megabytes:

ALTER ROLLBACK SEGMENT rsone 
SHRINK TO 100 M;

Related Topics

CREATE ROLLBACK SEGMENT

CREATE TABLESPACE

STORAGE clause

---

ALTER SEQUENCE

Purpose

To change the sequence by

• changing the increment between future sequence values
• setting or eliminating the minimum or maximum value
• changing the number of cached sequence numbers
• specifying whether the sequence continues to generate numbers after reaching the maximum or minimum value
• specifying whether sequence numbers must be ordered

For illustrations of some of these purposes, see "Examples".

Prerequisites

The sequence must be in your own schema or you must have ALTER privilege on the sequence or you must have ALTER ANY SEQUENCE system privilege.

Syntax

Keywords and Parameters

The keywords and parameters in this command serve the same purpose that they do in CREATE SEQUENCE.

Note:

• The sequence must be dropped and re-created to restart the sequence at a different number. Only future sequence numbers are affected by the ALTER SEQUENCE command.
• Some validations are performed. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.

Examples

Example I

This statement sets a new maximum value for the ESEQ sequence:

ALTER SEQUENCE eseq 
MAXVALUE 1500; 

Example II

This statement turns on CYCLE and CACHE for the ESEQ sequence:

ALTER SEQUENCE eseq 
CYCLE
CACHE 5; 

Related Topics

CREATE SEQUENCE

DROP SEQUENCE

---

ALTER SESSION

Purpose

To alter your current session in one of the following ways:

• enable or disable the SQL trace facility
• enable or disable global name resolution
• change the values of NLS parameters
• specify the size of the cache used to hold frequently used cursors
• enable or disable the closing of cached cursors on COMMIT or ROLLBACK
• in a parallel server, indicate that the session must access database files as if the session were connected to another instance
• enable, disable, and change the behavior of hash join operations
• change the handling of remote procedure call dependencies
• change transaction level handling
• close a database link
• send advice to remote databases to force an in-doubt distributed transaction
• permit or prohibit stored procedures and functions from issuing COMMIT and ROLLBACK statements
• change the goal of the cost-based optimization approach
• in a parallel server, enable DML statements to be considered for parallel execution
• to insert, update, or delete from tables with indexes or index partitions marked as unusable
• allow deferrable constraints to be checked either immediately following every DML statement or at the end of a transaction

Prerequisites

To enable and disable the SQL trace facility or to change the default label format, you must have ALTER SESSION system privilege.

To perform the other operations of this command, you do not need any privileges.

Syntax

Keywords and Parameters

ADVISE	sends advice to a remote database to force a distributed transaction. This advice appears on the remote database in the ADVICE column of the DBA_2PC_PENDING data dictionary view in the event of an in-doubt distributed transaction. (See also "Forcing In-Doubt Distributed Transactions".)The following are advice options:
	COMMIT	places the value 'C' in DBA_2PC_PENDING.ADVICE.
	ROLLBACK	places the value 'R' in DBA_2PC_PENDING.ADVICE.
	NOTHING	places the value ' ' in DBA_2PC_PENDING.ADVICE.
CLOSE DATABASE LINK	closes the database link dblink, eliminating your session's connection to the remote database. The database link cannot be currently in use by an active transaction or an open cursor. For more information, see "Closing Database Links".
COMMIT IN PROCEDURE	ENABLE	permits procedures and stored functions to issue these statements.
	DISABLE	prohibits procedures and stored functions from issuing these statements.
	See also "Transaction Control in Procedures and Stored Functions".
PARALLEL DML	specifies whether all subsequent DML transactions in the session will be considered for parallel execution. (See also "Parallel DML".)
	You can execute this option only between committed transactions. Uncommitted transactions must either be committed or rolled back prior to executing this command.
	ENABLE	executes the session's DML statements in parallel mode if a parallel hint or a parallel clause is specified.
	DISABLE	executes the session's DML statements serially. This is the default mode.
	FORCE	forces parallel execution of subsequent DML statements in the session if none of the parallel DML restrictions are violated. If no parallel clause or hint is specified, then a default level of parallelism (for both degree and instances) is used. Note: Using FORCE automatically causes all tables created in this session to be created with a default level of parallelism. The effect is the same as if you had specified the parallel clause (with default degree and default instances) with the CREATE TABLE statement.
SET	sets the session parameters that follow.
CLOSE_OPEN_CACHED_CURSORS	controls whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT or ROLLBACK.
	TRUE	causes open cursors to be closed at each COMMIT or ROLLBACK.
	FALSE	signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor.
CONSTRAINT[S]	determines when conditions specified by a deferrable constraint are enforced.
	IMMEDIATE	indicates that the conditions specified by the deferrable constraint are checked immediately after each DML statement; equivalent to issuing the SET CONSTRAINTS ALL IMMEDIATE command at the beginning of each transaction in your session. See the IMMEDIATE parameter of SET CONSTRAINT(S).
	DEFERRED	indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed; equivalent to issuing the SET CONSTRAINTS ALL DEFERRED command at the beginning of each transaction in your session. See the DEFERRED parameter of SET CONSTRAINT(S).
	DEFAULT	restores all constraints at the beginning of each transaction to their initial state of DEFERRED or IMMEDIATE.
FLAGGER	specifies FIPS flagging. See also "FIPS Flagging".
	ENTRY	flags for SQL92 Entry level.
	INTERMEDIATE	flags for SQL92 Intermediate level.
	FULL	flags for SQL92 Full level.
	OFF	turns off flagging
GLOBAL_NAMES	controls the enforcement of global name resolution for your session. For information on enabling and disabling global name resolution with this parameter, see ALTER SYSTEM.
	TRUE	enables global name resolution.
	FALSE	disables global name resolution.
HASH_JOIN_ENABLED	enables or disables the use of the hash join operation in queries. The default is TRUE, which enables hash joins.
HASH_AREA_SIZE	specifies in bytes the amount of memory to use for hash join operations. The default is twice the value of the SORT_AREA_SIZE initialization parameter.
HASH_MULTIBLOCK_IO_COUNT	specifies the number of data blocks to read and write during a hash join operation. The value multiplied by the DB_BLOCK_SIZE initialization parameter should not exceed 64 K. The default value for this parameter is 1. If the multithreaded server is used, the value is always 1, and any value given here is ignored.
INSTANCE	in a parallel server, accesses database files as if the session were connected to the instance specified by integer. For more information, see "Accessing the Database as if Connected to Another Instance in a Parallel Server".
ISOLATION_LEVEL	specifies how transactions containing database modifications are handled.
	SERIALIZABLE	Transactions in the session use the serializable transaction isolation mode as specified in SQL92. That is, if a serializable transaction attempts to execute a DML statement that updates rows that are updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates. The COMPATIBLE initialization parameter must be set to 7.3.0 or higher for SERIALIZABLE mode to work.
	READ COMMITTED	Transactions in the session will use the default Oracle transaction behavior. Thus, if the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.
MAX_DUMP_FILE_SIZE	specifies the upper limit of trace dump file size. Specify the maximum size as either a nonnegative integer that represents the number of blocks, or as 'UNLIMITED'. If 'UNLIMITED' is specified, no upper limit is imposed.
For more information on the following NLS parameters, see "Using NLS Parameters".
NLS_LANGUAGE	changes the language in which Oracle returns errors and other messages. This parameter also implicitly specifies new values for these items:
	language for day and month names and abbreviations and spelled values of other elements sort sequences B.C. and A.D. indicators A.M. and P.M. meridian indicators
NLS_TERRITORY	implicitly specifies new values for these items:
	default date format decimal character and group separators local currency symbol ISO currency symbol first day of the week for D date format element
NLS_DATE_FORMAT	explicitly specifies a new default date format. The 'fmt' value must be a date format model as specified in the section "Date Format Models".
NLS_DATE_LANGUAGE	explicitly changes the language for day and month names and abbreviations and spelled values of other date format elements.
NLS_NUMERIC_CHARACTERS	explicitly specifies a new decimal character and group separator. The 'text' value must have this form: dg' where: d is the new decimal character, and g is the new group separator.
	The decimal character and the group separator must be two different single-byte characters, and cannot be a numeric value or any of the following characters: "+" plus, "-" minus (or hyphen), "<" less-than, or ">" greater-than.
NLS_ISO_CURRENCY	explicitly specifies the territory whose ISO currency symbol should be used.
NLS_CURRENCY	explicitly specifies a new local currency symbol. The symbol cannot exceed 10 characters.
NLS_SORT	changes the sequence into which Oracle sorts character values.
	sort	specifies the name of a linguistic sort sequence.
	BINARY	specifies a binary sort.
	The default sort for all character sets is binary.
NLS_CALENDAR	explicitly specifies a new calendar type.
OPTIMIZER_MODE	specifies the approach and mode of the optimizer for your session. For more information on optimizer mode, see "Changing the Optimization Approach and Mode".
	ALL_ROWS	specifies the cost-based approach and optimizes for best throughput.
	FIRST_ROWS	specifies the cost-based approach and optimizes for best response time.
	RULE	specifies the rule-based approach.
	CHOOSE	causes the optimizer to choose an optimization approach based on the presence of statistics in the data dictionary.
PARTITION_VIEW_ENABLED	When set to TRUE, this parameter causes the optimizer to skip unnecessary table accesses in a partition view. For more information, see Oracle8 Reference.
PLSQL_V2_COMPATABILITY	modifies the compile-time behavior of PL/SQL programs to allow language constructs that are illegal in Oracle8 (PL/SQL V3), but were legal in Oracle7 (PL/SQL V2). See the PL/SQL User's Guide and Reference and Oracle8 Reference for more information about this session parameter.
	TRUE	enables Oracle8 PL/SQL V3 programs to execute Oracle7 PL/SQL V2 constructs.
	FALSE	disallows illegal Oracle7 PL/SQL V2 constructs. This is the default.
REMOTE_DEPENDENCIES_MODE	specifies how dependencies of remote stored procedures are handled by the session. For more information, refer Oracle8 Application Developer's Guide.
SESSION_CACHED_CURSORS	specifies the size of the session cache for holding frequently used cursors. integer specifies how many cursors can be retained in the cache. For more information on this parameter, see "Caching Session Cursors".
SKIP_UNUSABLE_INDEXES
	controls the use and reporting of tables with unusable indexes or index partitions.
	TRUE	disables error reporting of indexes marked as unusable. Allows inserts, deletes, and updates to tables with unusable indexes or index partitions.
	FALSE	enables error reporting of indexes marked as unusable. Does not allow inserts, deletes, and updates to tables with unusable indexes or index partitions. This is the default.
SQL_TRACE	controls the SQL trace facility for your session. See also "Enabling and Disabling the SQL Trace Facility".
	TRUE	enables the SQL trace facility.
	FALSE	disables the SQL trace facility.

Enabling and Disabling the SQL Trace Facility

The SQL trace facility generates performance statistics for the processing of SQL statements. You can enable and disable the SQL trace facility for all sessions on an Oracle instance with the initialization parameter SQL_TRACE. When you begin a session, Oracle enables or disables the SQL trace facility based on the value of this parameter. You can subsequently enable or disable the SQL trace facility for your own session with the SQL_TRACE option of the ALTER SESSION command.

For more information on the SQL trace facility, including how to format and interpret its output, see Oracle8 Tuning.

Example I

To enable the SQL trace facility for your session, issue the following statement:

ALTER SESSION 
SET SQL_TRACE = TRUE; 

Using NLS Parameters

Oracle contains support for use in different nations and with different languages. When you start an instance, Oracle establishes support based on the values of initialization parameters that begin with "NLS". For information on these parameters, see Oracle8 Reference. You use the NLS clauses of the ALTER SESSION command to change NLS characteristics dynamically for your session. You can query the dynamic performance table V$NLS_PARAMETERS to see the current NLS attributes for your session. The sections that follow describe the use of specific NLS parameters.

Language for Error Messages

You can specify a new language for error messages with the NLS_LANGUAGE parameter. Note that this parameter also implicitly changes other language-related items. Oracle provides error messages in a wide range of languages on many platforms.

Example II

The following statement changes the language for error messages to the French:

ALTER SESSION 
  SET NLS_LANGUAGE = French 

Oracle returns error messages in French:

SELECT * FROM emp
ORA-00942: Table ou vue n'existe pas 







Note:



The language you select must already have been installed. Refer to your operating system specific installation instructions.

  

Default Date Format

You can specify a new default date format either explicitly with the NLS_DATE_FORMAT parameter or implicitly with the NLS_TERRITORY parameter. For information on the default date format models, see the section "Date Format Models".

Example III

The following statement dynamically changes the default date format for your session to 'YYYY MM DD-HH24:MI:SS':

ALTER SESSION 
SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'

Oracle uses the new default date format:

SELECT TO_CHAR(SYSDATE) Today
FROM DUAL 
TODAY 
------------------- 
1997 08 12 14:25:56 

Language for Months and Days

You can specify a new language for names and abbreviations of months and days either explicitly with the NLS_DATE_LANGUAGE parameter or implicitly with the NLS_LANGUAGE parameter.

Example IV

The following statement changes the language for date format elements to the French:

ALTER SESSION 
SET NLS_DATE_LANGUAGE = French 

SELECT TO_CHAR(SYSDATE, 'Day DD Month YYYY') Today
FROM DUAL 

TODAY 
--------------------------- 
Mardi    28 Février   1997 

Decimal Character and Group Separator

You can specify new values for these number format elements either explicitly with the NLS_NUMERIC_CHARACTERS parameter or implicitly with the NLS_TERRITORY parameter:

D (decimal character)	is the character that separates the integer and decimal portions of a number.
G (group separator)	is the character that separates groups of digits in the integer portion of a number.

For information on how to use number format models, see "Number Format Models".

The decimal character and the group separator must be single-byte character and cannot be the same character. If the decimal character is not a period (.), you must use single quotation marks to enclose to enclose all number values that appear in expressions in your SQL statements. When not using a period for the decimal point, you should always use the TO_NUMBER function to ensure that a valid number is retrieved.

Example V

The following statement dynamically changes the decimal character to comma (,) and the group separator to period (.):

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.' ;

Oracle returns these new characters when you use their number format elements:

SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp ;

TOTAL 
-------------
FF29.025,00 

ISO Currency Symbol

You can specify a new value for the C number format element (the ISO currency symbol) either explicitly with the NLS_ISO_CURRENCY parameter or implicitly with the NLS_TERRITORY parameter. The value that you specify for these parameters is a territory whose ISO currency symbol becomes the value of the C number format element.

Example VI

The following statement dynamically changes the ISO currency symbol to the ISO currency symbol for the territory America:

ALTER SESSION
SET NLS_ISO_CURRENCY = America; 

SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total
FROM emp; 

TOTAL 
-------------
USD29,025.00 

Local Currency Symbol

You can specify a new value for the L number format element, (the local currency symbol) either explicitly with the NLS_CURRENCY parameter or implicitly with the NLS_TERRITORY parameter.

Example VII

The following statement dynamically changes the local currency symbol to 'DM':

ALTER SESSION
SET NLS_CURRENCY = 'DM'; 

SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total
FROM emp; 

TOTAL 
-------------
DM29.025,00 

Linguistic Sort Sequence

You can specify a new linguistic sort sequence or a binary sort either explicitly with the NLS_SORT parameter or implicitly with the NLS_LANGUAGE parameter.

Example VIII

The following statement dynamically changes the linguistic sort sequence to Spanish:

ALTER SESSION
SET NLS_SORT = XSpanish; 

Oracle sorts character values based on their position in the Spanish linguistic sort sequence.

Changing the Optimization Approach and Mode

The Oracle optimizer can use either of these approaches to optimize a SQL statement:

cost-based	The optimizer optimizes a SQL statement by considering statistics describing the tables, indexes, and clusters accessed by the statement as well as the information considered with the rule-based approach.
rule-based	The optimizer optimizes a SQL statement based on the indexes and clusters associated with the accessed tables, the syntactic constructs of the statement, and a heuristically ranked list of these constructs.

With the cost-based approach, the optimizer can optimize a SQL statement with one of these goals:

best throughput	is the minimal time necessary to return all rows accessed by the statement.
best response time	is the minimal time necessary to return the first row accessed by the statement.

When you start your instance, the optimization approach is established by the initialization parameter OPTIMIZER_MODE. If this parameter establishes the cost-based approach, the default goal is best throughput.

For information on how to choose a goal for the cost-based approach based on the characteristics of your application, see the Oracle8 Tuning.

FIPS Flagging

FIPS flagging causes an error message to be generated when a SQL statement is issued that is an extension of ANSI SQL92. In Oracle, there is currently no difference between Entry, Intermediate, or Full level flagging. Once flagging is set in a session, a subsequent ALTER SESSION SET FLAGGER command will work, but generates the message, ORA-00097. This allows FIPS flagging to be altered without disconnecting the session.

Caching Session Cursors

If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the session cursors can affect performance. The ALTER SESSION SET SESSION_CACHED_CURSORS command allows frequently used session cursors to be stored in a session cache even if they are closed. This is particularly useful for some Oracle tools. For example, Oracle Forms applications close all session cursors associated with a form when switching to another form; in this case, frequently used cursors would not have to be reparsed.

Oracle uses the shared SQL area to determine whether more than three parse requests were issued on a given statement. If so, Oracle moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session will find the cursor in the session cursor cache.

Session cursors are cached automatically if the initialization parameter SESSION_CACHED_CURSORS is set to a positive value. This parameter specifies the maximum number of session cursors to be kept in the cache. A least recently used algorithm ages out entries in the cache to make room for new entries when needed. You use the ALTER SESSION SET SESSION_CACHED_CURSORS command to dynamically enable session cursor caching.

For more information on session cursor caching, see Oracle8 Tuning.

Accessing the Database as if Connected to Another Instance in a Parallel Server

For optimum performance, each instance of a parallel server uses its own private rollback segments, freelist groups, and so on. A database is usually designed for a parallel server so that users connect to a particular instance and access data that is partitioned primarily for their use. If the users for that instance must connect to another instance, the data partitioning can be lost. The ALTER SESSION SET INSTANCE command allows users to access an instance as if they were connected to their usual instance.

Closing Database Links

A database link allows you to access a remote database in DELETE, INSERT, LOCK TABLE, SELECT, and UPDATE statements. When you issue a statement that uses a database link, Oracle creates a session for you on the remote database using the database link. The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter OPEN_LINKS.

You can use the CLOSE DATABASE LINK clause of the ALTER SESSION command to close a database link explicitly if you do not plan to use it again in your session. You may want to close a database link explicitly if the network overhead associated with leaving it open is costly. Before closing a database link, you must first close all cursors that use the link and then end your current transaction if it uses the link.

Example

This example updates the employee table on the SALES database using a database link, commits the transaction, and explicitly closes the database link:

UPDATE emp@sales 
SET sal = sal + 200
WHERE empno = 9001;
COMMIT; 
ALTER SESSION
CLOSE DATABASE LINK sales; 

Forcing In-Doubt Distributed Transactions

If a network or machine failure occurs during the commit process for a distributed transaction, the state of the transaction may be unknown or in doubt. The transaction can be manually committed or rolled back on each database involved in the transaction with the FORCE clause of the COMMIT or ROLLBACK commands.

Before committing a distributed transaction, you can use the ADVISE clause of the ALTER SESSION command to send advice to a remote database in the event a distributed transaction becomes in doubt. If the transaction becomes in doubt, the advice appears in the ADVICE column of the DBA_2PC_PENDING view on the remote database. The administrator of that database can then use this advice to decide whether to commit or roll back the transaction on the remote database. For more information on distributed transactions and how to decide whether to commit or roll back in-doubt distributed transactions, see Oracle8 Distributed Database Systems.

You issue multiple ALTER SESSION statements with the ADVISE clause in a single transaction. Each such statement sends advice to the databases referenced in the following statements in the transaction until another such statement is issued. This allows you to send different advice to different databases.

Example

This transaction inserts an employee record into the EMP table on the database identified by the database link SITE1 and deletes an employee record from the EMP table on the database identified by SITE2:

ALTER SESSION
ADVISE COMMIT 

INSERT INTO emp@site1
VALUES (8002, 'FERNANDEZ', 'ANALYST', 7566,
TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 3000, NULL, 20) 

ALTER SESSION
ADVISE ROLLBACK; 
DELETE FROM emp@site2
WHERE empno = 8002; 
COMMIT; 

This transaction has two ALTER SESSION statements with the ADVISE clause. If the transaction becomes in-doubt, SITE1 is sent the advice 'COMMIT' by virtue of the first ALTER SESSION statement and SITE2 is sent the advice 'ROLLBACK' by virtue of the second.

Transaction Control in Procedures and Stored Functions

Procedures and stored functions are written in PL/SQL, and they can issue COMMIT and ROLLBACK statements. If your application performs record management that would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, you may want to prevent procedures and stored functions called during your session from issuing these statements. You can do this with the following statement:

ALTER SESSION DISABLE COMMIT IN PROCEDURE;

If you subsequently call a procedure or a stored function that issues a COMMIT or ROLLBACK statement, Oracle returns an error and does not commit or roll back the transaction.

You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the following statement:

ALTER SESSION ENABLE COMMIT IN PROCEDURE; 

This command does not apply to database triggers. Triggers can never issue COMMIT or ROLLBACK statements.

Note: Some applications (such as SQL*Forms) automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation.

Parallel DML

When parallel DML is enabled for your session, all DML portions of statements issued are considered for parallel execution. Even with parallel DML enabled, however, some DML operations are restricted from parallelization, while others may still execute serially unless parallel hints and clauses are specified. For a detailed description of parallel DML features and hints, see Oracle8 Tuning.

The following restrictions apply to parallel DML operations:

• DML operations on clustered tables are not parallelized.
• DML operations with embedded functions that either write or read database or package states are not parallelized.
• DML operations on tables with triggers that could fire are not parallelized.
• DML operations on tables or schema objects containing object types, LONGs, or LOB datatypes are not parallelized.

Parallel DML mode can be modified only between committed transactions. Issuing this command following an uncommitted transaction will generate an error. Uncommitted transactions must be either committed or rolled back prior to issuing the ALTER SESSION ENABLE|DISABLE|FORCE PARALLEL DML command.

Example I

Issue the following statement to enable parallel DML mode for the current session:

ALTER SESSION ENABLE PARALLEL DML;

Example II

The following example modifies the current session to check all deferrable constraints immediately following each DML statement:

ALTER SESSION SET CONSTRAINTS IMMEDIATE;

Example III

The following statement modifies the current session to allow inserts into local index partitions marked as unusable:

ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;

Related Topics

ALTER SESSION

SET CONSTRAINT(S)

PL/SQL User's Guide and Reference
Oracle8 Reference
Oracle8 Tuning

---

ALTER SNAPSHOT

Purpose

To alter a snapshot in one of the following ways:

• changing its storage characteristics
• changing its automatic refresh mode and times

For illustrations of some of these purposes, see "Examples".

For more information on snapshots, including refreshing snapshots, see CREATE SNAPSHOT.

Prerequisites

To alter a snapshot's storage parameters, the snapshot must be contained in your own schema, or you must have the ALTER ANY SNAPSHOT system privilege.

For detailed information about the prerequisites for ALTER SNAPSHOT, see Oracle8 Replication.

Syntax

parallel_clause: See the PARALLEL clause

storage_clause: See the STORAGE clause

For the syntax of the following clauses, see ALTER TABLE:

• physical_attributes_clause
• LOB_storage_clause
• modify_LOB_storage_clause
• modify_partition_clause
• move_partition_clause
• add_partition_clause
• split_partition_clause
• modify_default_attributes_clause
• rename_partition_clause

Keywords and Parameters

schema	is the schema containing the snapshot. If you omit schema, Oracle assumes the snapshot is in your own schema.
snapshot	is the name of the snapshot to be altered.
modify_default_attributes	specifies new values for the default attributes of a partitioned table. For information about specifying the parameters of this clause, see ALTER TABLE.
physical_attributes_clause	change the values of the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and the storage characteristics for the internal table that Oracle uses to maintain the snapshot's data. For more information, see CREATE TABLE and the STORAGE clause.
LOGGING/NOLOGGING	specifies the logging attribute. For information about specifying this option, see ALTER TABLE.
CACHE/NOCACHE	for data that will be accessed frequently, specifies whether the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. For information about specifying this option, see ALTER TABLE.
LOB_storage_clause	specifies the LOB storage characteristics. For information about specifying the parameters of this clause, see ALTER TABLE.
modify_LOB_storage_clause	modifies the physical attributes of the LOB attribute lob_item or LOB object attribute. For information about specifying the parameters of this clause, see ALTER TABLE.
For more information on the following partitioning clauses, see "Partitioned Snapshots".
modify_partition_clause	modifies the real physical attributes of a table partition. For information about specifying the parameters of this clause, see ALTER TABLE.
move_partition_clause	moves table partition partition_name to another segment. For information about specifying the parameters of this clause, see ALTER TABLE.
add_partition_clause	adds a new partition new_partition_name to the "high" end of a partitioned table. For information about specifying the parameters of this clause, see ALTER TABLE.
split_partition_clause	creates two new partitions, each with a new segment and new physical attributes, and new initial extents. For information about specifying the parameters of this clause, see ALTER TABLE.
rename_partition_clause	renames table partition partition_name to new_partition_name. For information about specifying the parameters of this clause, see ALTER TABLE.
parallel_clause	specifies the degree of parallelism for the snapshot. See the PARALLEL clause on page 4-1022. When this clause is set for master tables, performance for snapshot creation and refresh may improve (depending on the snapshot definition query).
MODIFY PARTITION UNUSABLE LOCAL INDEXES
	marks all the local index partitions associated with partition_name as unusable.
MODIFY PARTITION REBUILD UNUSABLE LOCAL INDEXES
	rebuilds the unusable local index partitions associated with partition_name.
USING INDEX	changes the value of INITRANS, MAXTRANS, and STORAGE parameters for the index Oracle uses to maintain the snapshot's data. If USING INDEX is not specified then default values are used for the index.
REFRESH	changes the mode and times for automatic refreshes.
	FAST	specifies a fast refresh, or a refresh using the snapshot log associated with the master table.
	COMPLETE	specifies a complete refresh, or a refresh that re-creates the snapshot during each refresh.
	FORCE	specifies a fast refresh if one is possible or complete refresh if a fast refresh is not possible. Oracle decides whether a fast refresh is possible at refresh time.
	If you omit the FAST, COMPLETE, and FORCE options, Oracle uses FORCE by default.
	START WITH	specifies a date expression for the next automatic refresh time.
	NEXT	specifies a new date expression for calculating the interval between automatic refreshes.
	START WITH and NEXT values must evaluate to times in the future.
	WITH PRIMARY KEY	changes a ROWID snapshot to a primary key snapshot. Primary key snapshots allow snapshot master tables to be reorganized without impacting the snapshot's ability to continue to fast refresh. The master table must contain an enabled primary key constraint. See also "Primary Key Snapshots".
USING MASTER ROLLBACK SEGMENT	changes remote master rollback segment to be used during snapshot refresh; rollback_segment is the name of the rollback segment to be used. (To change the local snapshot rollback segment, use the DBMS_REFRESH package in Oracle8 Replication.) See also "Specifying Rollback Segments",
	DEFAULT	specifies that Oracle will choose which rollback segment to use. If you specify DEFAULT, you cannot specify rollback_segment.
	MASTER	specifies the remote rollback segment to be used at the remote master for the individual snapshot.
	LOCAL	specifies the remote rollback segment to be used for the local refresh group that contains the snapshot.

Examples

Example I

The following statement changes the automatic refresh mode for the HQ_EMP snapshot to FAST:

ALTER SNAPSHOT hq_emp
REFRESH FAST; 

The next automatic refresh of the snapshot will be a fast refresh provided it is a simple snapshot and its master table has a snapshot log that was created before the snapshot was created or last refreshed.

Because the REFRESH clause does not specify START WITH or NEXT values, the refresh intervals established by the REFRESH clause when the HQ_EMP snapshot was created or last altered are still used.

Example II

The following statement stores a new interval between automatic refreshes for the BRANCH_EMP snapshot:

ALTER SNAPSHOT branch_emp
REFRESH NEXT SYSDATE+7;

Because the REFRESH clause does not specify a START WITH value, the next automatic refresh occurs at the time established by the START WITH and NEXT values specified when the BRANCH_EMP snapshot was created or last altered.

At the time of the next automatic refresh, Oracle refreshes the snapshot, evaluates the NEXT expression SYSDATE+7 to determine the next automatic refresh time, and continues to refresh the snapshot automatically once a week.

Because the REFRESH clause does not explicitly specify a refresh mode, Oracle continues to use the refresh mode specified by the REFRESH clause of a previous CREATE SNAPSHOT or ALTER SNAPSHOT statement.

Example III

The following statement specifies a new refresh mode, next refresh time, and new interval between automatic refreshes of the SF_EMP snapshot:

ALTER SNAPSHOT sf_emp
REFRESH COMPLETE   
START WITH TRUNC(SYSDATE+1) + 9/24  
NEXT SYSDATE+7;

The START WITH value establishes the next automatic refresh for the snapshot to be 9:00 am tomorrow. At that point, Oracle performs a fast refresh of the snapshot, evaluates the NEXT expression, and subsequently refreshes the snapshot every week.

Specifying Rollback Segments

You can specify the rollback segments to be used during a refresh for both the master site and the local site. The master rollback segment is stored on a per-snapshot basis and is validated during snapshot creation and refresh. If the snapshot is complex, the master rollback segment, if specified, is ignored.

You can change local snapshot rollback segments using the DBMS_REFRESH package and is stored at the refresh group level. For information about the DBMS_REFRESH package, see Oracle8 Replication. If the auto-refresh parameters (START WITH and NEXT) are specified, a new refresh group is automatically created to refresh the snapshot with a background process. The local rollback segment, if specified, is associated with this new refresh group. An error is raised if the auto-refresh parameters are not specified, but a local rollback segment is.

Note: To direct Oracle to select the rollback segment automatically after one has been specified using CREATE SNAPSHOT or ALTER SNAPSHOT, specify the DEFAULT option with ALTER SNAPSHOT.

Example I

The following example changes the remote master rollback segment used during snapshot refresh to MASTER_SEG:

ALTER SNAPSHOT inventory 
  REFRESH USING MASTER ROLLBACK SEGMENT master_seg;  

Example II

The following example changes the remote master rollback segment used during snapshot refresh to one chosen by Oracle:

ALTER SNAPSHOT sales REFRESH USING DEFAULT MASTER ROLLBACK SEGMENT; 

Primary Key Snapshots

To change a ROWID snapshot to a primary key snapshot you must:

• include all columns of the primary key in the snapshot definition
• have an enabled primary key constraint defined on the snapshot master
• ensure that the updatable snapshot log is empty for all updatable snapshots

To fast refresh primary key snapshots you must first create a snapshot master log specifying WITH PRIMARY KEY. The snapshot master log can also store ROWIDs. The snapshot master log must be created before the snapshot is created in order for the snapshots to use the log to fast refresh.

For detailed information about primary key snapshots, see Oracle8 Replication.

Note: Primary key snapshots cannot be altered to ROWID snapshots. You must drop the primary key snapshot and re-create it as a ROWID snapshot.

Example I

The following example changes a ROWID to a primary key snapshot:

ALTER SNAPSHOT emp_rs REFRESH WITH PRIMARY KEY; 

Partitioned Snapshots

Partitioned snapshots are the same as partitioned tables because snapshots are basically tables. The options have the same syntax and semantics as the partitioned table options for CREATE TABLE and ALTER TABLE. The only difference is that the following operations are not allowed on snapshots and snapshot logs:

• DROP PARTITION
• TRUNCATE PARTITION
• EXCHANGE PARTITION

You cannot perform bulk deletions by dropping or truncating partitions on master tables. Thus, after dropping or truncating a partition, all snapshots must be refreshed manually. A fast refresh will probably produce incorrect results, but Oracle will not raise an error.

Related Topics

CREATE SNAPSHOT

DROP SNAPSHOT

STORAGE clause

---

ALTER SNAPSHOT LOG

Purpose

Changes the storage characteristics of a snapshot log. For more information on snapshot logs, see CREATE SNAPSHOT.

Prerequisites

Only the owner of the master table or a user with the SELECT privilege for the master table can alter a snapshot log. For detailed information about the prerequisites for ALTER SNAPSHOT LOG, see Oracle8 Replication.

Syntax

For the syntax of the following clauses, see ALTER TABLE:

• physical_attributes_clause
• modify_partition_clause
• rename_partition_clause
• move_partition_clause
• add_partition_clause
• split_partition_clause
• modify_default_attributes

Keywords and Parameters

schema	is the schema containing the master table. If you omit schema, Oracle assumes the snapshot log is in your own schema.
table	is the name of the master table associated with the snapshot log to be altered.
physical_attributes_clause	changes the value of PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters for the table, partition, the overflow data segment, or the default characteristics of a partitioned table. See the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of CREATE TABLE. See the example under "Modifying Physical Attributes".
rename_partition_clause	renames table partition partition_name to new_partition_name. For information about specifying the parameters of this clause, see ALTER TABLE.
modify_partition_clause	modifies the real physical attributes of a table partition. For information about specifying the parameters of this clause, see ALTER TABLE.
move_partition_clause	moves table partition partition_name to another segment. For information about specifying the parameters of this clause, see ALTER TABLE.
add_partition_clause	adds a new partition new_partition_name to the "high" end of a partitioned table. For information about specifying the parameters of this clause, see ALTER TABLE.
split_partition_clause	creates two new partitions, each with a new segment and new physical attributes, and new initial extents. For information about specifying the parameters of this clause, see ALTER TABLE. For more information see "Partitioned Snapshot Logs".
modify_default_attributes_clause	is a valid option only for a partitioned index. Use this option to specify new values for the default attributes of a partitioned index.
parallel_clause	specifies the degree of parallelism for the snapshot. See the PARALLEL clause. When this clause is set for master tables, performance during snapshot creation and refresh may improve (depending on the snapshot definition query).
LOGGING/NOLOGGING	specifies the logging attribute. For information about specifying this option, see ALTER TABLE.
CACHE/NOCACHE	for data that will be accessed frequently, specifies whether the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. For information about specifying this option, see ALTER TABLE.
ADD	changes the snapshot log so that it records the primary key values or ROWID values when rows in the snapshot master table are updated. This clause can also be used to record additional filter columns.
	PRIMARY KEY	specifies that the primary-key values of all rows updated should be recorded in the snapshot log.
	ROWID	specifies that the ROWID values of all rows updated should be recorded in the snapshot log.
	filter_column(s)	are non-primary-key columns referenced by snapshots. For information about filter columns, see Oracle8 Replication.
	For more information, see "Adding Primary Key, ROWID, and Filter Columns".

Modifying Physical Attributes

Example

The following statement changes the MAXEXTENTS value of a snapshot log:

ALTER SNAPSHOT LOG ON dept  
STORAGE MAXEXTENTS 50;

Adding Primary Key, ROWID, and Filter Columns

Snapshot logs can be altered to additionally record primary key, ROWID, or filter column information when snapshot master tables are updated. To stop recording any of this information, you must first drop the snapshot log and then re-create it.

Example

The following example alters an existing ROWID snapshot log to also record primary key information:

ALTER SNAPSHOT LOG ON sales ADD PRIMARY KEY;

Partitioned Snapshot Logs

Partitioned snapshot logs are the same as partitioned tables, because snapshot logs are basically tables. The options have the same syntax and semantics as the partitioned table options for CREATE TABLE and ALTER TABLE. The only difference is that the following operations are not allowed on snapshots and snapshot logs:

• DROP PARTITION
• TRUNCATE PARTITION
• EXCHANGE PARTITION

You cannot perform bulk deletions by dropping or truncating partitions on master tables. Therefore, after dropping or truncating a partition, all snapshots must be manually refreshed. A fast refresh will probably produce incorrect results, but Oracle will not raise an error.

Related Topics

ALTER TABLE

CREATE SNAPSHOT

CREATE SNAPSHOT LOG

DROP SNAPSHOT LOG

---

ALTER SYSTEM

Purpose

To dynamically alter your Oracle instance in one of the following ways:

• to explicitly perform a checkpoint
• to verify access to datafiles
• to terminate a session
• to enable or disable resource limits
• to enable distributed recovery in a single-process environment
• to disable distributed recovery
• to restrict logons to Oracle to only those users with RESTRICTED SESSION system privilege
• to clear all data from the shared pool in the system global area (SGA)
• to enable or disable global name resolution
• to dynamically change or disable limits or thresholds for concurrent usage licensing and named user licensing
• to manually archive redo log file groups or to enable or disable automatic archiving
• to manage shared server processes or dispatcher processes for the multithreaded server architecture
• to explicitly switch redo log file groups

Prerequisites

You must have ALTER SYSTEM system privilege.

Syntax

archive_log_clause: See the ARCHIVE LOG clause.

set_clause::=

dispatch_clause::=

options_clause::=

Keywords and Parameters

You can use the following options regardless of whether your instance has the database dismounted or mounted, open or closed:
RESTRICTED SESSION	specifies whether logon to Oracle is restricted
	ENABLE	allows only users with RESTRICTED SESSION system privilege to logon to Oracle.
	DISABLE	reverses the effect of the ENABLE RESTRICTED SESSION option, allowing all users with CREATE SESSION system privilege to log on to Oracle.
	For more information, see "Restricting Logons".
FLUSH SHARED_POOL	clears all data from the shared pool in the system global area (SGA). For more information, see "Clearing the Shared Pool".
You can use the following options when your instance has the database mounted, open or closed:
CHECKPOINT	performs a checkpoint.
	GLOBAL	performs a checkpoint for all instances that have opened the database.
	LOCAL	performs a checkpoint only for the thread of redo log file groups for your instance. You can use this option only when your instance has the database open.
	If you omit both the GLOBAL and LOCAL options, Oracle performs a global checkpoint. For more information, see "Performing a Checkpoint".
CHECK DATAFILES	GLOBAL	verifies that all instances that have opened the database can access all online datafiles.
	LOCAL	verifies that your instance can access all online datafiles.
	If you omit both the GLOBAL and LOCAL options, Oracle uses GLOBAL by default. For more information, see "Checking Datafiles".
You can use the following parameters and options only when your instance has the database open:
RESOURCE_LIMIT	controls resource limits. TRUE enables resource limits; FALSE disables resource limits. See also "Using Resource Limits".
GLOBAL_NAMES	controls the enforcement of global name resolution for your session. TRUE enables the enforcement of global names; FALSE disables the enforcement of global names. For more information, see "Global Name Resolution".
SCAN_INSTANCES	in a parallel server, specifies the number of instances to participate in parallelized operations. This syntax will be obsolete in the next major release.
CACHE_INSTANCES	in a parallel server, specifies the number of instances that will cache a table. This syntax will be obsolete in the next major release.
For more information on parallel operations, see Oracle8 Tuning. For more information on the following multithreaded server parameters, see "Managing Processes for the Multithreaded Server".
MTS_SERVERS	specifies a new minimum number of shared server processes.
MTS_DISPATCHERS	specifies a new number of dispatcher processes:
	protocol	is the network protocol of the dispatcher processes.
	integer	is the new number of dispatcher processes of the specified protocol.
	You can specify multiple MTS_DISPATCHERS parameters in a single command for multiple network protocols.
For more information on the following licensing parameters, see "Using Licensing Limits".
JOB_QUEUE_PROCESSES	specifies the number of job queue processes per instance (SNPn, where n is 0 to 9 followed by A to Z). Set this parameter to 1 or higher if you wish to have your snapshots updated automatically. One job queue process is usually sufficient unless you have many snapshots that refresh simultaneously. Oracle also uses job queue processes to process requests created by the DBMS_JOB package. For more information on managing table snapshots, see Oracle8 Replication.
LICENSE_MAX_SESSIONS	limits the number OS sessions on your instance. A value of 0 disables the limit.
LICENSE_SESSIONS_WARNING	establishes a threshold of sessions over which Oracle writes warning messages to the ALERT file for subsequent sessions. A value of 0 disables the warning threshold.
LICENSE_MAX_USERS	limits number of concurrent users on your database. A value of 0 disables the limit.
REMOTE_DEPENDENCIES_MODE	specifies how dependencies of remote stored procedures are handled by the server. For more information, refer to Oracle8 Application Developer's Guide.
SWITCH LOGFILE	switches redo log file groups. For more information, see "Switching Redo Log File Groups".
DISTRIBUTED RECOVERY	specifies whether or not distributed recovery is enabled.
	ENABLE	enables distributed recovery. In a single-process environment, you must use this option to initiate distributed recovery.
	DISABLE	switches redo log files.
	For more information, see "Enabling and Disabling Distributed Recovery".
ARCHIVE LOG	manually archives redo log files or enables or disables automatic archiving. See the ARCHIVE LOG clause.
KILL SESSION	terminates a session and any ongoing transactions. You must identify the session with both of the following values from the V$SESSION view:
	integer1	is the value of the SID column.
	integer2	is the value of the SERIAL# column.
	For more information, see "Terminating a Session".
DISCONNECT SESSION	disconnects the current session by destroying the dedicated server process (or virtual circuit if the connection was made via MTS). If configured, application failover will take effect. For more information about application failover see Oracle8 Tuning and Oracle8 Parallel Server Concepts and Administration. You must identify the session with both of the following values from the V$SESSION view:
	integer1	is the value of the SID column.
	integer2	is the value of the SERIAL# column.
	POST_TRANSACTION	allows ongoing transactions to complete before the session is disconnected. This keyword is required when DISCONNECT SESSION is specified. For more information, see "Disconnecting a Session".
PLSQL_V2_COMPATIBILITY	modifies the compile-time behavior of PL/SQL programs to allow language constructs that are illegal in Oracle8 (PL/SQL V3), but were legal in Oracle7 (PL/SQL V2). See the PL/SQL User's Guide and Reference and Oracle8 Reference for more information about this system parameter.
	TRUE	enables Oracle8 PL/SQL V3 programs to execute Oracle7 PL/SQL V2 constructs.
	FALSE	disallows illegal Oracle7 PL/SQL V2 constructs. This is the default.
MAX_DUMP_FILE_SIZE	specifies the trace dump file size upper limit for all user sessions. Specify the maximum size as either a nonnegative integer that represents the number of blocks, or as 'UNLIMITED'. If you specify 'UNLIMITED', no upper limit is imposed.
	DEFERRED	modifies the trace dump file size upper limit for future user sessions only.

Restricting Logons

By default, any user granted CREATE SESSION system privilege can log on to Oracle. The ENABLE RESTRICTED SESSION option of the ALTER SYSTEM command prevents logons by all users except those having RESTRICTED SESSION system privilege. Existing sessions are not terminated.

You may want to restrict logons if you are performing application maintenance and you want only application developers with RESTRICTED SESSION system privilege to log on. To restrict logons, issue the following statement:

ALTER SYSTEM
ENABLE RESTRICTED SESSION; 

You can then terminate any existing sessions using the KILL SESSION clause of the ALTER SYSTEM command.

After performing maintenance on your application, issue the following statement to allow any user with CREATE SESSION system privilege to log on:

ALTER SYSTEM
DISABLE RESTRICTED SESSION; 

Clearing the Shared Pool

The FLUSH SHARED_POOL option of the ALTER SYSTEM command clears all information from the shared pool in the system global area (SGA). The shared pool stores this information:

• cached data dictionary information
• shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages, and triggers

You might want to clear the shared pool before beginning performance analysis. To clear the shared pool, issue the following statement:

ALTER SYSTEM
FLUSH SHARED_POOL; 

The above statement does not clear shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages, or triggers that are currently being executed, or for SQL SELECT statements for which all rows have not yet been fetched.

Performing a Checkpoint

The CHECKPOINT clause of the ALTER SYSTEM command explicitly forces Oracle to perform a checkpoint. You can force a checkpoint if you want to ensure that all changes made by committed transactions are written to the data files on disk. For more information on checkpoints, see the "Recovery Structures" chapter of Oracle8 Concepts.

If you are using Oracle with the Parallel Server option in parallel mode, you can specify either the GLOBAL option to perform a checkpoint on all instances that have opened the database or the LOCAL option to perform a checkpoint on only your instance.

The following statement forces a checkpoint:

ALTER SYSTEM 
CHECKPOINT; 

Oracle does not return control to you until the checkpoint is complete.

Checking Datafiles

The CHECK DATAFILES clause of the ALTER SYSTEM command verifies access to all online datafiles. If any datafile is not accessible, Oracle writes a message to an ALERT file. You may want to perform this operation after fixing a hardware problem that prevented an instance from accessing a datafile. For more information on using this clause, see Oracle8 Parallel Server Concepts and Administration.

The following statement verifies that all instances that have opened the database can access all online datafiles:

ALTER SYSTEM
CHECK DATAFILES GLOBAL;

Using Resource Limits

When you start an instance, Oracle enables or disables resource limits based on the value of the initialization parameter RESOURCE_LIMIT. You can issue an ALTER SYSTEM statement with the RESOURCE_LIMIT option to enable or disable resource limits for subsequent sessions.

Enabling resource limits only causes Oracle to enforce the resource limits already assigned to users. To choose resource limit values for a user, you must create a profile, or a set of limits, and assign that profile to the user. For more information on this process, see CREATE PROFILE and CREATE USER.

This ALTER SYSTEM statement dynamically enables resource limits:

ALTER SYSTEM
SET RESOURCE_LIMIT = TRUE; 

Global Name Resolution

When you start an instance, Oracle determines whether to enforce global name resolution for remote objects accessed in SQL statements based on the value of the initialization parameter GLOBAL_NAMES. You can subsequently enable or disable global name resolution while your instance is running with the GLOBAL_NAMES parameter of the ALTER SYSTEM command. You can also enable or disable global name resolution for your session with the GLOBAL_NAMES parameter of the ALTER SESSION command discussed earlier in this chapter.

Oracle recommends that you enable global name resolution if you use or plan to use distributed processing. For more information on global name resolution and how Oracle enforces it, see "Referring to Objects in Remote Databases" and Oracle8 Distributed Database Systems.

Managing Processes for the Multithreaded Server

When you start your instance, Oracle creates shared server processes and dispatcher processes for the multithreaded server architecture based on the values of the following initialization parameters:

MTS_SERVERS	specifies the initial and minimum number of shared server processes. Oracle may automatically change the number of shared server processes if the load on the existing processes changes. While your instance is running, the number of shared server processes can vary between the values of the initialization parameters MTS_SERVERS and MTS_MAX_SERVERS.
MTS_DISPATCHERS	specifies one or more network protocols and the number of dispatcher processes for each protocol.

For more information on the multithreaded server architecture, see Oracle8 Concepts.

You can use the MTS_SERVERS and MTS_DISPATCHERS parameters of the ALTER SYSTEM command to perform one of the following operations while the instance is running:

• To create additional shared server processes: You can cause Oracle to create additional shared server processes by increasing the minimum number of shared server processes.
• To terminate existing shared server processes: Oracle terminates the shared server processes after it finishes processing their current calls, unless the load on the server processes is so high that it cannot be managed by the remaining processes.
• To create more dispatcher processes for a specific protocol: You can create additional dispatcher processes up to a maximum across all protocols specified by the initialization parameter MTS_MAX_DISPATCHERS.

  You cannot use this command to create dispatcher processes for network protocols that are not specified by the initialization parameter MTS_DISPATCHERS. To create dispatcher processes for a new protocol, you must change the value of the initialization parameter.

• To terminate existing dispatcher processes for a specific protocol: Oracle terminates the dispatcher processes only after their current user processes disconnect from the instance.

Example I

The following statement changes the minimum number of shared server processes to 25:

ALTER SYSTEM
SET MTS_SERVERS = 25; 

If there are currently fewer than 25 shared server processes, Oracle creates more. If there are currently more than 25, Oracle terminates some of them when they are finished processing their current calls if the load could be managed by the remaining 25.

Example II

The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5 and the number of dispatcher processes for the DECNET protocol to 10:

ALTER SYSTEM 
SET MTS_DISPATCHERS = 'TCP, 5'
MTS_DISPATCHERS = 'DECnet, 10'; 

If there are currently fewer than 5 dispatcher processes for TCP, Oracle creates new ones. If there are currently more than 5, Oracle terminates some of them after the connected users disconnect.

If there are currently fewer than 10 dispatcher processes for DECnet, Oracle creates new ones. If there are currently more than 10, Oracle terminates some of them after the connected users disconnect.

If there are currently existing dispatchers for another protocol, the above statement does not affect the number of dispatchers for that protocol.

Using Licensing Limits

Oracle enforces concurrent usage licensing and named user licensing limits specified by your Oracle license. When you start your instance, Oracle establishes the licensing limits based on the values of the following initialization parameters:

LICENSE_MAX_SESSIONS	establishes the concurrent usage licensing limit, or the limit for concurrent sessions. Once this limit is reached, only users with RESTRICTED SESSION system privilege can connect.
LICENSE_SESSIONS_WARNING	establishes a warning threshold for concurrent usage. Once this threshold is reached, Oracle writes warning messages to the database ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.
LICENSE_MAX_USERS	establishes the limit for users connected to your database. Once this limit is reached, more users cannot connect.

You can dynamically change or disable limits or thresholds while your instance is running using the LICENSE_MAX_SESSIONS, LICENSE_SESSIONS_WARNING, and LICENSE_MAX_USERS parameters of the ALTER SYSTEM command. Do not disable or raise session or user limits unless you have appropriately upgraded your Oracle license. For information on upgrading your license, contact your Oracle sales representative.

New limits apply only to future sessions and users:

• If you reduce the limit on sessions below the current number of sessions, Oracle does not end existing sessions to enforce the new limit. Users without RESTRICTED SESSION system privilege can begin new sessions only when the number of sessions falls below the new limit.
• If you reduce the warning threshold for sessions below the current number of sessions, Oracle writes a message to the ALERT file for all subsequent sessions.
• You cannot reduce the limit on users below the current number of users created for the database.

Example I

The following statement dynamically changes the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54:

ALTER SYSTEM 
SET LICENSE_MAX_SESSIONS = 64 
LICENSE_SESSIONS_WARNING = 54; 

If the number of sessions reaches 54, Oracle writes a warning message to the ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.

If the number of sessions reaches 64, only users with RESTRICTED SESSION system privilege can begin new sessions until the number of sessions falls below 64 again.

Example II

The following statement dynamically disables the limit for sessions on your instance:

ALTER SYSTEM
SET LICENSE_MAX_SESSIONS = 0; 

After you issue the above statement, Oracle no longer limits the number of sessions on your instance.

Example III

The following statement dynamically changes the limit on the number of users in the database to 200:

ALTER SYSTEM
SET LICENSE_MAX_USERS = 200; 

After you issue the above statement, Oracle prevents the number of users in the database from exceeding 200.

Switching Redo Log File Groups

The SWITCH LOGFILE option of the ALTER SYSTEM command explicitly forces Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. You may want to force a log switch to drop or rename the current redo log file group or one of its members, because you cannot drop or rename a file while Oracle is writing to it. The forced log switch affects only your instance's redo log thread. Note that when you force a log switch, Oracle begins to perform a checkpoint. Oracle returns control to you immediately rather than when the associated checkpoint is complete.

The following statement forces a log switch:

ALTER SYSTEM
SWITCH LOGFILE; 

Enabling and Disabling Distributed Recovery

Oracle allows you to perform distributed transactions, or transactions that modify data on multiple databases. If a network or machine failure occurs during the commit process for a distributed transaction, the state of the transaction may be unknown, or in doubt. Once the failure has been corrected and the network and its nodes are back online, Oracle recovers the transaction.

If you are using Oracle in multiple-process mode, this distributed recovery is performed automatically. If you are using Oracle in single-process (single user) mode, such as on the MS-DOS operating system, you must explicitly initiate distributed recovery with the following statement.

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

You may need to issue the above statement more than once to recover an in-doubt transaction, especially if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view DBA_2PC_PENDING. You can tell that the transaction is recovered when it no longer appears in DBA_2PC_PENDING. For more information about distributed transactions and distributed recovery, see Oracle8 Distributed Database Systems.

You can disable distributed recovery in both single-process and multiprocess mode with the following statement:

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY; 

You may want to disable distributed recovery for demonstration purposes. You can then enable distributed recovery again by issuing an ALTER SYSTEM statement with the ENABLE DISTRIBUTED RECOVERY clause.

Terminating a Session

The KILL SESSION clause of the ALTER SYSTEM command terminates a session, immediately performing the following tasks:

• rolling back its current transactions
• releasing all of its locks
• freeing all of its resources

You may want to kill the session of a user that is holding resources needed by other users. The user receives an error message indicating that the session has been killed; that user can no longer make calls to the database without beginning a new session. You can kill a session only on the same instance as your current session.

If you try to kill a session that is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, Oracle waits for this activity to complete, kills the session, and then returns control to you. If the waiting lasts as long as a minute, Oracle marks the session to be killed and returns control to you with a message indicating that the session is marked to be killed. Oracle then kills the session when the activity is complete.

Example

Consider this data from the V$SESSION dynamic performance table:

SELECT sid, serial, username
FROM v$session 

  SID    SERIAL USERNAME
----- --------- ----------------
    1         1
    2         1
    3         1
    4         1 
    5         1 
    7         1 
    8        28 OPS$BQUIGLEY 
   10       211 OPS$SWIFT 
   11        39 OPS$OBRIEN 
   12        13 SYSTEM  
   13         8 SCOTT 

The following statement kills the session of the user SCOTT using the SID and SERIAL# values from V$SESSION:

ALTER SYSTEM
KILL SESSION '13, 8';

Disconnecting a Session

The DISCONNECT SESSION clause is similar to the KILL SESSION clause, but with two distinct differences.

First, the ALTER SYSTEM DISCONNECT SESSION 'X, Y' POST_TRANSACTION command waits until any current transaction that the session is working on completes before taking effect.

Second, the session is disconnected rather than killed, which means that the dedicated server process (or virtual circuit if the connection was made through MTS) is destroyed by this command. Termination of a session's connection causes application failover to take effect if the appropriate system parameters are configured.

Disconnecting a session essentially allows you to perform a manual application failover. Using this command in a parallel server environment allows you to disconnect sessions on an overloaded instance and shift them to another instance.

The POST_TRANSACTION keyword is required.

Example

The following statement disconnects user SCOTT's session, using the SID and SERIAL# values from V$SESSION:

ALTER SYSTEM
DISCONNECT SESSION '13, 8' POST_TRANSACTION;

For more information about application failover, see Oracle8 Parallel Server Concepts and Administration and Oracle8 Tuning.

Related Topics

ALTER SESSION

ALTER SESSION

CREATE USER

ARCHIVE LOG clause

---

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

schema	is the schema containing the table. If you omit schema, Oracle assumes the table is in your own schema.
table	is the name of the table to be altered. You can alter the definition of an index-organized table.
ADD	adds a column or integrity constraint. You cannot ADD columns to an index-organized table. See also "Adding Columns".
MODIFY	modifies the definition of an existing column. If you omit any of the optional parts of the column definition (datatype, default value, or column constraint), these parts remain unchanged.You cannot MODIFY column definitions of index-organized tables. See also "Modifying Column Definitions".
	column	is the name of the column to be added or modified.
	datatype	specifies a datatype for a new column or a new datatype for an existing column. You can omit the datatype only if the statement also designates the column as part of the foreign key of a referential integrity constraint. Oracle automatically assigns the column the same datatype as the corresponding column of the referenced key of the referential integrity constraint.
	DEFAULT	specifies a default value for a new column or a new default for an existing column. Oracle assigns this value to the column if a subsequent INSERT statement omits a value for the column. If you are adding a new column to the table and specify the default value, Oracle inserts the default column value into all rows of the table. The datatype of the default value must match the datatype specified for the column. The column must also be long enough to hold the default value. A DEFAULT expression cannot contain references to other columns, the pseudocolumns CURRVAL, NEXTVAL, LEVEL, and ROWNUM, or date constants that are not fully specified.
	column_constraint	adds or removes a NOT NULL constraint to or from an existing column. See the syntax of column_constraint in the CONSTRAINT clause.
	table_constraint	adds an integrity constraint to the table. See the syntax of table_constraint in the CONSTRAINT clause. See also "REFs".
modify_default_attributes_clause	is a valid option only for partitioned tables. Use this option to specify new values for the default attributes of a partitioned table.
physical_attributes_clause	changes the value of PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters for the table, partition, the overflow data segment, or the default characteristics of a partitioned table. See the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of CREATE TABLE.
	storage_clause	changes the storage characteristics of the table, partition, overflow data segment, or the default characteristics of a partitioned table. See the STORAGE clause.
PCTTHRESHOLD	specifies the percentage of space reserved in the index block for an index-organized table row. Any portion of the row that exceeds the specified threshold is stored in the overflow area. If OVERFLOW is not specified, then rows exceeding the THRESHOLD limit are rejected. PCTTHRESHOLD must be a value from 0 to 50.
	INCLUDING column_name	specifies a column at which to divide an index-organized table row into index and overflow portions. All columns that follow column_name are stored in the overflow data segment. A column_name is either the name of the last primary key column or any non-primary-key column.
	OVERFLOW	specifies the overflow data segment physical storage attributes to be modified for the index-organized table. Parameters specified in this clause are only applicable to the overflow data segment. See CREATE TABLE.
	ADD OVERFLOW	adds an overflow data segment to the specified index-organized table.
	See also "Index-Organized Tables".
LOB	specifies the LOB storage characteristics for the newly added LOB column. You cannot use this clause to modify an existing LOB column.
lob_item	is the LOB column name or LOB object attribute for which you are explicitly defining tablespace and storage characteristics that are different from those of the table.
STORE AS
	lob_segname	specifies the name of the LOB data segment. You cannot use lob_segname if more than one lob_item is specified.
	ENABLE STORAGE IN ROW	specifies that the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information. This is the default.
	DISABLE STORAGE IN ROW	specifies that the LOB value is stored outside of the row regardless of the length of the LOB value.
	Note that the LOB locator is always stored in the row regardless of where the LOB value is stored. You cannot change the STORAGE IN ROW once it is set.
	CHUNK integer	specifies the number of bytes to be allocated for LOB manipulation. If integer is not a multiple of the database block size, Oracle rounds up (in bytes) to the next multiple. For example, if the database block size is 2048 and integer is 2050, Oracle allocates 4096 bytes (2 blocks).The maximum value is 32768 (32 K), which is the largest Oracle block size allowed. Note: The value of CHUNK must be less than or equal to the values of both INITIAL and NEXT (either the default values or those specified in the storage clause). If CHUNK exceeds the value of either INITIAL or NEXT, Oracle returns an error.
	PCTVERSION integer	is the maximum percentage of overall LOB storage space used for creating new versions of the LOB. The default value is 10, meaning that older versions of the LOB data rae not overwritten until 10% of the overall LOB storage space is used.
	INDEX lob_index_name	is the name of the LOB index segment. You cannot use lob_index_name if more than one lob_item is specified.
MODIFY LOB (lob_item)	modifies the physical attributes of the LOB attribute lob_item or LOB object attribute. You can only specify one LOB column for each MODIFY LOB clause. See also "LOB Columns".
NESTED TABLE nested_item STORE AS storage_table
	specifies storage_table as the name of the storage table in which the rows of all nested_item values reside. You must include this clause when modifying a table with columns or column attributes whose type is a nested table.
	The nested_item is the name of a column or a column-qualified attribute whose type is a nested table.
	The storage_table is the name of the storage table. The storage table is modified in the same schema and the same tablespace as the parent table. See also "Nested Table Columns".
drop_clause	drops an integrity constraint. See the DROP clause.
ALLOCATE EXTENT	explicitly allocates a new extent for the table, the partition, the overflow data segment, the LOB data segment, or the LOB index.
	SIZE	specifies the size of the extent in bytes. You can use K or M to specify the extent size in kilobytes or megabytes. If you omit this parameter, Oracle determines the size based on the values of the table's overflow data segment's, or LOB index's STORAGE parameters.
	DATAFILE	specifies one of the datafiles in the tablespace of the table, overflow data segment, LOB data tablespace, or LOB index to contain the new extent. If you omit this parameter, Oracle chooses the datafile.
	INSTANCE	makes the new extent available to the freelist group associated with the specified instance. If the instance number exceeds the maximum number of freelist groups, the former is divided by the latter, and the remainder is used to identify the freelist group to be used. An instance is identified by the value of its initialization parameter INSTANCE_NUMBER. If you omit this parameter, the space is allocated to the table, but is not drawn from any particular freelist group. Rather, the master freelist is used, and space is allocated as needed. For more information, see Oracle8 Concepts. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.
	Explicitly allocating an extent with this clause does affect the size for the next extent to be allocated as specified by the NEXT and PCTINCREASE storage parameters.
DEALLOCATE UNUSED	explicitly deallocates unused space at the end of the table, partition, overflow data segment, LOB data segment, or LOB index and makes the space available for other segments. You can free only unused space above the high-water mark. If KEEP is omitted, all unused space is freed. For more information, see DEALLOCATE UNUSED clause.
	KEEP	specifies the number of bytes above the high-water mark that the table, overflow data segment, LOB data segment, or LOB index will have after deallocation. If the number of remaining extents are less than MINEXTENTS, then MINEXTENTS is set to the current number of extents. If the initial extent becomes smaller than INITIAL, then INITIAL is set to the value of the current initial extent.
enable_clause	enables a single integrity constraint or all triggers associated with the table. See the ENABLE clause.
CACHE	for data that is accessed frequently, specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. CACHE is not a valid option for index-organized tables.
NOCACHE	for data that is not accessed frequently, specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. For LOBs, the LOB value is either not brought into the buffer cache or brought into the buffer cache and placed at the least recently used end of the LRU list. (The latter is the default behavior.)
	NOCACHE is not a valid option for index-organized tables.
LOGGING/NOLOGGING	LOGGING/NOLOGGING specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned table, table partition, or all partitions of a partitioned table will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file. When used with the modify_default_attributes_clause, this option affects the logging attribute of a partitioned table.
	LOGGING/NOLOGGING also specifies whether ALTER TABLE...MOVE and ALTER TABLE...SPLIT operations will be logged or not logged.
	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 this table, it is important to take a backup after the NOLOGGING operation.
	If the database is run in ARCHIVELOG mode, media recovery from a backup taken before the LOGGING operation will restore the table. However, media recovery from a backup taken before the NOLOGGING operation will not restore the table.
	The logging attribute of the base table is independent of that of its indexes.
	For more information about the LOGGING option and Parallel DML, see Oracle8 Parallel Server Concepts and Administration.
	NOLOGGING is not a valid keyword for altering index-organized tables.
RENAME TO	renames table to new_table_name.
partitioning_clauses	See also "Modifying Table Partitions".
MODIFY PARTITION [table partitions]	modifies the real physical attributes of a table partition partition_name. You can specify any of the following as new physical attributes for the partition: the logging attribute; PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameter; or storage parameters.
MODIFY PARTITION [index partitions]	modifies the attributes of an index partition partition_name. Note that you cannot specify the following options with clauses of the MODIFY PARTITION [table partitions] option.
	UNUSABLE LOCAL INDEXES	marks all the local index partitions associated with partition_name as unusable.
	REBUILD UNUSABLE LOCAL INDEXES	rebuilds the unusable local index partitions associated with partition_name.
RENAME PARTITION	renames table partition current_name to new_name.
MOVE PARTITION	moves table partition partition_name to another segment. You can move partition data to another tablespace, recluster data to reduce fragmentation, or change a create-time physical attribute.
ADD PARTITION	adds a new partition new_partition_name to the "high" end of a partitioned table. You can specify any of the following as new physical attributes for the partition: the logging attribute; the PCTFREE, PCTUSED, INITRANS, or MAXTRANS parameter; or storage parameters.
	VALUES LESS THAN (value_list)	specifies the upper bound for the new partition. The value_list is a comma-separated, ordered list of literal values corresponding to column_list. The value_list must collate greater than the partition bound for the highest existing partition in the table.
DROP PARTITION	removes partition partition_name, and the data in that partition, from a partitioned table.
TRUNCATE PARTITION	removes all rows from the partition partition_name in a table.
	DROP STORAGE	specifies that space from the deleted rows be deallocated and made available for use by other schema objects in the tablespace.
	REUSE STORAGE	specifies that space from the deleted rows remains allocated to the partition. The space is subsequently available only for inserts and updates to the same partition.
SPLIT PARTITION	from an original partition partition_name_old, creates two new partitions, each with a new segment and new physical attributes, and new initial extents. The segment associated with partition_name_old is discarded.
	AT (value_list)	specifies the new noninclusive upper bound for split_partition_1. The value_list must compare less than the pre-split partition bound for partition_name_old and greater than the partition bound for the next lowest partition (if there is one).
	INTO	describes the two partitions resulting from the split.
	partition_description, partition_description	specifies optimal names and physical attributes of the two partitions resulting from the split.
EXCHANGE PARTITION	converts partition partition_name into a nonpartitioned table, and a nonpartitioned table into a partition of a partitioned table by exchanging their data (and index) segments. The default behavior is EXCLUDING INDEXES WITH VALIDATION.
	WITH TABLE table	specifies the table with which the partition will be exchanged.
	INCLUDING INDEXES	specifies that the local index partitions be exchanged with the corresponding regular indexes.
	EXCLUDING INDEXES	specifies that all the local index partitions corresponding to the partition and all the regular indexes on the exchanged table are marked as unusable.
	WITH VALIDATION	specifies that any rows in the exchanged table that do not collate properly return an error.
	WITHOUT VALIDATION	specifies that the proper collation of rows in the exchanged table is not checked.
parallel_clause	specifies the degree of parallelism for the table. PARALLEL is not a valid option for index-organized tables. See the PARALLEL clause.
	ENABLE TABLE LOCK	enables DML and DDL locks on a table in a parallel server environment. For more information, see Oracle8 Parallel Server Concepts and Administration.
	disable_clause	disables a single integrity constraint or all triggers associated with the tables. See the DISABLE clause.
		Integrity constraints specified in DISABLED clauses must be defined in the ALTER TABLE statements or in a previously issued statement. You can also enable and disable integrity constraints with the ENABLE and DISABLE keywords of the CONSTRAINT clause. If you define an integrity constraint but do not explicitly enable or disable it, Oracle enables it by default.
	DISABLE TABLE LOCK	disables DML and DDL locks on a table to improve performance in a parallel server environment. For more information, see Oracle8 Parallel Server Concepts and Administration.

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

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 "Using 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

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.
ADD DATAFILE	adds the datafile specified by filespec to the tablespace. (See the syntax description of Filespec). You can add a datafile while the tablespace is online or offline. Be sure that the datafile is not already in use by another database.
AUTOEXTEND	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 commands.
	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. You can use K or M to specify this size in kilobytes or megabytes. The default is one data block.
	MAXSIZE	specifies maximum disk space allowed for automatic extension of the datafile.
	UNLIMITED	sets no limit on allocating disk space to the datafile.
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 only 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.
COALESCE	for each datafile in the tablespace, coalesces all contiguous free extents into larger contiguous extents.
	COALESCE cannot be specified with any other command option.
DEFAULT storage_clause	specifies the new default storage parameters for objects subsequently created in the tablespace. See the STORAGE clause.
MINIMUM EXTENT integer	controls free space fragmentation in the tablespace by ensuring that every used and/or free extent size in a tablespace is at least as large as, and is a multiple of, integer. For more information about using MINIMUM EXTENT to control space fragmentation, see Oracle8 Administrator's Guide.
ONLINE	brings the tablespace online.
OFFLINE	takes the tablespace offline and prevents further access to its segments.
	NORMAL	performs a checkpoint for all datafiles in the tablespace. All of these datafiles must be online. This is the default. You need not perform media recovery on this tablespace before bringing it back online. You must use this option if the database is in NOARCHIVELOG mode.
	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. Use this option when one or more datafiles in the tablespace are unavailable.
Suggestion: Before taking a tablespace offline for a long time, you may want to alter 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. You can reassign to such users new default and temporary tablespaces with the ALTER USER command.
BEGIN BACKUP	signifies that an open backup is to be performed on the datafiles that make up this tablespace. This option does not prevent users from accessing the tablespace. You must use this option before beginning an open backup. You cannot use this option on a read-only tablespace.
	Note: While the backup is in progress, you cannot: take the tablespace offline normally, shutdown the instance, or begin another backup of the tablespace.
END BACKUP	signifies that an open backup of the tablespace is complete. Use this option as soon as possible after completing an open backup. You cannot use this option 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 Oracle8 Administrator's Guide.
READ ONLY	signifies that no further write operations are allowed on the tablespace. 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 command ALTER DATABASE RENAME.
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 wherein 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.

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

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. See also "Invalid Triggers".
ENABLE	enables the trigger. See also "Enabling and Disabling Triggers".
DISABLE	disables the trigger. See also "Enabling and Disabling Triggers".
COMPILE	compiles the trigger.
	DEBUG	instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. This option can be used for normal triggers and for instead-of triggers.

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.

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

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.

Note: The ALTER TRIGGER command does not change the definition of an existing trigger. To redefine a trigger, you must use the CREATE TRIGGER command with the OR REPLACE option.

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.

Note: This command is available only if the Oracle objects option is installed on your database server.

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

schema	is the schema that contains the type. If you omit schema, Oracle creates the type in your current schema.
type_name	is the name of an object type, a nested table type, or a VARRAY type.
COMPILE	compiles the object type specification and body. This is the default if no option is specified.
	SPECIFICATION	compiles only the object type specification.
	BODY	compiles only the object type body.
REPLACE AS OBJECT	adds new member subprogram specifications. This option is valid only for object types.
attribute_name	is an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object.
MAP/ORDER MEMBER function_specification
	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. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.
		A scalar value is always manipulated as a single unit. Scalars are mapped directly to the underlying hardware. An integer, for example, occupies 4 or 8 contiguous bytes of storage, in memory or on disk.
		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.
	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.
		When instances of the same object type definition are compared in an ORDER BY clause, the order method function_specification is invoked.
		An object specification can contain only one ORDER method, which must be a function having the return type INTEGER.
	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. Note that 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, "Object Values".
MEMBER	specifies a function or procedure subprogram associated with the object type which is referenced as an attribute. For information about overloading subprogram names within a package, see the PL/SQL User's Guide and Reference. See also "Restriction". You must specify a corresponding method body in the object type body for each procedure or function specification. See CREATE TYPE BODY.
	procedure_specification	is the specification of a procedure subprogram.
	function_specification	is the specification of a function subprogram.
PRAGMA RESTRICT_REFERENCES	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 the PL/SQL User's Guide and Reference.
	method_name	is the name of the MEMBER function or procedure to which the pragma is being applied.
	WNDS	specifies constraint writes no database state (does not modify database tables).
	WNPS	specifies constraint writes no package state (does not modify packaged variables).
	RNDS	specifies constraint reads no database state (does not query database tables).
	RNPS	specifies constraint reads no package state (does not reference packages variables).

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

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

Prerequisites

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

Syntax

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.

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

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	identifies an index to be analyzed (if no FOR clause is used).
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, provided that no FOR clauses are used.
PARTITION	specifies that statistics will be gathered for (partition_name). You cannot use this option when analyzing clusters.
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. See also "Clusters".
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 option only when analyzing a table.
COMPUTE STATISTICS	computes exact statistics about the analyzed object and stores them in a data dictionary. See also "Collecting Statistics".
ESTIMATE STATISTICS	estimates statistics about the analyzed object and stores them in the data dictionary.
	SAMPLE	specifies the amount of data from the analyzed object Oracle samples to estimate statistics. If you omit this parameter, Oracle samples 1064 rows. 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 command:
	FOR TABLE	collects table statistics for the table.
	FOR ALL COLUMNS	collects column statistics for all columns and scalar object attributes.
		INDEX collects column statistics for all indexed columns in the table.
	FOR COLUMNS	collects column statistics for the specified columns and scalar object attributes.
	attribute	specifies the qualified column name of an item in an object.
	FOR ALL INDEXES	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 (partition_name) clause and the index option 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.
	Histogram statistics are described in Oracle8 Tuning. See also "Columns".
DELETE STATISTICS	deletes any statistics about the analyzed object that are currently stored in the data dictionary. See also "Deleting Statistics".
VALIDATE STRUCTURE	validates the structure of the analyzed object. If you use this option when analyzing a cluster, Oracle automatically validates the structure of the cluster's tables. If you use this option when analyzing a partitioned table, Oracle also verifies that the row belongs to the correct partition. See also "Validating Structures".
	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 all together, 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 option when validating a table, Oracle also validates the table's indexes. If you use this option when validating a cluster, Oracle also validates all the clustered tables' indexes, including the cluster index.
LIST CHAINED ROWS	identifies migrated and chained rows of the analyzed table or cluster. You cannot use this option 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 UTLCHAIN.SQL. The list table must be on your local database.
	To analyze index-organized tables, you must create a separate chained-rows table for each index-organized table created 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 package, and then execute this procedure to create an IOT_CHAINED_ROWS table for an index-organized table. See also "Listing Chained Rows".

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

THREAD	specifies the thread containing the redo log file group to be archived. You need to specify this parameter only if you are using Oracle with the Parallel Server option in parallel mode.
SEQUENCE	manually archives the online redo log file group identified by the log sequence number integer in the specified thread. If you omit the THREAD parameter, Oracle archives the specified group from the thread assigned to your instance.
CHANGE	manually archives the online redo log file group containing the redo log entry with the system change number (SCN) specified by integer in the specified thread. If the SCN is in the current redo log file group, Oracle performs a log switch. If you omit the THREAD parameter, Oracle archives the groups containing this SCN from all enabled threads. You can use this option only when your instance has the database open.
CURRENT	manually archives the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, Oracle archives all redo log file groups from all enabled threads, including