109 DBMS_STREAMS_TABLESPACE_ADM

Using DBMS_STREAMS_TABLESPACE_ADM

This section contains topics which relate to using the DBMS_STREAMS_TABLESPACE_ADM package.

• Overview

• Types

Overview

Either a simple tablespace or a self-contained tablespace set must be specified in each procedure in this package.

A self-contained tablespace has no references from the tablespace pointing outside of the tablespace. For example, if an index in the tablespace is for a table in a different tablespace, then the tablespace is not self-contained. A simple tablespace is a self-contained tablespace that uses only one datafile.

A simple tablespace must be specified in the following procedures:

• ATTACH_SIMPLE_TABLESPACE Procedure

• CLONE_SIMPLE_TABLESPACE Procedure

• DETACH_SIMPLE_TABLESPACE Procedure

• PULL_SIMPLE_TABLESPACE Procedure

A self-contained tablespace set has no references from inside the set of tablespaces pointing outside of the set of tablespaces. For example, if a partitioned table is partially contained in the set of tablespaces, then the set of tablespaces is not self-contained.

A self-contained tablespace set must be specified in the following procedures:

• ATTACH_TABLESPACES Procedure

• CLONE_TABLESPACES Procedure

• DETACH_TABLESPACES Procedure

• PULL_TABLESPACES Procedure

To determine whether a set of tablespaces is self-contained, use the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS.

Types

This package contains the PL/SQL types listed in Table 109-1.

DIRECTORY_OBJECT_SET Type

Contains the names of one or more directory objects. Each name must be a directory object created using the SQL statement CREATE DIRECTORY.

Syntax

TYPE DIRECTORY_OBJECT_SET IS TABLE OF VARCHAR2(32) 
  INDEX BY BINARY_INTEGER;

FILE Type

Contains the directory object associated with a directory and the name of the file in the directory.

Syntax

TYPE FILE IS RECORD(
   directory_object VARCHAR2(32),
   file_name        VARCHAR2(4000));

Attributes

FILE_SET Type

Contains one or more files.

Syntax

TYPE FILE_SET IS TABLE OF FILE 
   INDEX BY BINARY_INTEGER;

TABLESPACE_SET Type

Contains the names of one or more tablespaces.

Syntax

TYPE TABLESPACE_SET IS TABLE OF VARCHAR2(32) 
   INDEX BY BINARY_INTEGER;

Summary of DBMS_STREAMS_TABLESPACE_ADM Subprograms

ATTACH_SIMPLE_TABLESPACE Procedure

This procedure uses Data Pump to import a simple tablespace previously exported using the DBMS_STREAMS_TABLESPACE_ADM package, Data Pump export, or the Recovery Manager (RMAN) TRANSPORT TABLESPACE command.

Syntax

DBMS_STREAMS_TABLESPACE_ADM.ATTACH_SIMPLE_TABLESPACE(
   directory_object      IN  VARCHAR2,
   tablespace_file_name  IN  VARCHAR2,
   converted_file_name   IN  VARCHAR2  DEFAULT NULL,
   datafile_platform     IN  VARCHAR2  DEFAULT NULL,
   tablespace_name       OUT VARCHAR2);

Parameters

Usage Notes

To run this procedure, a user must meet the following requirements:

• Have IMP_FULL_DATABASE role

• Have READ and WRITE privilege on the directory object that contains the Data Pump export dump file and the datafiles for the tablespaces in the set, specified by the directory_object parameter

Automatic Storage Management (ASM) directories cannot be used with this procedure.

ATTACH_TABLESPACES Procedure

This procedure uses Data Pump to import a self-contained tablespace set previously exported using the DBMS_STREAMS_TABLESPACE_ADM package, Data Pump export, or the Recovery Manager (RMAN) TRANSPORT TABLESPACE command.

This procedure is overloaded and consists of the following versions:

• One version of the procedure uses a Data Pump job name in the datapump_job_name parameter. This job performs the Data Pump import to complete the attach operation. In addition, if the platform at the export database is different than the local database platform, then this procedure optionally can create datafiles for the tablespace set that can be used with the local platform.

• The other version of the procedure uses a file group that can consist of multiple versions of the tablespace set in a tablespace repository. A tablespace repository is a collection of tablespace sets in a file group repository. When this version of the procedure is run, a Data Pump import is performed. This version of the procedure uses the files in a file group version and can copy the export dump file, export log file, and the datafiles that comprise the tablespace set into the specified directories. The file group and version are specified using the file_group_name and version_name parameters, respectively. This version of the procedure does not require a datafiles platform specification if the platform at the export database is different than the local database platform. Instead, the tablespace set is migrated automatically to the correct platform when it is attached.

Syntax

DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
   datapump_job_name          IN OUT VARCHAR2,
   dump_file                  IN     FILE,
   tablespace_files           IN     FILE_SET,
   converted_files            IN     FILE_SET,
   datafiles_platform         IN     VARCHAR2  DEFAULT NULL,
   log_file                   IN     FILE      DEFAULT NULL,
   tablespace_names           OUT    TABLESPACE_SET);

DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
   file_group_name            IN   VARCHAR2,
   version_name               IN   VARCHAR2  DEFAULT NULL,
   datafiles_directory_object IN   VARCHAR2  DEFAULT NULL,
   logfile_directory_object   IN   VARCHAR2  DEFAULT NULL,
   repository_db_link         IN   VARCHAR2  DEFAULT NULL,
   tablespace_names           OUT  TABLESPACE_SET);

Parameters

Usage Notes

The following sections contain usage notes for this procedure:

• User Requirements

• Procedures Used to Clone or Detach a Tablespace Set

• When the Attach Database Is Different Than the Clone or Detach Database

• Automatic Storage Management Directories

User Requirements

To run either version of this procedure, a user must meet the following requirements:

• Have IMP_FULL_DATABASE role

• Have READ and WRITE privilege on the directory objects that contain the Data Pump export dump file and the datafiles for the tablespaces in the set, specified by the dump_file and tablespace_files parameters, or by the datafiles_directory_object parameter

• Have WRITE privilege on the directory object that will hold the Data Pump import log file, specified by the log_file parameter or logfile_directory_object parameter if it is non-NULL

If the Data Pump job version of the procedure is run, then the user must have WRITE privilege on the directory objects that will hold the converted datafiles for the tablespaces in the set if platform conversion is necessary. These directory objects are specified by the converted_files parameter if it is non-NULL.

If the file group version of the procedure is run, then the user must have the necessary privileges to manage the file group.

Procedures Used to Clone or Detach a Tablespace Set

After a tablespace set is cloned or detached using the CLONE_TABLESPACES or DETACH_TABLEPSACES procedure, respectively, the tablespace set can be attached to a database using the ATTACH_TABLESPACES procedure. If the Data Pump job version of the CLONE_TABLESPACES or DETACH_TABLEPSACES procedure was used, then use the Data Pump job version of the ATTACH_TABLESPACES procedure. If the file group version of the CLONE_TABLESPACES or DETACH_TABLEPSACES procedure was used, then use the file group version of the ATTACH_TABLESPACES procedure.

When the Attach Database Is Different Than the Clone or Detach Database

You can attach a tablespace set to a different database than the database from which the tablespace set was cloned or detached. The two databases might or might not share a file system. If the two databases do not share a file system, then you must transfer the dump file and datafiles to the remote system using the DBMS_FILE_TRANSFER package, FTP, or some other method. You can attach the tablespace set in one of the following ways depending on the version of the ATTACH_TABLESPACES procedure you use:

• If you use the Data Pump job version of the procedure, then specify the relevant files on the file system. The directory object names can be different in the databases.

• If you use the file group version of the procedure, then you can use the repository_db_link parameter to specify the database where tablespace repository resides. The directory objects for the files must exist and must match in the databases.

Automatic Storage Management Directories

Automatic Storage Management (ASM) directories can be specified for the directory objects that store data files and export dump files, but ASM directories cannot be specified for directory objects that store log files.

CLONE_SIMPLE_TABLESPACE Procedure

This procedure clones a simple tablespace. The specified tablespace must be online.

Specifically, this procedure performs the following actions:

1. Makes the specified tablespace read-only if it is not read-only

2. Uses Data Pump to export the metadata for the tablespace and places the dump file in the specified directory

3. Places the datafile for the specified tablespace in the specified directory

4. If this procedure made the tablespace read-only, then makes the tablespace read/write

In addition, this procedure optionally can create a datafile for the tablespace that can be used with a platform that is different than the local database platform.

Syntax

DBMS_STREAMS_TABLESPACE_ADM.CLONE_SIMPLE_TABLESPACE(
   tablespace_name      IN  VARCHAR2,
   directory_object     IN  VARCHAR2,
   destination_platform IN  VARCHAR2  DEFAULT NULL,
   tablespace_file_name OUT VARCHAR2);

Parameters

Usage Notes

To run this procedure, a user must meet the following requirements:

• Have EXP_FULL_DATABASE role

• Have access to at least one data dictionary view that contains information about the tablespaces. These views include DBA_TABLESPACES and USER_TABLESPACES.

• Have MANAGE TABLESPACE or ALTER TABLESPACE on a tablespace if the tablespace must be made read-only

• Have READ privilege on the directory object for the directory that contains the datafile for the tablespace. The name of this tablespace is specified by the tablespace_name parameter. If a directory object does not exist for this directory, then create the directory object and grant the necessary privileges before you run this procedure.

• Have READ and WRITE privilege on the directory object that will contain the Data Pump export dump file, specified by the directory_object parameter

• If the file group version of the procedure is run, then the user must have the necessary privileges to manage file group.

After cloning a tablespace using this procedure, you can add the tablespace to a different database using the ATTACH_SIMPLE_TABLESPACE procedure. If the database is a remote database and you want to use the ATTACH_SIMPLE_TABLESPACE procedure, then you can transfer the dump file and datafile to the remote system using the DBMS_FILE_TRANSFER package, FTP, or some other method.

Automatic Storage Management (ASM) directories cannot be used with this procedure.

CLONE_TABLESPACES Procedure

This procedure clones a set of self-contained tablespaces. All of the tablespaces in the specified tablespace set must be online.

Specifically, this procedure performs the following actions:

1. Makes any read/write tablespace in the specified tablespace set read-only

2. Uses Data Pump to export the metadata for the tablespaces in the tablespace set and places the dump file in the specified directory

3. Places the datafiles that comprise the specified tablespace set in the specified directory

4. If this procedure made a tablespace read-only, then makes the tablespace read/write

This procedure is overloaded and consists of the following versions:

• One version of the procedure uses a Data Pump job name in the datapump_job_name parameter. This job performs the Data Pump export. This version of the procedure completes the clone operation by placing the export dump file, export log file, and the datafiles that comprise the tablespace set in the specified directories, but the files are not added to a file group version. In addition, this version of the procedure optionally can create datafiles for the tablespace set that can be used with a platform that is different than the local database platform.

• The other version of the procedure uses a file group that can consist of multiple versions of the tablespace set in a tablespace repository. A tablespace repository is a collection of tablespace sets in a file group repository. When this version of the procedure is run, a Data Pump export is performed, and this version of the procedure completes the clone operation by placing the export dump file, export log file, and the datafiles that comprise the tablespace set in the appropriate file group version. The file group and version are specified using the file_group_name and version_name parameters, respectively. This version of the procedure does not require a destination platform specification if the destination platform is different. Instead, the tablespace set is migrated automatically to the correct platform when it is attached at the destination database using the file group version of the ATTACH_TABLESPACES procedure.

Syntax

DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
   datapump_job_name            IN OUT VARCHAR2,
   tablespace_names             IN     TABLESPACE_SET,
   dump_file                    IN     FILE,
   tablespace_directory_objects IN     DIRECTORY_OBJECT_SET,
   destination_platform         IN     VARCHAR2  DEFAULT NULL,
   log_file                     IN     FILE      DEFAULT NULL,
   tablespace_files             OUT    FILE_SET);

DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
   tablespace_names             IN  TABLESPACE_SET,
   tablespace_directory_object  IN  VARCHAR2  DEFAULT NULL,
   log_file_directory_object    IN  VARCHAR2  DEFAULT NULL,
   file_group_name              IN  VARCHAR2,
   version_name                 IN  VARCHAR2  DEFAULT NULL,
   repository_db_link           IN  VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

To run either version of this procedure, a user must meet the following requirements:

• Have EXP_FULL_DATABASE role

• Have access to at least one data dictionary view that contains information about the tablespaces. These views include DBA_TABLESPACES and USER_TABLESPACES.

• Have MANAGE TABLESPACE or ALTER TABLESPACE on a tablespace if the tablespace must be made read-only

• Have READ privilege on the directory objects for the directories that contain the datafiles for the tablespace set. The names of these tablespaces are specified by the tablespace_names parameter. If a directory object does not exist for one or more of these directories, then create the directory objects and grant the necessary privileges before you run this procedure.

• Have READ and WRITE privilege on the directory object that will contain the Data Pump export dump file, specified by the dump_file parameter or the tablespace_directory_object parameter

• Have WRITE privilege on the directory objects that will contain the copied datafiles for the tablespaces in the set, specified by the tablespace_directory_objects parameter if non-NULL or the tablespace_directory_object parameter

• Have WRITE privilege on the directory object that will contain the Data Pump export log file, specified by the log_file parameter if non-NULL or the log_file_directory_object parameter if non-NULL

If the file group version of the procedure is run, then the user must have the necessary privileges to manage the file group.

Automatic Storage Management (ASM) directories can be specified for the directory objects that store data files and export dump files, but ASM directories cannot be specified for directory objects that store log files.

After cloning a tablespace set using this procedure, you can attach the tablespaces to a different database using the ATTACH_TABLESPACES procedure.

DETACH_SIMPLE_TABLESPACE Procedure

This procedure detaches a simple tablespace. The specified tablespace must be online.

Specifically, this procedure performs the following actions:

1. Makes the specified tablespace read-only if it is not read-only

2. Uses Data Pump to export the metadata for the tablespace and places the dump file in the directory that contains the tablespace datafile

3. Drops the tablespace and its contents from the database

Syntax

DBMS_STREAMS_TABLESPACE_ADM.DETACH_SIMPLE_TABLESPACE(
   tablespace_name       IN  VARCHAR2,
   directory_object      OUT VARCHAR2,
   tablespace_file_name  OUT VARCHAR2);

Parameters

Usage Notes

To run this procedure, a user must meet the following requirements:

• Have EXP_FULL_DATABASE role

• Have access to at least one data dictionary view that contains information about the tablespaces. These views include DBA_TABLESPACES and USER_TABLESPACES.

• Have DROP TABLESPACE privilege

• Have MANAGE TABLESPACE or ALTER TABLESPACE on a tablespace if the tablespace must be made read-only

• Have READ and WRITE privilege on the directory object for the directory that contains the tablespace datafile. The name of this tablespace is specified by the tablespace_name parameter. If a directory object does not exist for this directory, then create the directory object and grant the necessary privileges before you run this procedure. This directory also will contain the Data Pump export dump file generated by this procedure.

After detaching a tablespace using this procedure, you can add the tablespace to a different database using the ATTACH_SIMPLE_TABLESPACE procedure. If the database is a remote database and you want to use the ATTACH_SIMPLE_TABLESPACE procedure, then you can transfer the dump file and datafile to the remote system using the DBMS_FILE_TRANSFER package, FTP, or some other method. You can use the two OUT parameters in this procedure to accomplish the attach or pull operation.

Automatic Storage Management (ASM) directories cannot be used with this procedure.

DETACH_TABLESPACES Procedure

This procedure detaches a set of self-contained tablespaces. All of the tablespaces in the specified tablespace set must be online and any table partitions must not span tablespaces in the tablespace set.

Specifically, this procedure performs the following actions:

1. Makes any read/write tablespace in the specified tablespace set read-only

2. Uses Data Pump to export the metadata for the tablespace set and places the dump file in the specified directory

3. Drops the tablespaces in the specified tablespace set and their contents from the database

This procedure does not move or copy the datafiles that comprise the specified tablespace set.

This procedure is overloaded and consists of the following versions:

• One version of the procedure uses a Data Pump job name in the datapump_job_name parameter. This job performs the Data Pump export. This version of the procedure completes the detach operation by placing the export dump file and export log file in the specified directories, but the files are not added to a file group version.

• The other version of the procedure uses a file group that can consist of multiple versions of the tablespace set in a tablespace repository. A tablespace repository is a collection of tablespace sets in a file group repository. When this version of the procedure is run, a Data Pump export is performed, and this version of the procedure completes the detach operation by placing the export dump file and export log file in the appropriate file group version. The datafiles that comprise the tablespace set are not moved or copied, but they are referenced in the version that is detached. The file group and version are specified using the file_group_name and version_name parameters, respectively. Also, if the destination platform is different, then the tablespace set is migrated automatically to the correct platform when it is attached at the destination database using the file group version of the ATTACH_TABLESPACES procedure.

Syntax

DBMS_STREAMS_TABLESPACE_ADM.DETACH_TABLESPACES(
   datapump_job_name         IN OUT VARCHAR2,
   tablespace_names          IN     TABLESPACE_SET,
   dump_file                 IN     FILE,
   log_file                  IN     FILE  DEFAULT NULL,
   tablespace_files          OUT    FILE_SET);

DBMS_STREAMS_TABLESPACE_ADM.DETACH_TABLESPACES(
   tablespace_names          IN TABLESPACE_SET,
   export_directory_object   IN VARCHAR2  DEFAULT NULL,
   log_file_directory_object IN VARCHAR2  DEFAULT NULL,
   file_group_name           IN VARCHAR2,
   version_name              IN VARCHAR2  DEFAULT NULL,
   repository_db_link        IN VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

To run this either version of this procedure, a user must meet the following requirements:

• Have EXP_FULL_DATABASE role

• Have access to at least one data dictionary view that contains information about the tablespaces. These views include DBA_TABLESPACES and USER_TABLESPACES.

• Have DROP TABLESPACE privilege

• Have MANAGE TABLESPACE or ALTER TABLESPACE on a tablespace if the tablespace must be made read-only

• Have READ privilege on the directory objects for the directories that contain the datafiles for the tablespace set. The names of these tablespaces are specified by the tablespace_names parameter. If a directory object does not exist for one or more of these directories, then create the directory objects and grant the necessary privileges before you run this procedure.

• Have READ and WRITE privilege on the directory object that will contain the Data Pump export dump file, specified by the dump_file parameter or the export_directory_object parameter

• Have WRITE privilege on the directory object that will contain the Data Pump export the log file, specified by the log_file parameter if non-NULL or by the log_file_directory_object parameter if non-NULL

If the file group version of the procedure is run, then the user must have the necessary privileges to manage the file group.

Automatic Storage Management (ASM) directories can be specified for the directory objects that store data files and export dump files, but ASM directories cannot be specified for directory objects that store log files.

After detaching a tablespace set using this procedure, you can attach the tablespaces to a different database using the ATTACH_TABLESPACES procedure.

PULL_SIMPLE_TABLESPACE Procedure

This procedure copies a simple tablespace from a remote database and attaches it to the current database. The specified tablespace at the remote database must be online.

Specifically, this procedure performs the following actions:

1. Makes the specified tablespace read-only at the remote database if it is not read-only

2. Uses Data Pump to export the metadata for the tablespace

3. Uses a database link and the DBMS_FILE_TRANSFER package to transfer the datafile for the tablespace and the log file for the Data Pump export to the current database

4. Places the datafile for the specified tablespace and the log file for the Data Pump export in the specified directory at the local database

5. If this procedure made the tablespace read-only, then makes the tablespace read/write

6. Uses Data Pump to import the metadata for the tablespace in the at the local database

In addition, this procedure optionally can create a datafile for the tablespace that can be used with the local platform, if the platform at the remote database is different than the local database platform.

Syntax

DBMS_STREAMS_TABLESPACE_ADM.PULL_SIMPLE_TABLESPACE(
   tablespace_name      IN VARCHAR2,
   database_link        IN VARCHAR2,
   directory_object     IN VARCHAR2  DEFAULT NULL,
   conversion_extension IN VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

To run this procedure, a user must meet the following requirements on the remote database:

• Have the EXP_FULL_DATABASE role

• Have EXECUTE privilege on the DBMS_STREAMS_TABLESPACE_ADM package

• Have access to at least one data dictionary view that contains information about the tablespaces. These views include DBA_TABLESPACES and USER_TABLESPACES.

• Have MANAGE TABLESPACE or ALTER TABLESPACE privilege on a tablespace if the tablespace must be made read-only

• Have READ privilege on the directory object for the directory that contains the datafile for the tablespace. The name of this tablespace is specified by the tablespace_name parameter. If a directory object does not exist for this directory, then create the directory object and grant the necessary privileges before you run this procedure.

To run this procedure, a user must meet the following requirements on the local database:

• Have the roles IMP_FULL_DATABASE and EXECUTE_CATALOG_ROLE

• Have WRITE privilege on the directory object that will contain the Data Pump export the log file, specified by the log_file parameter if non-NULL

• Have WRITE privilege on the directory object that will hold the datafile for the tablespace, specified by the directory_object parameter

Automatic Storage Management (ASM) directories cannot be used with this procedure.

PULL_TABLESPACES Procedure

This procedure copies a set of self-contained tablespaces from a remote database and attaches the tablespaces to the current database. All of the tablespaces in the specified tablespace set at the remote database must be online.

Specifically, this procedure performs the following actions:

1. Makes any read/write tablespace in the specified tablespace set at the remote database read-only

2. Uses Data Pump to export the metadata for the tablespaces in the tablespace set

3. Uses a database link and the DBMS_FILE_TRANSFER package to transfer the datafiles for the tablespace set and the log file for the Data Pump export to the current database

4. Places the datafiles that comprise the specified tablespace set in the specified directories at the local database

5. Places the log file for the Data Pump export in the specified directory at the local database

6. If this procedure made a tablespace read-only, then makes the tablespace read/write

7. Uses Data Pump to import the metadata for the tablespaces in the tablespace set at the local database

In addition, this procedure optionally can create datafiles for the tablespace set that can be used with the local platform, if the platform at the remote database is different than the local database platform.

Syntax

DBMS_STREAMS_TABLESPACE_ADM.PULL_TABLESPACES(
   datapump_job_name            IN OUT VARCHAR2,
   database_link                IN     VARCHAR2,
   tablespace_names             IN     TABLESPACE_SET,
   tablespace_directory_objects IN     DIRECTORY_OBJECT_SET,
   log_file                     IN     FILE,
   conversion_extension         IN     VARCHAR2  DEFAULT NULL);

Parameters

Usage Notes

To run this procedure, a user must meet the following requirements on the remote database:

• Have the EXP_FULL_DATABASE role

• Have EXECUTE privilege on the DBMS_STREAMS_TABLESPACE_ADM package

• Have access to at least one data dictionary view that contains information about the tablespaces. These views include DBA_TABLESPACES and USER_TABLESPACES.

• Have MANAGE TABLESPACE or ALTER TABLESPACE privilege on a tablespace if the tablespace must be made read-only

• Have READ privilege on the directory objects for the directories that contain the datafiles for the tablespace set. The names of these tablespaces are specified by the tablespace_names parameter. If a directory object does not exist for one or more of these directories, then create the directory objects and grant the necessary privileges before you run this procedure.

To run this procedure, a user must meet the following requirements on the local database:

• Have the roles IMP_FULL_DATABASE and EXECUTE_CATALOG_ROLE

• Have WRITE privilege on the directory object that will contain the Data Pump export the log file, specified by the log_file parameter if non-NULL

• Have WRITE privilege on the directory objects that will hold the datafiles for the tablespaces in the set, specified by the tablespace_directory_objects parameter

Automatic Storage Management (ASM) directories can be specified for the directory objects that store data files and export dump files, but ASM directories cannot be specified for directory objects that store log files.