Rem
Rem $Header: sdomvdct.sql 02-dec-2005.12:01:00 sravada Exp $
Rem
Rem sdomvdct.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdomvdct.sql - MoVe the DiCTionary data to a new tablespace
Rem
Rem    DESCRIPTION
Rem      This is required for use with sysaux tablespace. This procedure moves
Rem      all spatial dictionary data to a user specified tablespace.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sravada     12/16/03 - bug 3089374 
Rem    sravada     02/05/03 - remove SETs
Rem    sravada     09/27/02 - 
Rem    sravada     03/22/02 - sravada_move_dictionary_sysaux
Rem    sravada     03/21/02 - Created
Rem


CREATE OR REPLACE PROCEDURE MOVE_SDO ( new_tblspc IN varchar2)
IS
begin
    BEGIN
     EXECUTE IMMEDIATE
     ' drop table sdo_index_metadata_table_orig ';
        EXCEPTION
            WHEN OTHERS THEN null;
     END;

    BEGIN
     EXECUTE IMMEDIATE
     ' drop table sdo_geom_metadata_table_orig ';
        EXCEPTION
            WHEN OTHERS THEN null;
     END;

     EXECUTE IMMEDIATE
     'rename sdo_index_metadata_table to sdo_index_metadata_table_orig';   
     EXECUTE IMMEDIATE
     'rename sdo_geom_metadata_table to sdo_geom_metadata_table_orig';   
  
     EXECUTE IMMEDIATE
     ' create table sdo_index_metadata_table tablespace ' || new_tblspc ||
     ' as select * from sdo_index_metadata_table_orig';
    
     EXECUTE IMMEDIATE
     ' create table sdo_geom_metadata_table tablespace '|| new_tblspc  ||
     ' as select * from sdo_geom_metadata_table_orig' ;

    EXECUTE IMMEDIATE 
    ' DROP INDEX SDO_GEOM_IDX ';
    EXECUTE IMMEDIATE 
    ' CREATE INDEX SDO_GEOM_IDX ON  ' ||
    ' SDO_GEOM_METADATA_TABLE(SDO_OWNER,SDO_TABLE_NAME) ';

    EXECUTE IMMEDIATE 
    ' DROP INDEX SDO_IDX_MDATA_IDX ';
    EXECUTE IMMEDIATE 
    ' CREATE INDEX SDO_IDX_MDATA_IDX ON ' ||
    ' SDO_INDEX_METADATA_TABLE(SDO_INDEX_OWNER, SDO_INDEX_NAME, ' ||
   '  SDO_INDEX_PARTITION) ';

end;
/

show errors;
grant execute on MOVE_SDO to public;