Oracle 统计各张表的记录数

第一步：新增count_rows 函数

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;

第二步：调用count_rows 函数，统计各种表的记录数

select table_name, count_rows(table_name) nrows from user_tables 
order by nrows desc;

效果展示: