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

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

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.

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;

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.

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.

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

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.

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.

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;

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;

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.

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.

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

4 Commands

Summary of SQL Commands

Data Definition Language (DDL) Commands

Table 4-1 Data Definition Language Commands

Data Manipulation Language (DML) Commands

Table 4-2 Data Manipulation Language Commands

Transaction Control Commands

Table 4-3 Transaction Control Commands

Session Control Commands

Table 4-4 Session Control Commands

System Control Command

Table 4-5 System Control Command

Embedded SQL Commands

ALTER CLUSTER

Purpose

Prerequisites

Syntax

Keywords and Parameters

Altering Clusters

Example I

Example II

Related Topics

ALTER DATABASE

Purpose

Prerequisites

Syntax

Keywords and Parameters

Examples

Example I

Example II

Example III

Example IV

Example V

Example VI

Example VII

Example VIII

Example IX

Example XI

Example XII

Example XIII

Related Topics

ALTER FUNCTION

Purpose

Prerequisites

Syntax

Keywords and Parameters

Recompiling Standalone Functions

Example

Related Topics

ALTER INDEX

Purpose

Prerequisites

Syntax

Keywords and Parameters

Examples

Example I

Example II

Example III

Example III(a)

Example IV

Example V

Example VI

Example VII

Example VIII

Example IX

Example X

Example XI

Example XII

Example XIII

Related Topics

ALTER PACKAGE

Purpose

Prerequisites

Syntax

Keywords and Parameters

Recompiling Stored Packages

Recompiling Package Specifications

Recompiling Package Bodies

Example I

Example II

4
Commands