PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
View PDF |
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.
See Also:
Oracle Streams Concepts and Administration and Oracle Streams Replication Administrator's Guide for more information about this package and Streams |
This chapter contains the following topics:
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:
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:
To determine whether a set of tablespaces is self-contained, use the TRANSPORT_SET_CHECK
procedure in the Oracle supplied package DBMS_TTS
.
See Also:
Oracle Database Administrator's Guide for more information about self-contained tablespaces and tablespace sets |
This package contains the PL/SQL types listed in Table 99-1.
Contains the names of one or more directory objects. Each name must be a directory object created using the SQL statement CREATE
DIRECTORY
.
TYPE DIRECTORY_OBJECT_SET IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;
Contains the directory object associated with a directory and the name of the file in the directory.
TYPE FILE IS RECORD( directory_object VARCHAR2(32), file_name VARCHAR2(4000));
Contains one or more files.
TYPE FILE_SET IS TABLE OF FILE INDEX BY BINARY_INTEGER;
Contains the names of one or more tablespaces.
TYPE TABLESPACE_SET IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;
This procedure uses Data Pump to import a simple tablespace previously exported using the DBMS_STREAMS_TABLESPACE_ADM
package or Data Pump export.
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);
To run this procedure, a user must meet the following requirements:
IMP_FULL_DATABASE
roleREAD
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
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.
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);
To run this procedure, a user must meet the following requirements:
IMP_FULL_DATABASE
roleREAD
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, respectivelyWRITE
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
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
.
Clones a simple tablespace. The specified tablespace must be online.
Specifically, this procedure performs the following actions:
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:
EXP_FULL_DATABASE
roleDBA_TABLESPACES
and USER_TABLESPACES
.MANAGE
TABLESPACE
or ALTER
TABLESPACE
on a tablespace if the tablespace must be made read-onlyREAD
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.READ
and WRITE
privilege on the directory object that will contain the Data Pump export dump file, specified by the directory_object
parameterAfter 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.
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);
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:
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.
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);
To run this procedure, a user must meet the following requirements:
EXP_FULL_DATABASE
roleDBA_TABLESPACES
and USER_TABLESPACES
.MANAGE
TABLESPACE
or ALTER
TABLESPACE
on a tablespace if the tablespace must be made read-onlyREAD
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.READ
and WRITE
privilege on the directory object that will contain the Data Pump export dump file, specified by the dump_file
parameterWRITE
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
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.
Detaches a simple tablespace. The specified tablespace must be online.
Specifically, this procedure performs the following actions:
DBMS_STREAMS_TABLESPACE_ADM.DETACH_SIMPLE_TABLESPACE( tablespace_name IN VARCHAR2, directory_object OUT VARCHAR2, tablespace_file_name OUT VARCHAR2);
To run this procedure, a user must meet the following requirements:
EXP_FULL_DATABASE
roleDBA_TABLESPACES
and USER_TABLESPACES
.DROP
TABLESPACE
privilegeMANAGE
TABLESPACE
or ALTER
TABLESPACE
on a tablespace if the tablespace must be made read-onlyREAD
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.
See Also:
|
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:
To run this procedure, a user must meet the following requirements:
EXP_FULL_DATABASE
roleDBA_TABLESPACES
and USER_TABLESPACES
.DROP
TABLESPACE
privilegeMANAGE
TABLESPACE
or ALTER
TABLESPACE
on a tablespace if the tablespace must be made read-onlyREAD
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.READ
and WRITE
privilege on the directory object that will contain the Data Pump export dump file, specified by the dump_file
parameterWRITE
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.
See Also:
|
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);
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:
DBMS_FILE_TRANSFER
package to transfer the datafile for the tablespace and the log file for the Data Pump export to the current databaseIn 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.
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);
To run this procedure, a user must meet the following requirements on the remote database:
EXP_FULL_DATABASE
roleDBMS_STREAMS_TABLESPACE_ADM
packageDBA_TABLESPACES
and USER_TABLESPACES
.MANAGE
TABLESPACE
or ALTER
TABLESPACE
privilege on a tablespace if the tablespace must be made read-onlyREAD
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:
IMP_FULL_DATABASE
and EXECUTE_CATALOG_ROLE
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
WRITE
privilege on the directory object that will hold the datafile for the tablespace, specified by the directory_object
parameter
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:
DBMS_FILE_TRANSFER
package to transfer the datafiles for the tablespace set and the log file for the Data Pump export to the current databaseIn 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.
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);
To run this procedure, a user must meet the following requirements on the remote database:
EXP_FULL_DATABASE
roleDBMS_STREAMS_TABLESPACE_ADM
packageDBA_TABLESPACES
and USER_TABLESPACES
.MANAGE
TABLESPACE
or ALTER
TABLESPACE
privilege on a tablespace if the tablespace must be made read-onlyREAD
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:
IMP_FULL_DATABASE
and EXECUTE_CATALOG_ROLE
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
WRITE
privilege on the directory objects that will hold the datafiles for the tablespaces in the set, specified by the tablespace_directory_objects
parameter