Oracle | DBMS_STREAMS_TABLESPACE

Oracle DBMS_STREAMS_TABLESPACE_ADM Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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 and DBMS_FILE_TRANSFER.

Streams was deprecated 03-Jan-2018 but this package still exists in 23ai but not installed by default

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_IVK	DBMS_SYSTEM
DBMS_FILE_TRANSFER	DBMS_STREAMS_ADM_UTL	DBMS_SYS_ERROR
DBMS_LOGREP_UTIL	DBMS_STREAMS_MT	DBMS_TTS
DBMS_PLUGTS	DBMS_STREAMS_RPC	KU$_JOBSTATUS1220
DBMS_STANDARD	DBMS_STREAMS_TBS_INT	KU$_STATUS
DBMS_STREAMS_ADM	DBMS_STREAMS_TBS_INT_INVOK	PLITBLM

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

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	CLONE_TABLESPACES	GET_TRACE_LEVEL
ATTACH_TABLESPACES	DETACH_SIMPLE_TABLESPACE	SET_TRACE_LEVEL
CLONE_SIMPLE_TABLESPACE	DETACH_TABLESPACES

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

TBD

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

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

TBD

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

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;

      /

GET_TRACE_LEVEL

Returns the trace level from the current session dbms_streams_tablespace_adm.get_trace_level RETURN NUMBER;

SELECT dbms_streams_tablespace_adm.get_trace_level;

      

      GET_TRACE_LEVEL

      ---------------

                    0

SET_TRACE_LEVEL

Set the trace level in the current session dbms_streams_tablespace_adm.set_trace_level(trace_level IN NUMBER);

SELECT dbms_streams_tablespace_adm.get_trace_level;

      

      GET_TRACE_LEVEL

      ---------------

                    0

      

      exec dbms_streams_tablespace_adm.set_trace_level(10046);

      

      SELECT dbms_streams_tablespace_adm.get_trace_level;

      

      GET_TRACE_LEVEL

      ---------------

                10046