In this Document

Applies to:

Purpose

This Notes describes the various aspects of tablespace management and is to instruct engineers and customers how to better diagnose Tablespace related issues / questions.

Details

Overview of Tablespaces

A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

Before working with tablespaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections:

Using Multiple Tablespaces

Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:

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 need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.

Assigning Tablespace Quotas to Users

Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.

Creating Tablespaces

Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM tablespace or take it offline.
The SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM tablespace, management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it. The management of the SYSAUX tablespace is discussed separately in "Managing the SYSAUX Tablespace".
The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your datafiles will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace by adding datafiles. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the datafiles as specified.
To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly.
You can also use the CREATE UNDO TABLESPACE statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement.

CREATE TABLESPACE  ;

Tablespace types

Different tablespace types can be created for different purposes:

Permanent tablespaces

Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views. Sample create statements:

Temp tablespaces

Temp or temporary tablespaces are used to store data with short lifespan (transient data), for example: global temporary tables or sort results.

With a single temp tablespace, the database will only write to one temp file at a time. However, temporary tablespace groups, an Oracle 10g feature, can be created to allow Oracle to write to multiple temp files simultaneously.

Undo tablespaces

Undo tablespaces are used to store "before image" data that can be used to undo transactions. See ROLLBACK.

Locally Managed Tablespaces

The following statement creates a locally managed tablespace named lmtbsb and specifies AUTOALLOCATE:

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL 
    SEGMENT SPACE MANAGEMENT AUTO;

The SEGMENT SPACE MANAGEMENT MANUAL clause disables automatic segment space management.

For more information

Note 735978.1 - How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace?
Note 93771.1 - Introduction to Locally-Managed Tablespaces
Note 105120.1 - Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

Bigfile Tablespaces

Oracle lets you create bigfile tablespaces. This allows Oracle Database to contain tablespaces made up of single large files rather than numerous smaller ones. This lets Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. The consequence of this is that Oracle Database can now scale up to 8 exabytes in size. A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large.

Benefits of Bigfile Tablespaces

Considerations with Bigfile Tablespaces

Creating a Bigfile Tablespace

Identifying a Bigfile Tablespace

The following views contain a BIGFILE column that identifies a tablespace as a bigfile tablespace:

    DBA_TABLESPACES
    USER_TABLESPACES
    V$TABLESPACE

You can also identify a bigfile tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on OS/390.

For more information

Note 1287314.1 - Unable To Edit Bigfile System Tablespace In DBConsole
Note 243245.1 - 10G New Storage Features and Enhancements
Note 262472.1 - 10g: BIGFILE Type Tablespaces Versus SMALLFILE Type

Compressed Tablespaces

You can specify that all tables created in a tablespace are compressed by default. You specify the type of table compression using the DEFAULT keyword, followed by one of the compression type clauses used when creating a table.

The following statement indicates that all tables created in the tablespace are to use OLTP compression, unless otherwise specified:

You can override the default tablespace compression specification when you create a table in that tablespace.

Encrypted Tablespaces

You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications, so no application modification is necessary. Encrypted tablespaces primarily protect your data from unauthorized access by means other than through the database. For example, when encrypted tablespaces are written to backup media for travel from one Oracle database to another or for travel to an off-site facility for storage, they remain encrypted. Also, encrypted tablespaces protect data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.

Tablespace encryption does not address all security issues. It does not, for example, provide access control from within the database. Any user who is granted privileges on objects stored in an encrypted tablespace can access those objects without providing any kind of additional password or key.
When you encrypt a tablespace, all tablespace blocks are encrypted. All segment types are supported for encryption, including tables, clusters, indexes, LOBs (BASICFILE and SECUREFILE), table and index partitions, and so on.

CREATE TABLESPACE securespace
DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

CREATE TABLESPACE securespace
DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

Restrictions

Querying Tablespace Encryption Information

The DBA_TABLESPACES and USER_TABLESPACES data dictionary views include a column named ENCRYPTED. This column contains YES for encrypted tablespaces.

The view V$ENCRYPTED_TABLESPACES lists all currently encrypted tablespaces. The following query displays the name and encryption algorithm of encrypted tablespaces:

For more Information

Note 1228046.1 - Master Note For Transparent Data Encryption ( TDE )
Note 432776.1 - 11g New Feature : Transparent Data Encryption at Tablespace Level
Note 1152787.1 - How To Change The Tablespace Encryption Key?

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.
Temporary tablespaces are used to store the following:

Within a temporary tablespace, all sort operations for a particular instance share a single sort segment, and sort segments exist for every instance that performs sort operations that require temporary space. A sort segment is created by the first statement after startup that uses the temporary tablespace for sorting, and is released only at shutdown.
By default, a single temporary tablespace named TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the CREATE TABLESPACE statement. You can assign a temporary tablespace to each database user with the CREATE USER or ALTER USER statement. A single temporary tablespace can be shared by multiple users.
You cannot explicitly create objects in a temporary tablespace.

Default Temporary Tablespace

Users who are not explicitly assigned a temporary tablespace use the database default temporary tablespace, which for new installations is TEMP. You can change the default temporary tablespace for the database with the following command:

To determine the current default temporary tablespace for the database, run the following query:

Creating a Locally Managed Temporary Tablespace

Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database.

You also use different views for viewing information about tempfiles than you would for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.
To create a locally managed temporary tablespace, you use the CREATE TEMPORARY TABLESPACE statement, which requires that you have the CREATE TABLESPACE system privilege.
The following statement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the standard block size is 2K) is represented by a bit in the bitmap for the file.

Viewing Space Usage for Temporary Tablespaces

The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.

Multiple Temporary Tablespaces: Using Tablespace Groups

A tablespace group enables a user to consume temporary space from multiple tablespaces. Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

A tablespace group has the following characteristics:

Creating a Tablespace Group

You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.

Assigning a Tablespace Group as the Default Temporary Tablespace

For more information

Note 1099324.1 - DB 11.1: Temporary Tablespaces [Video]
Note 160426.1 - TEMPORARY Tablespaces : Tempfiles or Datafiles ?
Note 245645.1 - 10g: Temporary Tablespaces Group
Note 167135.1 - How to Incorporate Locally Managed Temporary Tablespaces into the Backup Strategy
Note 188846.1 -  Optimizing performance with temporary tablespaces.

Note 1498442.1 - Master Note: Overview of Oracle Temporary Tablespaces

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 different block sizes between databases.
Use the BLOCKSIZE clause of the CREATE TABLESPACE statement to create a tablespace with a block size different from the database standard block size. In order for the BLOCKSIZE clause to succeed, you must have already set the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE initialization parameter. Furthermore, the integer you specify in the BLOCKSIZE clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting. Although redundant, specifying a BLOCKSIZE equal to the standard block size, as specified by the DB_BLOCK_SIZE initialization parameter, is allowed.

Controlling the Writing of Redo Records

For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, suppressing redo generation can improve performance, and may be appropriate for easily recoverable operations. An example of such an operation is a CREATE TABLE...AS SELECT statement, which can be repeated in case of database or instance failure.

Specify the NOLOGGING clause in the CREATE TABLESPACE statement to suppress redo when these operations are performed for objects within the tablespace. If you do not include this clause, or if you specify LOGGING instead, then the database generates redo when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute.

The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying LOGGING or NOLOGGING at the schema object level -- for example, in a CREATE TABLE statement.

Altering Tablespace Availability

You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.

To alter the availability of a tablespace, use the ALTER TABLESPACE statement. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

Taking Tablespaces Offline

You cannot take the following tablespaces offline:

    SYSTEM
    The undo tablespace
    Temporary tablespaces

Bringing Tablespaces Online

You can bring any tablespace in an Oracle Database online whenever the database is open. A tablespace is normally online so that the data contained within it is available to database users.
If a tablespace to be brought online was not taken offline "cleanly" (that is, using the NORMAL clause of the ALTER TABLESPACE OFFLINE statement), you must first perform media recovery on the tablespace before bringing it online. Otherwise, the database returns an error and the tablespace remains offline.

Using Read-Only Tablespaces

Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protect historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.
You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace. You can execute statements that update the file description in the data dictionary, such as ALTER TABLE...ADD or ALTER TABLE...MODIFY, but you will not be able to use the new description until the tablespace is made read/write.

Making a Tablespace Read-Only

All tablespaces are initially created as read/write. Use the READ ONLY clause in the ALTER TABLESPACE statement to change a tablespace to read-only. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. Before you can make a tablespace read-only, the following conditions must be met.

For better performance while accessing data in a read-only tablespace, you can issue a query that accesses all of the blocks of the tables in the tablespace just before making it read-only. A simple query, such as SELECT COUNT (*), executed against each table ensures that the data blocks in the tablespace can be subsequently accessed most efficiently. This eliminates the need for the database to check the status of the transactions that most recently modified the blocks.

Making a Tablespace Read/Write

Making a Read-Only Tablespace Writable
Use the READ WRITE keywords in the ALTER TABLESPACE statement to change a tablespace to allow write operations. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.
A prerequisite to making the tablespace read/write is that all of the datafiles in the tablespace, as well as the tablespace itself, must be online. Use the DATAFILE...ONLINE clause of the ALTER DATABASE statement to bring a datafile online. The V$DATAFILE view lists the current status of datafiles.

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.

Delaying the Opening of Datafiles in Read-Only Tablespaces 

When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage, you should consider setting the READ_ONLY_OPEN_DELAYED initialization parameter to TRUE. This speeds certain operations, primarily opening the database, by causing datafiles in read-only tablespaces to be accessed for the first time only when an attempt is made to read data stored within them.

Note:

• RECOVER DATABASE and ALTER DATABASE OPEN RESETLOGS continue to access all read-only datafiles regardless of the parameter value. To avoid accessing read-only files for these operations, take those files offline.
• If a backup control file is used, the read-only status of some files may be inaccurate. This can cause some of these operations to return unexpected results. Care should be taken in this situation.

Altering and Maintaining Tablespaces

Increasing the Size of a Tablespace

You can increase the size of a tablespace by either increasing the size of a datafile in the tablespace or adding one.

Additionally, you can enable automatic file extension (AUTOEXTEND) to datafiles and bigfile tablespaces.

For more information

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles.htm#ADMIN11424
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles.htm#ADMIN11422

Altering a Locally Managed Tablespace

You can use the ALTER TABLESPACE statement on locally managed tablespaces for some operations, including the following:

For more information

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11375
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11378
http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles.htm#ADMIN012

Altering a Bigfile Tablespace

Two clauses of the ALTER TABLESPACE statement support datafile transparency when you are using bigfile tablespaces:

Altering 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:

You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.

You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. The view V$TEMPFILE displays online status for a tempfile.

The 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 ALTER TABLESPACE statements in the previous example.

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;

To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the ALTER DATABASE RENAME FILE command to update the database controlfiles.

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 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 while ensuring/keeping a minimum size of 20M, but could result in more than 20M also.

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.

For more information

Note 452697.1 - How To Shrink A Temporary Tablespace in 11G ?
Note 268870.1 - How to Shrink the datafile of Undo Tablespace

Renaming Tablespaces

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users tablespace:

When you rename a tablespace the database updates all references to the tablespace name in the data dictionary, control file, and (online) datafile headers. The database does not change the tablespace ID so if this tablespace were, for example, the default tablespace for a user, then the renamed tablespace would show as the default tablespace for the user in the DBA_USERS view.

The following affect the operation of this statement:

For more information

Note 270061.1 - How To Rename a Tablespace and Avoid Certain Errors
Note 789023.1 - How to drop or rename _$deleted tablespaces
Note 243332.1 - 10G: Rename Tablespace Enhancement

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace.

When you drop a tablespace, the file pointers in the control file of the associated database are removed. You can optionally direct Oracle Database to delete the operating system files (datafiles) that constituted the dropped tablespace. If you do not direct the database to delete the datafiles at the same time that it deletes the tablespace, you must later use the appropriate commands of your operating system to delete them.

You cannot drop a tablespace that contains any active segments. For example, if a table in the tablespace is currently being used or the tablespace contains undo data needed to roll back uncommitted transactions, you cannot drop the tablespace. The tablespace can be online or offline, but it is best to take the tablespace offline before dropping it.

To drop a tablespace, use the DROP TABLESPACE statement.

A message is written to the alert log for each datafile that is deleted. If an operating system error prevents the deletion of a file, the DROP TABLESPACE statement still succeeds, but a message describing the error is written to the alert log.

For more information

Note 111316.1 - How to 'DROP' a Datafile from a Tablespace
Note 389467.1 - Drop Tablespace Including Contents And Datafiles The Datafiles Are Not Automatically Deleted

http://docs.oracle.com/cd/E11882_01/server.112/e25494/dfiles.htm#ADMIN11435
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011

Managing the SYSAUX Tablespace

The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.
If the SYSAUX tablespace becomes unavailable, core database functionality will remain operational. The database features that use the SYSAUX tablespace could fail, or function with limited capability.

For more information

http://docs.oracle.com/cd/E11882_01/server.112/e25494/create.htm#ADMIN00203

Monitoring Occupants of the SYSAUX Tablespace

Moving Occupants Out Of or Into the SYSAUX Tablespace

You will have an option at component install time to specify that you do not want the component to reside in SYSAUX. Also, if you later decide that the component should be relocated to a designated tablespace, you can use the move procedure for that component, as specified in the V$SYSAUX_OCCUPANTS view, to perform the move.
The move procedure also lets you move a component from another tablespace into the SYSAUX tablespace.

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.

For more information

Note 243246.1 - SYSAUX New Mandatory Tablespace in Oracle 10g and higher
Note 301186.1 - How to relocate the SYSAUX TABLESPACE
Note 1292724.1 - Suggestions if your SYSAUX Tablespace grows rapidly or too large

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011

Migrating from a Dictionary Managed to a Locally Managed Tablespace

Use the TABLESPACE_MIGRATE_TO_LOCAL procedure to migrate a dictionary-managed tablespace to a locally managed tablespace. This operation is done online, but space management operations are blocked until the migration has been completed. Therefore, you can read or modify data while the migration is in progress, but if you are loading a large amount of data that requires the allocation of additional extents, then the operation may be blocked.

The statement to convert tbs_1 to a locally managed tablespace is as follows:

Migrating the SYSTEM Tablespace to a Locally Managed Tablespace

Use the DBMS_SPACE_ADMIN package to migrate the SYSTEM tablespace from dictionary-managed to locally managed. The following statement performs the migration:

 Before performing the migration the following conditions must be met:

All of these conditions, except for the cold backup, are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure.

For more infor mation

Note 735978.1 - How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace?
Note 93771.1 - Introduction to Locally-Managed Tablespaces
Note 105120.1 - Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

Transporting Tablespaces Between Databases

You can use the Transportable Tablespaces feature to copy a set of tablespaces from one Oracle Database to another.
This method for transporting tablespaces requires that you place the tablespaces to be transported in read-only mode until you complete the transporting process.

For more information

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmttbsb.htm#BRADV05141
Note 1166564.1 - Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues
Note 733824.1 - How To Recreate a database using TTS (Transportable TableSpace)

Tablespace Data Dictionary Views

There are many data dictionary and dynamic performance views that provide useful information about the tablespaces of a database like in the examples below

Example 1: Listing Tablespaces and Default Storage Parameters 

To list the names and default storage parameters of all tablespaces in a database, use the following query on the DBA_TABLESPACES view:

SELECT TABLESPACE_NAME "TABLESPACE",
   INITIAL_EXTENT "INITIAL_EXT",
   NEXT_EXTENT "NEXT_EXT",
   MIN_EXTENTS "MIN_EXT",
   MAX_EXTENTS "MAX_EXT",
   PCT_INCREASE
   FROM DBA_TABLESPACES;

TABLESPACE  INITIAL_EXT  NEXT_EXT  MIN_EXT   MAX_EXT    PCT_INCREASE  
----------  -----------  --------  -------   -------    ------------ 
RBS             1048576   1048576        2        40               0
SYSTEM           106496    106496        1        99               1
TEMP             106496    106496        1        99               0
TESTTBS           57344     16384        2        10               1
USERS             57344     57344        1        99               1

Example 2: Listing the Datafiles and Associated Tablespaces of a Database

To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:

SELECT  FILE_NAME, BLOCKS, TABLESPACE_NAME
   FROM DBA_DATA_FILES;

FILE_NAME                                      BLOCKS  TABLESPACE_NAME
------------                               ----------  -------------------
/U02/ORACLE/IDDB3/DBF/RBS01.DBF                  1536  RBS
/U02/ORACLE/IDDB3/DBF/SYSTEM01.DBF               6586  SYSTEM
/U02/ORACLE/IDDB3/DBF/TEMP01.DBF                 6400  TEMP
/U02/ORACLE/IDDB3/DBF/TESTTBS01.DBF              6400  TESTTBS
/U02/ORACLE/IDDB3/DBF/USERS01.DBF                 384  USERS

Example 3: Displaying Statistics for Free Space (Extents) of Each Tablespace

To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

SELECT TABLESPACE_NAME "TABLESPACE", FILE_ID,
   COUNT(*)    "PIECES",
   MAX(blocks) "MAXIMUM",
   MIN(blocks) "MINIMUM",
   AVG(blocks) "AVERAGE",
   SUM(blocks) "TOTAL"
   FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE    FILE_ID  PIECES   MAXIMUM    MINIMUM  AVERAGE    TOTAL
----------    -------  ------   -------    -------  -------   ------
RBS                 2       1       955        955      955      955
SYSTEM              1       1       119        119      119      119
TEMP                4       1      6399       6399     6399     6399
TESTTBS             5       5      6364          3     1278     6390
USERS               3       1       363        363      363      363

For more information

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011

Troubleshooting

Note 1270839.1 - Master Note for Tablespace Issues
Note 1267351.1 - TROUBLESHOOTING GUIDE (TSG) : ORA-1652: unable to extend temp segment
Note 1025288.6 - TROUBLESHOOTING GUIDE (TSG) - UNABLE TO CREATE / EXTEND Errors

References

Knowledge base:

Note 1270839.1 - Master Note for Tablespace Issues
Note 1166564.1 - Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues
Note 733824.1 - How To Recreate a database using TTS (Transportable TableSpace)

Note 93771.1 - Introduction to Locally-Managed Tablespaces

Note 243245.1 - 10G New Storage Features and Enhancements

Documentation:

http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011

References