8 Managing Tablespaces

Creating a Locally Managed Tablespace

You create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. This is the default for new permanent tablespaces, but you must specify if it you want to specify the management of the locally managed tablespace. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM).

If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice. AUTOALLOCATE is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having Oracle Database manage your space most likely outweighs this drawback.

If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This setting ensures that you will never have unusable space in your tablespace.

When you do not explicitly specify the type of extent management, Oracle Database determines extent management as follows:

• If the CREATE TABLESPACE statement omits the DEFAULT storage clause, then the database creates a locally managed autoallocated tablespace.

• If the CREATE TABLESPACE statement includes a DEFAULT storage clause, then the database considers the following:

  • If you specified the MINIMUM EXTENT clause, the database evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of PCTINCREASE is 0. If so, the database creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.

  • If you did not specify MINIMUM EXTENT clause, the database evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

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 AUTOALLOCATE;

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K. In contrast, dictionary-managed tablespaces have a minimum extent size of two database blocks. Therefore, in systems with block size smaller than 32K, autoallocated locally managed tablespace will be larger initially.

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.

The following example creates a tablespace with uniform 128K extents. (In a database with 2K blocks, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this file.

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

You cannot specify the DEFAULT storage clause, MINIMUM EXTENT, or TEMPORARY when you explicitly specify EXTENT MANAGEMENT LOCAL. If you want to create a temporary locally managed tablespace, use the CREATE TEMPORARY TABLESPACE statement.

Specifying Segment Space Management in Locally Managed Tablespaces

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed. You can choose either manual or automatic segment-space management.

• MANUAL: Manual segment-space management uses free lists to manage free space within segments. Free lists are lists of data blocks that have space available for inserting rows. With this form of segment-space management, you must specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for schema objects created in the tablespace. MANUAL is the default.

• AUTO: Automatic segment-space management uses bitmaps to manage the free space within segments. The bitmap describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. These bitmaps allow the database to manage free space automatically.

You can specify automatic segment-space management only for permanent, locally managed tablespaces. Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for schema objects created in the tablespace. If you specify these attributes, the database ignores them.

Automatic segment-space management delivers better space utilization than manual segment-space management. It is also self-tuning, in that it scales with increasing number of users or instances. In a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups. In addition, for many standard workloads, application performance with automatic segment-space management is better than the performance of a well-tuned application using manual segment-space management.

The following statement creates tablespace lmtbsb with automatic segment-space management:

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

The segment-space management you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot subsequently change the segment-space management mode of a tablespace.

Locally managed tablespaces using automatic segment-space management can be created as single-file, or bigfile, tablespaces, as described in "Bigfile Tablespaces".

Altering a Locally Managed Tablespace

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 (ONLINE/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 9, " Managing Datafiles and Tempfiles".

Creating a Bigfile Tablespace

To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...). Oracle Database automatically creates a locally managed tablespace with automatic segment-spec management. You can, but need not, specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO in this statement. However, the database returns an error if you specify EXTENT MANAGEMENT DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL. The remaining syntax of the statement is the same as for the CREATE TABLESPACE statement, but you can only specify one datafile. For example:

CREATE BIGFILE TABLESPACE bigtbs 
    DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
...

You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

If the default tablespace type was set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A bigfile tablespace is created by default.

If the default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.

Altering a Bigfile Tablespace

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

• RESIZE: The RESIZE clause lets you resize the single datafile in a bigfile tablespace to an absolute size, without referring to the datafile. For example:

  ALTER TABLESPACE bigtbs RESIZE 80G;
  
  

• AUTOEXTEND (used outside of the ADD DATAFILE clause):

  With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD DATAFILE clause. For example:

  ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;
  
  

An error is raised if you specify an ADD DATAFILE clause for 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.

Creating a Dictionary-Managed Tablespace

The following statement creates the dictionary-managed tablespace tbsa:

CREATE TABLESPACE tbsa
    DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M
    EXTENT MANAGEMENT DICTIONARY
    DEFAULT STORAGE (
        INITIAL 50K
        NEXT 50K
        MINEXTENTS 2
        MAXEXTENTS 50
        PCTINCREASE 0);

The tablespace has the following characteristics:

• The data of the new tablespace is contained in a single datafile, 50M in size.

• The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY.

• The default storage parameters for any segments created in this tablespace are specified.

The parameters specified in the preceding example determine segment storage allocation in the tablespace. These parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used. They are referred to as storage parameters, and are described in the following table:

The MINIMUM EXTENT parameter on the CREATE TABLESPACE statement also influences segment allocation. If specified, it ensures that all free and allocated extents in the tablespace are at least as large as, and a multiple of, a specified number of bytes. This clause provides one way to control free space fragmentation in the tablespace.

Specifying Tablespace Default Storage Parameters

When you create a new dictionary-managed tablespace, you can specify default storage parameters for objects that will be created in the tablespace. Storage parameters specified when an object is created override the default storage parameters of the tablespace containing the object. If you do not specify storage parameters when creating an object, the object segment automatically uses the default storage parameters for the tablespace.

Set the default storage parameters for a tablespace to account for the size of a typical object that the tablespace will contain (you estimate this size). You can specify different storage parameters for an unusual or exceptional object when creating that object. You can also alter your default storage parameters at a later time.

You cannot specify default storage parameters for tablespaces that are specifically created as locally managed.

Altering a Dictionary-Managed Tablespace

One common change to a database is adding a datafile. The following statement creates a new datafile for the tbsa tablespace:

ALTER TABLESPACE tbsa
   ADD DATAFILE '/u02/oracle/data/tbsa02.dbf' SIZE 1M;

You can also change the default storage parameters of a tablespace using the ALTER TABLESPACE statement, as illustrated in the following example:

ALTER TABLESPACE users
    DEFAULT STORAGE (
       NEXT 100K
       MAXEXTENTS 20
       PCTINCREASE 0);

New values for the default storage parameters of a tablespace affect only objects that are subsequently created, or extents subsequently allocated for existing segments within the tablespace.

Other reasons for issuing an ALTER TABLESPACE statement include, but are not limited to:

• Coalescing free space in a tablespace. See "Coalescing Free Space in Dictionary-Managed Tablespaces".

• Altering tablespace availability (ONLINE/OFFLINE). See "Altering Tablespace Availability ".

• Making a tablespace read-only or read/write. See "Using Read-Only Tablespaces".

• Adding or renaming a datafile, or enabling/disabling the autoextension of the size of a datafile in the tablespace. See Chapter 9, " Managing Datafiles and Tempfiles".

Coalescing Free Space in Dictionary-Managed Tablespaces

Over time, the free space in a dictionary-managed tablespace can become fragmented, making it difficult to allocate new extents. This section discusses how to defragment free space and includes the following topics:

• How Oracle Database Coalesces Free Space

• Manually Coalescing Free Space

• Monitoring Free Space

How Oracle Database Coalesces Free Space

A free extent in a dictionary-managed tablespace is made up of a collection of contiguous free blocks. When allocating new extents to a tablespace segment, the database uses the free extent closest in size to the required extent. In some cases, when segments are dropped, their extents are deallocated and marked as free, but adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes allocation of larger extents more difficult.

Oracle Database addresses fragmentation in several ways:

• When attempting to allocate a new extent for a segment, the database first tries to find a free extent large enough for the new extent. Whenever the database cannot find a free extent that is large enough for the new extent, it coalesces adjacent free extents in the tablespace and looks again.

• The SMON background process periodically coalesces neighboring free extents when the PCTINCREASE value for a tablespace is not zero. If you set PCTINCREASE=0, no coalescing of free extents occurs. If you are concerned about the overhead of ongoing coalesce operations of SMON, an alternative is to set PCTINCREASE=0, and periodically coalesce free space manually.

• When a segment is dropped or truncated, a limited form of coalescing is performed if the PCTINCREASE value for the segment is not zero. This is done even if PCTINCREASE=0 for the tablespace containing the segment.

• You can use the ALTER TABLESPACE ... COALESCE statement to manually coalesce any adjacent free extents.

The process of coalescing free space is illustrated in the following figure.

Figure 8-1 Coalescing Free Space

Description of the illustration admin017.gif

Note: Coalescing free space is not necessary for locally managed tablespaces, because bitmaps automatically track adjacent free space.

ALTER TABLESPACE tabsp_4 COALESCE;

SELECT  BLOCK_ID, BYTES, BLOCKS 
     FROM  DBA_FREE_SPACE
     WHERE TABLESPACE_NAME = 'TABSP_4'
     ORDER BY BLOCK_ID;

BLOCK_ID   BYTES      BLOCKS     
---------- ---------- ---------- 
         2      16384          2          
         4      16384          2          
         6      81920         10          
        16      16384          2          
        27      16384          2          
        29      16384          2          
        31      16384          2          
        33      16384          2          
        35      16384          2          
        37      16384          2          
        39       8192          1          
        40       8192          1          
        41     196608         24          
13 rows selected.

BLOCK_ID   BYTES      BLOCKS     
---------- ---------- ---------- 
         2     131072         16          
        27     311296         38          
2 rows selected.

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.

CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' 
     SIZE 20M REUSE
     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The Oracle Database default for SIZE is 1M. But if you want to specify another value for SIZE, you can do so as shown in the preceding statement.

The AUTOALLOCATE clause is not allowed for temporary tablespaces.

Creating a Bigfile Temporary Tablespace

Just as for regular tablespaces, you can create single-file (bigfile) temporary tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single-tempfile tablespace. See the sections "Creating a Bigfile Tablespace" and "Altering a Bigfile Tablespace" for information about bigfile tablespaces, but consider that you are creating temporary tablespaces that use tempfiles instead of datafiles.

Altering a Locally Managed Temporary Tablespace

Except for adding a tempfile, as illustrated in the following example, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace.

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

However, the ALTER DATABASE statement can be used to alter tempfiles.

The following statements take offline and bring online temporary files:

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

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

The following statement resizes a temporary file:

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

The following statement drops a temporary file and deletes the 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 file for the datafile 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 file.

It is also possible, but not shown, to use the ALTER DATABASE statement to enable or disable the automatic extension of an existing tempfile, and to rename (RENAME FILE) a tempfile.

Creating a Dictionary-Managed Temporary Tablespace

In earlier releases, you could create a dictionary-managed temporary tablespace by specifying the TEMPORARY keyword after specifying the tablespace name in a CREATE TABLESPACE statement. This syntax has been deprecated. It is still supported in case you are using dictionary-managed tablespaces, which are not supported by the CREATE TEMPORARY TABLEPSPACE syntax. If you do use this deprecated syntax, the extent cannot be locally managed, nor can you specify a nonstandard block size for the tablespace.

Oracle strongly recommends that you create locally managed temporary tablespaces containing tempfiles, as described in the preceding sections. The creation of new dictionary-managed tablespaces is scheduled for desupport.

Altering a Dictionary-Managed Temporary Tablespace

You can issue the ALTER TABLESPACE statement against a dictionary-managed temporary tablespace using many of the same keywords and clauses as for a permanent dictionary-managed tablespace. Restrictions are noted in the Oracle Database SQL Reference.

You can change an existing permanent dictionary-managed tablespace to a temporary tablespace, using the ALTER TABLESPACE statement. For example:

ALTER TABLESPACE tbsa TEMPORARY;

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.

For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
     SIZE 50M
     TABLESPACE GROUP group1;

ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;

Changing Members of a Tablespace Group

You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement.

The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.

CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf'
     SIZE 25M
     TABLESPACE GROUP group1;

The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 already belongs to group1, it is in effect moved from group1 to group2:

ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;

Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3.

You can remove a tablespace from a group as shown in the following statement:

ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';

Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.

Assigning a Tablespace Group as the Default Temporary Tablespace

Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:

ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;

Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2.

If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first be remove from the tablespace from the tablespace group. Likewise, you cannot drop a single temporary as long as it is the default temporary tablespace.

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 sales_1 and sales_2 were being transported to a different platform, you can execute the following query on both platforms to determine if the platforms are supported and their endian formats:

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.

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. See the example for TSPITR in the Oracle Database Backup and Recovery Advanced User's Guide.

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.

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.

   SQL> ALTER TABLESPACE sales_1 READ ONLY;
   
   Tablespace altered.
   
   SQL> ALTER TABLESPACE sales_2 READ ONLY;
   
   Tablespace altered.
   
   

2. Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.

   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:

   • The DUMPFILE parameter specifies the name of the structural information export file to be created, expdat.dmp.

   • The DIRECTORY parameter specifies the default directory object that points to the operating system location of the dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant REWRITE object privilege on the directory to PUBLIC.

   • 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.

   
   

3. When finished, exit back to SQL*Plus:

   $ EXIT
   

   
   

   See Also: Oracle Database Utilities for information about using the Data Pump utility

   
   

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:

   SQL> HOST
   
   

5. The RMAN CONVERT command is used to do the conversion. Connect to RMAN:

   $ RMAN TARGET /
   
   Recovery Manager: Release 10.1.0.0.0 
   
   Copyright (c) 1995, 2003, Oracle Corporation.  All rights reserved.
   
   connected to target database: salesdb (DBID=3295731590)
   
   

6. 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.

   RMAN> CONVERT TABLESPACE sales_1,sales_2 
   2> TO PLATFORM 'Microsoft Windows NT'
   3> FORMAT '/temp/%U';
   
   Starting backup at 08-APR-03
   using target database control file instead of recovery catalog
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: sid=11 devtype=DISK
   channel ORA_DISK_1: starting datafile conversion
   input datafile fno=00005 name=/u01/oracle/oradata/salesdb/sales_101.dbf
   converted datafile=/temp/data_D-10_I-3295731590_TS-ADMIN_TBS_FNO-5_05ek24v5
   channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
   channel ORA_DISK_1: starting datafile conversion
   input datafile fno=00004 name=/u01/oracle/oradata/salesdb/sales_101.dbf
   converted datafile=/temp/data_D-10_I-3295731590_TS-EXAMPLE_FNO-4_06ek24vl
   channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
   Finished backup at 08-APR-03
   

   
   

   See Also: Oracle Database Recovery Manager Reference for a description of the RMAN CONVERT command

   
   

7. Exit recovery manager:

   RMAN> exit
   Recovery Manager complete.
   

Step 4: Transport the Tablespace Set

Transport both the datafiles and the export file of the tablespaces to a place accessible to the target database. You can use any facility for copying flat files (for example, an operating system copy utility, ftp, the DBMS_FILE_TRANSFER package, or publishing on CDs).

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> DBFILE_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 datafiles are plugged in, 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: Plug In the Tablespace Set

Any privileged user can perform this step. To plug in a tablespace set, perform the following tasks:

1. Plug in the tablespaces and integrate the structural information using the Data Pump Import utility, impdp:

   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:

   • The DUMPFILE parameter specifies the exported file containing the metadata for the tablespaces to be imported.

   • The DIRECTORY parameter specifies the directory object that identifies the location of the dump file.

   • The TRANSPORT_DATAFILES parameter identifies all of the datafiles containing the tablespaces to be imported.

   • 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 plugged in. 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
   

   
   

   See Also: Oracle Database Utilities for information about using the import utility

   
   

2. If required, put the tablespaces into read/write mode as follows:

   ALTER TABLESPACE sales_1 READ WRITE;
   ALTER TABLESPACE sales_2 READ WRITE;
   

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 nonprefixed 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 nonprefixed 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 plug it into 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 plug in 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 plugged-in tablespace. Thus, when plugging in a tablespace on 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.

You can mount the same tablespace read-only on multiple databases in either of the following ways:

• Plug the tablespace into each of the databases on which you want to mount the tablespace. Generate a transportable set in a single database. Put the datafiles in the transportable set on a disk accessible to all databases. Import the structural information into each database.

• Generate the transportable set in one of the databases and plug it into other databases. If you use this approach, it is assumed that the datafiles are already on the shared disk, and they belong to an existing tablespace in one of the databases. You can make the tablespace read-only, generate the transportable set, and then plug the tablespace in to other databases while the datafiles remain in the same location on the shared disk.

You can make the 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 plugged into any Oracle Database, you can archive old/historical data in an enterprise data warehouse using the transportable tablespace procedures described in this chapter.

Using Transportable Tablespaces to Perform TSPITR

You can use transportable tablespaces to perform tablespace point-in-time recovery (TSPITR).