-- Transportable Tablespace Demo
-- Copyright (c) 2005, Caleb.com

-- Setup for the demo
start listener
start database FRED
drop user caleb cascade;
drop tablespaces trans1/2/3 and delete datafiles
create user caleb;
import caleb.dmp (see notes at end of file)

-- Examine tablespaces and objects
set linesize 100
SELECT tablespace_name, status
FROM dba_tablespaces
save ts
SELECT * FROM tab;
save tab

-- Examine constraints and indexes
SELECT table_name, constraint_name, constraint_type AS t, status
FROM user_constraints
ORDER BY table_name, constraint_type;
save cons

SELECT table_name, index_name, tablespace_name
FROM user_indexes
ORDER BY table_name;
save inds

-- Examine triggers
SELECT table_name, trigger_name
FROM user_triggers
ORDER BY table_name;
save trgs

-- Examine grants
SELECT table_name, privilege, grantee
FROM user_tab_privs_made
ORDER BY table_name;
save privs


-- Create the tablespaces that will be transported
CREATE TABLESPACE trans1
DATAFILE '/u01/app/oracle/oratrans/trans1.dbf' size 1M
AUTOEXTEND ON MAXSIZE 50M;
save ts1

CREATE TABLESPACE trans2
DATAFILE '/u01/app/oracle/oratrans/trans2.dbf' size 1M
AUTOEXTEND ON MAXSIZE 50M;
save ts2

CREATE TABLESPACE trans3
DATAFILE '/u01/app/oracle/oratrans/trans3.dbf' size 1M
AUTOEXTEND ON MAXSIZE 50M;
save ts3


-- Move some existing tables into the TTS set
ALTER TABLE employees MOVE TABLESPACE trans1;
save m1
ALTER TABLE departments MOVE TABLESPACE trans2;
save m2

-- Copy another existing table into the TTS set
-- (note there are dependancies between these tables)
CREATE TABLE locations2 TABLESPACE trans2 AS
SELECT * FROM locations;
save c1

-- Rebuild some of the indexes into the TTS set
ALTER INDEX emp_emp_id_pk REBUILD TABLESPACE trans3;
save i1
ALTER INDEX emp_name_ix REBUILD TABLESPACE trans3;
save i2

-- Create a new aggregrate table in the TTS set
CREATE TABLE empsales TABLESPACE trans1 AS
SELECT e.last_name, sum(o.order_total) AS total_orders
FROM employees e, oe.orders o
WHERE e.employee_id(+) = o.sales_rep_id
GROUP BY e.last_name;
save agg

-- Add a new record to prove it's not a trick
INSERT INTO empsales VALUES ('&name', 1000000.5);
save es1
COMMIT;

-- Review what we've created
COLUMN segment_name FORMAT a24
SELECT segment_name, segment_type, tablespace_name
FROM user_segments;
save segs

-- Verify no outside dependancies
EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('trans1,trans2,trans3', TRUE);
save tts_check
SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;
save tsv

-- Fix the dependancy by disabling constraint and explain
ALTER TABLE departments DISABLE CONSTRAINT dept_loc_fk;
save a1
ALTER INDEX dept_id_pk REBUILD TABLESPACE trans3;
save a2
ALTER TABLE employees DISABLE CONSTRAINT emp_email_uk;
save a3

-- Make tablespaces read only in preparation for transport
ALTER TABLESPACE trans1 READ ONLY;
ALTER TABLESPACE trans2 READ ONLY;
ALTER TABLESPACE trans3 READ ONLY;
save ro

-- Again verify no outside dependancies
@tts_check
@tsv

-- Ensure an Oracle Directory exists for the metadata export
CREATE OR REPLACE DIRECTORY trans_dir AS '/u01/app/oracle/oratrans';
save d1
GRANT READ, WRITE ON DIRECTORY trans_dir TO public;
save d2

-- Perform the metadata export
expdp caleb/small@FRED \
DUMPFILE=trans.dmp \
DIRECTORY=trans_dir \
TRANSPORT_TABLESPACES = trans1,trans2,trans3
-- or old way
exp TRANSPORT_TABLESPACE=y TABLESPACES=trans1 FILE=$ORACLE_BASE/oratrans/trans.dmp
sys@FRED as sysdba

-- Copy the tablespace data files and metadata export file to the target system
cd $ORACLE_BASE/oratrans
cp * /shrdat/oratrans

-- OR user RMAN to perform copy (required to convert)
rman target fred
CONVERT TABLESPACE trans1, trans2, trans3
TO PLATFORM 'Microsoft Windows IA (32-bit)'
FORMAT='$ORACLE_BASE/oratrans/%N_win.dbf';


-- At this point we are finished in the source DB.  
-- The tablespaces are still available to users in read-only mode.
-- We can make them read-write again after the copy or conversion is done.
ALTER TABLESPACE trans1 READ WRITE;
ALTER TABLESPACE trans2 READ WRITE;
ALTER TABLESPACE trans3 READ WRITE;
save rw

-- If an RMAN conversion was required due to endianness we could
-- do it either on the source system before transport, or on the
-- target system after transport.
COLUMN platform_name FORMAT a40
SELECT * FROM V$TRANSPORTABLE_PLATFORM
ORDER BY platform_id;
save ts_platform

-- Fire up the target system!

-- Setup required in both DBs
DROP USER bentley CASCADE;
DROP TABLESPACE trans1;
DROP TABLESPACE trans2;
DROP TABLESPACE trans3;
CREATE USER bentley IDENTIFIED BY stillmore
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
GRANT connect, resource, dba TO bentley;
cd d:\oradata\oratrans
del trans*.dbf
del trans.dmp


-- review tablespaces and objects
CONNECT bentley/stillmore@bing
@ts
@segs


-- Note: when creating all of the databases, the character sets must match:
-- NLS_CHARACTERSET = WE8ISO8859P1 (not the windows default!)
-- NLS_NCHAR_CHARACTERSET = AL16UTF16
SELECT * FROM v$nls_parameters
WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');
save charset


-- Ensure an Oracle Directory exists for the metadata import
CREATE OR REPLACE DIRECTORY trans_dir AS 'd:/oradata/oratrans';
save d1
GRANT READ, WRITE ON DIRECTORY trans_dir TO public;
save d2

-- Copy the files to the target directory

-- Import the metadata into the target system
impdp bentley/stillmore@bing parfile=trans.par
	DUMPFILE=trans.dmp 
	DIRECTORY=trans_dir 
	REMAP_SCHEMA=caleb:bentley
	TRANSPORT_DATAFILES='d:\oradata\oratrans\trans1.dbf',
                            'd:\oradata\oratrans\trans2.dbf',
                            'd:\oradata\oratrans\trans3.dbf' 
-- or the old way
imp transport_tablespace=y DATAFILEs='f:\oratrans\trans1.dbf' tablespaces=trans1 file='f:\oratrans\trans.dmp'

-- Query the data
SELECT * FROM tab;
@emps
@empsales

-- Show location of data (tablespace and DATAFILE)
@tabs

-- Show constraints and indexes are intact
@inds
@cons

-- Show triggers are intact
@trgs

-- Show grants
@privs

-- Demonstrate that we can 'plug in' the same tablespaces concurrently
-- into another database if all are read-only access

-- Import the metadata into a second target
CONNECT bentley/stillmore@bong
@d1
@d2
impdp ...
@tabs


SETUP notes:
------------
export hr schema and import into caleb
drop all but employees, departments and locations tables
compute statistics
GRANT SELECT on EMPLOYEES to moe, larry, joe;
CREATE VIEW view depsum AS
    SELECT d.department_name, sum(e.salary) cost
    FROM departments d, employees e
    WHERE d.department_id = e.department_id
    GROUP BY d.department_name;
CREATE OR REPLACE TRIGGER emp_sal
BEFORE insert OR update ON employees
FOR EACH ROW
BEGIN
  IF (:new.salary < 0) THEN
      raise_application_error(-20001,'Salary must must not be negative');
  END IF;
END;
/
export as caleb.dmp for re-use