====== Manually remove Oracle Ultra Search ======

If you query dba_registry, you might find that you have the Oracle Ultra Search component, and it's status is INVALID.  The best way to remedy this is to re-run the installer for Ultra Search, which is located in $ORACLE_HOME/ultrasearch/admin, but, if you do not have this directory, you could be at an impasse.  Further, having INVALID components during an upgrade can cause issues and errors to appear.

The preferred way to remove a component is to use DBCA.  However, if, like me, DBCA will not allow you remove it because it thinks it is already removed, then you have to resort to manual removal.

If you wish to remove this component completely, you can do so by running the following script.  Please note that I cannot take responsibility for any issues that may arise by using this.  You should check with Oracle support first.  This script came from an Oracle 10g installation in $ORACLE_HOME/rdbms/admin, and was called wkremove.sql.  You should check to see if your installation has this script already and use that copy if so.

Connect to sqlplus as sysdba.
<code sql>
set heading on
whenever sqlerror continue;

declare
  drop_context EXCEPTION;
  PRAGMA EXCEPTION_INIT(drop_context, -04043);
begin
   execute immediate 'drop context wk$context' ;

exception when drop_context then 
  null;
end;
/


declare
  drop_context EXCEPTION;
  PRAGMA EXCEPTION_INIT(drop_context, -04043);
begin
   execute immediate 'drop context wk$ctx_ldap' ;

exception when drop_context then 
  null;
end;
/

drop user wksys cascade;

drop role wkuser; 

drop user wkproxy cascade;

drop user wk_test cascade;

PROMPT Clean up interMedia Text dictionary
begin
  ctxsys.ctx_adm.recover;
end;
/

prompt Drop public synonyms
begin
  for s in (select synonym_name from all_synonyms where table_owner = 'WKSYS') loop
    execute immediate 'drop public synonym "'||s.synonym_name||'"';
  end loop;
end;
/

PROMPT Removing all XDB resources (ACLs and folders)
declare
  type t_cursor is ref cursor;
  l_cursor t_cursor;
  l_stmt VARCHAR2(4000);
  l_stmt2 VARCHAR2(4000);
  l_path varchar2(4000);
  l_result number := 0;
begin
 
  l_stmt := 'select count(*) from resource_view where any_path = ''/sys/apps/ultrasearch''';
  l_stmt2 := 'begin dbms_xdb.setacl(:1, :2); end;';
  begin
    execute immediate l_stmt into l_result; 
  exception 
    when others THEN
      l_result := 0;
  end;        

  if (l_result > 0) then
    -- Making sure all ACLs are not referenced
    l_stmt :=
    'select any_path from resource_view where ' ||
      ' UNDER_PATH(res, ''/sys/apps/ultrasearch'', 1) = 1' ||
      ' order by depth(1) desc';
    open l_cursor for l_stmt;
    loop
      fetch l_cursor into l_path;
      exit when l_cursor%NOTFOUND;
      execute immediate l_stmt2 using l_path, '/sys/acls/all_all_acl.xml';
      commit;
    end loop;
  
    -- Deleting all acls and folders
    l_stmt :=
      'select any_path from resource_view where ' ||
      ' UNDER_PATH(res, ''/sys/apps/ultrasearch'', 1) = 1' ||
      ' order by depth(1) desc';
    l_stmt2 := 'delete from resource_view where any_path = :1';
    open l_cursor for l_stmt;
    loop
      fetch l_cursor into l_path;
      exit when l_cursor%NOTFOUND;
      execute immediate l_stmt2 using l_path;
      commit;
    end loop;
  
    l_stmt2 := 'delete from resource_view where any_path = :1';
    execute immediate l_stmt2 using '/sys/apps/ultrasearch';
    execute immediate l_stmt2 using '/sys/apps/ultrasearch_acl.xml';
    commit;
  end if;
end;
/
</code>