Oracle | DBMS_STREAMS_TABLESPACE

Oracle DBMS_STREAMS_TABLESPACE_ADM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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_STANDARD	DBMS_SYSTEM
DBMS_FILE_GROUP	DBMS_STREAMS_ADM	DBMS_SYS_ERROR
DBMS_FILE_GROUP_UTL	DBMS_STREAMS_ADM_IVK	DBMS_TTS
DBMS_FILE_GROUP_UTL_INVOK	DBMS_STREAMS_ADM_UTL	DBMS_UTILITY
DBMS_FILE_TRANSFER	DBMS_STREAMS_MT	KU$_JOBSTATUS1220
DBMS_LOGREP_UTIL	DBMS_STREAMS_RPC	KU$_STATUS
DBMS_LOGREP_UTIL_INVOK	DBMS_STREAMS_TBS_INT	PLITBLM
DBMS_PLUGTS	DBMS_STREAMS_TBS_INT_INVOK

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

      FROM dual;

      

      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

      FROM dual;

      

      GET_TRACE_LEVEL

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

                    0

      

      exec dbms_streams_tablespace_adm.set_trace_level(10046);

      

      SELECT dbms_streams_tablespace_adm.get_trace_level

      FROM dual;

      

      GET_TRACE_LEVEL

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

                10046