Transporting Tablespaces Between Databases: A Procedure and Example

The following steps summarize the process of transporting a tablespace. Details for each step are provided in the subsequent example.
1. For cross-platform transport, check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view.
Ignore this step if you are transporting your tablespace set to the same platform.
2. Pick a self-contained set of tablespaces.
3. Generate a transportable tablespace set.
A transportable tablespace set (or transportable set) consists of datafiles for the set of tablespaces being transported and an export file containing structural information (metadata) for the set of tablespaces. You use Data Pump or EXP to perform the export.

 Note:

If any of the tablespaces contain XMLTypes, you must use EXP.
If you are transporting the tablespace set to a platform with different endianness from the source platform, you must convert the tablespace set to the endianness of the target platform. You can perform a source-side conversion at this step in the procedure, or you can perform a target-side conversion as part of step 4.

Note:

This method of generating a transportable tablespace requires that you temporarily make the tablespace read-only. If this is undesirable, you can use the alternate method known as transportable tablespace from backup.
4. Transport the tablespace set.
Copy the datafiles and the export file to a place that is accessible to the target database.
If you have transported the tablespace set to a platform with different endianness from the source platform, and you have not performed a source-side conversion to the endianness of the target platform, you should perform a target-side conversion now.
5. Import the tablespace set.
Invoke the Data Pump utility or IMP to import the metadata for the set of tablespaces into the target database.
Note:
If any of the tablespaces contain XMLTypes, you must use IMP.
Example
The steps for transporting a tablespace are illustrated more fully in the example that follows, where it is assumed the following datafiles and tablespaces exist:

Step 1: Determine if Platforms are Supported and Endianness

This step is only necessary if you are transporting the tablespace set to a platform different from the source platform.
If you are transporting the tablespace set to a platform different from the source
platform, then determine if cross-platform tablespace transport is supported
for both the source and target platforms, and determine the endianness of each
platform. If both platforms have the same endianness, no conversion is necessary. Otherwise you must do a conversion of the tablespace set either at
the source or target database.
If you are transporting sales_1 and sales_2 to a different platform, you can execute the following query on each platform. If the query returns a row, the platform supports cross-platform tablespace transport.

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT

FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

The following is the query result from the source platform:

PLATFORM_NAME ENDIAN_FORMAT
-----------------------------------------------------
Solaris[tm] OE (32-bit) Big

The following is the result from the target platform:

PLATFORM_NAME ENDIAN_FORMAT

------------------------------------------------------
Microsoft Windows NT Little

You can see that the endian formats are different and thus a conversion is necessary for transporting the tablespace set.

 Step 2: Pick a Self-Contained Set of Tablespaces

There may be logical or physical dependencies between objects in the transportable set and those outside of the set. You can only transport a
set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. Some examples of self contained tablespace violations are:

• An index inside the set of tablespaces is for a table outside of the set of tablespaces.

Note:

It is not a violation if a corresponding index for a table is outside of the set of tablespaces.

• A partitioned table is partially contained in the set of tablespaces.

The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.

• A referential integrity constraint points to a table across a set boundary.

When transporting a set of tablespaces, you can choose to include referential integrity constraints. However, doing so can affect whether or not a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers.

• A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.

• An XML DB schema (*.xsd) that was registered by user A imports a global schema that was registered by user B, and the following is true: the default tablespace for user A is tablespace A, the default tablespace for user B is tablespace B, and only tablespace A is included in the set of tablespaces.

To determine whether a set of tablespaces is self-contained, you can invoke the
TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.

 When you invoke the DBMS_TTS package, you specify the list of tablespaces in the transportable set to be checked for self containment. You can optionally
specify if constraints must be included. For strict or full containment, you must additionally set the TTS_FULL_CHECK parameter to TRUE.

 The strict or full containment check is for cases that require capturing not only
references going outside the transportable set, but also those coming into the
set. Tablespace Point-in-Time Recovery (TSPITR) is one such case where
dependent objects must be fully contained or fully outside the transportable
set.

 For example, it is a violation to perform TSPITR on a tablespace containing a table t but not its index i because the index and data will be inconsistent after the transport. A full containment check ensures that there are no dependencies
going outside or coming into the transportable set.

 Note:

The default for transportable tablespaces is to check for self containment rather than full containment.

The following statement can be used to determine whether tablespaces sales_1 and sales_2 are self-contained, with referential integrity constraints taken into   consideration (indicated by TRUE).

 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);

After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty. The following example illustrates a case where there are two violations: a foreign key constraint, dept_fk, across the tablespace set boundary, and a partitioned table, jim.sales, that is partially contained in the tablespace set.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
--------------------------------------------------------------------------
Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table JIM.DEPT in tablespace OTHER Partitioned table JIM.SALES is
partially contained in the transportable set

These violations must be resolved before sales_1 and sales_2 are transportable. As noted in the next step, one choice for bypassing the integrity constraint
violation is to not export the integrity constraints.

Step 3: Generate a Transportable Tablespace Set
Any privileged user can perform this step. However, you must have been assigned the EXP_FULL_DATABASE role to perform a transportable tablespace export operation.
Note:
This method of generating a transportable tablespace requires that you temporarily make the tablespace read-only. If this is undesirable, you can use the alternate method known as transportable tablespace from backup.
After ensuring you have a self-contained set of tablespaces that you want to
transport, generate a transportable tablespace set by performing the following
actions:
1. Make all tablespaces in the set you are copying read-only.
2. SQL> ALTER TABLESPACE sales_1 READ ONLY;
3.  
4. Tablespace altered.
5.  
6. SQL> ALTER TABLESPACE sales_2 READ ONLY;
7.
8. Tablespace altered.
9.
10.  Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.

Note:

If any of the tablespaces have XMLTypes, you must use EXP instead of Data Pump. Ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).

SQL> HOST $ EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir  

TRANSPORT_TABLESPACES = sales_1,sales_2
You must always specify TRANSPORT_TABLESPACES, which determines the mode of the export operation. In this example:
o  The DUMPFILE parameter specifies the name of the structural information export file to be created, expdat.dmp.
o The DIRECTORY parameter specifies the default directory object that points to the operating system or Automatic Storage Management location of the dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to PUBLIC.
o Triggers and indexes are included in the export operation by default.

If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter, as shown in the following example:

EXPDP system/password
DUMPFILE=expdat.dmp DIRECTORY = dpump_dir

TRANSPORT_TABLESPACES=sales_1,sales_2
TRANSPORT_FULL_CHECK=Y

 In this example, the Data Pump export utility verifies that there are no dependencies between the objects inside the transportable set and objects outside the transportable set. If the tablespace set being transported is not self-contained, then the export fails and indicates that the transportable set is not self-contained. You must then return to Step 1 to resolve all violations.

Notes:

The Data Pump utility is used to export only data dictionary structural information (metadata) for the tablespaces. No actual data is unloaded, so this operation goes relatively quickly even for large tablespace sets.
11. When finished, exit back to SQL*Plus:
12. $ EXIT
If sales_1 and sales_2 are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set, then convert the datafiles composing the sales_1 and sales_2 tablespaces:
4. From SQL*Plus, return to the host system:
5. SQL> HOST
6.
7. The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:
8. $ RMAN TARGET /
9.
10. Recovery Manager: Release 10.1.0.0.0
11.
12. Copyright (c) 1995, 2003, Oracle Corporation. All rights reserved.
13.
14. connected to target database: salesdb (DBID=3295731590)
15.
16.  Convert the datafiles into a temporary location on the source platform. In this example, assume that the temporary location, directory /temp, has already been created. The converted datafiles are assigned names by the system.
17. RMAN> CONVERT TABLESPACE sales_1,sales_2
18. 2> TO PLATFORM 'Microsoft Windows NT'
19. 3> FORMAT '/temp/%U';
20.
21. Starting backup at 08-APR-03
22. using target database control file instead of recovery catalog
23. allocated channel: ORA_DISK_1
24. channel ORA_DISK_1: sid=11 devtype=DISK
25. channel ORA_DISK_1: starting datafile conversion
26. input datafile fno=00005 name=/u01/oracle/oradata/salesdb/sales_101.dbf
27. converted datafile=/temp/data_D-10_I-3295731590_TS-ADMIN_TBS_FNO-5_05ek24v5
28. channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
29. channel ORA_DISK_1: starting datafile conversion
30. input datafile fno=00004 name=/u01/oracle/oradata/salesdb/sales_101.dbf
31. converted datafile=/temp/data_D-10_I-3295731590_TS-EXAMPLE_FNO-4_06ek24vl
32. channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
33. Finished backup at 08-APR-03
34.  Exit Recovery Manager:
35. RMAN> exit
36. Recovery Manager complete.

Step 4: Transport the Tablespace Set

Transport both the datafiles and the export file of the tablespaces to a place that is accessible to the target database.

If both the source and destination are files systems, you can use:

• Any facility for copying flat files (for example, an operating system copy utility or ftp)
• The DBMS_FILE_TRANSFER package
• RMAN
• Any facility for publishing on CDs

If either the source or destination is an Automatic Storage Management (ASM) disk group, you can use:

• ftp to or from the /sys/asm virtual folder in the XML DB repository
• The DBMS_FILE_TRANSFER package
• RMAN

Caution:

Exercise caution when using the UNIX dd utility to copy raw-device files between databases. The dd utility can be used to copy an entire source raw-device file, or it can be invoked with options that instruct it to copy only a specific range of blocks from the source raw-device file.

It is difficult to ascertain actual datafile size for a raw-device file because of hidden control information that is stored as part of the datafile. Thus, it is
advisable when using the dd utility to specify copying the entire source
raw-device file contents.

 If you are transporting the tablespace set to a platform with endianness that is different from the source platform, and you have not yet converted the tablespace set, you must do so now. This example assumes that you have completed the following steps before the transport:

1. Set the source tablespaces to be transported to be read-only.
2. Use the export utility to create an export file (in our example, expdat.dmp).
Datafiles that are to be converted on the target platform can be moved to a temporary location on the target platform. However, all datafiles, whether already converted or not, must be moved to a designated location on the target
database.
Now use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs, as shown in this hypothetical example:
RMAN> CONVERT DATAFILE
2> '/hq/finance/work/tru/tbs_31.f',
3>'/hq/finance/work/tru/tbs_32.f', 4> '/hq/finance/work/tru/tbs_41.f'
5> TO PLATFORM="Solaris[tm] OE (32-bit)"
6> FROM PLATFORM="HP TRu64 UNIX"
7> DB_FILE_NAME_CONVERT=
8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
9> PARALLELISM=5;

You identify the datafiles by filename, not by tablespace name. Until the
tablespace metadata is imported, the local instance has no way of knowing the
desired tablespace names. The source and destination platforms are optional.
RMAN determines the source platform by examining the datafile, and the target
platform defaults to the platform of the host running the conversion.

 Step 5: Import the Tablespace Set Note:

If you are transporting a tablespace of a different block size than the standard block size of the database receiving the tablespace set, then you must first have a DB_nK_CACHE_SIZE initialization parameter entry in the receiving database parameter file.
For example, if you are transporting a tablespace with an 8K block size into a
database with a 4K standard block size, then you must include a  DB_8K_CACHE_SIZE initialization parameter entry in the parameter file. If it is not already included in the parameter file, this parameter can be set using the ALTER SYSTEM SET statement.
Any privileged user can perform this step. To import a tablespace set, perform the following tasks:
1. Import the tablespace metadata using the Data Pump Import utility, impdp:
Note:
If any of the tablespaces contain XMLTypes, you must use IMP instead of Data Pump.
IMPDP system/password
DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_DATAFILES=/salesdb/sales_101.dbf,/salesdb/sales_201.dbf
REMAP_SCHEMA=(dcranney:smith)
REMAP_SCHEMA=(jfee:williams)
In this example we specify the following:
O    The DUMPFILE parameter specifies the exported file containing the metadata for the tablespaces to be imported.
O    The DIRECTORY parameter specifies the directory object that identifies the location of the dump file.
O    The TRANSPORT_DATAFILES parameter identifies all of the datafiles containing the tablespaces to be imported.
O     The REMAP_SCHEMA parameter changes the ownership of database objects. If you do not specify REMAP_SCHEMA, all database objects (such as tables and indexes) are created in the same user schema as in the source database, and those users must already exist in the target database. If they do not exist, then the import utility returns an error. In this example, objects in the tablespace set owned by dcranney in the source database will be owned by smith in the target database after the tablespace set is imported. Similarly, objects owned by jfee in the source database will be owned by Williams in the target database. In this case, the target database is not required to have users dcranney and jfee, but must have users smith and williams.
After this statement executes successfully, all tablespaces in the set being copied remain in read-only mode. Check the import logs to ensure that no error has occurred.
When dealing with a large number of datafiles, specifying the list of datafile names in the statement line can be a laborious process. It can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:

 IMPDP system/password PARFILE='par.f'

where the parameter file, par.f contains the following:

DIRECTORY=dpump_dir
DUMPFILE=expdat.dmp
TRANSPORT_DATAFILES="'/db/sales_jan','/db/sales_feb'"
REMAP_SCHEMA=dcranney:smith
REMAP_SCHEMA=jfee:williams

2. If required, put the tablespaces into read/write mode as follows:
3. ALTER TABLESPACE sales_1 READ WRITE;
4. ALTER TABLESPACE sales_2 READ WRITE;

Using Transportable Tablespaces: Scenarios

The following sections describe some uses for transportable tablespaces:

Transporting and Attaching Partitions for Data Warehousing

Typical enterprise data warehouses contain one or more large fact tables. These fact tables can be partitioned by date, making the enterprise data warehouse a
historical database. You can build indexes to speed up star queries. Oracle
recommends that you build local indexes for such historically partitioned
tables to avoid rebuilding global indexes every time you drop the oldest partition from the historical database.
Suppose every month you would like to load one month of data into the data warehouse. There is a large fact table in the data warehouse called sales, which has the following columns:

 CREATE TABLE sales (invoice_no NUMBER, sale_year INT NOT NULL,

sale_month INT NOT NULL, sale_day INT NOT NULL)
PARTITION BY RANGE (sale_year, sale_month,sale_day)
(partition jan98 VALUES LESS THAN (1998, 2, 1),
partition feb98 VALUES LESS THAN (1998, 3, 1),
partition mar98 VALUES LESS THAN (1998, 4, 1),
partition apr98 VALUES LESS THAN (1998, 5, 1),
partition may98 VALUES LESS THAN (1998, 6, 1),
partition jun98 VALUES LESS THAN (1998, 7, 1));

You create a local non-prefixed index:

CREATE INDEX sales_index ON sales(invoice_no) LOCAL;

Initially, all partitions are empty, and are in the same default tablespace. Each month, you want to create one partition and attach it to the partitioned sales table.

Suppose it is July 1998, and you would like to load the July sales data into the
partitioned table. In a staging database, you create a new tablespace, ts_jul.
You also create a table, jul_sales, in that tablespace with exactly the same
column types as the sales table. You can create the table jul_sales using the
CREATE TABLE ... AS SELECT statement. After creating and populating jul_sales, you can also create an index, jul_sale_index, for the table, indexing the same column as the local index in the sales table. After building the index, transport the tablespace ts_jul to the data warehouse.

In the data warehouse, add a partition to the sales table for the July sales data. This also creates another partition for the local non-prefixed index:

 ALTER TABLE sales ADD PARTITION jul98 VALUES LESS THAN (1998, 8, 1);

Attach the transported table jul_sales to the table sales by exchanging it with the new partition:
ALTER TABLE sales EXCHANGE PARTITION jul98 WITH TABLE jul_sales
INCLUDING INDEXES
WITHOUT VALIDATION;
This statement places the July sales data into the new partition jul98, attaching
the new data to the partitioned table. This statement also converts the index
jul_sale_index into a partition of the local index for the sales table. This statement should return immediately, because it only operates on the structural information and it simply switches database pointers. If you know that the data in the new partition does not overlap with data in previous partitions, you are advised to specify the WITHOUT VALIDATION clause. Otherwise, the statement goes through all the new data in the new partition in an attempt to validate the range of that partition.

If all partitions of the sales table came from the same staging database (the staging database is never destroyed), the exchange statement always succeeds. In general, however, if data in a partitioned table comes from different databases, it is possible that the exchange operation may fail. For example, if
the jan98 partition of sales did not come from the same staging database, the
preceding exchange operation can fail, returning the following error:

ORA-19728: data object number conflict between table JUL_SALES and partition JAN98 in table SALES

To resolve this conflict, move the offending partition by issuing the following statement:

ALTER TABLE sales MOVE PARTITION jan98;

Then retry the exchange operation.

After the exchange succeeds, you can safely drop jul_sales and jul_sale_index (both are now empty). Thus you have successfully loaded the July sales data into your data warehouse.

 Publishing Structured Data on CDs

Transportable tablespaces provide a way to publish structured data on CDs. A data provider can load a tablespace with data to be published, generate the transportable set, and copy the transportable set to a CD. This CD can then be distributed.
When customers receive this CD, they can add the CD contents to an existing database without having to copy the datafiles from the CD to disk storage. For example, suppose on a Windows NT machine D: drive is the CD drive. You can import a transportable set with datafile catalog.f and export file expdat.dmp as
follows:

IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir

TRANSPORT_DATAFILES='D:\catalog.f'

You can remove the CD while the database is still up. Subsequent queries to the
tablespace return an error indicating that the database cannot open the datafiles on the CD. However, operations to other parts of the database are not affected. Placing the CD back into the drive makes the tablespace readable again.

Removing the CD is the same as removing the datafiles of a read-only tablespace. If you shut down and restart the database, the database indicates that it cannot find the removed datafile and does not open the database (unless you set the initialization parameter READ_ONLY_OPEN_DELAYED to TRUE). When READ_ONLY_OPEN_DELAYED is set to TRUE, the database reads the file only when someone queries the transported tablespace. Thus, when transporting a tablespace from a CD, you should always set the READ_ONLY_OPEN_DELAYED initialization parameter to TRUE,
unless the CD is permanently attached to the database.

 Mounting the Same Tablespace Read-Only on Multiple Databases

You can use transportable tablespaces to mount a tablespace read-only on multiple databases. In this way, separate databases can share the same data on disk instead of duplicating data on separate disks. The tablespace datafiles must be accessible by all databases. To avoid database corruption, the tablespace must remain read-only in all the databases mounting the tablespace.

The following are two scenarios for mounting the same tablespace read-only on
multiple databases:

• The tablespace originates in a database that is separate from the databases that will share the tablespace.

You generate a transportable set in the source database, put the transportable set onto a disk that is accessible to all databases, and then import the metadata into each database on which you want to mount the tablespace.

• The tablespace already belongs to one of the databases that will share the tablespace.

It is assumed that the datafiles are already on a shared disk. In the database where the tablespace already exists, you make the tablespace read-only, generate the transportable set, and then import the tablespace into the other databases, leaving the datafiles in the same location on the shared disk.

You can make a disk accessible by multiple computers in several ways. You can use either a cluster file system or raw disk. You can also use network file system (NFS), but be aware that if a user queries the shared tablespace while NFS is down, the database will hang until the NFS operation times out.

Later, you can drop the read-only tablespace in some of the databases. Doing so does not modify the datafiles for the tablespace. Thus, the drop operation does not corrupt the tablespace. Do not make the tablespace read/write unless only one database is mounting the tablespace.

Archiving Historical Data Using Transportable Tablespaces

Since a transportable tablespace set is a self-contained set of files that can be
imported into any Oracle Database, you can archive old/historical data in an
enterprise data warehouse using the transportable tablespace procedures
described in this chapter.

 Moving Databases Across Platforms Using Transportable Tablespaces

You can use the transportable tablespace feature to migrate a database to a different platform by creating a new database on the destination platform and performing a transport of all the user tablespaces.

You cannot transport the SYSTEM tablespace. Therefore, objects such as sequences, PL/SQL packages, and other objects that depend on the SYSTEM tablespace are not transported. You must either create these objects manually on the destination database, or use Data Pump to transport the objects that are not moved by transportable tablespace.

 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

PIECES shows the number of free space extents in the tablespace file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each tablespace file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing tablespace.