Oracle Transportable Tablespaces
Version 18c

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.
General Information You can use the transportable tablespaces feature to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases. The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces were not required to be of the same block size as the target database's standard block size. Transporting tablespaces is particularly useful for:

Moving data from OLTP systems to data warehouse staging systems Updating data warehouses and data marts from staging systems Loading data marts from central data warehouses Archiving OLTP and data warehouse systems efficiently Data publishing to internal and external customers Performing Tablespace Point-in-Time Recovery (TSPITR)

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

LIMITATIONS
Be aware of the following limitations as you plan for transportable tablespace use:

The source and target database must be on the same hardware platform. For example, you can transport tablespaces between Sun Solaris Oracle databases, or you can transport tablespaces between Windows NT Oracle databases. However, you cannot transport a tablespace from a Sun Solaris Oracle database to a Windows database. The source and target database must use the same character set and national character set. You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. Transportable tablespaces do not support: Materialized views/replication Function-based indexes.
New in 12.2 TDE, in 12.2 supports ARIA, GOST, and SEED encryption algorithms.
  • ARIA (Academia, Research Institute, and Agency) for South Korea
  • GOST (GOsudarstvennyy STandart) for Russia
  • SEED (Korea Information Security Agency (KISA) for South Korea
This feature includes support for both encryption and hashing algorithms and is available for use with data-at-rest encryption. Certain countries require the use of their specific national and government standards for encryption. Deployment of TDE database encryption in these countries can proceed now that these national and government algorithms are supported.
Dependencies
CDB_DATAFILES DBA_DATA_FILES V$TRANSPORTABLE_PLATFORM
CDB_TABLESPACES DBA_TABLESPACES V$DB_TRANSPORTABLE_PLATFORM
 
Demo
TTS Demo conn sys@pdbdev as sysdba

set pagesize 35
col platform_name format a35

desc gv$transportable_platform

SELECT *
FROM v$transportable_platform
ORDER BY 1;

CREATE TABLESPACE tts
DATAFILE 'c:\temp\tts.dbf' size 10M
BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

SELECT tablespace_name, contents, status
FROM dba_tablespaces;

CREATE OR REPLACE DIRECTORY trans_dir AS 'c:\tts';

GRANT READ, WRITE ON DIRECTORY trans_dir TO public;

ALTER USER uwclass QUOTA UNLIMITED ON tts;

conn uwclass/uwclass@pdbdev

CREATE TABLE t1 (
pid   NUMBER(5),
lname VARCHAR2(20))
TABLESPACE uwdata;

ALTER TABLE t1
ADD CONSTRAINT pk_t1
PRIMARY KEY (pid)
USING INDEX
PCTFREE 0
TABLESPACE tts;

CREATE TABLE t2 (
pid   NUMBER(5),
lname VARCHAR2(20))
TABLESPACE tts;

ALTER TABLE t2
ADD CONSTRAINT pk_t2
PRIMARY KEY (pid)
USING INDEX
PCTFREE 0
TABLESPACE uwdata;


conn sys@pdbdev as sysdba

exec dbms_tts.transport_set_check('tts', TRUE);

SELECT *
FROM transport_set_violations;


conn uwclass/uwclass@pdbdev

ALTER TABLE t1 MOVE TABLESPACE tts;

ALTER INDEX pk_t2 REBUILD TABLESPACE tts;


conn sys@pdbdev as sysdba

exec dbms_tts.transport_set_check('tts', TRUE);

SELECT *
FROM transport_set_violations;

ALTER TABLESPACE tts READ ONLY;

-- in o/s window
expdp system/manager
DUMPFILE=tts.dmp
DIRECTORY=trans_dir
TRANSPORT_TABLESPACES=tts

-- copy the datafile tts.dbf to c:\tts

-- create the following datapump parameter file: save it as trans.par

DUMPFILE=tts.dmp
DIRECTORY=trans_dir
REMAP_SCHEMA=uwclass:harvard
TRANSPORT_DATAFILES='c:\temp\tts.dbf'


conn sys@pdbdev as sysdba

DROP TABLESPACE tts including contents and datafiles;

-- in o/s window

-- copy the datafile tts.dbf back to c:\temp


impdp system/manager parfile=c:\tts\trans.par


conn sys@pdbdev as sysdba

SELECT tablespace_name, contents, status
FROM dba_tablespaces
ORDER BY 1;

SELECT object_name, created
FROM dba_objects_ae
WHERE owner = 'HARVARD';

Related Topics
DataPump Executable
DBMS_CSX_ADMIN
DBMS_CSX_INT
DBMS_DATAPUMP
DBMS_EXTENDED_TTS_CHECK
DBMS_PLUGTS
DBMS_PLUGTSP
DBMS_TDB
DBMS_TTS
Export
Import
Tablespaces
What's New In 21c
What's New In 23c

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