SET LINESIZE 80;
SET SERVEROUTPUT ON FORMAT WRAP;
DECLARE
 db_name    VARCHAR2(30);
 db_version VARCHAR2(30);
 v_count    NUMBER := 0;
 v_metadata mdsys.sdo_geom_metadata_table%ROWTYPE;
 v_statement   VARCHAR2 (250);

 no_spatial EXCEPTION;

 CURSOR c_feat IS SELECT comp_name,status,version
   FROM dba_registry ORDER BY comp_id;
 CURSOR c_inval IS SELECT * FROM dba_objects
   WHERE status !='VALID' AND OWNER = 'MDSYS' ORDER BY object_type, object_name;
 CURSOR c_other_inval IS SELECT * FROM dba_objects
   WHERE status !='VALID' AND OWNER in ('SYS','PUBLIC') AND (OBJECT_NAME LIKE 'SDO_%' OR OBJECT_NAME LIKE 'ST_%' OR OBJECT_NAME LIKE '%_SDO_%') ORDER BY object_type, object_name;
 CURSOR c_spatial_indexes IS
    SELECT i.owner,i.index_name,i.table_owner,i.table_name,c.column_name,
     i.status,i.domidx_status,i.domidx_opstatus
   FROM dba_indexes i, dba_ind_columns c
   WHERE i.ityp_name = 'SPATIAL_INDEX' AND i.owner = c.index_owner
    AND i.index_name = c.index_name ORDER BY 1,2;
 CURSOR c_spatial_columns IS SELECT owner,table_name,column_name
  FROM dba_tab_columns
  WHERE data_type = 'SDO_GEOMETRY' AND owner != 'MDSYS' ORDER BY 1,2,3;

 PROCEDURE display_banner
 IS
 BEGIN
   DBMS_OUTPUT.PUT_LINE( '*************************************************************************');
 END display_banner;

 PROCEDURE count_spatial_data(the_schema VARCHAR2,the_table VARCHAR2,the_column VARCHAR2)
 IS
  TYPE my_cursor_type IS REF CURSOR;
  my_cursor  my_cursor_type;
  v_token    NUMBER := 0;
 BEGIN
  OPEN my_cursor FOR 'SELECT count(*) FROM '||the_schema||'.'||the_table||' WHERE '|| the_column || ' IS NOT NULL';
  FETCH my_cursor INTO v_token;
  DBMS_OUTPUT.PUT('.... ' || v_token || ' rows with spatial data in column ');
  CLOSE my_cursor;
  EXCEPTION
    WHEN OTHERS THEN RETURN;
  END count_spatial_data;

 PROCEDURE show_geom_metadata(the_schema VARCHAR2,the_table VARCHAR2,
                               the_column VARCHAR2,tab_owner VARCHAR2)
 IS
  TYPE my_cursor_type IS REF CURSOR;
  my_cursor  my_cursor_type;
  my_cursor2 my_cursor_type;
  dimname VARCHAR2(64);
  lb number;
  ub number;
  tolerance number;
  v_token    NUMBER := 0;
 BEGIN
  OPEN my_cursor FOR 'SELECT * FROM mdsys.sdo_geom_metadata_table WHERE sdo_owner='''||the_schema||''' AND sdo_table_name='''||the_table||''' AND sdo_column_name='''|| the_column || '''';
  FETCH my_cursor INTO v_metadata;
  IF my_cursor%NOTFOUND THEN
     DBMS_OUTPUT.PUT_LINE('...... This index does not have a VALID row in MDSYS.SDO_GEOM_METADATA_TABLE.');
     RAISE NO_DATA_FOUND;
  END IF;
  DBMS_OUTPUT.PUT ('.... Table = ' || tab_owner || '.' || v_metadata.sdo_table_name);
  DBMS_OUTPUT.PUT (', Column = ' || v_metadata.sdo_column_name);
  DBMS_OUTPUT.PUT_LINE(', SRID = ' || v_metadata.sdo_srid);
  DBMS_OUTPUT.PUT_LINE('.... DimInfo: ');
  OPEN my_cursor2 FOR
  'SELECT d.sdo_dimname,d.sdo_lb,d.sdo_ub,d.sdo_tolerance
   FROM mdsys.sdo_geom_metadata_table a, table(a.sdo_diminfo) d
   WHERE a.sdo_owner = ''' || v_metadata.sdo_owner ||
     ''' AND a.sdo_table_name = ''' || v_metadata.sdo_table_name ||
     ''' AND a.sdo_column_name = ''' || v_metadata.sdo_column_name || '''';
  LOOP
   FETCH my_cursor2 INTO dimname,lb,ub,tolerance;
   EXIT WHEN my_cursor2%NOTFOUND;
   DBMS_OUTPUT.PUT('...... Dim Name = ' || dimname);
   DBMS_OUTPUT.PUT(', Lower Bound = ' || lb);
   DBMS_OUTPUT.PUT(', Upper Bound = ' || ub);
   DBMS_OUTPUT.PUT_LINE(', Tolerance = ' || tolerance);
  END LOOP;
  CLOSE my_cursor2;
  CLOSE my_cursor;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN CLOSE my_cursor;
    WHEN OTHERS THEN RETURN;
  END show_geom_metadata;

BEGIN
 DBMS_OUTPUT.ENABLE(900000);
 SELECT name INTO db_name FROM v$database;
 SELECT version INTO db_version FROM v$instance;

 DBMS_OUTPUT.PUT_LINE( 'Oracle Locator/Spatial Health Check Tool              ' || TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
 DBMS_OUTPUT.PUT_LINE('.');
 display_banner;
 DBMS_OUTPUT.PUT_LINE('Database:');
 display_banner;
 DBMS_OUTPUT.PUT_LINE ('--> name:                    ' || db_name );
 DBMS_OUTPUT.PUT_LINE ('--> version:                 ' || db_version );
 DBMS_OUTPUT.PUT_LINE ('.');

 display_banner;
 DBMS_OUTPUT.PUT_LINE ( 'Database Components:');
 display_banner;
 FOR v_feat IN c_feat
 LOOP
   DBMS_OUTPUT.PUT_LINE( '--> ' || rpad(v_feat.comp_name, 35) || ' ' || rpad(v_feat.version, 10) || '   ' || rpad(v_feat.status, 10));
 END LOOP;
 DBMS_OUTPUT.PUT_LINE ('.');

 display_banner;
 IF dbms_registry.is_in_registry('SDO') = TRUE THEN
   DBMS_OUTPUT.PUT_LINE ('Oracle Spatial Status and Version:');
   display_banner;
   DBMS_OUTPUT.PUT_LINE('Oracle Spatial status is '||
    dbms_registry.status('SDO')||' and is at version '||
    dbms_registry.version('SDO'));
 ELSIF dbms_registry.is_in_registry('ORDIM') = TRUE THEN
    SELECT 1 INTO v_count FROM dba_users WHERE username = 'MDSYS';
   IF v_count = 1 THEN
     DBMS_OUTPUT.PUT_LINE ('Oracle Locator is installed as part of');
     display_banner;
     DBMS_OUTPUT.PUT_LINE(dbms_registry.comp_name('ORDIM')||' status is '||
        dbms_registry.status('ORDIM')||' and is at version '||
        dbms_registry.version('ORDIM'));
   END IF;
 ELSE
   RAISE no_spatial;
 END IF;
 DBMS_OUTPUT.PUT_LINE ('.');

 display_banner;
 DBMS_OUTPUT.PUT_LINE ( 'Invalid Objects in MDSYS Schema:');
 display_banner;
 FOR v_inval IN c_inval
 LOOP
   DBMS_OUTPUT.PUT_LINE( '.. MDSYS.' || rpad(v_inval.object_name,30) ||
     ' -  ' || v_inval.object_type );
   v_count := c_inval%rowcount;
 END LOOP;
 IF v_count = 0 THEN
   DBMS_OUTPUT.PUT_LINE('There are no Invalid objects in the MDSYS schema');
 END IF;
 DBMS_OUTPUT.PUT_LINE ('.');

 display_banner;
 DBMS_OUTPUT.PUT_LINE ( 'Invalid SDO-related Objects owned by SYS or PUBLIC:');
 display_banner;
 FOR v_other_inval IN c_other_inval
 LOOP
   DBMS_OUTPUT.PUT_LINE( '.. ' || v_other_inval.owner || '.' ||
    v_other_inval.object_name || ' -  ' || v_other_inval.object_type );
   v_count := c_other_inval%rowcount;
 END LOOP;
 IF v_count = 0 THEN
   DBMS_OUTPUT.PUT_LINE('There are no Invalid SDO-related Objects owned by SYS or PUBLIC');
 END IF;
 DBMS_OUTPUT.PUT_LINE ('.');

 display_banner;
 DBMS_OUTPUT.PUT_LINE ('Tables with Spatial Columns:');
 display_banner;
 v_count := 0;
 FOR v_spatial_columns IN c_spatial_columns LOOP
 DBMS_OUTPUT.PUT_LINE('.. ' || v_spatial_columns.owner ||
   '.' || v_spatial_columns.table_name || ' has ');
 count_spatial_data(v_spatial_columns.owner,v_spatial_columns.table_name,v_spatial_columns.column_name);
 DBMS_OUTPUT.PUT_LINE(v_spatial_columns.column_name);
 v_count := c_spatial_columns%rowcount;
 END LOOP;
 IF v_count = 0 THEN
   DBMS_OUTPUT.PUT_LINE('There are no Spatial columns');
 END IF;
 DBMS_OUTPUT.PUT_LINE ('.');

 display_banner;
 DBMS_OUTPUT.PUT_LINE ('Spatial Indexes:');
 display_banner;
 v_count := 0;
 FOR v_spatial_indexes IN c_spatial_indexes LOOP
 DBMS_OUTPUT.PUT('.. ' || v_spatial_indexes.owner ||
   '.' || v_spatial_indexes.index_name || ' is ');
 IF (v_spatial_indexes.status != 'VALID' OR
     v_spatial_indexes.domidx_status != 'VALID' OR
     v_spatial_indexes.domidx_opstatus != 'VALID') THEN
   DBMS_OUTPUT.PUT_LINE('INVALID');
   DBMS_OUTPUT.PUT_LINE('.... INDEX STATUS => '||v_spatial_indexes.status);
   DBMS_OUTPUT.PUT_LINE('.... DOMAIN INDEX STATUS => '||v_spatial_indexes.domidx_status);
   DBMS_OUTPUT.PUT_LINE('.... DOMAIN INDEX OPERATION STATUS => '||v_spatial_indexes.domidx_opstatus);
 ELSE
   DBMS_OUTPUT.PUT_LINE('VALID');
 END IF;
 -- get MDSYS.SDO_GEOM_METADATA_TABLE per index
 show_geom_metadata(v_spatial_indexes.owner,v_spatial_indexes.table_name,
    v_spatial_indexes.column_name,v_spatial_indexes.table_owner);
 DBMS_OUTPUT.PUT_LINE ('.');
 v_count := c_spatial_indexes%rowcount;
 END LOOP;
 IF v_count = 0 THEN
   DBMS_OUTPUT.PUT_LINE('There are no Spatial indexes');
   DBMS_OUTPUT.PUT_LINE ('.');
 END IF;
 
 display_banner;
 DBMS_OUTPUT.PUT_LINE ('Testing Spatial Index Creation:');
 display_banner;
 -- Create sp_healthcheck user
 SELECT COUNT (1) INTO v_count FROM dba_users
  WHERE username = 'SP_HEALTHCHECK';
 IF v_count != 0 THEN
  DBMS_OUTPUT.PUT_LINE ('..Dropping user SP_HEALTHCHECK');
  EXECUTE IMMEDIATE ('DROP USER sp_healthcheck CASCADE');
  DBMS_OUTPUT.PUT_LINE ('....User SP_HEALTHCHECK dropped successfully');
 END IF;
 DBMS_OUTPUT.PUT_LINE ('..Creating user SP_HEALTHCHECK');
 v_statement := 'GRANT connect,resource TO sp_healthcheck IDENTIFIED BY sp_healthcheck';
 EXECUTE IMMEDIATE (v_statement);
 EXECUTE IMMEDIATE ('GRANT create sequence to sp_healthcheck');
 EXECUTE IMMEDIATE ('GRANT create table to sp_healthcheck');
 DBMS_OUTPUT.PUT_LINE ('....User SP_HEALTHCHECK created successfully');
 -- Create Non-Geodetic index
 DBMS_OUTPUT.PUT_LINE ('..Testing creation of Non-Geodetic index');
 v_statement :=
   'CREATE TABLE sp_healthcheck.sp_hc_test (id NUMBER, geom MDSYS.SDO_GEOMETRY)';
 DBMS_OUTPUT.PUT_LINE('....Creating table SP_HC_TEST');
 EXECUTE IMMEDIATE(v_statement);
 DBMS_OUTPUT.PUT_LINE('....Inserting test data');
 v_statement :=
      'INSERT INTO sp_healthcheck.sp_hc_test VALUES (1,'
   || 'MDSYS.SDO_GEOMETRY(2003,NULL,NULL,'
   || 'MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),'
   || 'MDSYS.SDO_ORDINATE_ARRAY(1,1,5,7)))';
 EXECUTE IMMEDIATE(v_statement);
 v_statement :=
      'INSERT INTO mdsys.sdo_geom_metadata_table '
   || 'VALUES (''SP_HEALTHCHECK'',''SP_HC_TEST'',''GEOM'','
   || 'MDSYS.SDO_DIM_ARRAY('
   || 'MDSYS.SDO_DIM_ELEMENT(''X'', 0, 20, 0.005),'
   || 'MDSYS.SDO_DIM_ELEMENT(''Y'', 0, 20, 0.005)), NULL)';
 EXECUTE IMMEDIATE(v_statement);
 EXECUTE IMMEDIATE('COMMIT');
 v_statement :=
      'CREATE INDEX sp_healthcheck.sp_hc_idx '
   || 'ON sp_healthcheck.sp_hc_test(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
 DBMS_OUTPUT.PUT_LINE('....Creating non-geodetic index SP_HC_IDX');
 EXECUTE IMMEDIATE(v_statement);
 DBMS_OUTPUT.PUT_LINE ('....Non-geodetic index SP_HC_IDX created successfully');
 -- Create Geodetic index
 DBMS_OUTPUT.PUT_LINE ('..Testing creation of Geodetic index');
 v_statement :=
  'CREATE TABLE sp_healthcheck.sp_hc_test_geod(id NUMBER, geom MDSYS.SDO_GEOMETRY)';
 DBMS_OUTPUT.PUT_LINE('....Creating table SP_HC_TEST_GEOD');
 EXECUTE IMMEDIATE(v_statement);
 v_statement :=
      'INSERT INTO sp_healthcheck.sp_hc_test_geod VALUES (1,'
   || 'MDSYS.SDO_GEOMETRY(2003,8307,NULL,'
   || 'MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),'
   || 'MDSYS.SDO_ORDINATE_ARRAY(-90,45,-75,60)))';
 DBMS_OUTPUT.PUT_LINE('....Inserting test data');
 EXECUTE IMMEDIATE(v_statement);
 v_statement :=
      'INSERT INTO mdsys.sdo_geom_metadata_table '
   || 'VALUES (''SP_HEALTHCHECK'',''SP_HC_TEST_GEOD'',''GEOM'','
   || 'MDSYS.SDO_DIM_ARRAY('
   || 'MDSYS.SDO_DIM_ELEMENT(''Longitude'', -180, 180, 10),'
   || 'MDSYS.SDO_DIM_ELEMENT(''Latitude'', -90, 90, 10)), 8307)';
 EXECUTE IMMEDIATE(v_statement);
 EXECUTE IMMEDIATE('COMMIT');
 v_statement :=
      'CREATE INDEX sp_healthcheck.sp_hc_geod_idx '
   || 'ON sp_healthcheck.sp_hc_test_geod(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX';
 DBMS_OUTPUT.PUT_LINE('....Creating geodetic index SP_HC_GEOD_IDX');
 EXECUTE IMMEDIATE(v_statement);
 DBMS_OUTPUT.PUT_LINE ('....Geodetic index SP_HC_GEOD_IDX created successfully');
 DBMS_OUTPUT.PUT_LINE ('  ');
 DBMS_OUTPUT.PUT_LINE ('..Dropping user SP_HEALTHCHECK');
 EXECUTE IMMEDIATE ('DROP USER sp_healthcheck CASCADE');
 DBMS_OUTPUT.PUT_LINE ('....User SP_HEALTHCHECK dropped successfully');
 DBMS_OUTPUT.PUT_LINE ('  ');
 DBMS_OUTPUT.PUT_LINE ('Spatial Index Creation Test complete');
 DBMS_OUTPUT.PUT_LINE ('.');

 display_banner;

 EXCEPTION
  WHEN no_spatial THEN
   DBMS_OUTPUT.PUT_LINE ('!! Neither Oracle Spatial nor Oracle Locator is installed !!');
   display_banner;
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT('....');
   DBMS_OUTPUT.PUT_LINE (SQLERRM);
   display_banner;

END;
/
SET SERVEROUTPUT OFF