SQL>ALTER TABLESPACE lmtemp SHRINK SPACE KEEP 20M;
Which requirement must be fulfilled for this command to succeed?
A. The tablespace must be locally managed.
B. The tablespace must have only one temp file.
C. The tablespace must be made nondefault and offline.
D. The tablespace can remain as the default but must have no active sort operations.
Answer: A
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN12353
Shrinking a Locally Managed Temporary Tablespace
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 temp file of a temporary tablespace. Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or temp file. The optional KEEP
clause defines a minimum size for the tablespace or temp file.
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
while ensuring a minimum size of 20M.
ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;
The following example shrinks the temp file lmtemp02.dbf
of the locally managed temporary tablespace lmtmp2
. Because the KEEP
clause is omitted, the database attempts to shrink the temp file to the minimum possible size.
ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';