Rem
Rem sbdelins.sql
Rem
Rem Copyright (c) 2006, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sbdelins.sql - StandBy statistics collection DELete INStance
Rem
Rem    DESCRIPTION
Rem	 SQL*PLUS command file which removes a standby database instance
Rem      for performance data collection
Rem
Rem    NOTES
Rem      Must be run as standby statspack user, stdbyperf.
Rem      Please purge all associated snapshots before deleting instance. 
Rem      Once a instance is deleted, we cannot purge its snapshots.   
Rem 
Rem    MODIFIED   (MM/DD/YY)
Rem    shsong      03/04/07 - Fix bug
Rem    wlohwass    12/06/06 - Created
Rem

--
--  List configured standby instances
@@sblisins

whenever sqlerror exit;

prompt
prompt
prompt Enter the INSTANCE NAME of the standby database instance to remove
prompt -------------------------------------------------------------------

prompt You entered: &&ins_name

column db_link      heading "DB Link"  new_value db_link      format a32;
column package_name heading "Package"  new_value package_name format a32;

select db_link db_link
     , package_name package_name
  from stats$standby_config
 where inst_name = '&&ins_name';


prompt
prompt INSTANCE &&ins_name WILL BE REMOVED FROM THE CONFIGURATION
prompt
prompt DROPPING DATABASE LINK: &&db_link
prompt DROPPING PACKAGE      : &&package_name
prompt
prompt Do you want to continue (y/n) ?
prompt You entered: &&key

begin
  if upper('&&key') <> 'Y' then
    raise_application_error(-20101, 'Install failed - Aborted by user');
  end if;
end;
/

whenever sqlerror continue;

prompt
prompt ... Dropping database link

drop database link &&db_link; 


prompt
prompt
prompt ... Dropping table

drop table v$lock_type_&&ins_name;
drop table v$database_&&ins_name;
drop table v$instance_&&ins_name;

prompt
prompt
prompt ... Dropping package
prompt

drop package &&package_name;


--
-- remove configuration
delete from stats$standby_config
 where inst_name = '&&ins_name';

commit;

undefine key ins_name db_link package_name