达梦数据库查询表空间,及各表记录数

1.执行

CREATE OR REPLACE FUNCTION count_rows (
table_name IN varchar2,
owner IN varchar2 := NULL
)
RETURN number AUTHID current_user
AS
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;

2.查询记录数

select table_name, count_rows(table_name) nrows from user_tables

3.查询记录数以及各表占存储
select A.TABLE_NAME,A.nrows,B.OWNER ,B.KB
from (select table_name, count_rows(table_name) nrows from user_tables group by TABLE_NAME) as A
left join (select owner,segment_name,sum(bytes)/1024 as KB from dba_segments where segment_type='TABLE'group by owner,segment_name) as B
on A.TABLE_NAME=B.SEGMENT_NAME
where B.OWNER='CECPORTAL' order by nrows desc;
————————————————
版权声明：本文为CSDN博主「navy_1024」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_41223299/article/details/105136026