/*
|| Oracle 10gR2 Transportable Tablespace Enhancements Listing 2
||
|| Demonstrates new Oracle 10gR2 transportable tablespace features, including:
|| - How to transport tablespaces without taking the tablespaces offline
|| - How to create a tablespace "jukebox" of versioned tablespaces
|| - How to plug a tablespace version into an auxiliary instance
|| - How to unplug a tablespace version from an auxiliary instance
|| - How to clone a tablespace version
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10gR2
|| Scheduling features and should be carefully proofread before executing it
|| against any existing Oracle database to insure that no potential damage
|| can occur.
||
*/

/* 
|| Listing 2.1: Preparations for tablespace transport operations:
|| - Create a new tablespace
|| - Create and populate a new table within the new tablespace
|| - Create an RMAN backup of the new tablespace
*/

-----
-- Create sales force hierarchy tablespace and objects
-----
DROP TABLESPACE lmt_sfh INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE lmt_sfh
    DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/lmt_sfh.dbf'
    SIZE 1M REUSE
    AUTOEXTEND ON
    MAXSIZE 2M
    SEGMENT SPACE MANAGEMENT AUTO;

DROP TABLE sh.sales_force_hierarchy PURGE;
CREATE TABLE sh.sales_force_hierarchy (
     geo_area       VARCHAR2(4)     NOT NULL    PRIMARY KEY
    ,sales_person   VARCHAR2(40)    NOT NULL
)
    TABLESPACE lmt_sfh
    PCTFREE 5
    STORAGE (INITIAL 128K);

-----
-- Populate the new table
-----
INSERT INTO sh.sales_force_hierarchy VALUES('CMPY', 'Zinger, Edward');
INSERT INTO sh.sales_force_hierarchy VALUES('EAST', 'Campana, Felicia');
INSERT INTO sh.sales_force_hierarchy VALUES('WEST', 'Mahoney, Peter');
INSERT INTO sh.sales_force_hierarchy VALUES('EA00', 'Page, Geraldine');
INSERT INTO sh.sales_force_hierarchy VALUES('SE00', 'Hummus, Giorgio');
INSERT INTO sh.sales_force_hierarchy VALUES('MW00', 'Capricola, Luigi');
INSERT INTO sh.sales_force_hierarchy VALUES('WE00', 'Whitehall, Lobelia');
INSERT INTO sh.sales_force_hierarchy VALUES('EA10', 'Oddbody, Clarence');
INSERT INTO sh.sales_force_hierarchy VALUES('EA20', 'Goldfinger, Auric');
INSERT INTO sh.sales_force_hierarchy VALUES('SE10', 'Germaine, Johnson');
INSERT INTO sh.sales_force_hierarchy VALUES('SE20', 'Paisan, Humberto');
INSERT INTO sh.sales_force_hierarchy VALUES('MW10', 'Austin, Stephanie');
INSERT INTO sh.sales_force_hierarchy VALUES('MW20', 'Gardenia, Vincenzo');
INSERT INTO sh.sales_force_hierarchy VALUES('WE10', 'Cruz, Euphegenia');
INSERT INTO sh.sales_force_hierarchy VALUES('WE20', 'Anthemum, Christopher');
COMMIT;

-----
-- RMAN script to back up the newly-created tablespace
-----
RUN {
    BACKUP TABLESPACE lmt_sfh;
}

/* 
|| Listing 2.2: Transporting a tablespace from RMAN backup sets
*/

-----
-- RMAN script to create the transportable tablespace from RMAN backups.
-- Note that a full database backup was taken before running this script!
-----
RUN {
    TRANSPORT TABLESPACE lmt_sfh
    TABLESPACE DESTINATION '/u02/ttxports'
    AUXILIARY DESTINATION '/u02/ttxports';
}

/* 
|| Listing 2.3: Creating a repository for tablespace versions:
|| 1.) Create directory object that references this database's datafiles
|| 2.) Create directory objects for storage of tablespace versions
|| 3.) Create file group for storage of tablespace datafiles and metadata
*/

-----
-- Create directory object (DBFILES_ORCL102) to reflect where the datafiles 
-- for all the tablespaces in this database reside
-----
DROP DIRECTORY dbfiles_orcl102;
CREATE DIRECTORY dbfiles_orcl102 
    AS 'C:\oracle\oradata\orcl102';
GRANT READ,WRITE ON DIRECTORY dbfiles_orcl102 TO PUBLIC;

-----
-- Create directory objects for storage of tablespace versions
-----
DROP DIRECTORY rptrepos_tsv_dir_v1;
CREATE DIRECTORY rptrepos_tsv_dir_v1 
    AS 'C:\_dba\rptrepostsv\v1';
GRANT READ,WRITE ON DIRECTORY rptrepos_tsv_dir_v1 TO PUBLIC;
DROP DIRECTORY rptrepos_tsv_dir_v2;
CREATE DIRECTORY rptrepos_tsv_dir_v2 
    AS 'C:\_dba\rptrepostsv\v2';
GRANT READ,WRITE ON DIRECTORY rptrepos_tsv_dir_v2 TO PUBLIC;
DROP DIRECTORY rptrepos_tsv_dir_v3;
CREATE DIRECTORY rptrepos_tsv_dir_v3 
    AS 'C:\_dba\rptrepostsv\v3';
GRANT READ,WRITE ON DIRECTORY rptrepos_tsv_dir_v3 TO PUBLIC;

-----
-- Create file group for storage of tablespace datafiles and metadata
-- STATUS: Success
-----
BEGIN
    DBMS_FILE_GROUP.CREATE_FILE_GROUP(
         file_group_name => 'sh.grp_rptrepos'
        ,keep_files => 'Y'
        ,min_versions => 3
        ,max_versions => DBMS_FILE_GROUP.INFINITE
        ,retention_days => DBMS_FILE_GROUP.INFINITE
        ,default_directory => NULL
        ,comments => 'Reporting Repostory Tablespace Versions'
    );
END;
/

/* 
|| Listing 2.4: Add the first version of the tablespace set to the repository
*/

DECLARE
    salesforce_tts_set  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
    salesforce_tts_set(1) := 'lmt_ref';
    salesforce_tts_set(2) := 'lmt_sfh';
    salesforce_tts_set(3) := 'lmt_xact';
    DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
         tablespace_names => salesforce_tts_set
        ,tablespace_directory_object => 'rptrepos_tsv_dir_v1'
        ,file_group_name => 'sh.grp_rptrepos'
        ,version_name => 'salesforce_v1'
    );
END;
/

/* 
|| Listing 2.5: Add another version of the tablespace set to the repository
*/

-----
-- Reload the aggregate sales data "from scratch"
-----

TRUNCATE TABLE SH.AGGR_SALES;

INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','USG',167372,1812237.067);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','USG',68557,742307.77);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','USG',98815,1069929.24);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','USG',17682,191453.623);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','USG',50875,550854.15);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','USG',45030,487566.83);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','USG',53785,582362.47);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','USG',14984,162240.76);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','USG',2698,29212.87);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','USG',11002,119125.26);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','USG',39873,431728.90);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','USG',35018,379160.90);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','USG',10012,108405.93);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','USG',31698,343213.27);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','USG',22087,239149.20);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','RTL',353114,3823377.15);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','RTL',180517,1954565.87);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','RTL',172597,1868811.28);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','RTL',83943,908901.23);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','RTL',96574,1045664.64);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','RTL',77589,840102.66);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','RTL',95008,1028708.62);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','RTL',31569,341816.50);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','RTL',52374,567084.72);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','RTL',52987,573722.04);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','RTL',43587,471942.60);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','RTL',32568,352633.28);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','RTL',45021,487469.38);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','RTL',58987,638687.64);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','RTL',36021,390020.98);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','WHL',1741465,18855886.43);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','WHL',634907,6874519.03);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','WHL',1106558,11981367.40);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','WHL',381359,4129202.71);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','WHL',253548,2745316.33);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','WHL',432779,4685957.90);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','WHL',673779,7295409.50);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','WHL',189874,2055879.72);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','WHL',191485,2073322.99);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','WHL',120568,1305462.08);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','WHL',132980,1439854.25);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','WHL',210903,2283573.32);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','WHL',221876,2402384.58);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','WHL',310698,3364113.67);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','WHL',363081,3931295.84);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','SLG',112513,1218245.76);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','SLG',42335,458386.45);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','SLG',70178,759859.31);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','SLG',21130,228787.19);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','SLG',21205,229599.26);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','SLG',31122,336976.57);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','SLG',39056,422882.75);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','SLG',10032,108622.48);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','SLG',11098,120164.71);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','SLG',11204,121312.43);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','SLG',10001,108286.83);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','SLG',15035,162792.97);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','SLG',16087,174183.60);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','SLG',22041,238651.13);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','SLG',17015,184231.61);
INSERT INTO SH.AGGR_SALES VALUES('CPY','CMPY','ITL',10449,113137.59);
INSERT INTO SH.AGGR_SALES VALUES('RGN','EAST','ITL',4762,51561.03);
INSERT INTO SH.AGGR_SALES VALUES('RGN','WEST','ITL',5687,61576.56);
INSERT INTO SH.AGGR_SALES VALUES('DST','EA00','ITL',2889,31280.94);
INSERT INTO SH.AGGR_SALES VALUES('DST','SE00','ITL',1873,20280.09);
INSERT INTO SH.AGGR_SALES VALUES('DST','MW00','ITL',2203,23853.20);
INSERT INTO SH.AGGR_SALES VALUES('DST','WE00','ITL',3484,37723.36);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA01','ITL',2100,22737.96);
INSERT INTO SH.AGGR_SALES VALUES('TER','EA02','ITL',789,8542.98);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE01','ITL',568,6150.08);
INSERT INTO SH.AGGR_SALES VALUES('TER','SE02','ITL',1305,14130.02);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW01','ITL',1195,12938.98);
INSERT INTO SH.AGGR_SALES VALUES('TER','MW02','ITL',1008,10914.22);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE01','ITL',3100,33565.56);
INSERT INTO SH.AGGR_SALES VALUES('TER','WE02','ITL',384,4157.80);

COMMIT;

-----
-- Apply selected updates to the Sales Force Hierarchy
-----
UPDATE sh.sales_force_hierarchy 
   SET sales_person = 'Goldfinger, Auric'  
 WHERE geo_area = 'CMPY';
UPDATE sh.sales_force_hierarchy
   SET sales_person = 'DeSalvo, June'      
 WHERE geo_area = 'EA20';
UPDATE sh.sales_force_hierarchy
   SET sales_person = 'Whitehall, Lobelia' 
 WHERE geo_area = 'WEST';
UPDATE sh.sales_force_hierarchy
   SET sales_person = 'Umberlie, Latricia' 
 WHERE geo_area = 'WE00';
UPDATE sh.sales_force_hierarchy
   SET sales_person = 'Paisan, Humberto'   
 WHERE geo_area = 'SE00';
UPDATE sh.sales_force_hierarchy
   SET sales_person = 'Menendez, Juan'     
 WHERE geo_area = 'SE20';
UPDATE sh.sales_force_hierarchy
   SET sales_person = 'Gardenia, Vincenzo' 
 WHERE geo_area = 'MW00';
UPDATE sh.sales_force_hierarchy
   SET sales_person = 'DiGiovanni, Anthony'
 WHERE geo_area = 'MW20';

COMMIT;

-----
-- Add the second version of the tablespace set to the repository
-----
DECLARE
    salesforce_tts_set  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
    salesforce_tts_set(1) := 'lmt_ref';
    salesforce_tts_set(2) := 'lmt_sfh';
    salesforce_tts_set(3) := 'lmt_xact';
    DBMS_STREAMS_TABLESPACE_ADM.CLONE_TABLESPACES(
         tablespace_names => salesforce_tts_set
        ,tablespace_directory_object => 'rptrepos_tsv_dir_v2'
        ,file_group_name => 'sh.grp_rptrepos'
        ,version_name => 'salesforce_v2'
    );
END;
/

/* 
|| Listing 2.6: Retrieving a tablespace version from the repository
*/

-----
-- Create database link to allow connection to tablespace version 
-- repository. Note that a network address entry for the tablespace 
-- repository database has already been added to this server's TNSNAMES.ORA 
-----
DROP DATABASE LINK tspversions;
CREATE DATABASE LINK tspversions
    CONNECT TO system
    IDENTIFIED BY oracle
    USING 'netlink.orcl102.xp';
;

-----
-- Create directory objects for storage of tablespace versions
-----
DROP DIRECTORY rptrepos_tsv_dir_v1;
CREATE DIRECTORY rptrepos_tsv_dir_v1
    AS 'G:\tspversions\v1';
GRANT READ,WRITE ON DIRECTORY rptrepos_tsv_dir_v1 TO PUBLIC;
DROP DIRECTORY rptrepos_tsv_dir_v2;
CREATE DIRECTORY rptrepos_tsv_dir_v2
    AS 'G:\tspversions\v2';
GRANT READ,WRITE ON DIRECTORY rptrepos_tsv_dir_v2 TO PUBLIC;

-----
-- Transfer the files from the tablespace repository server to the 
-- local server using DBMS_FILE_TRANSFER
-----
BEGIN
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v1'
        ,source_file_name => 'LMT_REF01.DBF'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v1'
        ,destination_file_name => 'LMT_REF01.DBF'
    );
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v1'
        ,source_file_name => 'LMT_SFH.DBF'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v1'
        ,destination_file_name => 'LMT_SFH.DBF'
    );
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v1'
        ,source_file_name => 'LMT_XACT01.DBF'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v1'
        ,destination_file_name => 'LMT_XACT01.DBF'
    );
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v1'
        ,source_file_name => 'EXPDAT18.DMP'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v1'
        ,destination_file_name => 'EXPDAT18.DMP'
    );
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v2'
        ,source_file_name => 'LMT_REF01.DBF'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v2'
        ,destination_file_name => 'LMT_REF01.DBF'
    );
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v2'
        ,source_file_name => 'LMT_SFH.DBF'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v2'
        ,destination_file_name => 'LMT_SFH.DBF'
    );
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v2'
        ,source_file_name => 'LMT_XACT01.DBF'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v2'
        ,destination_file_name => 'LMT_XACT01.DBF'
    );
    DBMS_FILE_TRANSFER.GET_FILE(
         source_directory_object => 'rptrepos_tsv_dir_v2'
        ,source_file_name => 'EXPDAT20.DMP'
        ,source_database => 'tspversions'
        ,destination_directory_object => 'rptrepos_tsv_dir_v2'
        ,destination_file_name => 'EXPDAT20.DMP'
    );
END;
/

-----
-- "Plug in" the first version of the tablespace set to the repository
-----
DECLARE
    salesforce_tts_set  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
    DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
         file_group_name => 'SH.GRP_RPTREPOS'
        ,version_name => 'SALESFORCE_V1'
        ,datafiles_directory_object => 'RPTREPOS_TSV_DIR_V1'
        ,repository_db_link => 'TSPVERSIONS'
        ,tablespace_names => salesforce_tts_set
    );
END;
/

-----
-- Run a report to confirm the switchover
-----
TTITLE 'Total Units and Sales Revenue'
-- Confirm total units and revenue amounts
SELECT COUNT(units), SUM(units), SUM(revenue)
  FROM sh.aggr_sales
;

TTITLE 'Current Sales Force Hierarchy'
SELECT *
  FROM sh.sales_force_hierarchy
;
TTITLE OFF


/* 
|| Listing 2.7: Switching to a different tablespace version 
||              retrieved from the tablespace version repository
*/

-----
-- Now drop the tablespaces that were plugged in as Version 1, and then  
-- "plug in" the second version of the tablespace set to the repository
-----
DROP TABLESPACE lmt_xact INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE lmt_ref  INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE lmt_sfh  INCLUDING CONTENTS AND DATAFILES;

DECLARE
    salesforce_tts_set  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
    DBMS_STREAMS_TABLESPACE_ADM.ATTACH_TABLESPACES(
         file_group_name => 'SH.GRP_RPTREPOS'
        ,version_name => 'SALESFORCE_V2'
        ,datafiles_directory_object => 'RPTREPOS_TSV_DIR_V2'
        ,repository_db_link => 'TSPVERSIONS'
        ,tablespace_names => salesforce_tts_set
    );
END;
/

-----
-- Detaching a tablespace set from the database. This will remove the tablespaces
-- and their datafiles from the database, but they will be saved in the tablespace
-- repository
-----

DECLARE
    salesforce_tts_set  DBMS_STREAMS_TABLESPACE_ADM.TABLESPACE_SET;
BEGIN
    salesforce_tts_set(1) := 'lmt_ref';
    salesforce_tts_set(2) := 'lmt_sfh';
    salesforce_tts_set(3) := 'lmt_xact';
    DBMS_STREAMS_TABLESPACE_ADM.DETACH_TABLESPACES(
         tablespace_names => salesforce_tts_set
        ,file_group_name => 'sh.grp_rptrepos'
        ,version_name => 'salesforce_v3'
    );
END;
/

/* 
|| Listing 2.8: Maintaining the tablespace version repository
*/

-----
-- Alter properties of an existing File Group
-----
BEGIN
    DBMS_FILE_GROUP.ALTER_FILE_GROUP(
         file_group_name => 'sh.grp_rptrepos'
        ,max_versions => 10
        ,retention_days => 30
        ,new_comments => 'Tablespace Repository (10 versions, kept 30 days)'
    );
END;
/

-----
-- Alter properties of an existing version in an existing File Group
-----
BEGIN
    DBMS_FILE_GROUP.ALTER_VERSION(
         file_group_name => 'sh.grp_rptrepos'
        ,version_name => 'salesforce_v3'
        ,new_version_name => 'salesforce_v0'
        ,new_comments => 'Sales Force Tablespace Version 0 (was 3)'
    );
END;
/

-----
-- Alter properties of an existing file in an existing version of 
-- an existing File Group
-----
BEGIN
    DBMS_FILE_GROUP.ALTER_FILE(
         file_group_name => 'sh.grp_rptrepos'
        ,file_name => 'TSP_SFH.DBF'
        ,version_name => 'salesforce_v3'
        ,new_file_name => 'TSP_SFH01.DBF'
        ,new_comments => 'Datafile for Tablespace TSF_SFH'
    );
END;
/

-----
-- Purge any existing File Groups that have exceeded their retention policy
-----
BEGIN
    DBMS_FILE_GROUP.PURGE_FILE_GROUP (
         file_group_name => 'sh.grp_rptrepos'
    );
END;
/

-----
-- Remove an existing file from a specific versioned set within an 
-- existing File Group, and delete it permanently
-----
BEGIN
    DBMS_FILE_GROUP.REMOVE_FILE(
         file_group_name => 'sh.grp_rptrepos'
        ,file_name => 'LMT_SFH.DBF'
        ,version_name => 'salesforce_v1'
        ,keep_file => 'N'
    );
END;
/

-----
-- Drop an existing tablespace version from a File Group, but retain 
-- any of its files on disk
-----
BEGIN
    DBMS_FILE_GROUP.DROP_VERSION (
         file_group_name => 'sh.grp_rptrepos'
        ,version_name => 'salesforce_v1'
        ,keep_files => 'Y'
    );
END;
/

-----
-- Drop an entire existing File Group, including all of its files
-----
BEGIN
    DBMS_FILE_GROUP.DROP_FILE_GROUP (
         file_group_name => 'sh.grp_rptrepos'
        ,keep_files => 'N'
    );
END;
/

/* 
|| Listing 2.9: Obtaining metadata about tablespace versions
*/

SET PAGESIZE 50
SET LINESIZE 110

-- Show high-level metadata about file groups in the repository
TTITLE 'Tablespace File Groups'
COL file_group_owner    FORMAT A08          HEADING 'File|Group|Owner'
COL file_group_name     FORMAT A16          HEADING 'File Group Name'
COL keep_files          FORMAT A04          HEADING 'Keep|File'
COL min_versions        FORMAT 99999        HEADING 'Min|Vsns'    
COL max_versions        FORMAT 99999999999  HEADING 'Max|Vsns'    
COL retention_days      FORMAT 99999999999  HEADING 'Days|Rtnd'
COL default_directory   FORMAT A16          HEADING 'Default|Directory' WRAP
COL comments            FORMAT A24          HEADING 'Comments' WRAP
SELECT 
     file_group_owner
    ,file_group_name
    ,keep_files
    ,min_versions
    ,max_versions
    ,retention_days
    ,default_directory
    ,comments
  FROM dba_file_groups
 ORDER BY 
     file_group_owner
    ,file_group_name
;
TTITLE OFF

-- Show high-level metadata about versions stored in each file group
TTITLE 'Tablespace Versions In File Groups'
COL file_group_owner    FORMAT A08      HEADING 'File|Group|Owner'
COL file_group_name     FORMAT A16      HEADING 'File Group Name'
COL version             FORMAT 999999   HEADING 'Vrsn #'
COL version_name        FORMAT A16      HEADING 'Version Name' WRAP
COL default_directory   FORMAT A16      HEADING 'Default|Directory' WRAP
COL comments            FORMAT A24      HEADING 'Comments' WRAP
SELECT 
     file_group_owner
    ,file_group_name
    ,version
    ,version_name
    ,default_directory
    ,comments
  FROM dba_file_group_versions
 ORDER BY 
     file_group_owner
    ,file_group_name
    ,version
;
TTITLE OFF

-- Show export-related information for each tablespace version
TTITLE 'Tablespace Versions Export Information'
COL file_group_owner    FORMAT A08      HEADING 'File|Group|Owner'
COL file_group_name     FORMAT A16      HEADING 'File Group Name'
COL version             FORMAT 999999   HEADING 'Vrsn #'
COL version_name        FORMAT A16      HEADING 'Version Name'
COL export_version      FORMAT A10      HEADING 'Exp|Vsn#'
COL export_scn          FORMAT 99999999 HEADING 'Export|SCN#'
COL platform_name       FORMAT A24      HEADING 'Platform|Name' WRAP
COL source_global_name  FORMAT A48      HEADING 'Source Global Name' WRAP
SELECT
     file_group_owner
    ,file_group_name
    ,version
    ,version_name
    ,export_version
    ,platform_name
    ,export_scn
    ,source_global_name
  FROM dba_file_group_export_info
 ORDER BY 
     file_group_owner
    ,file_group_name
    ,version
;
TTITLE OFF

-- Show file sets for each versioned file group
TTITLE 'File Sets In Versioned File Groups'
COL file_group_owner    FORMAT A08      HEADING 'File|Group|Owner'
COL file_group_name     FORMAT A16      HEADING 'File Group Name'
COL version             FORMAT 999999   HEADING 'Vrsn #'
COL version_name        FORMAT A16      HEADING 'Version Name'
COL file_directory      FORMAT A24      HEADING 'Default|Directory' WRAP
COL file_type           FORMAT A12      HEADING 'File|Type' WRAP
COL file_size           FORMAT 99999999 HEADING 'File Size'
COL file_block_size     FORMAT 99999    HEADING 'File|Block|Size'
COL comments            FORMAT A24      HEADING 'Comments' WRAP
SELECT
     file_group_owner
    ,file_group_name
    ,version
    ,version_name
    ,file_directory
    ,file_type
    ,file_size
    ,file_block_size    
    ,comments
  FROM dba_file_group_files
 ORDER BY 
     file_group_owner
    ,file_group_name
    ,version
    ,file_directory
;
TTITLE OFF

-- Show information about the tablespaces present in the file set 
-- for versions that contain a DataPump dump file
TTITLE 'Tablespaces Present In File Group Set'
COL file_group_owner    FORMAT A08      HEADING 'File|Group|Owner'
COL file_group_name     FORMAT A16      HEADING 'File Group Name'
COL version             FORMAT 999999   HEADING 'Vrsn #'
COL version_name        FORMAT A16      HEADING 'Version Name'
COL tablespace_name     FORMAT A24      HEADING 'Tablespace Name'
SELECT
     file_group_owner
    ,file_group_name
    ,version_name
    ,version
    ,tablespace_name
  FROM dba_file_group_tablespaces
 ORDER BY 
     file_group_owner
    ,file_group_name
    ,version
    ,tablespace_name
;
TTITLE OFF

-- Show information about the tables that could be imported using 
-- the file set
TTITLE 'Tables Contained In File Group Set'
COL file_group_owner    FORMAT A08      HEADING 'File|Group|Owner'
COL file_group_name     FORMAT A16      HEADING 'File Group Name'
COL version             FORMAT 999999   HEADING 'Vrsn #'
COL version_name        FORMAT A16      HEADING 'Version Name'
COL tablespace_name     FORMAT A12      HEADING 'TSP Name'
COL owner               FORMAT A12      HEADING 'Table|Owner'
COL table_name          FORMAT A24      HEADING 'Table Name'
COL scn                 FORMAT 99999999 HEADING 'SCN#'
SELECT 
     file_group_owner
    ,file_group_name
    ,version
    ,version_name
    ,tablespace_name
    ,owner
    ,table_name
    ,scn
  FROM dba_file_group_tables
 ORDER BY 
     file_group_owner
    ,file_group_name
    ,version
    ,tablespace_name
    ,owner
    ,table_name
;
TTITLE OFF