表很多，查看表的数量

表太多了，查看表的情况:
select substr(object_name, 0,4), count() from user_objects where object_type = 'TABLE' group by substr(object_name, 0,4) order by count() desc;
select count(0) from user_tables where table_name like 'FYDW%';
select count(0) from user_tables where table_name like 'DWBM%'
select count(0) from user_tables where table_name like 'CODE%';

FYDW临时表
--创建存储过程删除7天前创建的FYDW临时表
（正常表3000多个，
先切换到用户下面再查
select count() from user_tables;
select count() from user_objects a,user_tables b where a.OBJECT_NAME=b.TABLE_NAME and b.TEMPORARY='Y' and created < sysdate - 7;
select count(*) from user_objects where object_type = 'TABLE' and temporary ='Y' and created <sysdate - 7'😉
select * from user_objects where object_type = 'TABLE' and temporary ='Y' and created <sysdate - 7'😉

create or replace procedure p_drop_fydw as
v_tab_name varchar(30);
cursor cur_drop_temq is
select object_name
from user_objects
where object_type = 'TABLE'
and object_name like 'FYDW%' and temporary = 'Y'
and created < sysdate - 7;
begin
open cur_drop_temq;
loop
fetch cur_drop_temq
into v_tab_name;
exit when cur_drop_temq%notfound;
execute immediate 'drop table ' || v_tab_name || ' purge';
end loop;
close cur_drop_temq;
end;
/

create or replace procedure p_drop_fydw as
v_tab_name varchar(30);
cursor cur_drop_temq is
select object_name
from user_objects
where object_type = 'TABLE'
and object_name like 'TEMQ_%' and temporary = 'Y'
and created < sysdate - 7;
begin
open cur_drop_temq;
loop
fetch cur_drop_temq
into v_tab_name;
exit when cur_drop_temq%notfound;
execute immediate 'drop table ' || v_tab_name || ' purge';
end loop;
close cur_drop_temq;
end;
/

exec STJT.p_drop_fydw