SQL> select 'drop table '||OWNER||'.'||TABLE_NAME||';' from DBA_TABLES where TABLE_NAME like '%IMPORT_FULL%';

declare
     l_imptablename varchar2(64) := 'SYS_IMPORT_FULL_01';
     l_imptableowner varchar2(12) := '';
     l_cnt simple_integer := 0;
     l_wrongTZ simple_integer := 41;
     l_correctTZ simple_integer := 35;
begin
   while (l_wrongTZ = l_wrongTZ)
   loop
   begin
    select count(*) into l_cnt from DBA_TABLES where TABLE_NAME=l_imptablename;   
    if (l_cnt = 1)
    then
      select OWNER into l_imptableowner from DBA_TABLES where TABLE_NAME=l_imptablename; 
       while (l_wrongTZ = l_wrongTZ)
       loop
        begin
          execute immediate 'select count(*) from '||l_imptableowner||'.'||l_imptablename||' where property='||to_char(l_wrongTZ) into l_cnt;
          if (l_cnt = 1) 
          then begin
                 execute immediate 'update '||l_imptableowner||'.'||l_imptablename||' set property='||to_char(l_correctTZ)||' where property='||to_char(l_wrongTZ);
                 commit;
                 dbms_output.put_line('TZ info has been updated in '||upper(l_imptableowner)||'.'||upper(l_imptablename));
                 goto l_exit;
                 end;
           else
              dbms_lock.sleep(0.1);
          end if;
        end;
       end loop;
    end if;
  end;
  end loop;	
  <<l_exit>>
  l_wrongTZ:=l_wrongTZ;
end;

declare
	l_object_id simple_integer:=0;
	l_table_name string(128):='';
	l_tstz_presence char(1):='';
	cursor l_cursor is 
		 select OBJECT_ID, OBJECT_NAME 
	           from dba_objects 
		  where OWNER='schema-name-is-here' 
		    and OBJECT_TYPE='TABLE'
	  	  order by OBJECT_NAME;
		
begin
	open l_cursor;
	loop
	fetch l_cursor into l_object_id, l_table_name;
	exit when l_cursor%NOTFOUND;
	l_tstz_presence:=sys.dbms_metadata_util.has_tstz_cols(l_object_id);
        if (l_tstz_presence) = 'Y' 
	then
	    dbms_output.put_line(l_table_name||':'||l_tstz_presence);
	end if;	
	end loop;
	close l_cursor;
end;

while (l_wrongTZ = l_wrongTZ)

DECLARE

  l_imptableowner varchar2(100);

begin
  
  SELECT owner 
  INTO l_imptableowner 
  FROM dba_tables
  WHERE table_name = 'SYS_IMPORT_FULL_01';
  
  UPDATE SYS_IMPORT_FULL_01 
  SET property = '35' 
  WHERE property = '41';
  
  IF sql%rowcount > 0 THEN
    dbms_output.put_line('TZ info has been updated in '||upper(l_imptableowner)||'.SYS_IMPORT_FULL_01';
  END IF;
  
END;

begin
  
  FOR rec IN (SELECT distinct table_name 
              FROM dba_tab_cols 
              where owner = '<owner>' 
              and data_type like 'TIMESTAMP% WITH TIME ZONE') LOOP

    dbms_output.put_line(rec.table_name);

  end loop;

end;