Create and manage temporary, permanent, and undo tablespaces

Using Multiple Tablespaces
Using multiple tablespaces allows you more flexibility in performing database operations.Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently.

Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you must increase the size of a tablespace, then add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles. Review your data in light of these factors and decide how many tablespaces you need for your database design.

Locally Managed Tablespaces
Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:
■ Fast, concurrent space operations. Space allocations and deallocations modify
locally managed resources (bitmaps stored in header files).
■ Enhanced performance
■ Readable standby databases are allowed, because locally managed temporary tablespaces do not generate any undo or redo.
■ Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size.
■ User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
■ Coalescing free extents is unnecessary for locally managed tablespaces.
 Creating a Locally Managed Tablespace

SQL> CREATE TABLESPACE lmtbsb DATAFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\lmtbsb01.dbf' SIZE 50M
  2  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Tablespace created.

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.
The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.
SQL> CREATE TABLESPACE lmtbsb1 DATAFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\lmtbsb101.dbf' SIZE 50M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Tablespace created.

You cannot specify the DEFAULT storage clause, MINIMUM EXTENT, or TEMPORARY when you explicitly specify EXTENT MANAGEMENT LOCAL.
Segment Space Management
Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.It is also self-tuning, in that it scales with increasing number of users or instances.

Temporary Tablespaces
A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.
Space Allocation in a Temporary Tablespace
V$SORT_SEGMENT
V$TEMPSEG_USAGE
DBA_TEMP_FREE_SPACE (NA in 10g)

SQL> CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\lmtemp01.dbf'
  2  SIZE 20M REUSE
  3  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
Tablespace created.

V$TEMPFILE
DBA_TEMP_FILES
V$DATAFILE
DBA_DATA_FILES

Tablespace Group
A tablespace group enables a user to consume temporary space from multiple tablespaces.

SQL> CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\lmtemp201.dbf'
  2  SIZE 50M
  3  TABLESPACE GROUP group1;
Tablespace created.
SQL> ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
Tablespace altered.
SQL>
SQL> CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\lmtemp301.dbf'
  2  SIZE 25M
  3  TABLESPACE GROUP group1;
Tablespace created.
SQL>

SQL> ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;
Tablespace altered.

SQL> ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';  -- remove from group
Tablespace altered.
SQL> ALTER DATABASE OCM1 DEFAULT TEMPORARY TABLESPACE group2;
Database altered.

Specifying Nonstandard Block Sizes for Tablespaces
You can create tablespaces with block sizes different from the standard database block size, which is specified by the DB_BLOCK_SIZE initialization parameter. This feature lets you transport tablespaces with unlike block sizes between databases.
The following statement creates tablespace lmtbsb, but specifies a block size that differs from the standard database block size (as specified by the DB_BLOCK_SIZE initialization parameter):

SQL> CREATE TABLESPACE lmtbsb9 DATAFILE 'C:\oracle\PROD\OCM\oradata\OCM1\DATAFILE\lmtbsb901.dbf' SIZE 50M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
  3  BLOCKSIZE 16K;
Tablespace created.

Creating a Read-Only Tablespace on a WORM Device
Follow these steps to create a read-only tablespace on a CD-ROM or WORM (Write
Once-Read Many) device.
1. Create a writable tablespace on another device. Create the objects that belong in the tablespace and insert your data.
2. Alter the tablespace to make it read-only.
3. Copy the datafiles of the tablespace onto the WORM device. Use operating system commands to copy the files.
4. Take the tablespace offline.
5. Rename the datafiles to coincide with the names of the datafiles you copied onto your WORM device. Use ALTER TABLESPACE with the RENAME DATAFILE clause. Renaming the datafiles changes their names in the control file.
6. Bring the tablespace back online.
Altering Tablespace

ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;
ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

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.
The following example shrinks the locally managed temporary tablespace lmtmp1 to a size of 20M.

ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;

Because the KEEP clause is omitted in below example, the database attempts to shrink the tempfile to the minimum possible size.

ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

Diagnosing and Repairing Locally Managed Tablespace Problems
Oracle Database includes the DBMS_SPACE_ADMIN package, which is a collection of aids for diagnosing and repairing problems in locally managed tablespaces.

Transportable Tablespaces
You can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another. Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, and you use Data Pump to transfer only the metadata of the tablespace objects to the new database.