Oracle8i SQL Reference
Release 8.1.5
A67779-01

Library
Product
Contents
Index

Prev Next

7
SQL Statements (continued)

ALTER CLUSTER

Syntax


physical_attributes_clause::=


storage_clause: See "storage_clause".

allocate_extent_clause::=


deallocate_unused_clause::=


parallel_clause::=


Purpose

To redefine storage and parallelism characteristics of a cluster.

For information on creating a cluster, see "CREATE CLUSTER".

To remove tables from a cluster, see "DROP CLUSTER" and "DROP TABLE".

Note:

You cannot use this statement to change the number or the name of columns in the cluster key, and you cannot change the tablespace in which the cluster is stored.  

Prerequisites

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

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. For a description of these parameters, see "CREATE CLUSTER".  

 

storage_clause  

changes the storage characteristics for the cluster. See the "storage_clause".

Restriction: You cannot change the values of the storage parameters INITIAL and MINEXTENTS for a cluster.  

SIZE integer  

determines how many cluster keys will be stored in data blocks allocated to the cluster. For a description of the SIZE parameter, see "CREATE CLUSTER".

Restriction: You can change the SIZE parameter only for an indexed cluster, not for a hash cluster.  

allocate_extent_clause  

explicitly allocates a new extent for the cluster.

Restriction: You can allocate a new extent only for an indexed cluster, not for a hash cluster.  

 

SIZE  

specifies the size of the extent in bytes. Use K or M to specify the extent size in kilobytes or megabytes.

When you explicitly allocate an extent with this clause, Oracle does not evaluate the cluster's storage parameters and determine a new size for the next extent to be allocated (as it does when you create a table). Therefore, specify SIZE if you do not want Oracle to use a default value.  

 

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 INSTANCE, the extent is available to all instances. Use this parameter only if you are using Oracle with the Parallel Server option in parallel mode.  

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.  

 

KEEP  

specifies the number of bytes above the high water mark that the cluster will have after deallocation. If the number of remaining extents is 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. If you omit KEEP, all unused space is freed.  

 

For a more complete description of this clause, see "ALTER TABLE".  

parallel_clause  

changes the default degree of parallelism for queries and DML on the cluster. For more detailed information, see the Notes to the parallel_clause of "CREATE TABLE".  

 

NOPARALLEL  

specifies serial execution. This is the default.  

 

PARALLEL  

causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.  

 

PARALLEL integer  

specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.  

 

Restriction: If the tables in cluster contain any columns of LOB or user-defined object type, this statement as well as subsequent INSERT, UPDATE, or DELETE operations on cluster are executed serially without notification.  

Examples

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 the CUSTOMER cluster, keeping 30 kilobytes of unused space for future use: 

ALTER CLUSTER scott.customer 
    DEALLOCATE UNUSED KEEP 30 K;

ALTER DATABASE

Syntax


recover_clauses::=


general_recovery_clause::=


managed_recovery_clause::=


datafile/tempfile_clauses::=


filespec: See "filespec".

controlfile_clauses::=


logfile_clauses::=


logfile_descriptor::=


autoextend_clause::=


maxsize_clause::=


parallel_clause::=


Purpose

To modify, maintain, or recover an existing database.

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

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

For information on creating a database, see "CREATE DATABASE".

Prerequisites

You must have ALTER DATABASE system privilege.

To specify the RECOVER clause, you must also have the OSDBA role enabled.

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. The database identifier is not related to the Net8 database specification.  

You can use the following clauses 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 Oracle8i Backup and Recovery Guide.  

 

CLONE DATABASE  

mounts the clone database. For more information, see the Oracle8i Backup and Recovery Guide.  

CONVERT  

completes the conversion of the Oracle7 data dictionary. After you use this clause, the Oracle7 data dictionary no longer exists in the Oracle database. Use this clause only when you are migrating to Oracle8i. For more information, see Oracle8i Migration.  

ACTIVATE STANDBY DATABASE  

changes the state of a standby database to an active database. For more information, see Oracle8i Backup and Recovery Guide.  

OPEN  

opens the database, making it available for normal use. You must mount the database before you can open it. You must activate a standby database before you can open it.  

 

READ ONLY  

restricts users to read-only transactions, preventing them from generating redo logs. You can use this clause to make a standby database available for queries even while archive logs are being copied from the primary database site.  

 

 

Restrictions:

  • You cannot open a database READ ONLY if it is currently opened READ WRITE by another instance.

  • You cannot open a database READ ONLY if it requires recovery.

  • You cannot take tablespaces offline while the database is open READ ONLY. However, you can take datafiles offline and online, and you can recover offline datafiles and tablespaces while the database is open READ ONLY.

 

 

READ WRITE  

opens the database in read-write mode, allowing users to generate redo logs. This is the default.  

 

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 clause to open the database after performing media recovery with an incomplete recovery using the RECOVER clause or with a backup control file. After opening the database with this clause, you should perform a complete database backup.  

 

NORESETLOGS  

leaves the log sequence number and redo log files in their current state.  

 

Restriction: You can specify RESETLOGS and NORESETLOGS only after performing incomplete media recovery or complete media recovery with a backup control file. In any other case, Oracle uses the NORESETLOGS automatically.  

You can use any of the following clauses when your instance has the database mounted, open or closed, and the files involved are not in use:  

general_recovery_clause  

lets you design media recovery for the database or standby database, or for specified tablespaces or files. For more information on media recovery, see Oracle8i Backup and Recovery Guide.  

 

Note: If you do not have special media requirements, Oracle Corporation recommends that you use the SQL*Plus RECOVER statement. For more information, see SQL*Plus User's Guide and Reference.  

 

Restrictions:

  • You can recover the entire database only when the database is closed.

  • Your instance must have the database mounted in exclusive mode.

  • You can recover tablespaces or datafiles when the database is open or closed, provided that the tablespaces or datafiles to be recovered are offline.

  • You cannot perform media recovery if you are connected to Oracle through the multi-threaded server architecture.

 

AUTOMATIC  

automatically generates the name of the next archived redo log file needed to continue the recovery operation. Oracle uses the LOG_ARCHIVE_DEST (or LOG_ARCHIVE_DEST_1) and LOG_ARCHIVE_FORMAT parameters (or their defaults) to generate the target redo log filename. If the file is found, the redo contained in that file is applied. If the file is not found, Oracle prompts you for a filename, displaying the generated filename as a suggestion.  

 

If you specify neither AUTOMATIC nor LOGFILE, Oracle prompts you for a filename, displaying the generated filename as a suggestion. You can then accept the generated filename or replace it with a fully qualified filename. If you know the archived filename differs from what Oracle would generate, you can save time by using the LOGFILE clause.  

FROM 'location'  

specifies the location from which the archived redo log file group is read. The value of location must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle assumes the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_1.  

STANDBY DATABASE  

recovers the standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open.  

DATABASE  

recovers the entire database. This is the default. You can use this clause only when the database is closed.  

 

Note: This clause recovers only online datafiles.  

 

UNTIL  

specifies the duration of the recovery operation.  

 

 
  • CANCEL performs cancel-based recovery. This clause recovers the database until you issue the ALTER DATABASE RECOVER statement with the RECOVER CANCEL clause.

 

 

 
  • TIME performs time-based recovery. This parameter recovers the database to the time specified by the date. The date must be a character literal in the format 'YYYY-MM-DD:HH24:MI:SS'.

 

 

 
  • CHANGE performs change-based recovery. This parameter recovers the database to a transaction-consistent state immediately before the system change number (SCN) specified by integer.

 

 

USING BACKUP CONTROLFILE  

specifies that a backup control file is being used instead of the current control file.  

TABLESPACE  

recovers only the specified tablespaces. You can use this clause if the database is open or closed, provided the tablespaces to be recovered are offline.  

DATAFILE  

recovers the specified datafiles. You can use this clause when the database is open or closed, provided the datafiles to be recovered are offline.  

STANDBY TABLESPACE | DATAFILE  

reconstructs a lost or damaged datafile or tablespace in the standby database using archived redo log files copied from the primary database and a control file.  

 

UNTIL [CONSISTENT WITH] CONTROLFILE  

specifies that the recovery of an old standby datafile or tablespace uses the current standby database control file. However, any redo in advance of the standby controlfile will not be applied. The keywords CONSISTENT WITH are optional and are provided for semantic clarity.  

LOGFILE  

continues media recovery by applying the specified redo log file.  

CONTINUE  

continues multi-instance recovery after it has been interrupted to disable a thread.  

CONTINUE DEFAULT  

continues recovery using the redo log file that Oracle would automatically generate if no other logfile were specified. This clause is equivalent to specifying AUTOMATIC, except that Oracle does not prompt for a filename.  

CANCEL  

terminates cancel-based recovery.  

managed_recovery_clause  

specifies sustained standby recovery mode. This mode assumes that the standby database is an active component of an overall standby database architecture. A primary database actively archives its redo log files to the standby site. As these archived redo logs arrive at the standby site, they become available for use by a managed standby recovery operation. Sustained standby recovery is restricted to media recovery. For more information on the parameters of this clause, see Oracle8i Backup and Recovery Guide.

Restrictions: The same restrictions apply as are listed under general_recovery_clause.  

TIMEOUT integer  

specifies in minutes the wait period of the sustained recovery operation. The recovery process waits for integer minutes for a requested archived log redo to be available for writing to the standby database. If the redo log file does not become available within that time, the recovery process terminates with an error message. You can then issue the statement again to return to sustained standby recovery mode.  

 

If you do not specify this clause, the database remains in sustained standby recovery mode until you reissue the statement with the RECOVER CANCEL clause or until instance shutdown or failure.  

CANCEL  

terminates the sustained recovery operation after applying all the redo in the current archived redo file.  

CANCEL IMMEDIATE  

terminates the sustained recovery operation after applying all the redo in the current archived redo file or after the next redo log file read, whichever comes first.

Restriction: This clause cannot be issued from the same session that issued the RECOVER MANAGED STANDBY DATABASE statement.  

parallel_clause  

specifies whether the recovery of media will be parallelized. For additional information, see the Notes to the parallel_clause of "CREATE TABLE".  

 

NOPARALLEL  

specifies serial execution. This is the default.  

 

PARALLEL  

causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.  

 

PARALLEL integer  

specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.  

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. For more information on global names, see Oracle8i Distributed Database Systems.  

 

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.  

RENAME FILE  

renames datafiles, tempfiles, 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 before specifying this clause.  

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 Oracle8i Migration.  

You can use the following clauses 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 PUBLIC, 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.  

CHARACTER SET

NATIONAL CHARACTER SET  

CHARACTER SET changes the character set the database uses to store data. NATIONAL CHARACTER SET changes the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. Specify character_set without quotation marks.  

 

WARNING: You cannot roll back an ALTER DATABASE CHARACTER SET or ALTER DATABASE NATIONAL CHARACTER SET statement. Therefore, you should perform a full backup before issuing either of these statements.  

 

Restrictions:

  • You must have SYSDBA system privilege, and you must start up the database in restricted mode (for example, with the SQL*Plus STARTUP RESTRICT command).

  • The current character set must be a strict subset of the character set to which you change. That is, each character represented by a codepoint value in the source character set must be represented by the same codepoint value in the target character set. For a list of valid character sets, see Oracle8i National Language Support Guide.

 

datafile/tempfile_clauses  

let you modify datafiles and tempfiles.  

You can use any of the following clauses 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 clause 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.  

 

Restriction: 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 increase or decrease the size of the datafile to the specified absolute size in bytes. Use K or M to specify this size in kilobytes or megabytes. There is no default, so you must specify a size.

If sufficient disk space is not available for the increased size, or if the file contains data beyond the specified decreased size, Oracle returns an error.  

 

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

 

 

ON enables autoextend.  

 

 

NEXT specifies in bytes the size of the next increment of disk space to be automatically allocated to the datafile when more extents are required. 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 Oracle8i Backup and Recovery Guide.  

TEMPFILE  

Lets you resize your temporary datafile or specify the autoextend_clause, with the same effect as with a permanent datafile.

Restriction: You cannot specify TEMPFILE unless the database is open.  

 

DROP  

drops tempfile from the database. The tablespace remains.  

logfile_clauses  

lets you add, drop, or modify log files.  

ARCHIVELOG  

specifies that the contents of a redo log file group must be archived before the group can be reused. This mode prepares for the possibility of media recovery. Use this clause 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  

specifies that 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.  

Use the ARCHIVELOG clause and NOARCHIVELOG clause only if your instance has the database mounted in parallel server disabled mode, but not open.  

ADD LOGFILE  

adds one or more redo log file groups to the specified thread, making them available to the instance assigned the thread.  

 

THREAD integer  

is applicable only if you are using Oracle with the Parallel Server option in parallel mode. If you omit THREAD, the redo log file group is added to the thread assigned to your instance.  

 

GROUP integer  

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 REUSE. 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 integer  

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. Specify a redo log file group as indicated for the ADD LOGFILE MEMBER clause.

  • To drop the current log file group, you must first issue an ALTER SYSTEM SWITCH LOGFILE statement. See "ALTER SYSTEM".

  • You cannot drop a redo log file group if it needs archiving.

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

  • To drop a log file in the current log, you must first issue an ALTER SYSTEM SWITCH LOGFILE statement. See "ALTER 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 statement 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.  

 

Do not 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 statement is interrupted by a system or instance failure, then the database may hang. If this occurs, reissue the statement after 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 you have taken the datafile offline with the database in ARCHIVELOG mode (that is, you specified ALTER DATABSE ... DATAFILE OFFLINE without the DROP keyword), 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 statement completes.  

controlfile_clauses  

 

 

CREATE STANDBY CONTROLFILE  

creates a control file to be used to maintain a standby database. For more information, see Oracle8i Backup and Recovery Guide. If the file already exists, you must specify REUSE.  

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

 

TO TRACE  

writes SQL statements to the database's trace file rather than making a physical backup of the control file. The SQL statements can 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 statements from the trace file into a script file, edit the statements 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.

 

Examples

READ ONLY / READ WRITE Example

The first statement below opens the database in read-only mode. The second statement returns the database to read-write mode and clears the online redo logs: 

ALTER DATABASE OPEN READ ONLY;

ALTER DATABASE OPEN READ WRITE RESETLOGS;
PARALLEL Example

The following statement performs tablespace recovery using parallel recovery processes: 

ALTER DATABASE
   RECOVER TABLESPACE binky
   PARALLEL;
Redo Log File Group Example

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;
Redo Log File Group Member Example

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;
Dropping a Log File Member

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

ALTER DATABASE stocks  
    DROP LOGFILE MEMBER 'diskc:log3.log';
Renaming a Log File Member

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.

Dropping All Log File Group Members

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

ALTER DATABASE stocks DROP LOGFILE GROUP 3;
Adding a Redo Log File Group

The following statement adds a redo log file group containing three members to thread 5 (in an Oracle Parallel Server environment) 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' );
Disabling a Parallel Server Thread

The following statement disables thread 5 in a parallel server: 

ALTER DATABASE stocks  
    DISABLE THREAD 5;
Enabling a Parallel Server Thread

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;
Creating a New Datafile

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

ALTER DATABASE 
    CREATE DATAFILE 'disk1:db1.dat' AS 'disk2:db1.dat';
Changing the Global Database Name

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;
Character Set Example

The following statements change the database character set and national character set to the WE8ISO8859P1 character set: 

ALTER DATABASE db1 CHARACTER SET WE8ISO8859P1;
ALTER DATABASE db1 NATIONAL CHARACTER SET WE8ISO8859P1;

The database name is optional, and the character set name is specified without quotation marks.

Resizing a Datafile

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

ALTER DATABASE  
    DATAFILE 'disk1:db1.dat' RESIZE 10 M;
Clearing a Log File

The following statement clears a log file: 

ALTER DATABASE  
    CLEAR LOGFILE 'disk3:log.dbf';
Database Recovery Examples

The following statement performs complete recovery of the entire database, letting Oracle generate the name of the next archived redo log file needed: 

ALTER DATABASE 
  RECOVER AUTOMATIC DATABASE;

The following statement explicitly names a redo log file for Oracle to apply: 

ALTER DATABASE 
    RECOVER LOGFILE 'diska:arch0006.arc';

The following statement performs time-based recovery of the database: 

ALTER DATABASE 
    RECOVER AUTOMATIC UNTIL TIME '1998-10-27:14:00:00';

Oracle recovers the database until 2:00 pm on October 27, 1998.

The following statement recovers the tablespace USER5: 

ALTER DATABASE 
    RECOVER TABLESPACE user5;

The following statement recovers the standby datafile /FINANCE/STBS_21.f, using the corresponding datafile in the original standby database, plus all relevant archived logs and the current standby database control file: 

ALTER DATABASE  
   RECOVER STANDBY DATAFILE '/finance/stbs_21.f' 
   UNTIL CONTROLFILE;
Managed Standby Database Examples

The following statement recovers the standby database in managed (sustained) standby recovery mode: 

ALTER DATABASE 
   RECOVER MANAGED STANDBY DATABASE;

The following statement puts the database in managed standby recovery mode. The sustained recovery process will wait up to 60 minutes for the next archive log: 

ALTER DATABASE 
   RECOVER MANAGED STANDBY DATABASE TIMEOUT 60;

If each subsequent log arrives within 60 minutes of the last log, sustained recovery continues indefinitely or until manually terminated.

The following statement terminates the managed recovery operation: 

ALTER DATABASE 
   RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE;

The sustained recovery operation terminates before the next group of redo is read 
from the current redo log file. Media recovery ends in the "middle" of applying 
redo from the current redo log file.

ALTER DIMENSION

Syntax


level_clause::=


hierarchy_clause::=


join_clause::=


attribute_clause::=


Purpose

To change the hierarchical relationships or dimension attributes of a dimension. For more information on dimensions, see "CREATE DIMENSION".

Prerequisites

The dimension must be in your schema or you must have the ALTER ANY DIMENSION system privilege to use this statement.

A dimension is always altered under the rights of the owner.

Keywords and Parameters

The following keywords and parameters have meaning unique to ALTER DIMENSION. The remaining keywords and parameters have the same functionality that they have in the CREATE DIMENSION statement. See "CREATE DIMENSION".

schema  

is the schema of the dimension you want to modify. If you do not specify schema, Oracle assumes the dimension is in your own schema.  

dimension  

is the name of the dimension. This dimension must already exist.  

ADD  

lets you add a level, hierarchy, or attribute to the dimension. Adding one of these elements does not invalidate any existing materialized view.

Oracle processes ADD LEVEL clauses prior to any other ADD clauses.  

DROP  

lets you drop a level, hierarchy, or attribute from the dimension. Any level, hierarchy, or attribute you specify must already exist.

Restriction: If any attributes or hierarchies reference a level, you cannot drop the level until you either drop all the referencing attributes and hierarchies or specify CASCADE.  

 

CASCADE  

causes Oracle to drop any attributes or hierarchies that reference the level, along with the level itself.  

 

RESTRICT  

prevents Oracle from dropping a level that is referenced by any attributes or hierarchies. This is the default.  

COMPILE  

explicitly recompiles an invalidated dimension. Oracle automatically compiles a dimension when you issue an ADD clause or DROP clause. However, if you alter an object referenced by the dimension (for example, if you drop and then re-create a table referenced in the dimension), the dimension will be invalidated, and you must recompile it explicitly.  

Examples

This example modifies the TIME dimension: 

ALTER DIMENSION time 
   DROP HIERARCHY week_month;
ALTER DIMENSION time
   DROP ATTRIBUTE cur_date;
ALTER DIMENSION time
   ADD LEVEL day IS time_tab.t_day
   ADD ATTRIBUTE day DETERMINES t_holiday;

ALTER FUNCTION

Syntax


Purpose

To recompile an invalid standalone stored function. Explicit recompilation eliminates the need for implicit run-time recompilation and prevents associated run-time compilation errors and performance overhead.

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

Note:

This statement does not change the declaration or definition of an existing function. To redeclare or redefine a function, use the CREATE FUNCTION statement with the OR REPLACE clause; see "CREATE FUNCTION".  

Prerequisites

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

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. If Oracle does not compile the function successfully, you can see the associated compiler error messages with the SQL*Plus command SHOW ERRORS.  

DEBUG  

instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.  

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, and GET_BAL remains invalid.

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

ALTER INDEX

Syntax


deallocate_unused_clause::=


allocate_extent_clause::=


parallel_clause::=


physical_attributes_clause::=


storage_clause: See "storage_clause".

rebuild_clause::=


compression_clause::=


partitioning_clauses::=


modify_default_attributes_clause::=


modify_partition_clause::=


rename_partition/ subpartition_clause::=


drop_partition_clause::=


split_partition_clause::=


partition_description::=


modify_subpartition_clause::=


Purpose

To change or rebuild an existing index.

For information on creating an index, see "CREATE INDEX".

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 or subpartitions.

You must have tablespace quota to modify, rebuild, or split an index partition or to modify or rebuild an index subpartition.

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.  

 

Restrictions:

  • If index is a domain index, you can specify only the PARAMETERS clause, the RENAME clause, or the rebuild_clause (with or without the PARAMETERS clause). No other clauses are valid.

  • You cannot alter or rename a domain index that is marked LOADING or FAILED. If an index is marked FAILED, the only clause you can specify is REBUILD. For information on the LOADING and FAILED states of domain indexes, see Oracle8i Data Cartridge Developer's Guide.

 

deallocate_unused_clause  

explicitly deallocates unused space at the end of the index and makes the freed space available for other segments in the tablespace. Only unused space above the high water mark can be freed. For more information on this clause, see "ALTER TABLE".

If index is range-partitioned or hash-partitioned, Oracle deallocates unused space from each index partition. If index is a local index on a composite-partitioned table, Oracle deallocates unused space from each index subpartition.

Restrictions:

  • You cannot specify this clause for an index on a temporary table.

  • You cannot specify this clause and also specify the rebuild_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. If you omit KEEP, all unused space is freed.  

 

For a complete description of this clause, see "ALTER TABLE".  

allocate_extent_clause  

explicitly allocates a new extent for the index. For a local index on a hash-partitioned table, Oracle allocates a new extent for each partition of the index.

Restriction: You cannot specify this clause for an index on a temporary table or for a range-partitioned or composite-partitioned 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 SIZE, Oracle determines the size based on the values of the index's storage parameters.  

 

DATAFILE  

specifies one of the datafiles in the index's tablespace to contain the new extent. If you omit DATAFILE, 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 change the values of the NEXT and PCTINCREASE storage parameters, so does not affect the size of the next extent to be allocated.  

parallel_clause  

changes the default degree of parallelism for queries and DML on the index. For additional information, see the Notes to the parallel_clause of "CREATE TABLE".  

 

NOPARALLEL  

specifies serial execution. This is the default.  

 

PARALLEL  

causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances multiplied by the value of the PARALLEL_THREADS_PER_CPU initialization parameter.  

 

PARALLEL integer  

specifies the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution processes. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.  

 

Restriction: You cannot specify this clause for an index on a temporary table.  

physical_attributes_clause  

lets you change the values of parameters for a nonpartitioned index, all partitions and subpartitions of a partitioned index, a specified partition, or all subpartitions of a specified partition. See these parameters in "CREATE TABLE".

Restrictions:

  • You cannot specify this clause for an index on a temporary table.

  • You cannot specify the PCTUSED parameter when altering an index.

  • You cannot change the value of the PCTFREE parameter for the index as a whole (ALTER INDEX) or for a partition (ALTER INDEX ... MODIFY PARTITION). You can specify PCTFREE in all other forms of the ALTER INDEX statement.

 

 

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

LOGGING| NOLOGGING  

LOGGING|NOLOGGING specifies that subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned index, a range or hash index partition, or all partitions or subpartitions of a composite-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 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.

Restriction: You cannot specify this clause for an index on a temporary table.  

 

For more information about LOGGING and parallel DML, see Oracle8i Concepts and the Oracle8i Parallel Server Concepts and Administration.  

RECOVERABLE| UNRECOVERABLE  

These keywords are deprecated and have been replaced with LOGGING and NOLOGGING, respectively. Although RECOVERABLE and UNRECOVERABLE are supported for backward compatibility, Oracle Corporation strongly recommends that you use the LOGGING and NOLOGGING keywords.  

 

RECOVERBLE is not a valid keyword for creating partitioned tables or LOB storage characteristics. UNRECOVERABLE is not a valid keyword for creating partitioned or index-organized tables. Also, it can be specified only with the AS subquery clause of CREATE INDEX.  

rebuild_clause  

re-creates an existing index or one of its partitions or subpartitions. For a function-based index, this clause also enables the index. If the function on which the index is based does not exist, the rebuild statement will fail.  

 

Restrictions:

  • You cannot rebuild an index on a temporary table.

  • You cannot rebuild an entire partitioned index. You must rebuild each partition or subpartition, as described below.

  • You cannot also specify the deallocate_unused_clause in this statement.

  • You cannot change the value of the PCTFREE parameter for the index as a whole (ALTER INDEX) or for a partition (ALTER INDEX ... MODIFY PARTITION). You can specify PCTFREE in all other forms of the ALTER INDEX statement.

 

 

PARTITION partition  

rebuilds one partition of an index. You can also use this clause 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 Oracle8i Administrator's Guide.

Restriction: You cannot specify this clause for a local index on a composite-partitioned table. Instead, use the REBUILD SUBPARTITION clause.  

 

SUBPARTITION subpartition  

rebuilds one subpartition of an index. You can also use this clause to move an index subpartition to another tablespace. If you do not specify TABLESPACE, the subpartition is rebuilt in the same tablespace.

Restrictions: The only parameters you can specify for a subpartition are TABLESPACE and the parallel_clause.  

 

REVERSE | NOREVERSE  

specifies whether the bytes of the index block are stored in reverse order.

  • REVERSE stores the bytes of the index block in reverse order and excludes 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.

 

 

 

Restrictions:

  • You cannot reverse a bitmap index or an index-organized table.

  • You cannot specify REVERSE or NOREVERSE for a partition or subpartition.

 

 

TABLESPACE  

specifies the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it.  

 

COMPRESS  

enables key compression, which eliminates repeated occurrence of key column values. Use integer to specify the prefix length (number of prefix columns to compress).

  • For unique indexes, the range of valid prefix length values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

  • For nonunique indexes, the range of valid prefix length values is from 1 to the number of key columns. The default prefix length is number of key columns.

Oracle compresses only nonpartitioned indexes that are nonunique or unique indexes of at least two columns.

Restriction: You cannot specify COMPRESS for a bitmapped index.  

 

NOCOMPRESS  

disables key compression. This is the default.  

 

ONLINE  

specifies that DML operations on the table or partition are allowed during rebuilding of the index.

Restriction: Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.  

 

COMPUTE STATISTICS  

enables you to collect statistics at relatively little cost during the rebuilding of an index. These statistics are stored in the data dictionary for ongoing use by the optimizer in choosing a plan of execution for SQL statements.

The types of statistics collected depend on the type of index you are rebuilding.  

 

 

Note: If you create an index using another index (instead of a table), the original index might not provide adequate statistical information. Therefore, Oracle generally uses the base table to compute the statistics, which will improve the statistics but may negatively affect performance.  

 

.  

Additional methods of collecting statistics are available in PL/SQL packages and procedures. See Oracle8i Supplied Packages Reference.  

 

LOGGING | NOLOGGING  

specifies whether the ALTER INDEX...REBUILD operation will be logged.  

PARAMETERS  

applies only to domain indexes. This clause specifies the parameter string for altering the index (or, in the rebuild_clause, rebuilding the index). The maximum length of the parameter string is 1000 characters. This string is passed uninterpreted to the appropriate indextype routine. For more information on these routines, see Oracle8i Data Cartridge Developer's Guide. For more information on domain indexes, see "CREATE INDEX".  

 

Restrictions:

  • You cannot specify this clause for any indexes other than domain indexes.

  • The parameter string is passed to the appropriate routine only if index is not marked UNUSABLE.

 

ENABLE  

applies only to a function-based index that has been disabled because a user-defined function used by the index was dropped or replaced. This clause enables such an index if

  • the function is currently valid,

  • the signature of the current function matches the signature of the function when the index was created, and

  • the function is currently marked as DETERMINISTIC.

Restriction: You cannot specify any other clauses of ALTER INDEX in the same statement with ENABLE.  

DISABLE  

applies only to a function-based index. This clause enables you to disable the use of a function-based index. You might want to do so, for example, while working on the body of the function. Afterward you can either rebuild the index or specify another ALTER INDEX statement with the ENABLE keyword.  

UNUSABLE  

marks the index or index partition(s) or index subpartition(s) 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.

Restriction: You cannot specify this clause for an index on a temporary table.  

RENAME TO  

renames index to new_index_name. The new_index_name is a single identifier and does not include the schema name.  

COALESCE  

instructs Oracl