Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 3 of 11
filespec: See "filespec".
maxsize_clause::=
storage_clause: See "storage_clause".
To alter an existing tablespace or one or more of its datafiles or tempfiles.
For information on creating a tablespace, see "CREATE TABLESPACE".
If you have ALTER
TABLESPACE
system privilege, you can perform any of this statement's operations. If you have MANAGE
TABLESPACE
system privilege, you can only perform the following operations:
Before you can make a tablespace read-only, the following conditions must be met:
SYSTEM
tablespace can never be made read-only, because it contains the SYSTEM
rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, Oracle recommends that you drop the rollback segments before you make a tablespace read-only.
Performing this function in restricted mode may help you meet these restrictions, because only users with RESTRICTED
SESSION
system privilege can be logged on.
tablespace |
is the name of the tablespace to be altered. |
|
|
Note: For locally managed temporary tablespaces, the only clause you can specify in this statement in the ADD clause. |
|
|
specifies the default logging attribute of all tables, indexes, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels. |
|
|
When an existing tablespace logging attribute is changed by an |
|
|
Only the following operations support |
|
|
||
|
In |
|
datafile/tempfile_clauses |
adds or modifies a datafile or tempfile. |
|
|
|
Adds to the tablespace a datafile or tempfile specified by filespec (see "filespec"). You can add a datafile or tempfile to a locally managed tablespace that is online or to a dictionary managed tablespace that is online or offline. Be sure the file is not in use by another database. |
|
Note: For locally managed temporary tablespaces, this is the only clause you can specify at any time. |
|
|
|
renames one or more of the tablespace's datafiles. Take the tablespace offline before renaming the datafile. Each 'filename' must fully specify a datafile using the conventions for filenames on your operating system. |
|
|
This clause merely associates the tablespace with the new file rather than the old one. This clause does not actually change the name of the operating system file. You must change the name of the file through your operating system. |
autoextend_clause |
enables or disables the autoextending of the size of the datafile in the tablespace. |
|
|
|
disables autoextend if it is turned on. |
|
|
enables autoextend. |
|
|
specifies the size in bytes of the next increment of disk space to be allocated automatically 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_clause |
specifies maximum disk space allowed for automatic extension of the datafile. |
|
|
|
|
specifies the new default storage parameters for objects subsequently created in the tablespace. For a dictionary-managed temporary table, Oracle considers only the Restriction: You cannot specify this clause for a locally managed tablespace. |
|
|
controls free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, integer. This clause is not relevant for a dictionary-managed temporary tablespace. Restriction: You cannot specify this clause for a locally managed tablespace.
See Also: Oracle8i Administrator's Guide for more information about using |
|
|
brings the tablespace online. |
|
|
takes the tablespace offline and prevents further access to its segments. |
|
|
|
flushes all blocks in all datafiles in the tablespace out of the SGA. You need not perform media recovery on this tablespace before bringing it back online. This is the default. |
|
|
performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Any offline files may require media recovery before you bring the tablespace back online. |
|
|
does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online. |
|
|
takes the production database tablespaces in the recovery set offline for tablespace point-in-time recovery. For additional information see Oracle8i Backup and Recovery Guide. |
Suggestion: Before taking a tablespace offline for a long time, you may want to alter the tablespace allocation of any users who have been assigned the tablespace as either a default or temporary tablespace. When the tablespace is offline, these users cannot allocate space for objects or sort areas in the tablespace. See Also: "ALTER USER". |
||
|
signifies that an open backup is to be performed on the datafiles that make up this tablespace. This clause does not prevent users from accessing the tablespace. You must use this clause before beginning an open backup. You cannot use this clause on a read-only tablespace. |
|
|
Note: While the backup is in progress, you cannot take the tablespace offline normally, shut down the instance, or begin another backup of the tablespace. |
|
|
signifies that an open backup of the tablespace is complete. Use this clause as soon as possible after completing an open backup. You cannot use this clause on a read-only tablespace. |
|
|
If you forget to indicate the end of an online tablespace backup, and an instance failure or |
|
|
places the tablespace in transition read-only mode. In this state, existing transactions can complete (commit or roll back), but no further write operations (DML) are allowed to the tablespace except for rollback of existing transactions that previously modified blocks in the tablespace.
Once a tablespace is read only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL statement See Also: Oracle8i Concepts for more information on read-only tablespaces. |
|
|
signifies that write operations are allowed on a previously read-only tablespace. |
|
|
specifies that the tablespace is to be converted from a temporary to a permanent one. A permanent tablespace is one in which permanent database objects can be stored. This is the default when a tablespace is created. |
|
|
specifies that the tablespace is to be converted from a permanent to a temporary one. A temporary tablespace is one in which no permanent database objects can be stored. Objects in a temporary tablespace persist only for the duration of the session. |
|
|
for each datafile in the tablespace, coalesces all contiguous free extents into larger contiguous extents. |
The following statement signals to the database that a backup is about to begin:
ALTER TABLESPACE accounting BEGIN BACKUP;
The following statement signals to the database that the backup is finished:
ALTER TABLESPACE accounting END BACKUP;
This example moves and renames a datafile associated with the ACCOUNTING
tablespace from 'DISKA:PAY1.DAT
' to 'DISKB:RECEIVE1.DAT'
:
ALTER TABLESPACE
statement with the OFFLINE
clause:
ALTER TABLESPACE accounting OFFLINE NORMAL;
DISKA:PAY1.DAT
' to 'DISKB:RECEIVE1.DAT
' using your operating system's commands.
ALTER TABLESPACE
statement with the RENAME DATAFILE
clause:
ALTER TABLESPACE accounting RENAME DATAFILE 'diska:pay1.dbf' TO 'diskb:receive1.dbf';
ALTER TABLESPACE
statement with the ONLINE
clause:
ALTER TABLESPACE accounting ONLINE;
The following statement adds a datafile to the tablespace and changes the default logging attribute to NOLOGGING
. When more space is needed, new extents of size 10 kilobytes will be added up to a maximum of 100 kilobytes:
ALTER TABLESPACE accounting NOLOGGING ADD DATAFILE 'disk3:pay3.dbf' SIZE 50K AUTOEXTEND ON NEXT 10K MAXSIZE 100K;
Altering a tablespace logging attribute has no affect on the logging attributes of the existing schema objects within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.
The following statement changes the allocation of every extent of TABSPACE_ST
to a multiple of 128K:
ALTER TABLESPACE tabspace_st MINIMUM EXTENT 128K;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|