Oracle DBMS_STREAMS_TABLESPACE_ADM
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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 (Updated to 12.2.0.1)
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_STANDARD    
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);
DECLARE
 dirobj   VARCHAR2(30);
 tbsfname VARCHAR2(30);
BEGIN
  dbms_streams_tablespace_adm.detach_simple_tablespace('UWCLASS', dirobj, tbsfname);
  dbms_output.put_line(dirobj);
  dbms_output.put_line(tbsfname);
END;
/
 
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
Built-in Functions
Built-in Packages
DBMS_FILE_GROUP
DBMS_STREAMS
DBMS_STREAMS_ADM
DBMS_STREAMS_ADM_UTIL_INT
DBMS_STREAMS_ADVISOR_ADM
DBMS_STREAMS_AUTH
DBMS_STREAMS_CDC_ADM
DBMS_STREAMS_CONTROL_ADM
DBMS_STREAMS_HANDLER_ADM
DBMS_STREAMS_MC
DBMS_STREAMS_MC_INV
DBMS_STREAMS_MESSAGING
DBMS_STREAMS_PUB_RPC
DBMS_STREAMS_RPC_INTERNAL
DBMS_STREAMS_TABLESPACE_ADM
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved