Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER CLUSTER to ALTER SEQUENCE, 3 of 18
Use the ALTER
DATABASE
statement to modify, maintain, or recover an existing database.
See Also:
|
You must have the ALTER
DATABASE
system privilege.
To specify the RECOVER
clause, you must also have the SYSDBA
system privilege.
alter_database::=
alter_database
Groups of ALTER DATABASE syntax:
startup_clauses
recovery_clauses
general_recovery
full_database_recovery
partial_database_recovery
parallel_clause::=
parallel_clause
managed_standby_recovery
database_file_clauses
autoextend_clause
maxsize_clause::=
maxsize_clause
logfile_clauses
logfile_descriptor::=
logfile_descriptor
controlfile_clauses
standby_database_clauses
default_settings_clauses
set_time_zone_clause
conversion_clauses
redo_thread_clauses
Specify the name of 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 Oracle Net database specification.
The startup_clauses
let you mount and open the database so that it is accessible to users.
Use the MOUNT
clause to mount the database. Do not use this clause when the database is mounted.
Specify MOUNT
STANDBY
DATABASE
to mount the standby database. As soon as this statement executes, the standby instance receives archived redo logs from the primary instance and archives the logs to the STANDBY_ARCHIVE_DEST
location.
Specify MOUNT
CLONE
DATABASE
to mount the clone database.
Use the OPEN
clause to make the database 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.
If you specify only OPEN
, without any other keywords, the default is OPEN
READ
WRITE
NORESETLOGS
.
Specify READ
WRITE
to open the database in read/write mode, allowing users to generate redo logs. This is the default.
Specify RESETLOGS
to reset 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 specify RESETLOGS
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.
Specify NORESETLOGS
to retain the current state of the log sequence number and redo log files.
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.
Specify READ
ONLY
to restrict 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 on the OPEN clause:
READ
ONLY
if it is currently opened READ
WRITE
by another instance.
READ
ONLY
if it requires recovery.
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
.
The recovery_clauses
include post-backup operations.
See Also:
Oracle9i Backup and Recovery Concepts and Oracle9i Recovery Manager User's Guide and Reference for information on backing up the database |
The general_recovery
clause lets you design media recovery for the database or standby database, or for specified tablespaces or files. You can use this clause when your instance has the database mounted, open or closed, and the files involved are not in use.
Restrictions:
See Also:
RECOVER
command
Specify AUTOMATIC
if you want Oracle to automatically generate the name of the next archived redo log file needed to continue the recovery operation. If the LOG_ARCHIVE_DEST_
n
parameters are defined, Oracle scans those that are valid and enabled for the first local destination. It uses that destination in conjunction with LOG_ARCHIVE_FORMAT
to generate the target redo log filename. If the LOG_ARCHIVE_DEST_
n
parameters are not defined, Oracle uses the value of the LOG_ARCHIVE_DEST
parameter instead.
If the resulting file is found, Oracle applies the redo contained in that file. 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 that the archived filename differs from what Oracle would generate, you can save time by using the LOGFILE
clause.
Specify FROM
'location'
to indicate 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 that the archived redo log file group is in the location specified by the initialization parameter LOG_ARCHIVE_DEST
or LOG_ARCHIVE_DEST_1
.
The full_database_recovery
clause lets you recover an entire database.
Specify the DATABASE
clause to recover the entire database. This is the default. You can use this clause only when the database is closed.
Specify the STANDBY
DATABASE
clause to recover 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.
UNTIL
clause to specify the duration of the recovery operation.
CANCEL
indicates cancel-based recovery. This clause recovers the database until you issue the ALTER
DATABASE
statement with the RECOVER
CANCEL
clause.
TIME
indicates 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
indicates 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
if you want to use a backup control file instead of the current control file.
The partial_database_recovery
clause lets you recover individual tablespaces and datafiles.
Specify the TABLESPACE
clause to recover only the specified tablespaces. You can use this clause if the database is open or closed, provided the tablespaces to be recovered are offline.
Specify the DATAFILE
clause to recover the specified datafiles. You can use this clause when the database is open or closed, provided the datafiles to be recovered are offline.
Specify STANDBY
TABLESPACE
to reconstruct a lost or damaged tablespace in the standby database using archived redo log files copied from the primary database and a control file.
Specify STANDBY
DATAFILE
to reconstruct a lost or damaged datafile in the standby database using archived redo log files copied from the primary database and a control file.
UNTIL
[CONSISTENT
WITH
] CONTROLFILE
if you want the recovery of an old standby datafile or tablespace to use 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.
Specify the LOGFILE
'filename
' to continue media recovery by applying the specified redo log file.
Use the TEST
clause to conduct a trial recovery. A trial recovery is useful if a normal recovery procedure has encountered some problem. It lets you look ahead into the redo stream to detect possible additional problems. The trial recovery applies redo in a way similar to normal recovery, but it does not write changes to disk, and it rolls back its changes at the end of the trial recovery.
The ALLOW
integer
CORRUPTION
clause lets you specify, in the event of logfile corruption, the number of corrupt blocks that can be tolerated while allowing recovery to proceed.
When you use this clause during trial recovery (that is, in conjunction with the TEST
clause), integer
can exceed 1. When using this clause during normal recovery, integer
cannot exceed 1.
See Also:
|
Use the PARALLEL
clause to specify whether the recovery of media will be parallelized.
Specify NOPARALLEL
for serial execution. This is the default.
Specify PARALLEL
if you want 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.
Specification of integer indicates 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 servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.
Specify CONTINUE
to continue multi-instance recovery after it has been interrupted to disable a thread.
Specify CONTINUE
DEFAULT
to continue 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.
Specify CANCEL
to terminate cancel-based recovery.
The managed_standby_recovery
clause specifies automated standby recovery mode. This mode assumes that the automated 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. Automated standby recovery is restricted to media recovery. You can use this clause when your instance has the database mounted, open or closed, and the files involved are not in use.
Restrictions: The same restrictions apply as are listed under general_recovery
.
See Also:
Oracle9i User-Managed Backup and Recovery Guide for more information on the parameters of this clause |
Specify NODELAY
if the need arises to apply a delayed archivelog immediately on the standby database. This clause overrides any setting of DELAY
in the LOG_ARCHIVE_DEST_
n
parameter on the primary database. If you omit this clause, application of the archivelog is delayed according to the parameter setting. If DELAY
was not specified in the parameter, the archivelog is applied immediately.
Use the TIMEOUT
clause to specify in minutes the wait period of the managed recovery operation. The recovery process waits for integer
minutes for a requested archived log redo to be available for writing to the automated 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 automated standby recovery mode.
Restriction: You cannot specify TIMEOUT
if you have specified DISCONNECT
[FROM
SESSION
]. TIMEOUT
applies only to foreground recovery operations, whereas the DISCONNECT
clause initiates background recovery operations.
If you do not specify TIMEOUT
, the database remains in automated standby recovery mode until you reissue the statement with the RECOVER
CANCEL
clause or until instance shutdown or failure.
Specify CANCEL
to terminate the managed standby recovery operation after applying all the redo in the current archived redo file. If you specify only CANCEL
, session control returns when the recovery process actually terminates.
CANCEL
IMMEDIATE
to terminate the managed recovery operation after applying all the redo in the current archived redo file or after the next redo log file read, whichever comes first. Session control returns when the recovery process actually terminates.
Restriction: The CANCEL
IMMEDIATE
clause cannot be issued from the same session that issued the RECOVER
MANAGED
STANDBY
DATABASE
statement.
CANCEL
IMMEDIATE
NOWAIT
is the same as CANCEL
IMMEDIATE
except that session control returns immediately, not after the recovery process terminates.
CANCEL
NOWAIT
terminates the managed recovery operation after the next redo log file read and returns session control immediately.
Specify DISCONNECT
to indicate that the managed redo process (MRP), an Oracle background process, should apply archived redo files as a detached background process. Doing so leaves the current session available for other tasks. (The FROM
SESSION
keywords are optional and are provided for semantic clarity.)
See Also:
Oracle9i Data Guard Concepts and Administration for information on the managed redo process |
Specify FINISH
to recover the current log standby logfiles of the standby database. This clause is useful in the event of the failure of the primary database, when the logwriter (LGWR) process has been transmitting redo to the standby current logs. This clause overrides any delay intervals specified for the archivelogs, so that Oracle applies the logs immediately.
Specify NOWAIT
to have control returned immediately rather than after the recovery process is complete.
See Also:
|
Specify END
BACKUP
to take out of online backup mode any datafiles in the database currently in online backup mode. The database must be mounted but not open when you perform this operation.
You can end online ("hot") backup operations in three ways. During normal operation, you can take a tablespace out of online backup mode using the ALTER
TABLESPACE
... END
BACKUP
statement. Doing so avoids the increased overhead of leaving the tablespace in online backup mode.
After a system failure, instance failure, or SHUTDOWN
ABORT
operation, Oracle does not know whether the files in online backup mode match the files at the time the system crashed. If you know the files are consistent, you can take either individual datafiles or all datafiles out of online backup mode. Doing so avoids media recovery of the files upon startup.
ALTER
DATABASE
DATAFILE
... END
BACKUP
statement. See
database_file_clauses
.
ALTER
TABLESPACE
... END
BACKUP
statement.
The database_file_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.
Use the CREATE
DATAFILE
clause to create 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.
AS
NEW
to create an Oracle-managed datafile with a system-generated filename, the same size as the file being replaced, in the default file system location for datafiles.
AS
filespec
to assign a filename (and optional size) for the new datafile.
If you specify AS
filespec
, and filename
is an existing Oracle-managed datafile, then Oracle deletes the old file. If you specify AS
filespec
and filename
is an existing user-managed datafile, Oracle leaves the file as is and does not return an error.
If you omit the AS
clause entirely, Oracle creates the new file with the same 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.
The DATAFILE
clauses affect your database files as follows:
Specify ONLINE
to bring the datafile online.
Specify OFFLINE
to take 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.
If the database is in NOARCHIVELOG
mode, you must specify the DROP
clause to take a datafile offline. However, the DROP
clause does not remove the datafile from the database. To do that, you must drop the tablespace in which the datafile resides. Until you do so, the datafile remains in the data dictionary with the status RECOVER
or OFFLINE
.
If the database is in ARCHIVELOG
mode, Oracle ignores the DROP
keyword.
Specify RESIZE
if you want Oracle to attempt 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.
Specify END
BACKUP
to take the datafile out of online backup mode. The END
BACKUP
clause is described more fully at the top level of the syntax of ALTER
DATABASE
. See "END BACKUP Clause".
Use the TEMPFILE
clause to 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.
Specify DROP
to drop tempfile
from the database. The tablespace remains.
If you specify INCLUDING
DATAFILES
, Oracle also deletes the associated operating system files and writes a message to the alert log for each such deleted file.
Use the autoextend_clause
to enable or disable the automatic extension of a new datafile or tempfile. If you do not specify this clause, these files are not automatically extended.
Specify ON
to enable autoextend.
Specify OFF
to turn off autoextend if is turned on.
Use the NEXT
clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. Use K
or M
to specify this size in kilobytes or megabytes. The default is the size of one data block.
Use the MAXSIZE
clause to specify the maximum disk space allowed for automatic extension of the datafile.
Use the UNLIMITED
clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.
Use the RENAME
FILE
clause to rename datafiles, tempfiles, or redo log file members. You must create each filename using the conventions for filenames on your operating system before specifying this clause.
This clause renames only files in the control file. It does not actually rename them on your operating system. The operating system files continue to exist, but Oracle no longer uses them. If the old files were Oracle managed, Oracle drops the old operating system file after this statement executes, because the control file no longer points to them as datafiles, tempfiles, or redo log files.
The logfile clauses let you add, drop, or modify log files.
Use the ARCHIVELOG
clause and NOARCHIVELOG
clause only if your instance has the database mounted but not open, with Real Application Clusters disabled.
Specify ARCHIVELOG
if you want the contents of a redo log file group to 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 and mounting the database with Real Application Clusters disabled.
Specify NOARCHIVELOG
if you do not want the contents of a redo log file group to be archived so that the group can be reused. This mode does not prepare for recovery after media failure.
Use the ADD
LOGFILE
clause to add one or more redo log file groups to the specified thread, making them available to the instance assigned the thread. If you specify STANDBY
, the redo log file created is for use by standby databases only.
To learn whether a logfile has been designated for online or standby database use, query the TYPE
column of the V$LOGFILE
dynamic performance view.
The THREAD
clause is applicable only if you are using Oracle with the Real Application Clusters option in parallel mode. integer
is the thread number. The number of threads you can create is limited by the value of the MAXINSTANCES
parameter specified in the CREATE
DATABASE
statement.
If you omit THREAD
, the redo log file group is added to the thread assigned to your instance.
The GROUP
clause 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
.
Each filespec
specifies a redo log file group containing one or more members (that is, one or more copies).
See Also:
|
Use the ADD
LOGFILE
MEMBER
clause to add 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 STANDBY
for symmetry, to indicate that the logfile member is for use only by a standby database. However, this keyword is not required. If group integer
was added for standby database use, all of its members will be used only for standby databases as well.
You can specify an existing redo log file group in one of two ways:
Specify the value of the GROUP
parameter that identifies the redo log file group.
List all members of the redo log file group. You must fully specify each filename according to the conventions of your operating system.
Specify the ADD
SUPPLEMENTAL
LOG
DATA
clause to place additional column data into the log stream any time an update operation is performed. This information can be used by LogMiner and any products building on LogMiner technology.
When you specify PRIMARY
KEY
COLUMNS
, Oracle ensures, for all tables with a primary key, that all columns of the primary key are placed into the redo log whenever an update operation is performed. If no primary key is defined, Oracle places into the redo log a set of columns that uniquely identifies the row. This set may include all columns with a fixed-length maximum size.
When you specify UNIQUE
INDEX
COLUMNS
, Oracle ensures, for all tables with a unique key, that if any unique key columns are modified, all other columns belonging to the unique are also placed into the redo log.
Use the DROP
LOGFILE
clause to drop all members of a redo log file group. Specify a redo log file group as indicated for the ADD
LOGFILE
MEMBER
clause.
ALTER
SYSTEM
SWITCH
LOGFILE
statement.
Use the DROP
LOGFILE
MEMBER
clause to drop one or more redo log file members. Each 'filename'
must fully specify a member using the conventions for filenames on your operating system.
ALTER
SYSTEM
SWITCH
LOGFILE
statement.
DROP
LOGFILE
clause.
Use the DROP
SUPPLEMENTAL
LOG
DATA
clause to instruct Oracle to stop placing additional log information into the redo log stream whenever an update operation occurs. This statement terminates the effect of a previous ADD
SUPPLEMENTAL
LOG
DATA
statement.
Use the CLEAR
LOGFILE
clause to reinitialize 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
if you want to reuse a redo log that was not archived.
UNRECOVERABLE
DATAFILE
if you have taken the datafile offline with the database in ARCHIVELOG
mode (that is, you specified ALTER
DATABASE
... 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.
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.
The controlfile_clauses
let you create or back up a control file.
Use the CREATE
STANDBY
CONTROLFILE
clause to create a control file to be used to maintain a standby database. If the file already exists, you must specify REUSE
.
Use the BACKUP
CONTROLFILE
clause to back up the current control file.
Specify 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
.
Specify TO
TRACE
if you want Oracle to write SQL statements to the database's trace file rather than making a physical backup of the control file. You can use SQL statements written to the trace file to start up the database, re-create the control file, and recover and open the database appropriately, based on the created control file. The database must be open or mounted when you specify this clause.
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).
NORESETLOGS
indicates that the SQL statement written to the trace file for starting the database is ALTER
DATABASE
OPEN
NORESETLOGS
. This is the default.
RESETLOGS
indicates that the SQL statement written to the trace file for starting the database is ALTER
DATABASE
OPEN
RESETLOGS
.
Use these clauses to activate the standby database or to specify whether it is in protected or unprotected mode.
See Also:
Oracle9i Data Guard Concepts and Administration for descriptions of the standby database and for information on maintaining and using standby databases |
The ACTIVATE
STANDBY
DATABASE
clause changes the state of a standby database to an active database and prepares it to become the primary database. The database must be mounted before you can specify this clause. The keyword PHYSICAL
is optional.
The SET
STANDBY
DATABASE
clause lets you specify whether your database environment is in no-data-loss mode. In this mode, Oracle places highest priority on maintaining an absolute match between the primary and standby databases. The standby database must be mounted, and no Real Application Clusters instance can have the primary database open, even in exclusive mode.
Specify PROTECTED
to indicate that the standby instance must contain at least one standby archivelog destination to be archived by the logwriter (LGWR
) process in order for the primary database to be opened and to remain open in the event the last connection from primary to standby database is lost. In a Real Application Clusters environment, Oracle will verify that the LGWR
processes of all instances that have the primary database open archive to the same standby databases.
If a connection to the last standby database is lost, Oracle will shut down the primary instance. Therefore, you should use this setting only if absolute correspondence between the primary and standby databases is more important than availability of the database.
Specify UNPROTECTED
to indicate that the instance does not require any standby databases to be maintained by the logwriter process. This is the default.
Use this setting if the absolute correspondence between the primary and standby databases is not as important as availability of the database.
To determine whether a database is in PROTECTED
or UNPROTECTED
mode, query the STANDBY_DATABASE
column of the V$DATABASE
dynamic performance view.
Specify the REGISTER
LOGFILE
clause from the standby database to register log files from the failed primary. This operation is required unless missing log files from the failed primary have been copied to the directory specified in the STANDBY_ARCH_DEST
initialization parameter.
Specify OR
REPLACE
to allow an existing archivelog entry in the standby database to be updated, for example, when its location or filespec changes. The SCNs of the entries must match exactly, and the original entry must have been created by the managed standby log transmittal mechanism.
On the primary database, specify PREPARE
TO
SWITCHOVER
TO
STANDBY
to prepare the current primary database for switchover to standby status. On one of the standby databases, issue a PREPARE
TO
SWITCHOVER
TO
PRIMARY
statement to prepare the standby database for switchover to primary status.
Use these clauses to modify the default settings of the database.
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. The database must be open.
Cautions:
|
Notes on Changing Character Sets:
In Oracle9i, CLOB
data is stored as UCS-2 (two-byte fixed-width Unicode) for multibyte database character sets. For single-byte database character sets, CLOB
data is stored in the database character set. When you change the database or national character set with an ALTER
DATABASE
statement, no data conversion is performed. Therefore, if you change the database character set from single byte to multibyte using this statement, CLOB
columns will remain in the original database character set. This may introduce data inconsistency in your CLOB
columns. Likewise, if you change the national character set from one Unicode set to another, your SQL NCHAR
columns (NCHAR
, NVARCHAR2
, NCLOB
) may be corrupted.
The recommended procedure for changing database character sets is:
CLOB
and SQL NCHAR
datatype columns.
CLOB
and SQL NCHAR
columns.
ALTER
DATABASE
statements to change the character set and national character set.
CLOB
and SQL NCHAR
columns.
Restrictions:
SYSDBA
system privilege, and you must start up the database in restricted mode (for example, with the SQL*Plus STARTUP
RESTRICT
command).
Use the SET
TIME_ZONE
clause to set the time zone of the database. You can specify the time zone in two ways:
hh:mm
is -12:00 to +14:00.
TZNAME
column of the V$TIMEZONE_NAMES
dynamic performance view.
Oracle normalizes all new TIMESTAMP
WITH
LOCAL
TIME
ZONE
data to the time zone of the database when the data is stored on disk. Oracle does not automatically update existing data in the database to the new time zone.
After setting or changing the time zone with this clause, you must restart the database for the new time zone to take effect.
Specify this clause to change the default temporary tablespace of the database. After this operation completes, Oracle automatically reassigns to the new default temporary tablespace all users who had been assigned to the old default temporary tablespace. You can then drop the old default temporary tablespace if you wish.
To learn the name of the current default temporary tablespace, query the PROPERTY_VALUE
column of the DATABASE_PROPERTIES
data dictionary table where the PROPERTY_NAME
= 'DEFAULT_TEMP_TABLESPACE
'.
Restriction: The tablespace you assign or reassign as the default temporary tablespace must have a standard block size.
Specify RESET
COMPATIBILITY
to mark the database to be reset to an earlier version of Oracle when the database is next restarted. Do not use this clause when the database is mounted.
See Also:
Oracle9i Database Migration for more information on downgrading to an earlier version of Oracle |
Use the CONVERT
clause to complete the conversion of the Oracle7 data dictionary. After you use this clause, the Oracle7 data dictionary no longer exists in the Oracle database.
Use these clauses to enable and disable the thread of redo log file groups.
In an Oracle Real Application Clusters environment, specify ENABLE
THREAD
to enable the specified thread of redo log file groups. The thread must have at least two redo log file groups before you can enable it. The database must be open.
Specify PUBLIC
to make 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
.
See Also:
Oracle9i Real Application Clusters Administration for more information on enabling and disabling threads |
Specify DISABLE
THREAD
to disable the specified thread, making it unavailable to all instances. The database must be open, but you cannot disable a thread if an instance using it has the database mounted.
See Also:
Oracle9i Real Application Clusters Administration for more information on enabling and disabling threads |
Specify RENAME
GLOBAL_NAME
to change 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. Do not use this clause when the database is mounted.
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;
The following statement performs tablespace recovery using parallel recovery processes:
ALTER DATABASE RECOVER TABLESPACE ts1 PARALLEL;
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 (in a Real Application Clusters 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' );
The following statement makes the temp
tablespace the default temporary tablespace of the database. This statement either establishes a default temporary tablespace if none was specified at create time, or replaces an existing default temporary tablespace with temp
:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
The following statement disables thread 5 in a Real Application Clusters environment:
ALTER DATABASE stocks DISABLE THREAD 5;
The following statement enables thread 5 in a Real Application Clusters environment, 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 'disk2:db1.dat'
based on the file 'disk1: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 statements change the database character set and national character set to the UTF8 character set:
ALTER DATABASE db1 CHARACTER SET UTF8; ALTER DATABASE db1 NATIONAL CHARACTER SET UTF8;
The database name is optional, and the character set name is specified without quotation marks.
The following statement attempts to change the size of datafile 'disk1:db1.dat'
:
ALTER DATABASE DATAFILE 'disk1:db1.dat' RESIZE 10 M;
The following statement clears a log file:
ALTER DATABASE CLEAR LOGFILE 'disk3:log.dbf';
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 p.m. 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;
The following statement recovers the standby database in automated standby recovery mode:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
The following statement puts the database in automated standby recovery mode. The managed 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, recovery continues indefinitely or until manually terminated.
The following statement terminates the managed recovery operation:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL IMMEDIATE;
The managed 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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|