Friday 10 July 2015

Clear SDE schema from unwanted Spatial Tables, Search in all fields in all oracle schemas

delete from column_registry
where OWNER in ('FLEET_V1','GIS_B','ATLAS_V1', 'GIS_V1', 'GIS','gis', 'GIS_CODING', 'GIS_TEST', 'GIS_C', 'gis_c', 'GIS_D', 'gis_d'  );

delete from column_registry
where OWNER ='GIS_V1' AND table_name = 'LT_STATIONS_GIS';
commit;

delete from GEOMETRY_COLUMNS
where F_TABLE_SCHEMA in ('FLEET_V1','GIS_B','ATLAS_V1', 'GIS_V1', 'GIS','gis', 'GIS_CODING', 'GIS_TEST', 'GIS_C', 'gis_c', 'GIS_D', 'gis_d'  );

delete from GEOMETRY_COLUMNS
where F_TABLE_SCHEMA ='GIS_V1' and f_table_name='LT_STATIONS_GIS';

delete from LAYERS
where OWNER in ('FLEET_V1','GIS_B','ATLAS_V1', 'GIS_V1', 'GIS','gis', 'GIS_CODING', 'GIS_TEST', 'GIS_C', 'gis_c', 'GIS_D', 'gis_d' );

delete from LAYERS
where OWNER ='GIS_V1' AND table_name = 'LT_STATIONS_GIS';

delete from GDB_ITEMS
where NAME like 'FLEET_V1%' or NAME like'GIS_B%' or NAME like'ATLAS_V1%' or  NAME like'GIS_V1%' or NAME like'GIS%' or NAME like'GIS_CODING%'
or NAME like'gis_c%' or NAME like'gis_d%' or NAME like'gis%' or NAME like'GIS_D%' ;

delete from GDB_ITEMS
where NAME ='GIS_V1.LT_STATIONS_GIS';

COMMIT;

DROP USER username CASCADE;


To search in all fields in oracle schemas 
DECLARE
  match_count integer;
  v_search_string varchar2(4000) := 'fff';
BEGIN  
  FOR t IN (SELECT owner, table_name, column_name FROM all_tab_columns) LOOP   
    EXECUTE IMMEDIATE    
      'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
      ' WHERE '||t.column_name||' = :1'   
       INTO match_count  
      USING v_search_string; 
    IF match_count > 0 THEN 
      dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
    END IF; 
  END LOOP;
END;

No comments:

Post a Comment