Rem
Rem $Header: ordim/admin/imremdo.sql /main/10 2009/03/16 08:32:06 smavris Exp $
Rem
Rem imremdo.sql
Rem
Rem Copyright (c) 2007, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem imremdo.sql - Internal Oracle Multimedia installation removal script
Rem
Rem DESCRIPTION
Rem This internal script deinstalls Oracle Multimedia
Rem
Rem NOTES
Rem - This script should not be invoked directly. It is invoked by the
Rem top level script 'imremov.sql'
Rem - must be run as sysdba
Rem - This script assumes all checking has been done and removes Oracle
Rem Multimedia with force.
Rem - Due to resource issues, objects are dropped individually and
Rem commits are liberally used.
Rem - Does not currently remove Locator
Rem
Rem MODIFIED (MM/DD/YY)
Rem smavris 03/05/09 - Invoke locator removal script
Rem smavris 03/03/09 - Remove sys.validate_ordim on Oracle Multimedia
Rem removal
Rem smavris 02/25/09 - Add dbms_assert to quote names so drops work on
Rem objects that aren't all upper case
Rem smavris 02/06/09 - Add exception handling, deregister XML schemas,
Rem reduce code.
Rem myalavar 01/30/08 - drop objects in orddata
Rem smavris 01/11/08 - Delete individual objects to avoid ora-00054
Rem smavris 10/22/07 - Check to see if Oracle Multimedia schemas have
Rem been removed
Rem myalavar 08/22/07 - drop orddata
Rem smavris 06/04/07 - Script that does the actual installation removal.
Rem smavris 05/29/07 - Oracle Multimedia installation removal script
Rem smavris 05/29/07 - Created
Rem
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
prompt
prompt Removing Oracle Multimedia
declare
stmt varchar2(1000);
obj_name varchar2(64);
type cursor_type is ref cursor;
query_crs cursor_type ;
begin
begin
sys.dbms_registry.removing('ORDIM');
exception when others then
if instr(sqlerrm, 'ORA-39705') = 0 then
-- unexpected error
raise;
end if;
end;
--
-- Remove Public Synonyms (for all but Java Classes)
--
begin
stmt := ' select SNAME from sys.synonyms ' ||
' where creator in (''ORDSYS'', ''ORDPLUGINS'', ' ||
' ''SI_INFORMTN_SCHEMA'', ''ORDDATA'') '||
' and SYNTYPE=''PUBLIC'' '||
' and TABTYPE<> ''JAVA CLASS'' ';
open query_crs for stmt;
loop
fetch query_crs into obj_name;
exit when query_crs%NOTFOUND ;
begin
execute immediate ' drop public synonym ' ||
sys.dbms_assert.enquote_name(obj_name) || ' force';
exception when others then
null;
end;
end loop;
close query_crs;
commit;
end;
--
-- Remove Tables
--
begin
for cur in (select table_name, owner from SYS.dba_tables
where owner in ('ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA')) loop
begin
execute immediate ' drop table '||
cur.owner || '.' ||
sys.dbms_assert.enquote_name(cur.table_name) ||
' cascade constraints purge';
exception when others then
null;
end;
end loop;
--======= drop ORDDATA tables=======================
-- drop tables in reverse order of creation
-- note: cannot drop nested tables, they are automatically dropped
-- when the parent table is dropped
for cur in ( select tab.table_name, tab.duration
from sys.dba_tables tab, sys.dba_objects obj
where tab.table_name = obj.object_name
and tab.owner='ORDDATA'
and tab.nested = 'NO'
order by obj.timestamp desc) loop
begin
-- truncate session duration temp table to avoid error
-- ORA-14452: attempt to create, alter or drop an index on
-- temporary table already in use
if (cur.duration = 'SYS$SESSION') then
execute immediate 'truncate table orddata.' ||
sys.dbms_assert.enquote_name(cur.table_name);
end if;
execute immediate ' drop table orddata.' ||
sys.dbms_assert.enquote_name(cur.table_name)
|| ' cascade constraints purge';
exception when others then
null;
end;
end loop;
commit;
end;
--
-- Remove Functions, Procedures, Packages, Views, Sequences
--
begin
for cur in
(select owner, object_type, object_name
from sys.dba_objects
where object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE',
'VIEW', 'SEQUENCE')
and owner in ('ORDSYS', 'ORDPLUGINS',
'SI_INFORMTN_SCHEMA', 'ORDDATA')
order by owner asc, object_type asc) loop
begin
execute immediate 'drop ' || cur.object_type || ' ' || cur.owner ||
'.' || sys.dbms_assert.enquote_name(cur.object_name);
exception when others then
null;
end;
end loop;
commit;
end;
--
-- Deregister XML Schemas
--
begin
for cur in
(select owner, schema_url
from sys.dba_xml_schemas
where owner in ('ORDSYS', 'ORDPLUGINS',
'SI_INFORMTN_SCHEMA', 'ORDDATA')
order by owner asc) loop
begin
xdb.dbms_xmlschema.deleteschema
(cur.schema_url,
xdb.dbms_xmlschema.delete_cascade_force);
exception when others then
null;
end;
end loop;
commit;
end;
--
-- Remove Types
--
begin
for cur in
(select owner, object_type, object_name
from SYS.dba_objects
where object_type = 'TYPE'
and owner in ('ORDSYS', 'ORDPLUGINS',
'SI_INFORMTN_SCHEMA', 'ORDDATA')
order by owner asc) loop
begin
execute immediate 'drop type ' || cur.owner ||
'.' || sys.dbms_assert.enquote_name(cur.object_name) ||
' force';
exception when others then
null;
end;
end loop;
commit;
end;
end;
/
-- Remove Validatation Procedure from SYS
begin
execute immediate 'drop procedure sys.validate_ordim';
exception when others then
if instr(sqlerrm, 'ORA-04043') = 0 then
-- unexpected error
raise;
end if;
end;
/
-- Remove JAVA Classes
begin
sys.dbms_java.dropjava('-synonym -schema ordsys ord/jlib/ordimann.jar');
exception when others then
if instr(sqlerrm, 'ORA-29532') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
sys.dbms_java.dropjava('-synonym -schema ordsys ord/jlib/mlibwrapper_jai.jar');
exception when others then
if instr(sqlerrm, 'ORA-29532') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
sys.dbms_java.dropjava('-synonym -schema ordsys ord/jlib/jai_core.jar');
exception when others then
if instr(sqlerrm, 'ORA-29532') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
sys.dbms_java.dropjava('-synonym -schema ordsys ord/jlib/jai_codec.jar');
exception when others then
if instr(sqlerrm, 'ORA-29532') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
sys.dbms_java.dropjava('-synonym -schema ordsys ord/jlib/ordimimg.jar');
exception when others then
if instr(sqlerrm, 'ORA-29532') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
sys.dbms_java.dropjava('-synonym -schema ordsys ord/jlib/ordimdcm.jar');
exception when others then
if instr(sqlerrm, 'ORA-29532') = 0 then
-- unexpected error
raise;
end if;
end;
/
commit;
begin
execute immediate 'drop user orddata cascade';
exception when others then
if instr(sqlerrm, 'ORA-01918') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
execute immediate 'drop user ordplugins cascade';
exception when others then
if instr(sqlerrm, 'ORA-01918') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
execute immediate 'drop user si_informtn_schema cascade';
exception when others then
if instr(sqlerrm, 'ORA-01918') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
execute immediate 'drop role ordadmin';
exception when others then
if instr(sqlerrm, 'ORA-01919') = 0 then
-- unexpected error
raise;
end if;
end;
/
--
-- Remove Locator. We can do this unconditionally since the Locator script
-- will check to see if SDO is installed and will not remove Locator if
-- SDO is installed.
--
@?/md/admin/mddinloc.sql
begin
sys.dbms_registry.removed('ORDIM');
exception when others then
if instr(sqlerrm, 'ORA-39705') = 0 then
-- unexpected error
raise;
end if;
end;
/
begin
execute immediate 'drop user ordsys cascade';
exception when others then
if instr(sqlerrm, 'ORA-01918') = 0 then
-- unexpected error
raise;
end if;
end;
/