Rem
Rem $Header: sdo/admin/sdoupggeom.sql /st_sdo_11.2.0/1 2011/06/02 14:00:54 sravada Exp $
Rem
Rem sdoupggeom.sql
Rem
Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. 
Rem 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     06/02/11 - bug 12617136
Rem    sravada     05/05/09 - Created
Rem

alter session set current_schema=MDSYS;

@@sdoeoper.sql

/* Alter all the indexes to make them unusable */
create or replace function md_isversioned(iowner varchar2, iname varchar2)
return varchar2 as
tname varchar2(64);
begin
tname := NULL;
  begin
    select distinct  c.TABLE_NAME  into  tname
                       from  all_sdo_index_info b,
                       all_wm_versioned_tables c
                      where
                      b.SDO_INDEX_OWNER = iowner and
                      b.INDEX_NAME = iname and
                      c.TABLE_NAME||'_LT' = b.TABLE_NAME and
                      c.owner = b.TABLE_OWNER and
                      c.state = 'VERSIONED' ;
     exception when others then NULL;
   end;
 return tname;
end;
/


declare
 type                    cursor_type is REF CURSOR;
 query_crs               cursor_type ;
 owner  varchar2(64);
 iname  varchar2(64);
 pname  varchar2(64);
 tname  varchar2(64);
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 ;
    tname := md_isversioned(owner, iname);
    if (tname is NULL ) then /* not versioned */
      execute immediate ' alter index '|| dbms_assert.enquote_name(owner,false)
            ||'.' || dbms_assert.enquote_name(iname,false) || ' unusable ';
    else
       dbms_wm.AlterVersionedTable(owner||'.'
         ||tname, 'DDL',
            'ddl="alter index '||owner||'.'
         ||iname||' unusable", force=true') ;
    end if;
  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 ;
    tname := md_isversioned(owner, iname);
    if (tname is NULL) then
      execute immediate ' alter index '|| dbms_assert.enquote_name(owner,false)
          ||'.' || dbms_assert.enquote_name(iname,false) || ' modify partition '
       || dbms_assert.enquote_name(pname,false) || ' unusable ';
    else
       dbms_wm.AlterVersionedTable(owner||'.'||tname, 'DDL',
          'ddl="alter index '|| owner||'.'||iname||
                  '  modify partition '||pname||'  unusable", force=true' ) ;
    end if;
  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(64);
 iname  varchar2(64);
 pname  varchar2(64);
 tname  varchar2(64);
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 ;
    tname := md_isversioned(owner, iname);
    if (tname is NULL ) then /* not versioned */
      execute immediate ' alter index '|| dbms_assert.enquote_name(owner,false)
            ||'.' || dbms_assert.enquote_name(iname,false) ||
               ' rebuild parameters (''index_status=usable'') ';
    else
       dbms_wm.AlterVersionedTable(owner||'.'||tname, 'DDL',
            'ddl="alter index '||owner||'.'||iname||
              ' rebuild parameters (''index_status=usable'') ", force=true') ;
    end if;
  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 ;
    tname := md_isversioned(owner, iname);
    if (tname is NULL) then
      execute immediate ' alter index '|| dbms_assert.enquote_name(owner,false)
         ||'.' || dbms_assert.enquote_name(iname,false) ||
            ' rebuild partition ' || dbms_assert.enquote_name(pname,false) ||
              ' parameters (''index_status=usable'') ';
    else
      dbms_wm.AlterVersionedTable(owner||'.'||tname, 'DDL',
       'ddl="alter index '||owner||'.'||iname||
             ' rebuild partition ' || pname ||
         ' parameters (''index_status=usable'') ",force=true') ;
    end if;
  END LOOP;
end;
/

drop function md_isversioned;