Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\md\admin\seme111.sql

Rem
Rem $Header: sdo/admin/seme111.sql /main/3 2009/05/28 09:16:02 matperry Exp $
Rem
Rem seme111.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      seme111.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    matperry    05/26/09 - add trace messages
Rem    matperry    04/23/09 - Created
Rem

-----------------------------------------------------------------------------------------------
-- The "e" script reverts the component's data dictionary back from release NNN+1 to release --
-- NNN. Since the cmpdbdwg.sql script calls the script for the downgrade release, these      --
-- scripts are stacked in the reverse order. They are called in the context of the NEW       --
-- server, before the RDBMS itself is downgraded. Set the version to the release version     --
-- (3 digits), not a patch version, in the dbms_registry.downgraded procedure invocation.    --
-----------------------------------------------------------------------------------------------

set echo off;
alter session set current_schema=MDSYS;
set define on;

-- make sure we do the downgrade without errors --
whenever sqlerror continue;

Rem comment out for current release script
--@@myceNNN+1

-- create dummy packages if necessary to avoid errors
declare
  sem_exists number := 0;
begin

-- if semantics doesn't exist we need a dummy sdo_sem_downgrade package 
  -- and we need a dummy rdf_apis_internal package
  select count(*) into sem_exists
  from all_objects
  where owner = 'MDSYS' and object_name = 'SDO_RDF_TRIPLE_S';

if (sem_exists = 0) then
    execute immediate '
      create or replace package mdsys.sdo_sem_downgrade_utl as
        function data_112_exists(notify_level in integer)
        return boolean;
      end sdo_sem_downgrade_utl;';
  end if;

end;
/
show errors;

-- first uninstall OLS / VPD / CTX / WM optional components --

COLUMN :script_name1 NEW_VALUE comp_file1 NOPRINT
VARIABLE script_name1 VARCHAR2(30)

COLUMN :script_name2 NEW_VALUE comp_file2 NOPRINT
VARIABLE script_name2 VARCHAR2(30)

COLUMN :script_name3 NEW_VALUE comp_file3 NOPRINT
VARIABLE script_name3 VARCHAR2(30)

declare
  sem_exists      number := 0;
  network_exists  number := 0;
  type_111_exists number := 0;
  type_112_exists number := 0;
  refcount        number := 0;
begin

-- see if semantics is installed --
  select count(*) into sem_exists
  from all_objects
  where owner = 'MDSYS' and object_name = 'SDO_RDF_TRIPLE_S';

-- if semantics is installed then keep going

if (sem_exists > 0) then

-- see what type of install we are dealing with --
    select count(*) into type_112_exists
    from all_objects
    where owner = 'MDSYS' and object_name = 'RDF_PARAMETER';

-- if 11.2 is installed then keep going 
    if (type_112_exists > 0) then

-- make sure the database is compatible with 11.1 --
      if (mdsys.sdo_sem_downgrade_utl.data_112_exists(0) = false) then
        
        begin
          execute immediate
            'select count(*) from mdsys.rdf_parameter
	     where namespace = ''COMPONENT'' and attribute = ''RDFCTX'''
          into refcount;
          exception when others then null;
        end;
	if (refcount > 0) then 
	  :script_name1 := '@sdordfctx_rm.sql';
	else
	  :script_name1 := dbms_registry.nothing_script;
	end if;

refcount := 0;
        begin
          execute immediate
  	    'select count(*) from mdsys.rdf_parameter
	     where namespace = ''COMPONENT'' and attribute = ''RDFOWM'''
          into refcount;
          exception when others then null;
        end;
	if (refcount > 0) then 
	  :script_name2 := '@sdordfwm_rm.sql';
	else
	  :script_name2 := dbms_registry.nothing_script;
	end if;

refcount := 0;
        begin
	  execute immediate
            'select count(*) from mdsys.rdf_parameter
	     where namespace = ''COMPONENT'' and attribute in (''RDFVPD'', ''RDFOLS'')'
          into refcount;
          exception when others then null;
        end;
	if (refcount > 0) then 
	  :script_name3 := '@sdordfsa_rm.sql';
	else
	  :script_name3 := dbms_registry.nothing_script;
	end if; 
      
      else
        :script_name1 := dbms_registry.nothing_script;
        :script_name2 := dbms_registry.nothing_script;
        :script_name3 := dbms_registry.nothing_script;
      end if;

else 
      :script_name1 := dbms_registry.nothing_script;
      :script_name2 := dbms_registry.nothing_script;
      :script_name3 := dbms_registry.nothing_script;
    end if;

else 
    :script_name1 := dbms_registry.nothing_script;
    :script_name2 := dbms_registry.nothing_script;
    :script_name3 := dbms_registry.nothing_script;
  end if;

end;
/
show errors;

SELECT :script_name1 FROM DUAL;
@&comp_file1
SELECT :script_name2 FROM DUAL;
@&comp_file2
SELECT :script_name3 FROM DUAL;
@&comp_file3

-- make sure schema is set to mdsys --
alter session set current_schema=MDSYS;

-- now remove standard semantics components --
declare
  sem_exists      number := 0;
  network_exists  number := 0;
  type_111_exists number := 0;
  type_112_exists number := 0;
  semTypesList    sys.ODCIVarchar2List;
  semPackagesList sys.ODCIVarchar2List;
begin

-- see if semantics is installed --
  select count(*) into sem_exists
  from all_objects
  where owner = 'MDSYS' and object_name = 'SDO_RDF_TRIPLE_S';

-- if semantics is installed then keep going

if (sem_exists > 0) then

-- see what type of install we are dealing with --
    select count(*) into type_112_exists
    from all_objects
    where owner = 'MDSYS' and object_name = 'RDF_PARAMETER';

-- if 11.2 is installed then keep going 
    if (type_112_exists > 0) then

-- make sure the database is compatible with 11.1 --
      if (mdsys.sdo_sem_downgrade_utl.data_112_exists(0) = false) then

-- see if RDF Network is present in DB
        select count(*) into network_exists 
        from all_tables
        where table_name='RDF_VALUE$' and owner='MDSYS';

-- passed all checks ... now do the downgrade --

-- DROP packages, views, and classes that were added in the new release

-- SKOS --
        -- get rid of SKOS rulebase if there is an rdf_network --
        if (network_exists > 0) then
          execute immediate
            'begin
               mdsys.rdf_apis_internal.drop_rulebase(''SKOSCORE'', null, false);
             end;';
        end if;
    
        -- Virtual Model --
        begin
	  execute immediate 'drop view mdsys.sem_vmodel_datasets';
          exception when others then null;
	end;

begin
	  execute immediate 'drop view mdsys.rdf_vmodel_datasets';
	  exception when others then null;
        end;

begin
	  execute immediate 'drop view mdsys.sem_vmodel_info';
	  exception when others then null;
        end;

begin
	  execute immediate 'drop view mdsys.rdf_vmodel_info';
	  exception when others then null;
        end;

-- OLS support --
	begin
	  execute immediate 'drop package mdsys.sem_rdfsa_ir';
	  exception when others then null;
	end;

begin
	  execute immediate 'drop procedure mdsys.RDF$GRANT_OLS_PRIVSe';
	  exception when others then null;
	end;

-- sameAs optimization --
	begin
	  execute immediate 'drop package mdsys.sdo_sem_cli';
	  exception when others then null;
	end;

-- DROP force any new types, operators, and indextypes;

-- OLS support --
	begin
	  execute immediate 'drop type mdsys.rdfsa_resource force';
	  exception when others then null;
	end;

begin
	  execute immediate 'drop type mdsys.rdfsa_labelgen force';
	  exception when others then null;
	end;

-- sameAs Types --
        begin
          execute immediate 'drop type mdsys.sem_sameas_list force';
          exception when others then null;
        end;
    
        begin
          execute immediate 'drop type mdsys.sem_sameas_rec force';
          exception when others then null;
        end;
    
    	-- ALTER TYPE for existing types back to their prior release definitions

-- ALTER OPERATOR and INDEXTYPE back to their prior release bindings and properties

-- UPDATE new columns to values appropriate for the old release and for when the database is re-upgraded

-- Virtual Model support --
	-- only update these views if an rdf network exists --

if (network_exists > 0) then

-- revert to pre-virtual_model metadata views --

-- rdf_model$ --
	  EXECUTE IMMEDIATE '
	    CREATE OR REPLACE VIEW rdf_model$ (
	      owner
	    , model_id
	    , model_name
	    , table_name
	    , column_name
	    , model_tablespace_name
	    ) AS
	    SELECT 
	      au.username
	    , rmi.model_id
	    , rmi.model_name
	    , ao.object_name
	    , rmi.column_name
	    , rmi.model_tablespace_name
	    FROM rdf_model_internal$ rmi,all_users au,all_objects ao
	    WHERE rmi.owner_id=au.user_id AND ao.owner=au.username AND
		  rmi.table_id=ao.object_id
	  ';

-- 11g API
	  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_MODEL$ AS SELECT * FROM MDSYS.RDF_MODEL$';
	  EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_MODEL$ TO PUBLIC';

-- rules_index_info --
	  EXECUTE IMMEDIATE '
	    create or replace view RDF_RULES_INDEX_INFO (
	      OWNER, INDEX_NAME, INDEX_VIEW_NAME, STATUS, MODEL_COUNT,RULEBASE_COUNT)
	    as
	      select p.owner, p.indexname, ''RDFI_'' || p.indexname,
		     DECODE(p.status,
			    ''INPROGRESS'',
			    SUBSTR(MDSYS.RDF_APIS_INTERNAL.TEST_INPROGRESS(
				     ''RULES_INDEX'', p.indexname), 
				   0, 30),
			    p.status),
		     SUM(DECODE(rpd.deptyp, ''M'', 1, 0)),
		     SUM(DECODE(rpd.deptyp, ''R'', 1, 0))
	      from mdsys.rdf_precomp$ p, mdsys.rdf_precomp_dep$ rpd
	      where p.model_id = rpd.model_id
	      group by p.owner, p.indexname, p.status';

-- rules_index_datasets --
	  EXECUTE IMMEDIATE '
	    create or replace view RDF_RULES_INDEX_DATASETS (
	      INDEX_NAME, DATA_TYPE, DATA_NAME) 
	    as 
	      select p.indexname, ''MODEL'', m.model_name
	      from mdsys.rdf_precomp$ p, mdsys.rdf_precomp_dep$ pd, 
		   mdsys.rdf_model_internal$ m
	      where p.model_id = pd.model_id
		AND pd.deptyp = ''M'' 
		AND pd.depid = m.model_ID
	      UNION ALL
	      select p.indexname, ''RULEBASE'', r.name
	      from mdsys.rdf_precomp$ p, mdsys.rdf_precomp_dep$ pd, 
		   mdsys.rdf_rulebase$ r
	      where p.model_id = pd.model_id
		AND pd.deptyp = ''R'' 
		AND pd.depid = r.id';

-- 11g API --
	  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_RULES_INDEX_INFO AS SELECT * FROM MDSYS.RDF_RULES_INDEX_INFO';
	  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW MDSYS.SEM_RULES_INDEX_DATASETS AS SELECT * FROM MDSYS.RDF_RULES_INDEX_DATASETS';

-- grant SELECT privileges --
	  EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_RULES_INDEX_INFO     TO PUBLIC';
	  EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.SEM_RULES_INDEX_DATASETS TO PUBLIC';

EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.RDF_RULES_INDEX_INFO     TO PUBLIC';
	  EXECUTE IMMEDIATE 'GRANT SELECT ON MDSYS.RDF_RULES_INDEX_DATASETS TO PUBLIC';

-- end if network exists --
	end if;

-- Undo any modifications that were made to user objects during the upgrade.

-- Truncate new tables; it is not necessary to drop new tables
    
        -- sameAS / INC Inference --
        begin
          execute immediate 'drop table mdsys.rdf_clique$';
          exception when others then null;
        end;

begin
          execute immediate 'drop table mdsys.rdf_ts$';
          exception when others then null;
        end;

begin
          execute immediate 'drop table mdsys.rdf_delta$';
          exception when others then null;
        end;

-- OLS Support --
	begin
	  execute immediate 'drop table mdsys.rdf_parameter';
	  exception when others then null;
	end;

-- drop all packages and type bodies --
        semTypesList := sys.ODCIVarchar2List('SEM_TERM', 'SEM_RULE_NODE', 'SEM_RULE',
          'SEM_PREDICATE', 'SEM_INDEXTYPE_IM', 'SEM_GRAPH', 'SDO_SEM_STATS', 'SDO_RDF_TRIPLE_S',
          'RDF_MATCH_IMPL_T');

semPackagesList := sys.ODCIVarchar2List('SEM_UPGRADE_TO_11', 'SEM_HASH_INTERNAL',
          'SDO_SEM_VALIDATE', 'SDO_SEM_UTL', 'SDO_SEM_PERF', 'SDO_SEM_MAGIC',
          'SDO_SEM_LOG', 'SDO_SEM_INFI', 'SDO_SEM_INFH', 'SDO_SEM_INFERENCE', 
          'SDO_SEM_DOWNGRADE', 'SDO_SEM_CTX', 'SDO_SEM_CONST', 'SDO_SEM_BT', 'SDO_RDF_MIG', 'SDO_RDF_INTERNAL',
          'SDO_RDF', 'RDF_APIS_USER', 'RDF_APIS_OPER_INTERNAL', 'RDF_APIS_INTERNAL', 'RDF_APIS');

-- drop type bodies --
        for i in 1..semTypesList.count loop
          begin
            dbms_output.put_line('drop type body mdsys.' || semTypesList(i));
            execute immediate 'drop type body mdsys.' || semTypesList(i);
          exception when others then null;
          end;
        end loop;

-- drop packages --
        for i in 1..semPackagesList.count loop
          begin
            dbms_output.put_line('drop package mdsys.' || semPackagesList(i));
            execute immediate 'drop package mdsys.' || semPackagesList(i);
          exception when others then null;
          end;
        end loop;

-- just drop the body here so that operators and indextypes remain valid --
        begin
          execute immediate 'drop package body mdsys.sdo_sem_operator';
        exception when others then null;
        end;

-- drop one procedure --
        begin
          execute immediate 'drop procedure mdsys.load_predefined_rulebases';
        exception when others then null;
        end;

commit;

else -- database uses 11.2-specific features
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'Semantics data is not compatible with version 11.1. Not downgrading to Release 11.1.');
        raise_application_error(-20000, 
          'Semantics data is not compatible with version 11.1');
      end if;

else -- semantics install present but not 11.2

-- see if 11.1 is installed --
      -- see if 11 SDO_RDF_TRIPLE_S type is present in DB
      select count(*) into type_111_exists 
      from all_type_attrs
      where type_name='SDO_RDF_TRIPLE_S' and attr_name='RDF_C_ID' 
	    and owner='MDSYS';

-- if 11.1 is installed then there is nothing to do 
      -- if 11.1 is not installed then we have a problem
      if (type_111_exists = 0) then
        SYS.DBMS_SYSTEM.KSDWRT(SYS.DBMS_SYSTEM.TRACE_FILE, 
          'Semantic Technologies Release is earlier than 11.1. Not downgrading to Release 11.1.');
      end if;

end if;

end;
/
show errors;

alter session set current_schema=SYS;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de