Oracle8
SQL Reference
Release 8.0 A58225-01 |
|
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:
The tables in the following sections provide a functional summary of SQL commands and are divided into these categories:
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.
Data manipulation language (DML) commands query and manipulate data in existing schema objects. These commands do not implicitly commit the current transaction.
All DML commands except the EXPLAIN PLAN command are supported
in PL/SQL.
Transaction control commands manage changes made by DML commands.
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 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.
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.
Command | Purpose |
---|---|
ALTER SYSTEM |
Alter the Oracle instance by performing a specialized function. |
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:
Redefines storage and parallelism characteristics of a cluster.
See also "Altering Clusters".
The cluster must be in your own schema or you must have ALTER
ANY CLUSTER system privilege.
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". |
You can perform these tasks with the ALTER CLUSTER command:
You cannot perform these tasks with the ALTER CLUSTER command:
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.
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;
To alter an existing database in one of these ways:
For illustrations of some of these purposes, see "Examples".
You must have ALTER DATABASE system privilege.
recover_clause: See the "RECOVER
clause".
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. |
For more information on using the ALTER DATABASE command
for database maintenance, see the Oracle8
Administrator's Guide.
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;
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;
The following statement drops the redo log file member added in the previous example:
ALTER DATABASE stocks DROP LOGFILE MEMBER 'diskc:log3.log';
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.
The following statement drops all members of the redo log file group 3:
ALTER DATABASE stocks DROP LOGFILE GROUP 3;
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' );
The following statement disables thread 5 in a parallel server:
ALTER DATABASE stocks DISABLE THREAD 5;
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;
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';
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;
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.
The following statement clears a log file:
ALTER DATABASE CLEAR LOGFILE 'disk3:log.dbf';
To recompile a standalone stored function. See also "Recompiling
Standalone Functions".
The function must be in your own schema or you must have
ALTER ANY PROCEDURE system privilege.
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". |
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.
Use ALTER INDEX to:
For illustrations of some of these purposes, see "Examples".
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:
parallel_clause: See "PARALLEL
clause".
storage_clause: See "STORAGE
clause".
deallocate_unused_clause: See "DEALLOCATE
UNUSED clause".
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: 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. |
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);
The following example drops index partition IX_ANTARTICA:
ALTER INDEX sales_area_ix DROP PARTITION ix_antarctica;
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 );
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 );
The following statement marks the IDX_ACCTNO index as UNUSABLE:
ALTER INDEX idx_acctno UNUSABLE;
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;
The following example marks partition IDX_FEB96 of index IDX_ACCTNO as UNUSABLE:
ALTER INDEX idx_acctno MODIFY PARTITION idx_feb96 UNUSABLE;
The following statement sets the parallel attributes for index ARTIST_IX:
ALTER INDEX artist_ix PARALLEL (DEGREE 4, INSTANCES 3);
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;
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;
The following example renames an index:
ALTER INDEX emp_ix1 RENAME TO employee_ix1;
The following example renames an index partition:
ALTER INDEX employee_ix2 RENAME PARTITION emp_ix2_p3 TO employee_ix2_p3;
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.
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;
To recompile a stored package. See also "Recompiling
Stored Packages".
The package must be in your own schema or you must have ALTER
ANY PROCEDURE system privilege.
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.
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.
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.
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.
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.
To recompile a stand-alone stored procedure. See also "Recompiling
Stored Procedures".
The procedure must be in your own schema or you must have
ALTER ANY PROCEDURE system privilege.
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. |
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.
To add, modify, or remove a resource limit or password management
in a profile. See also "Examples".
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".
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: |
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:
The following example makes a password unavailable for reuse for 90 days:
ALTER PROFILE prof LIMIT PASSWORD_REUSE_TIME 90 PASSWORD_REUSE_MAX UNLIMITED;
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;
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.
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;
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.
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.
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.
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:
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".
You must have ALTER RESOURCE COST system privilege.
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.
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:
where:
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:
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:
To change the authorization needed to enable a role. See
also "Changing Authorizations".
You must either have been granted the role with the ADMIN
OPTION or have ALTER ANY ROLE system privilege.
The keywords and parameters in the ALTER ROLE command all
have the same meaning as in the CREATE ROLE command; see CREATE
ROLE.
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:
The following example changes the role ANALYST to IDENTIFIED GLOBALLY:
ALTER ROLE analyst IDENTIFIED GLOBALLY;
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.
To alter a rollback segment by
For more information, see "Altering Rollback
Segments".
You must have ALTER ROLLBACK SEGMENT system privilege.
storage_clause: See "STORAGE
clause".
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. |
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:
This statement brings the rollback segment RSONE online:
ALTER ROLLBACK SEGMENT rsone ONLINE;
This statement changes the STORAGE parameters for RSONE:
ALTER ROLLBACK SEGMENT rsone STORAGE (NEXT 1000 MAXEXTENTS 20);
This statement attempts to resize a rollback segment to an optimum size of 100 megabytes:
ALTER ROLLBACK SEGMENT rsone SHRINK TO 100 M;
To change the sequence by
For illustrations of some of these purposes, see "Examples".
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.
The keywords and parameters in this command serve the same
purpose that they do in "CREATE SEQUENCE".
Note:
This statement sets a new maximum value for the ESEQ sequence:
ALTER SEQUENCE eseq MAXVALUE 1500;
This statement turns on CYCLE and CACHE for the ESEQ sequence:
ALTER SEQUENCE eseq CYCLE CACHE 5;