oracle删除数据库中的所有数据的拼接语句

最新推荐文章于 2022-03-22 14:05:25 发布

bo o ya ka

最新推荐文章于 2022-03-22 14:05:25 发布

阅读量154

点赞数

原文链接：http://www.cnblogs.com/doudou618/p/4369261.html

版权

create or replace function count_rows
/**
查询各表实际记录数
*/
(table_name in varchar2,
owner in varchar2 default null)
return number
authid current_user
IS
num_rows number;
stmt varchar2(2000);
begin
if owner is null then
stmt := 'select count(*) from "'||table_name||'"';
else
stmt := 'select count(*) from "'||owner||'"."'||table_name||'"';
end if;
execute immediate stmt into num_rows;
return num_rows;
end;

select table_name, count_rows(table_name) cnt, 'TRUNCATE TABLE ' || table_name || ' ;' || chr(10) || 'ANALYZE TABLE ' || table_name || ' ESTIMATE STATISTICS;' from user_tables where count_rows(table_name) > 0;

转载于:https://www.cnblogs.com/doudou618/p/4369261.html