99
DBMS_STREAMS_TABLESPACE_ADM

The DBMS_STREAMS_TABLESPACE_ADM package, one of a set of Streams packages, provides administrative interfaces for copying tablespaces between databases and moving tablespaces from one database to another. This package uses transportable tablespaces, Data Pump, and the DBMS_FILE_TRANSFER package.

This chapter contains the following topics:

• Using DBMS_STREAMS_TABLESPACE_ADM
  • Overview
  • Types
• Summary of DBMS_STREAMS_TABLESPACE_ADM Subprograms

---

Using DBMS_STREAMS_TABLESPACE_ADM

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

Table 99-1 DBMS_STREAMS_TABLESPACE_ADM Types

Type	Description
DIRECTORY_OBJECT_SET Type	Contains the names of one or more directory objects
FILE Type	Contains the directory object associated with a directory and the name of the file in the directory
FILE_SET Type	Contains one or more files
TABLESPACE_SET Type	Contains the names of one or more tablespaces

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

Table 99-2 FILE Attributes

Attribute	Description
directory_object	The name of a directory object. You must specify the name of a directory object created using the SQL statement CREATE DIRECTORY.
file_name	The name of the file in the corresponding directory associated with the directory object

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

Table 99-3  DBMS_STREAMS_TABLESPACE_ADM Package Subprograms

Subprogram	Description
ATTACH_SIMPLE_TABLESPACE Procedure	Uses Data Pump to import a simple tablespace previously exported using the DBMS_STREAMS_TABLESPACE_ADM package or Data Pump export
ATTACH_TABLESPACES Procedure	Uses Data Pump to import a self-contained tablespace set previously exported using the DBMS_STREAMS_TABLESPACE_ADM package or Data Pump export
CLONE_SIMPLE_TABLESPACE Procedure	Clones a simple tablespace. The tablespace can later be attached to a database.
CLONE_TABLESPACES Procedure	Clones a set of self-contained tablespaces. The tablespaces can later be attached to a database.
DETACH_SIMPLE_TABLESPACE Procedure	Detaches a simple tablespace. The tablespace can later be attached to a database.
DETACH_TABLESPACES Procedure	Detaches a set of self-contained tablespaces. The tablespaces can later be attached to a database.
PULL_SIMPLE_TABLESPACE Procedure	Copies a simple tablespace from a remote database and attaches it to the current database
PULL_TABLESPACES Procedure	Copies a set of self-contained tablespaces from a remote database and attaches the tablespaces to the current database

---

ATTACH_SIMPLE_TABLESPACE Procedure

This procedure uses Data Pump to import a simple tablespace previously exported using the DBMS_STREAMS_TABLESPACE_ADM package or Data Pump export.

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

Table 99-4  ATTACH_SIMPLE_TABLESPACE Procedure Parameters

Parameter	Description
directory_object	The directory that contains the Data Pump dump file and the datafile for the tablespace. You must specify the name of a directory object created using the SQL statement CREATE DIRECTORY. The name of the Data Pump export dump file must be the same as the datafile name for the tablespace, except with a .dmp extension. If the converted_file_name is non-NULL, specify the dump file produced by the export database, not the file name after conversion. The Data Pump import log file is written to this directory. The name of the log file is the same as the datafile name for the tablespace, except with an .alg extension. If a file already exists with the same name as the log file in the directory, then the file is overwritten. If NULL, then an error is raised.
tablespace_file_name	The name of the datafile for the tablespace being imported. If NULL, then an error is raised.
converted_file_name	If the datafile_platform parameter is non-NULL and is not the same as the platform of the local import database, then specify a file name for the converted datafile. The datafile is converted to the platform of the local import database and copied to the new file name. The existing datafile is not modified nor deleted. If non-NULL and the datafile_platform parameter is NULL, then this parameter is ignored. If non-NULL and the datafile_platform parameter specifies the same platform as the local import database, then this parameter is ignored. If NULL and the datafile_platform parameter is non-NULL, then an error is raised.
datafile_platform	Specify NULL if the platform is the same for the export database and the current import database. Specify the platform for the export database if the platform is different for the export database and the import database. You can determine the platform of a database by querying the PLATFORM_NAME column in the V$DATABASE dynamic performance view. The V$TRANSPORTABLE_PLATFORM dynamic performance view lists all platforms that support cross-platform transportable tablespaces.
tablespace_name	Contains the name of the attached tablespace. The attached tablespace is read-only. You can use this parameter in an ALTER TABLESPACE statement to make it read/write if necessary.

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

  See Also: Overview

---

ATTACH_TABLESPACES Procedure

This procedure uses Data Pump to import a self-contained tablespace set previously exported using the DBMS_STREAMS_TABLESPACE_ADM package or Data Pump export.

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 export database is different than the local database platform.

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

Parameters

Table 99-5  ATTACH_TABLESPACES Procedure Parameters

Parameter	Description
data_pump_job_name	The Data Pump job name. Specify a Data Pump job name if you want to adhere to naming conventions or if you want to track the job more easily. If NULL, then the system generates a Data Pump job name.
dump_file	The file name of the Data Pump dump file to import. If NULL or if a file attribute is NULL, then an error is raised.
tablespace_files	The file set that contains the datafiles for the tablespace set being imported. If NULL, then an error is raised.
converted_files	If the datafiles_platform parameter is non-NULL and is not the same as the platform for the local import database, then specify a file set with the names of the converted datafiles. The datafiles are converted to the platform of the local import database and copied to the new file names. In this case, the number of files in the specified file set must match the number of files in the file set specified for the tablespace_files parameter. The existing datafiles are not modified nor deleted. If non-NULL and the datafiles_platform parameter is NULL, then this parameter is ignored. If non-NULL and the datafiles_platform parameter specifies the same platform as the local import database, then this parameter is ignored. If NULL and the datafiles_platform parameter is non-NULL, then an error is raised.
datafiles_platform	Specify NULL if the platform is the same for the export database and the current import database. Specify the platform for the export database if the platform is different for the export database and the import database. You can determine the platform of a database by querying the PLATFORM_NAME column in the V$DATABASE dynamic performance view. The V$TRANSPORTABLE_PLATFORM dynamic performance view lists all platforms that support cross-platform transportable tablespaces.
log_file	Specify the log file name for the Data Pump import. If NULL or if at least one file parameter is NULL, then the system generates a log file name with the extension .alg and places it in the Data Pump export dump file directory. If a file already exists with the same name as the log file in the directory, then the file is overwritten.
tablespace_names	Contains the names of the attached tablespaces. The attached tablespace is read-only. You can use the names in this parameter in an ALTER TABLESPACE statement to make them read/write if necessary.

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 objects that contain the Data Pump export dump file and the datafiles for the tablespaces in the set, specified by the dump_file parameter and the tablespace_files parameter, respectively
• Have WRITE privilege on the directory object that will hold the Data Pump import log file, specified by the log_file parameter if it is non-NULL
• 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.

  See Also: Overview Summary of DBMS_STREAMS_TABLESPACE_ADM Subprograms

---

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

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

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.

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

Table 99-6  CLONE_SIMPLE_TABLESPACE Procedure Parameters

Parameter	Description
tablespace_name	The tablespace to be cloned. If NULL, then an error is raised.
directory_object	The directory where the Data Pump export dump file, the Data Pump export log file, and the datafile for the tablespace are placed. You must specify the name of a directory object created using the SQL statement CREATE DIRECTORY. The name of the Data Pump export dump file is the same as the datafile name for the tablespace, except with a .dmp extension. If a file already exists with the same name as the dump file in the directory, then an error is raised. The name of the log file is the same as the datafile name for the tablespace, except with a .clg extension. If a file already exists with the same name as the log file in the directory, then the file is overwritten. If NULL, then an error is raised.
destination_platform	Specify NULL if the platform is the same for the current export database and the intended import database. Specify the platform for the intended import database if the platform is different for the export database and the import database. You can determine the platform of a database by querying the PLATFORM_NAME column in the V$DATABASE dynamic performance view. The V$TRANSPORTABLE_PLATFORM dynamic performance view lists all platforms that support cross-platform transportable tablespaces.
tablespace_file_name	Contains the name of the cloned tablespace datafile. This datafile is placed in the directory specified by the parameter directory_object.

---

CLONE_TABLESPACES 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

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

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

Parameters

Table 99-7  CLONE_TABLESPACES Procedure Parameters

Parameter	Description
data_pump_job_name	The Data Pump job name. Specify a Data Pump job name if you want to adhere to naming conventions or if you want to track the job more easily. If NULL, then the system generates a Data Pump job name.
tablespace_names	The tablespace set to be cloned. If NULL, then an error is raised.
dump_file	The file name of the Data Pump dump file that is exported. If NULL or if a file attribute is NULL, then an error is raised. If the specified file already exists, then an error is raised.
tablespace_directory_objects	The set of directory objects into which the datafiles for the tablespaces are copied. If more than one directory object is in the set, then a datafile is copied to each directory object in the set in sequence. In this case, if the end of the directory object set is reached, then datafile copying starts again with the first directory object in the set. If NULL, then datafiles for the tablespace set are copied to the dump file directory.
destination_platform	Specify NULL if the platform is the same for the current export database and the intended import database. Specify the platform for the intended import database if the platform is different for the export database and the import database. You can determine the platform of a database by querying the PLATFORM_NAME column in the V$DATABASE dynamic performance view. The V$TRANSPORTABLE_PLATFORM dynamic performance view lists all platforms that support cross-platform transportable tablespaces.
log_file	Specify the log file name for the Data Pump export. If NULL or if at least one file parameter is NULL, then the system generates a log file name with the extension .clg and places it in the dump file directory. If a file already exists with the same name as the log file in the directory, then the file is overwritten.
tablespace_files	Contains the datafiles for the cloned tablespace set. These datafiles are placed in the directories specified by the directory objects in the parameter tablespace_directory_objects.

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

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

---

DETACH_SIMPLE_TABLESPACE 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

Table 99-8  DETACH_SIMPLE_TABLESPACE Procedure Parameters

Parameter	Description
data_pump_job_name	The Data Pump job name. Specify a Data Pump job name if you want to adhere to naming conventions or if you want to track the job more easily. If NULL, then the system generates a Data Pump job name.
directory_object	Contains the directory where the Data Pump export dump file and the Data Pump export log file are placed. The procedure uses the directory of the datafile for the tablespace. Therefore, make sure a directory object created using the SQL statement CREATE DIRECTORY exists for this directory. The name of the Data Pump export dump file is the same as the datafile name for the tablespace, except with a .dmp extension. If a file already exists with the same name as the dump file in the directory, then an error is raised. The name of the log file is the same as the datafile name for the tablespace, except with a .dlg extension. If a file already exists with the same name as the log file in the directory, then the file is overwritten.
tablespace_file_name	Contains the name of the detached tablespace datafile.

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 may use the two OUT parameters in this procedure to accomplish the attach or pull operation.

---

DETACH_TABLESPACES 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

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

After detaching a tablespace set using this procedure, you can add the tablespaces in the set to a different database using the ATTACH_TABLESPACES procedure. If the database is a remote database and you want to use the ATTACH_TABLESPACES procedure, then you can transfer the dump file and datafiles to the remote system using the DBMS_FILE_TRANSFER package, FTP, or some other method.

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

Parameters

Table 99-9  DETACH_TABLESPACES Procedure Parameters

Parameter	Description
data_pump_job_name	The Data Pump job name. Specify a Data Pump job name if you want to adhere to naming conventions or if you want to track the job more easily. If NULL, then the system generates a Data Pump job name.
tablespace_names	The tablespace set to be detached. If NULL, then an error is raised.
dump_file	The file name of the Data Pump dump file that is exported. If NULL or if a file attribute is NULL, then an error is raised. If the specified file already exists, then an error is raised.
log_file	Specify the log file name for the Data Pump export. If NULL or if at least one file parameter is NULL, then the system generates a log file name with the extension .dlg and places it in the dump file directory. If a file already exists with the same name as the log file in the directory, then the file is overwritten.
tablespace_files	Contains the names of the datafiles for the detached tablespace set.

---

PULL_SIMPLE_TABLESPACE 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

Table 99-10  PULL_SIMPLE_TABLESPACE Procedure Parameters

Parameter	Description
tablespace_name	The tablespace to be pulled. If NULL, then an error is raised.
database_link	The name of the database link to the database that contains the tablespace to pull. If NULL, then an error is raised.
directory_object	The directory object to which the datafile for the tablespace is copied on the local database. You must specify the name of a directory object created using the SQL statement CREATE DIRECTORY. The Data Pump import log file is written to this directory. The name of the log file is the same as the datafile name for the tablespace, except with a .plg extension. If a file already exists with the same name as the log file in the directory, then the file is overwritten. If NULL, then an error is raised.
conversion_extension	Specify NULL if the platform is the same for the remote export database and the current import database. If the platform is different for the export database and the import database, then specify an extension for the tablespace datafile that is different than the extension for the tablespace datafile at the remote database. In this case, the procedure transfers the datafile to the import database and converts it to be compatible with the current import database platform automatically. After conversion is complete, the original datafile is deleted at the import database.

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

  See Also: Overview

---

PULL_TABLESPACES 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

Table 99-11  PULL_TABLESPACES Procedure Parameters

Parameter	Description
data_pump_job_name	The Data Pump job name. Specify a Data Pump job name if you want to adhere to naming conventions or if you want to track the job more easily. If NULL, then the system generates a Data Pump job name.
database_link	The name of the database link to the database that contains the tablespace set to pull. If NULL, then an error is raised.
tablespace_names	The tablespace set to be pulled. If NULL, then an error is raised.
tablespace_directory_objects	The set of directory objects to which the datafiles for the tablespaces are copied. If more than one directory object is in the set, then a datafile is copied to each directory object in the set in sequence. In this case, if the end of the directory object set is reached, then datafile copying starts again with the first directory object in the set. If NULL, then an error is raised.
log_file	Specify the log file name for the Data Pump export. If NULL or if at least one file parameter is NULL, then the system generates a log file name with the extension .plg and places it in one of the datafile directories. If a file already exists with the same name as the log file in the directory, then the file is overwritten.
conversion_extension	Specify NULL if the platform is the same for the remote export database and the current import database. If the platform is different for the export database and the import database, then specify an extension for the tablespace datafiles that is different than the extension for the tablespace datafiles at the remote database. In this case, the procedure transfers the datafiles to the import database and converts them to be compatible with the current import database platform automatically. After conversion is complete, the original datafiles are deleted at the import database.

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

  See Also: Overview Summary of DBMS_STREAMS_TABLESPACE_ADM Subprograms