Oracle DBMS_STREAMS_TABLESPACE_ADM
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Provides an API for copying tablespaces between databases and moving tablespaces from one database to another. In the process utilizes transportable tablespaces, Data Pump, DBMS_FILE_TRANSFER, and DBMS_FILE_GROUP
AUTHID CURRENT_USER
Data Types TYPE tablespace_set IS TABLE OF VARCHAR2(32) INDEX BY BINARY_INTEGER;

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

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

TYPE file_set IS TABLE OF file INDEX BY BINARY_INTEGER;
Dependencies
DBMS_DATAPUMP DBMS_STREAMS_ADM DBMS_SYSTEM
DBMS_FILE_GROUP DBMS_STREAMS_ADM_IVK DBMS_SYS_ERROR
DBMS_FILE_GROUP_UTL DBMS_STREAMS_ADM_UTL DBMS_TTS
DBMS_FILE_GROUP_UTL_INVOK DBMS_STREAMS_MT DBMS_UTILITY
DBMS_FILE_TRANSFER DBMS_STREAMS_RPC KU$_JOBSTATUS1210
DBMS_LOGREP_UTIL DBMS_STREAMS_TBS_INT KU$_STATUS
DBMS_PLUGTS DBMS_STREAMS_TBS_INT_INVOK PLITBLM
DBMS_REPCAT_UTL    
Documented Yes
Exceptions
Error Code Reason
ORA-06564 no_permissions_error
ORA-23609 directory_object_not_found
ORA-23610 internal_error
ORA-23611 not_simple_tablespace
ORA-23612 tablespace_not_found
ORA-23635 tablespaces_in_target_db
ORA-23636 invalid_tablespace_names
ORA-23657 file_converted_to_exists
First Available 10.1.0.4
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmssts.sql
Subprograms
 
ATTACH_SIMPLE_TABLESPACE
Uses Data Pump to import a simple tablespace previously exported using DBMS_STREAMS_TABLESPACE_ADM 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);
TBD
 
ATTACH_TABLESPACES
Uses Data Pump to import a self-contained tablespace set previously exported using DBMS_STREAMS_TABLESPACE_ADM, Data Pump export, or the RMAN TRANSPORT TABLESPACE command

Overload 1
dbms_streams_tablespace_adm.attach_tablespace(
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);
TBD
Overload 2 dbms_streams_tablespace_adm.attach_tablespace(
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);
DECLARE
  uw_tts_set dbms_streams_tablespace_adm.tablespace_set;
BEGIN
  dbms_streams_tablespace_adm.attach_tablespaces(
    file_group_name => 'SH.GRP_RPTREPOS',
    version_name => 'SALESFORCE_V2',
    datafiles_directory_object => 'RPTREPOS_TSV_DIR_V2',
    repository_db_link => 'TSPVERSIONS',
    tablespace_names => 'ORADATA');
END;
/
 
CLONE_SIMPLE_TABLESPACE
Clones a simple tablespace that can later be attached to a database 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);
TBD
 
CLONE_TABLESPACES
Clones a set of self-contained tablespaces that  can later be attached to a database

Overload 1
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);
TBD
Overload e 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);
DECLARE
 uw_tts_set dbms_streams_tablespace_adm.tablespace_set;
BEGIN
  uw_tts_set(1) := 'lmt_ref';
  uw_tts_set(2) := 'lmt_sfh';
  uw_tts_set(3) := 'lmt_xact';
  dbms_streams_tablespace_adm.clone_tablespaces(
    tablespace_names => uw_tts_set,
    tablespace_directory_object => 'new_df_location',
    file_group_name => 'sh.grp_rptrepos',
    version_name => 'salesforce_v1');
END;
/
 
DETACH_SIMPLE_TABLESPACE
Detaches a simple tablespace that can later be attached to a database dbms_streams_tablespace_adm.detach_simple_tablespace(
tablespace_name      IN  VARCHAR2,
directory_object     OUT VARCHAR2,
tablespace_file_name OUT VARCHAR2);
 
 
DETACH_TABLESPACES
Detaches a set of self-contained tablespaces that can later be attached to a database

Overload 1
dbms_streams_tablespace_adm.detach_tablespace(
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);
TBD
Overload 2 dbms_streams_tablespace_adm.detach_tablespace(
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);
DECLARE
 uw_tts_set dbms_streams_tablespace_adm.tablespace_set;
BEGIN
  uw_tts_set(1) := 'lmt_ref';
  uw_tts_set(2) := 'lmt_sfh';
  uw_tts_set(3) := 'lmt_xact';
  dbms_streams_tablespace_adm.detach_tablespaces(
    tablespace_names => salesforce_tts_set,
    file_group_name => 'sh.grp_rptrepos',
    version_name => 'salesforce_v3');
END;
/
 
PULL_SIMPLE_TABLESPACE
Copies a simple tablespace from a remote database and attaches it to the current database 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,
convert_directory_object IN VARCHAR2 DEFAULT NULL);
TBD
 
PULL_TABLESPACES
Copies a set of self-contained tablespaces from a remote database and attaches them to the current database 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,
convert_directory_object     IN     VARCHAR2 DEFAULT NULL);
TBD

Related Topics
DBMS_FILE_GROUP
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_AUTH
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved