rem -----------------------------------------------------------------------
rem Filename:   ttstest.sql
rem Purpose:    Demonstrate Oracle 8i transportable tablespaces
rem Notes:      This example script will create a tablespace, export it's
rem             definitions, drop it and re-import it.
rem Date:       12-Feb-2000
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

def TABLESPACE_NAME=test_ts
def DATAFILE_NAME=/tmp/test_ts_file1.dbf

-- Create a test tablespace and table
create tablespace &&TABLESPACE_NAME
        datafile '&&DATAFILE_NAME' size 10M reuse
        extent management local;
create table test (a date) tablespace &&TABLESPACE_NAME;
insert into test values (sysdate);

-- Test if the tablespace can be transported
exec sys.dbms_tts.transport_set_check('&&TABLESPACE_NAME', TRUE);
prompt Transport set violations:
select * from sys.transport_set_violations;

alter tablespace &&TABLESPACE_NAME read only;

! exp system/manager transport_tablespace=yes tablespaces=&&TABLESPACE_NAME triggers=no constraints=no

drop tablespace &&TABLESPACE_NAME including contents;
-- This should now give an error
select count(*) from test;

! imp system/manager transport_tablespace=yes tablespaces=&&TABLESPACE_NAME datafiles=\('&&DATAFILE_NAME'\)

alter tablespace &&TABLESPACE_NAME read write;

-- The table should now be back
select count(*) from test;

-- Cleanup
drop table test;
drop tablespace &&TABLESPACE_NAME;