Transportable tablespaces:
Oracle Transportable tablespaces are a useful tool
because all of the metadata for the tablespace is
self-contained,
making it easy to copy the tablespace from one
database to another database.
SQL> select * from V$TRANSPORTABLE_PLATFORM ;
SQL > select name, platform_id,platform_name from v$database;
ALTER TABLE hr.copy_emp ENABLE ROW MOVEMENT;
SELECT tp.endian_format
FROM v$transportable_platform tp, v$database d
WHERE tp.platform_name = d.platform_name;
Practical###
CONN / AS SYSDBA
CREATE TABLESPACE test_data DATAFILE '/u01/app/oracle/oradata/orcl/test_data01.dbf'
SIZE 10M AUTOEXTEND ON NEXT 1M;
CREATE USER test_user IDENTIFIED BY test_user DEFAULT TABLESPACE test_data
TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON test_data;
GRANT CREATE SESSION, CREATE TABLE TO test_user;
SQL > CONN test_user/test_user
CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);
INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level, 'Description for ' || level
FROM dual
CONNECT BY level <= 10000;
###################################################################################
###################################################################################
######### source database ##############
CONN / AS SYSDBA
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints =>
TRUE);
-- "PL/SQL procedure successfully completed."
SQL>
----- The TRANSPORT_SET_VIOLATIONS view is used to check for any violations.
SQL > SELECT * FROM transport_set_violations;
--Assuming no violations are produced
--we are ready to proceed by switching
--the tablespace to read only mode.
SQL> ALTER TABLESPACE test_data READ ONLY;
-- we export the tablespace metadata using the export (expdp or exp) utility.
CONN / AS SYSDBA
SQL> CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
SQL> GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
-- now export the tablespace metadata
expdp userid=system/password directory=temp_dir transport_tablespaces=test_data
dumpfile=test_data.dmp logfile=test_data_exp.log
--Copy the datafile to the appropriate location on the destination database server.
--Also copy the dump file to a suitable place on the destination database server.
-- You may use binary FTP or SCP to perform this copy.
now
source tablespace can now be switched back to read/write mode.
SQL > ALTER TABLESPACE test_data READ WRITE;
"Tablespace altered."
SQL>
###########################################################################
###########################################################################
########## Destination Database #############
--Create any users in the destination database
--that owned objects within the tablespace being transported,
--assuming they do not already exist.
CONN / AS SYSDBA
SQL > CREATE USER test_user IDENTIFIED BY test_user;
SQL > GRANT CREATE SESSION, CREATE TABLE TO test_user;
#######Now we import the metadata into the destination database
CONN / AS SYSDBA
SQL > CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
SQL > GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
###### We can now import the tablespace metadata.
impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp
logfile=test_data_imp.log
transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'
--Switch the new tablespace into read write mode.
SQL> ALTER TABLESPACE test_data READ WRITE;
--the tablespace is now available in the destination database.
SQL > SELECT tablespace_name, plugged_in, status
FROM dba_tablespaces
WHERE tablespace_name = 'TEST_DATA';
