Rem 
Rem File Name : ckclean.sql
Rem 
Rem This scripts first runs the ckparam.txt file to set the environment variables. It then
Rem removes all the database objects which were created for Repository installation.
Rem
Rem Author    : Oracle Corporation UK
Rem

set verify off
@ckparams.txt
WHENEVER SQLERROR EXIT FAILURE

prompt 'Enter password for SYS ....'
conn sys@&conn as sysdba

WHENEVER SQLERROR CONTINUE

spool ckclean.log

prompt Dropping CKRAU_TEMP synonym ....
drop synonym "&REPOSOWNER".ckrau_temp;

prompt Dropping table CKRAU_TEMP
drop table CKRAU_TEMP;

prompt 
prompt Dropping Repository Owner
drop user &REPOSOWNER cascade;

prompt Dropping default tablespaces ......
prompt ***********************************

prompt
prompt Dropping CONSTANT_GROW_INDEXES tablespaces ......
drop tablespace constant_grow_indexes;

prompt
prompt Dropping CONSTANT_GROW_TABLES tablespaces ......
drop tablespace constant_grow_tables;

prompt
prompt Dropping DEPENDENCY_INDEXES tablespaces ......
drop tablespace dependency_indexes;

prompt
prompt Dropping DEPENDENCY_TABLES tablespaces ......
drop tablespace dependency_tables;

prompt
prompt Dropping DEPENDENCY_INDEXES tablespaces ......
drop tablespace diagram_indexes;

prompt
prompt Dropping DIAGRAM_TABLES tablespaces ......
drop tablespace diagram_tables;

prompt
prompt Dropping LOB_DATA tablespaces ......
drop tablespace lob_data;

prompt
prompt Dropping RAPID_GROW_INDEXES tablespaces ......
drop tablespace rapid_grow_indexes;

prompt
prompt Dropping RAPID_GROW_TABLES tablespaces ......
drop tablespace rapid_grow_tables;

prompt
prompt Dropping SYSTEM_META_INDEXES tablespaces ......
drop tablespace system_meta_indexes;

prompt
prompt Dropping SYSTEM_META_TABLES tablespaces ......
drop tablespace system_meta_tables;

prompt
prompt Dropping TEMPORARY_INDEXES tablespaces ......
drop tablespace temporary_indexes;

prompt
prompt Dropping TEMPORARY_TABLES tablespaces ......
drop tablespace temporary_tables including contents;

prompt
prompt Dropping VERSION_INDEXES tablespaces ......
drop tablespace version_indexes;

prompt
prompt Dropping VERSION_TABLES tablespaces ......
drop tablespace version_tables;

--prompt Bring the rollback segment offline .....
--alter rollback segment &RBSNAME offline;


set serveroutput on
declare
  l_mode       varchar2(30);
  l_plsql_str  varchar2(500);
  l_rbs_name   varchar2(40) := '&RBSNAME';
  l_rtbl_space varchar2(40) := '&RTBLSPACE';
  l_data_file  varchar2(1000) := '&RDFILENAME';
begin
  begin
    select UPPER(value)
    into   l_mode
    from   v$parameter
    where  UPPER(name) = 'UNDO_MANAGEMENT';
  exception
    when no_data_found then
	  l_mode := 'MANUAL';
  end;
  
  if l_mode = 'MANUAL' then
    dbms_output.put_line('Getting the rollback segment Offline ...');
	l_plsql_str := ' alter rollback segment ' || l_rbs_name || ' offline ';
	execute immediate l_plsql_str;
	
	dbms_output.put_line('Dropping tablespace ' || l_rtbl_space );
	l_plsql_str := ' drop tablespace ' || l_rtbl_space || ' including contents' ;
	execute immediate l_plsql_str;
  end if;
end;
/

set serveroutput off

prompt Dropping user created tablespaces ...
prompt **************************************

--prompt Dropping &RTBLSPACE tablespace ....
--drop tablespace &rtblspace including contents;

prompt Dropping &TEMPTBLSPACE tablespace ....
drop tablespace &temptblspace including contents;

prompt
prompt Following datafiles needs to be removed manually
prompt ------------------------------------------------

prompt
prompt &DF_NAME1
prompt
prompt &DF_NAME2
prompt
prompt &DF_NAME3
prompt
prompt &DF_NAME4
prompt
prompt &DF_NAME5
prompt
prompt &DF_NAME6
prompt
prompt &DF_NAME7
prompt
prompt &DF_NAME8
prompt
prompt &DF_NAME9
prompt
prompt &DF_NAME10
prompt
prompt &DF_NAME11
prompt
prompt &DF_NAME12
prompt
prompt &DF_NAME13
prompt
prompt &DF_NAME14
prompt
prompt &DF_NAME15
prompt
prompt &RDFILENAME
prompt
prompt &TEMPFILENAME

prompt
prompt Cleanup Operation complete
prompt ***********************************
prompt 

spool off
