-- Created by Kainan.yang 10/11/2010;
-- Purpose: For Bug 10188770. This script is used to Remove these temp snapshots used by Copy & Paste.
-- As the current mechanism will keep one snapshot per user session. 
-- This means that, over time, a considerable number of these 'temporary'
-- snapshots could build up.
-- So after a while(depends on there are how many sessions used Copy&paste), 
-- customer can use this script to clean the snapshots.
-- Note:
--    1. This script should be executed by OWBSYS
--    2. Before removing the temp snapshots for copy&paste, all other user
--       sessions should be closed. (Avoid deleting any using copy&paste snapshots.)
-- Usage: 1. Connect to Repository with OWBSYS 
--             e.g. SQLPLUS> connect OWBSYS/<OWBSYS Password>
--        2. Call this script:
--             e.g. SQLPLUS>@fix10188770.sql
             


declare
    retValue number;
    v_stmt_str VARCHAR2(20);
begin
    v_stmt_str := 'set role OWB_USER';
    execute immediate v_stmt_str;
    for b in (select workspaceid from cmpworkspace_r where workspaceid > 1) loop
        owb_workspace_manager.set_workspace(b.workspaceid);
        for c in (select snapshotname from snapshotlookup where snapshotid < 0) loop
            retValue := snapshot.domcmoperation(snapshot.DELETE_SNAPSHOT,c.snapshotname, retvalue);
        end loop;
    end loop;
    commit;
end;

/