Oracle8 SQL Reference 
Release 8.0 
A58225-01
 
Library
 
Product
 
Contents
 
Index
 

Prev Next

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:

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:


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:

You cannot perform these tasks with the ALTER CLUSTER command:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

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:

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

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:

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"


Prev
Prev
Next
Next
 
Oracle
Copyright © 1997 Oracle Corporation. 
All Rights Reserved. 

Library

Product

Contents

Index