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

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

Rem
Rem $Header: sdo/admin/sdoupggeom.sql /main/1 2009/05/13 19:24:36 sravada Exp $
Rem
Rem sdoupggeom.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates. All rights reserved. 
Rem
Rem    NAME
Rem      sdoupggeom.sql - SDOUPgradeGEOMetry.sql
Rem
Rem    DESCRIPTION
Rem      Use this script to upgrade the SDO_GEOMETRY type to extend
Rem      the SDO_ORDINATES varray to 10M numbers.
Rem      Run the script while connected as SYSDBA user.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sravada     05/05/09 - Created
Rem

alter session set current_schema=MDSYS;

@@sdoeoper.sql

/* Alter all the indexes to make them unusable */

declare
 type                    cursor_type is REF CURSOR;
 query_crs               cursor_type ;
 owner  varchar2(32);
 iname  varchar2(32);
 pname  varchar2(32);
begin
  OPEN query_crs for ' select  SDO_INDEX_OWNER,SDO_INDEX_NAME ' ||
                     '  from all_sdo_index_metadata ' ||
                     ' where nvl(SDO_PARTITIONED,0) = 0 ';
  LOOP
    FETCH query_crs into owner, iname;
    EXIT when query_crs%NOTFOUND ;
    execute immediate ' alter index '|| owner ||'.' || iname || ' unusable ';
  END LOOP;
  OPEN query_crs for ' select  SDO_INDEX_OWNER,SDO_INDEX_NAME, SDO_INDEX_PARTITION ' ||
                     '  from all_sdo_index_metadata ' ||
                     ' where SDO_PARTITIONED = 1 ';
  LOOP
    FETCH query_crs into owner, iname, pname;
    EXIT when query_crs%NOTFOUND ;
    
    execute immediate ' alter index '|| owner ||'.' || iname || 
            ' modify partition ' || pname || ' unusable ';
  END LOOP;
end;
/

alter type mdsys.sdo_ordinate_array modify limit 10000000  cascade;

@@prvtsidx.plb
@@prvtoper.plb

alter type SDO_GEOMETRY compile body;

alter view USER_SDO_INDEX_METADATA compile;
alter view ALL_SDO_INDEX_METADATA compile;
alter view USER_SDO_INDEX_INFO compile;
alter view ALL_SDO_INDEX_INFO compile;

declare
 type                    cursor_type is REF CURSOR;
 query_crs               cursor_type ;
 oname  varchar2(32);
begin

OPEN query_crs for 
   ' select object_name from all_objects where status = ''INVALID''
     and object_type = ''PACKAGE'' and owner = ''MDSYS'' ';
  LOOP
    FETCH query_crs into oname;
    EXIT when query_crs%NOTFOUND ;
     execute immediate ' alter package ' || oname || ' compile ';
  END LOOP;

OPEN query_crs for 
   ' select object_name from all_objects where status = ''INVALID''
     and object_type = ''PACKAGE BODY'' and owner = ''MDSYS'' ';
  LOOP
    FETCH query_crs into oname;
    EXIT when query_crs%NOTFOUND ;
     execute immediate ' alter package ' || oname || ' compile body ';
  END LOOP;

OPEN query_crs for 
   ' select object_name from all_objects where status = ''INVALID''
     and object_type = ''TYPE BODY'' and owner = ''MDSYS'' ';
  LOOP
    FETCH query_crs into oname;
    EXIT when query_crs%NOTFOUND ;
     execute immediate ' alter type ' || oname || ' compile body ';
  END LOOP;

OPEN query_crs for 
   ' select object_name from all_objects where status = ''INVALID''
     and object_type = ''TRIGGER'' and owner = ''MDSYS'' ';
  LOOP
    FETCH query_crs into oname;
    EXIT when query_crs%NOTFOUND ;
     execute immediate ' alter trigger ' || oname || ' compile ';
  END LOOP;

OPEN query_crs for 
   ' select object_name from all_objects where status = ''INVALID''
     and object_type = ''VIEW'' and owner = ''MDSYS'' ';
  LOOP
    FETCH query_crs into oname;
    EXIT when query_crs%NOTFOUND ;
     execute immediate ' alter view ' || oname || ' compile ';
  END LOOP;

OPEN query_crs for 
   ' select object_name from all_objects where status = ''INVALID''
     and object_type = ''FUNCTION'' and owner = ''MDSYS'' ';
  LOOP
    FETCH query_crs into oname;
    EXIT when query_crs%NOTFOUND ;
     execute immediate ' alter function ' || oname || ' compile ';
  END LOOP;

end;
/

alter indextype spatial_index compile;
alter indextype RTREE_INDEX compile;

select * from all_objects where status = 'INVALID' and owner = 'MDSYS';

declare
 type                    cursor_type is REF CURSOR;
 query_crs               cursor_type ;
 owner  varchar2(32);
 iname  varchar2(32);
 pname  varchar2(32);
begin
  OPEN query_crs for ' select  SDO_INDEX_OWNER,SDO_INDEX_NAME ' ||
                     '  from all_sdo_index_metadata ' ||
                     ' where nvl(SDO_PARTITIONED,0) = 0 ';
  LOOP
    FETCH query_crs into owner, iname;
    EXIT when query_crs%NOTFOUND ;
    execute immediate ' alter index '|| owner ||'.' || iname || 
          ' rebuild parameters (''index_status=usable'')  ';
  END LOOP;
  OPEN query_crs for ' select  SDO_INDEX_OWNER,SDO_INDEX_NAME, SDO_INDEX_PARTITION ' ||
                     '  from all_sdo_index_metadata ' ||
                     ' where SDO_PARTITIONED = 1 ';
  LOOP
    FETCH query_crs into owner, iname, pname;
    EXIT when query_crs%NOTFOUND ;

execute immediate ' alter index '|| owner ||'.' || iname ||
            ' rebuild partition ' || pname ||
              ' parameters (''index_status=usable'') ';
  END LOOP;
end;
/

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de