declare
begin
  begin
   execute immediate 
  ' CREATE  TABLE SDO_INDEX_METADATA_TABLE
     (SDO_INDEX_OWNER varchar2(32),
      SDO_INDEX_TYPE varchar2(32),
      SDO_LEVEL NUMBER,
      SDO_NUMTILES NUMBER, SDO_MAXLEVEL NUMBER,
      SDO_COMMIT_INTERVAL NUMBER,  SDO_INDEX_TABLE varchar2(32),
      SDO_INDEX_NAME varchar2(32), SDO_INDEX_PRIMARY NUMBER,
      SDO_TSNAME varchar2(32),
      SDO_COLUMN_NAME varchar2(2048),
      SDO_RTREE_HEIGHT  number, 
      SDO_RTREE_NUM_NODES number,
      SDO_RTREE_DIMENSIONALITY number,
      SDO_RTREE_FANOUT number,
      SDO_RTREE_ROOT varchar2(32),
      SDO_RTREE_SEQ_NAME varchar2(32),
      SDO_FIXED_META RAW(255), SDO_TABLESPACE varchar2(32),
      SDO_INITIAL_EXTENT  varchar2(32), SDO_NEXT_EXTENT varchar2(32),
      SDO_PCTINCREASE NUMBER, SDO_MIN_EXTENTS NUMBER, 
           SDO_MAX_EXTENTS NUMBER) ';
    exception when others then null;
   end;
  
  begin
  execute immediate 
' Alter table SDO_INDEX_METADATA_TABLE
modify (SDO_COLUMN_NAME varchar2(2048)) ';
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add ( SDO_INDEX_DIMS number)   ';
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add ( SDO_LAYER_GTYPE varchar2(32)) '; 
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add 
( SDO_RTREE_PCTFREE number)   ';
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add 
( SDO_INDEX_PARTITION varchar2(32))   ';
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add 
( SDO_PARTITIONED number)   ';
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add 
( SDO_RTREE_QUALITY number)   ';
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add 
( SDO_INDEX_VERSION number)   ';
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add
( SDO_INDEX_GEODETIC varchar2(8))   ' ;
    exception when others then null;
   end;
                                                                                
  begin
  execute immediate 
 ' Alter table SDO_INDEX_METADATA_TABLE add
( SDO_INDEX_STATUS varchar2(32))   ';
    exception when others then null;
   end;

  
  begin 
    execute immediate
    ' update sdo_index_metadata_table set SDO_INDEX_STATUS = '||
    '''VALID''  where SDO_INDEX_STATUS is NULL';    
    exception when others then null;
  end;

  begin 
    execute immediate
    'update sdo_index_metadata_table set SDO_INDEX_DIMS =  2 ' || 
    ' where SDO_INDEX_GEODETIC  = ''TRUE'' and SDO_RTREE_DIMENSIONALITY=3';  
    exception when others then null;
  end;
                                                                                


 begin 
  execute immediate 
  ' alter table sdo_index_metadata_table add 
      (SDO_NL_INDEX_TABLE varchar2(33)) ';
    exception when others then null;
   end;



 begin
 execute immediate 
 ' alter table sdo_index_metadata_table add 
      (SDO_DML_BATCH_SIZE number) ';
    exception when others then null;
   end;


 begin
 execute immediate 
 'alter table sdo_index_metadata_table add 
      (SDO_RTREE_ENT_XPND number) ';
    exception when others then null;
   end;


 begin
 execute immediate 
 'ALTER TABLE sdo_index_metadata_table 
  ADD (SDO_ROOT_MBR mdsys.sdo_geometry) ';
    exception when others then null;
   end;

end;
/
declare 
begin 
 begin
 execute immediate 
  ' ALTER TABLE sdo_index_metadata_table RENAME ' ||
  ' COLUMN sdo_root_mbr to old_sdo_root_mbr';
 exception when others then null;
 end;
        
 begin
 execute immediate 
  ' ALTER TABLE sdo_index_metadata_table ' || 
  ' ADD (SDO_ROOT_MBR mdsys.sdo_geometry default null)' ;
 exception when others then null;
 end;

 begin
 execute immediate 
  ' UPDATE sdo_index_metadata_table set sdo_root_mbr = old_sdo_root_mbr';
 exception when others then null;
 end;

 begin
 execute immediate 
  'ALTER TABLE sdo_index_metadata_table DROP(old_sdo_root_mbr)';
 exception when others then null;
 end;
end;
/
create or replace  view my_sdo_index_metadata as
 select SDO_INDEX_OWNER, SDO_INDEX_TYPE,
        SDO_LEVEL, SDO_NUMTILES, SDO_MAXLEVEL,
        SDO_COMMIT_INTERVAL, SDO_INDEX_TABLE,
        SDO_INDEX_NAME,  SDO_INDEX_PRIMARY,
	SDO_TSNAME, SDO_COLUMN_NAME, 
	SDO_RTREE_HEIGHT, SDO_RTREE_NUM_NODES,
        SDO_RTREE_DIMENSIONALITY, SDO_RTREE_FANOUT,
        SDO_RTREE_ROOT, SDO_RTREE_SEQ_NAME,
        SDO_FIXED_META, SDO_TABLESPACE,
        SDO_INITIAL_EXTENT, SDO_NEXT_EXTENT,
        SDO_PCTINCREASE, SDO_MIN_EXTENTS,
        SDO_MAX_EXTENTS, SDO_INDEX_DIMS,
        SDO_LAYER_GTYPE, SDO_RTREE_PCTFREE,
        SDO_INDEX_PARTITION, SDO_PARTITIONED,
        SDO_RTREE_QUALITY, SDO_INDEX_VERSION,  
        SDO_INDEX_GEODETIC, SDO_INDEX_STATUS, 
  	SDO_NL_INDEX_TABLE, 
        SDO_DML_BATCH_SIZE, SDO_RTREE_ENT_XPND,
	SDO_ROOT_MBR                                           
 from SDO_INDEX_METADATA_TABLE
 where sdo_index_owner = sys_context('userenv', 'CURRENT_SCHEMA');
create or replace  view user_sdo_index_metadata as
 select SDO_INDEX_OWNER, SDO_INDEX_TYPE,
        SDO_LEVEL, SDO_NUMTILES, SDO_MAXLEVEL,
        SDO_COMMIT_INTERVAL, SDO_INDEX_TABLE,
        SDO_INDEX_NAME,  SDO_INDEX_PRIMARY,
	SDO_TSNAME, SDO_COLUMN_NAME, 
	SDO_RTREE_HEIGHT, SDO_RTREE_NUM_NODES,
        SDO_RTREE_DIMENSIONALITY, SDO_RTREE_FANOUT,
        SDO_RTREE_ROOT, SDO_RTREE_SEQ_NAME,
        SDO_FIXED_META, SDO_TABLESPACE,
        SDO_INITIAL_EXTENT, SDO_NEXT_EXTENT,
        SDO_PCTINCREASE, SDO_MIN_EXTENTS,
        SDO_MAX_EXTENTS, SDO_INDEX_DIMS,
        SDO_LAYER_GTYPE, SDO_RTREE_PCTFREE,
        SDO_INDEX_PARTITION, SDO_PARTITIONED,
        SDO_RTREE_QUALITY, SDO_INDEX_VERSION,  
        SDO_INDEX_GEODETIC, SDO_INDEX_STATUS, 
  	SDO_NL_INDEX_TABLE, 
        SDO_DML_BATCH_SIZE, SDO_RTREE_ENT_XPND,
	SDO_ROOT_MBR                                           --- bug2719909
 from SDO_INDEX_METADATA_TABLE
 where
 (exists
   (select index_name from user_indexes
    where index_name=sdo_index_name and
          sdo_index_owner = sys_context('userenv', 'CURRENT_SCHEMA')));
create or replace  view all_sdo_index_metadata as
 select SDO_INDEX_OWNER, SDO_INDEX_TYPE,
        SDO_LEVEL, SDO_NUMTILES, SDO_MAXLEVEL, SDO_COMMIT_INTERVAL,
        SDO_INDEX_TABLE, SDO_INDEX_NAME, SDO_INDEX_PRIMARY, SDO_TSNAME,
        SDO_COLUMN_NAME, SDO_RTREE_HEIGHT, SDO_RTREE_NUM_NODES, 
        SDO_RTREE_DIMENSIONALITY, SDO_RTREE_FANOUT, SDO_RTREE_ROOT,
        SDO_RTREE_SEQ_NAME, SDO_FIXED_META, SDO_TABLESPACE,
        SDO_INITIAL_EXTENT, SDO_NEXT_EXTENT, SDO_PCTINCREASE,
        SDO_MIN_EXTENTS, SDO_MAX_EXTENTS, SDO_INDEX_DIMS, SDO_LAYER_GTYPE,
        SDO_RTREE_PCTFREE, SDO_INDEX_PARTITION, SDO_PARTITIONED,
        SDO_RTREE_QUALITY, SDO_INDEX_VERSION, SDO_INDEX_GEODETIC,
        SDO_INDEX_STATUS,	 
	SDO_NL_INDEX_TABLE, 
        SDO_DML_BATCH_SIZE, SDO_RTREE_ENT_XPND,
	SDO_ROOT_MBR                                           --- bug2719909
 from SDO_INDEX_METADATA_TABLE
 where
 (exists
   (select index_name from all_indexes
    where index_name=sdo_index_name and
          owner = sdo_index_owner));
declare
begin
  begin
  execute immediate 
    ' drop index sdo_idx_mdata_idx';
  exception when others then NULL;
  end;
end;
/
create index sdo_idx_mdata_idx on 
 sdo_index_metadata_table(sdo_index_owner, SDO_INDEX_NAME, SDO_INDEX_PARTITION);
grant select on user_sdo_index_metadata to public;
grant select on my_sdo_index_metadata to public;
grant select on all_sdo_index_metadata to public;
create or replace  public synonym my_sdo_index_metadata for 
mdsys.my_sdo_index_metadata;
create or replace  public synonym sdo_index_metadata for 
mdsys.user_sdo_index_metadata;
create  or replace public synonym user_sdo_index_metadata for 
mdsys.user_sdo_index_metadata;
create  or replace public synonym all_sdo_index_metadata for 
mdsys.all_sdo_index_metadata; 
commit;
create or replace  view user_sdo_index_info as
 select SDO_INDEX_NAME index_name,  table_owner, table_name,  
       REPLACE(sdo_column_name, '"') column_name,
       SDO_INDEX_TYPE, SDO_INDEX_TABLE, SDO_INDEX_STATUS
 from user_sdo_index_metadata,
     user_indexes
 where index_name = sdo_index_name;
grant select on user_sdo_index_info to public;
create or replace public synonym user_sdo_index_info for  
   mdsys.user_sdo_index_info;
create or replace  view all_sdo_index_info as
 select SDO_INDEX_OWNER, SDO_INDEX_NAME index_name,  table_owner, table_name,
       REPLACE(sdo_column_name, '"')  column_name,
       SDO_INDEX_TYPE, SDO_INDEX_TABLE, SDO_INDEX_STATUS
 from all_sdo_index_metadata,
     all_indexes
 where index_name = sdo_index_name and owner=sdo_index_owner;
grant select on all_sdo_index_info to public;
create or replace public synonym all_sdo_index_info for 
  mdsys.all_sdo_index_info;
declare
begin
   begin
   execute immediate 
   'DROP TABLE SDO_TXN_IDX_INSERTS ';
    exception when others then null;
   end;
                                                                                
   begin
   execute immediate 
   ' DROP TABLE SDO_TXN_IDX_DELETES ';
    exception when others then null;
   end;
                                                                                
   begin
   execute immediate 
   ' DROP TABLE SDO_TXN_IDX_EXP_UPD_RGN ';
    exception when others then null;
   end;
                                                                                
end;
/
CREATE GLOBAL TEMPORARY TABLE SDO_TXN_IDX_INSERTS 
  (SDO_TXN_IDX_ID	varchar2(32),
   RID			varchar2(24),
   START_1		number,
   END_1		number,
   START_2		number,
   END_2		number,
   START_3		number,
   END_3		number,
   START_4		number,
   END_4		number,
   primary key(sdo_txn_idx_id, rid)
);
grant insert, delete, update, select on mdsys.sdo_txn_idx_inserts to public;
create or replace  public synonym sdo_txn_idx_inserts 
  for mdsys.sdo_txn_idx_inserts;
commit;
CREATE GLOBAL TEMPORARY TABLE SDO_TXN_IDX_DELETES 
  (SDO_TXN_IDX_ID	varchar2(32),
   RID			varchar2(24),
   START_1		number,
   END_1		number,
   START_2		number,
   END_2		number,
   START_3		number,
   END_3		number,
   START_4		number,
   END_4		number,
   primary key(sdo_txn_idx_id, rid)
);
grant insert, delete, select on mdsys.sdo_txn_idx_deletes to public;
create or replace  public synonym sdo_txn_idx_deletes 
  for mdsys.sdo_txn_idx_deletes;
CREATE GLOBAL TEMPORARY TABLE SDO_TXN_IDX_EXP_UPD_RGN
  (SDO_TXN_IDX_ID       varchar2(32),
   RID                  varchar2(24),
   START_1              number,
   END_1                number,
   START_2              number,
   END_2                number,
   START_3              number,
   END_3                number,
   START_4              number,
   END_4                number,
   primary key(sdo_txn_idx_id, rid)
);
grant insert, delete, update, select on mdsys.sdo_txn_idx_exp_upd_rgn to public;
create or replace  public synonym sdo_txn_idx_exp_upd_rgn
  for mdsys.sdo_txn_idx_exp_upd_rgn;