This section covers various subjects that relate to altering and maintaining tablespaces. Included are the following topics:
You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment space management. Coalescing free extents is unnecessary for locally managed tablespaces. However, you can use the
ALTER TABLESPACE statement on locally managed tablespaces for some operations, including the following:
Adding a datafile. For example:
ALTER TABLESPACE lmtbsb ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;
Altering tablespace availability (
OFFLINE). See "Altering Tablespace Availability".
Making a tablespace read-only or read/write. See "Using Read-Only Tablespaces".
Renaming a datafile, or enabling or disabling the autoextension of the size of a datafile in the tablespace. See Chapter 13, "Managing Datafiles and Tempfiles".
Two clauses of the
ALTER TABLESPACE statement support datafile transparency when you are using bigfile tablespaces:
ALTER TABLESPACE bigtbs RESIZE 80G;
With a bigfile tablespace, you can use the
AUTOEXTEND clause outside of the
DATAFILE clause. For example:
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
Note:You cannot use the
ALTER TABLESPACEstatement, with the
TEMPORARYkeyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the
CREATE TEMPORARY TABLESPACEstatement to create a locally managed temporary tablespace.
You can use
ALTER TABLESPACE to add a tempfile, take a tempfile offline, or bring a tempfile online, as illustrated in the following examples:
ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE; ALTER TABLESPACE lmtemp TEMPFILE OFFLINE; ALTER TABLESPACE lmtemp TEMPFILE ONLINE;
Note:You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. The view
V$TEMPFILEdisplays online status for a tempfile.
ALTER DATABASE statement can be used to alter tempfiles.
The following statements take offline and bring online tempfiles. They behave identically to the last two
TABLESPACE statements in the previous example.
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
The following statement resizes a tempfile:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;
The following statement drops a tempfile and deletes its operating system file:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
The tablespace to which this tempfile belonged remains. A message is written to the alert log for the tempfile that was deleted. If an operating system error prevents the deletion of the file, the statement still succeeds, but a message describing the error is written to the alert log.
It is also possible to use the
ALTER DATABASE statement to enable or disable the automatic extension of an existing tempfile, and to rename (
RENAME FILE) a tempfile. See Oracle Database SQL Language Reference for the required syntax.
Note:To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the
FILEcommand to update the database controlfiles.
Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space. After the sort operation completes, the extra space is not released; it is just marked as free and available for reuse. Therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. For this reason, the database enables you to shrink locally managed temporary tablespaces and release unused space.
You use the
SHRINK SPACE clause of the
ALTER TABLESPACE statement to shrink a temporary tablespace, or the
SHRINK TEMPFILE clause of the
ALTER TABLESPACE statement to shrink a specific tempfile of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or tempfile. The optional
KEEP clause defines a minimum size for the tablespace or tempfile.
Shrinking is an online operation, which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.
The following example shrinks the locally managed temporary tablespace
lmtmp1 to a size of 20M.
ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;
The following example shrinks the tempfile
lmtemp02.dbf of the locally managed temporary tablespace
lmtmp2. Because the
KEEP clause is omitted, the database attempts to shrink the tempfile to the minimum possible size.
ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';