| General |
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 are 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 an Windows NT Oracle 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. |
| |
| Demo |
conn / as
sysdba
set pagesize 25
col platform_name format a35
desc gv$transportable_platform
SELECT *
FROM v$transportable_platform
ORDER BY 1;
CREATE TABLESPACE tts
DATAFILE 'c: emp ts.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: ts';
GRANT READ, WRITE ON DIRECTORY trans_dir TO public;
ALTER USER uwclass QUOTA UNLIMITED ON tts;
conn uwclass/uwclass
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 / as sysdba
exec dbms_tts.transport_set_check('tts', TRUE);
SELECT *
FROM transport_set_violations;
conn uwclass/uwclass
ALTER TABLE t1 MOVE TABLESPACE tts;
ALTER INDEX pk_t2 REBUILD TABLESPACE tts;
conn / 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: ts
-- create the following datapump parameter file: save it as trans.par
DUMPFILE=tts.dmp
DIRECTORY=trans_dir
REMAP_SCHEMA=uwclass:scott
TRANSPORT_DATAFILES='c: emp ts.dbf'
conn / as sysdba
DROP TABLESPACE tts including contents and datafiles;
-- in o/s window
-- copy the datafile tts.dbf back to c: emp
impdp system/manager parfile=c: ts rans.par
conn / as sysdba
SELECT tablespace_name, contents, status
FROM dba_tablespaces
ORDER BY 1;
SELECT object_name, created
FROM dba_objects
WHERE owner = 'SCOTT'; |
|
