SPOOL REMOVE_SIM.LOG
SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 10000;
SET SQLBLANKLINES ON

@@..\CONFIG.SQL

CONNECT &&DBA_USER/&&DBA_PASSWORD@&&DATABASE

PROMPT DROPPING SIM SCHEMA
DROP USER &&SIM_APP_SCHEMA CASCADE;

PROMPT DROPPING ROLES 
DROP ROLE &&SIM_ROLE;

PROMPT DROPPING SIM SYNONYMS FROM STORUSER
DROP SYNONYM &&STORUSER_USER..SIM_ACTIVITY_CPORG;
DROP SYNONYM &&STORUSER_USER..SIM_ACTIVITY_CPORG_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_ACTIVITY_LOC_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_ACTIVITY_PERSONNEL;
DROP SYNONYM &&STORUSER_USER..SIM_ACTIVITY_PERSONNEL_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_ACTIVITY_PROJECTS;
DROP SYNONYM &&STORUSER_USER..SIM_ACTIVITY_PROJECTS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_ACTUAL_ACTIVITY_LOC;
DROP SYNONYM &&STORUSER_USER..SIM_BIO_RESULTS_GRP;
DROP SYNONYM &&STORUSER_USER..SIM_BIO_RESULTS_GRP_INDIVIDUAL;
DROP SYNONYM &&STORUSER_USER..SIM_BRGI_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_BRG_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_CHARACTERISTIC_USGS_CODES;
DROP SYNONYM &&STORUSER_USER..SIM_COMMIT;
DROP SYNONYM &&STORUSER_USER..SIM_CONFIG;
DROP SYNONYM &&STORUSER_USER..SIM_ESTUARY_LOC;
DROP SYNONYM &&STORUSER_USER..SIM_ESTUARY_LOC_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_DETAILS;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_DETAILS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_DETAILS_TYPE;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_LINES;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_LINES_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_EVENT_LOG_TYPE;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_LOCATION_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_LOG;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_PROJECT_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_RESULT_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_STATION_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_STORET_IDS;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_STORET_IDS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_EXPORT_WELL_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_FIELD_ACTIVITIES;
DROP SYNONYM &&STORUSER_USER..SIM_FIELD_ACTIVITIES_VW;
DROP SYNONYM &&STORUSER_USER..SIM_FIELD_ACTIVITIES_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_FIELD_ACTIVITY_PARENT;
DROP SYNONYM &&STORUSER_USER..SIM_FIELD_ACTIVITY_PARENT_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_GREAT_LAKE;
DROP SYNONYM &&STORUSER_USER..SIM_GREAT_LAKE_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_HELP;
DROP SYNONYM &&STORUSER_USER..SIM_HLP_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_LOCATIONS_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_LOG;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_LOG_VW;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_LOG_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_PROJECT_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_RESULT_CHECKS;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_RESULT_EXTRAS;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_RESULT_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_STATION_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_IMPORT_WELLS_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_ALLOW_VALUES;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_CFG;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_CFG_VW;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_CFG_DETS;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_CFG_DETS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_CFG_INSTRS;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_CFG_INSTRS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_CFG_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_COLS;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_DEFS;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_FMTS;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_FMT_CHOICES;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_INSTRS;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_INSTR_CHOICES;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_LINES;
DROP SYNONYM &&STORUSER_USER..SIM_IMP_LINES_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_INTERFACE_EXTRAS_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_LIB_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_MANAGE_CONFIG_PKG;
DROP SYNONYM &&STORUSER_USER..SIM_OCEAN;
DROP SYNONYM &&STORUSER_USER..SIM_OCEAN_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_PROJECTS;
DROP SYNONYM &&STORUSER_USER..SIM_PROJECTS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_RESULTS;
DROP SYNONYM &&STORUSER_USER..SIM_RESULTS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_RESULT_CLASS_INDICATOR;
DROP SYNONYM &&STORUSER_USER..SIM_RESULT_CLASS_INDICATOR_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_RESULT_LAB_REMARKS;
DROP SYNONYM &&STORUSER_USER..SIM_SAMPLE;
DROP SYNONYM &&STORUSER_USER..SIM_SAMPLE_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_SESSION_VARS;
DROP SYNONYM &&STORUSER_USER..SIM_SRLR_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_STATIONS;
DROP SYNONYM &&STORUSER_USER..SIM_STATIONS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_STATION_LOCATIONS;
DROP SYNONYM &&STORUSER_USER..SIM_STATION_LOCATIONS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_STATION_SORT;
DROP SYNONYM &&STORUSER_USER..SIM_STATION_VISITS;
DROP SYNONYM &&STORUSER_USER..SIM_STATION_VISITS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_STATION_WELLS;
DROP SYNONYM &&STORUSER_USER..SIM_STATION_WELLS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_TRANSLATIONS;
DROP SYNONYM &&STORUSER_USER..SIM_TRANSLATIONS_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_TRANSLATIONS_VW;
DROP SYNONYM &&STORUSER_USER..SIM_TRAP_NET;
DROP SYNONYM &&STORUSER_USER..SIM_TRAP_NET_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_TRAWL_OPS_DET;
DROP SYNONYM &&STORUSER_USER..SIM_TRAWL_OPS_DET_SEQ;
DROP SYNONYM &&STORUSER_USER..SIM_TRIPS;
DROP SYNONYM &&STORUSER_USER..SIM_TRIPS_SEQ;

BEGIN
	IF UPPER('&&CREATE_SIM_TABLESPACES')='YES' THEN
		DBMS_OUTPUT.PUT_LINE('DROPPING TABLESPACES');
		BEGIN
			EXECUTE IMMEDIATE 'DROP TABLESPACE &&DATA_TABLESPACE';
		EXCEPTION 
			WHEN OTHERS THEN 
				DBMS_OUTPUT.PUT_LINE(SQLERRM);
		END;
		BEGIN
 			EXECUTE IMMEDIATE 'DROP TABLESPACE &&INDEX_TABLESPACE';
		EXCEPTION 
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE(SQLERRM);
		END;
 	END IF;
END;
/

SPOOL OFF
SPOOL DELETE.BAT
BEGIN
	DBMS_OUTPUT.PUT_LINE('ECHO Ignore any errors that follow (they''re not a problem)...');
	IF UPPER('&&CREATE_SIM_TABLESPACES')='YES' THEN
		DBMS_OUTPUT.PUT_LINE('DEL "&&INDEX_TABLESPACE_DATA_FILE"');
		DBMS_OUTPUT.PUT_LINE('DEL "&&DATA_TABLESPACE_DATA_FILE"');
 	END IF;
END;
/

SPOOL OFF
SPOOL DONE_REMOVE_SIM.LOG
PROMPT DONE REMOVING SIM 
SPOOL OFF
EXIT