How to drop USERS tables including its datafiles

DROP USER OE CASCADE;
SELECT * FROM Dba_Data_Files;
SELECT * FROM dba_tablespaces;
CREATE TABLESPACE def
   DATAFILE 'D:\APP\ORACLE\PRODUCT\10.2.0\ORADATA\DBWARE\def01.DBF'
   SIZE 100M AUTOEXTEND ON
ALTER DATABASE DEFAULT TABLESPACE def;
ALTER TABLESPACE USERS OFFLINE;
drop tablespace users including contents and datafiles;

SELECT  'DROP '||TABLE_TYPE||' ' ||TABLE_NAME||' ;'  FROM dba_tables
WHERE TABLESPACE_NAME='USERS';

select t.owner, t.table_name, count(*)
from dba_tables t
join (
  select s.owner, s.segment_name, s.tablespace_name from dba_tables t join dba_segments s on s.owner = t.owner and s.segment_name = t.TABLE_NAME and s.segment_type = 'LOBSEGMENT' group by s.owner, s.segment_name, s.tablespace_name
    ) l
on t.owner = l.owner
and t.table_name = l.segment_name
group by t.owner, t.table_name
having count(*) > 1 ;


select * from dba_segments
where tablespace_name ='USERS'
and segment_type like '%LOB%';



ALTER USER oe IDENTIFIED BY oe ACCOUNT UNLOCK;


SELECT 'DROP '||TABLE_TYPE||' ' ||TABLE_NAME||' ;' from cat ORDER BY TABLE_TYPE;




declare
v_str varchar2(4000);
new_ts varchar2(100):='def';
BEGIN
for c1 in(select table_name, column_name, segment_name from user_lobs where segment_name IN(select segment_name from user_segments where tablespace_name='USERS'))
LOOP
v_str := 'alter table '||c1.table_name||' move tablespace '||new_ts||' lob('||c1.column_name||') store as (tablespace '||new_ts||')';
dbms_output.put_line(v_str);
END LOOP;
END;
/