-- Drops the ODMRSYS Repostory and all internal User objects residing in the user accounts, that
-- were created by using Data Miner.
--
-- This would include data mining models, tables/views generated by the Create Table node,
-- hidden tables used to store results view via Data Miner, text specification objects used
-- to support text transformations.
-- Additionally, all the Data Miner workflows are dropped as part of dropping the ODMRSYS repository.
--
-- Successful completion of the script requires there to be no sessions active with the role of ODMRUSER.
-- Active ODMRUSER sessions can result in object locks that block the dropping of the repository.
-- The following parameter is available for detecting and managing such sessions.
-- 1. Action Flags:
--    R = report only, do not disconnect any sessions
--    D = disconnect only, displays disconnect  only
--    DR or RD = disconnect and report
-- Example:
-- Option 1: Conservative approach, which will require the DBA to manually review active sessions and 
-- determine how to end them. The drop process will stop if there are active sessions and a log
-- is produced listing the sessions to be manually reviewed and handled.
-- @dropRepositoryAndUserObjects.sql R  
-- Option 2: Force drop repository approach which will disconnect sessions and proceed with drop as 
-- long as all sessions succesfully disconnected.
-- @dropRepositoryAndUserObjects.sql DR  


SET SERVEROUTPUT ON;

WHENEVER OSERROR EXIT;
WHENEVER SQLERROR EXIT SQL.SQLCODE;

DEFINE D_SESSION_ACTION = &1

EXECUTE dbms_output.put_line('Start Data Miner Repository Drop Process. ' || systimestamp);

-- Bug16575250 display db, repository and wf version
@@displayVersion.sql

-- disconnects any odmr sessions
@@disconnectODMRSessions.sql &D_SESSION_ACTION T

-- revoke grants on ODMRUSER role and associated objects
@@usergrantshelper.sql REVOKE ODMRUSER

-- run a final check to insure that no sessions were missed (should not be any ODMRUSER roles granted)
@@disconnectODMRSessions.sql &D_SESSION_ACTION T

WHENEVER SQLERROR CONTINUE;
-- Since the state of the repository may be such that the operations below may result
-- in errors, the process will continue. The scripts should capture all errors and report them,
-- so this may be unnecessary.

--Drop Users Internal Tables/Views created by ODMr
@@dropUserTablesViews.sql D

--Drop Oracle Scheduler Chain objects
@@dropSchedulerObjects.sql D

--Drop public synonyms of ODMRSyS
@@dropPublicSynonyms.sql 

WHENEVER SQLERROR EXIT SQL.SQLCODE;
-- DropSchema
@@dropSchema.sql
--Drop ODMRSYS Repository
@@dropRepository.sql

--Check ODMRSYS
@@dbHasSchema.sql

-- Make sure the odmr.xsd is removed Bug16583626

WHENEVER SQLERROR EXIT SQL.SQLCODE;

EXECUTE dbms_output.put_line('End Data Miner Repository Drop Process. ' || systimestamp);

exit;