–1 在当前用户创建一次事务级临时表
CREATE GLOBAL TEMPORARY TABLE T_SUM
(OWNER VARCHAR(50),
TAB_NAME VARCHAR(128),
ROW_NUM INT) ON COMMIT DELETE ROWS;
–2 查询当前登录用户,每张表的数据量
COMMIT;
DECLARE
T_SUM int;
SQL_TEXT varchar(128);
BEGIN
T_SUM:=0;
FOR T IN
(
SELECT OWNER,TABLE_NAME FROM ALL_TABLES WHERE OWNER=(SELECT USER)
)
LOOP
SQL_TEXT:='SELECT COUNT(1) FROM '||T.OWNER||'.'||T.TABLE_NAME;
EXECUTE IMMEDIATE SQL_TEXT into T_SUM;
INSERT INTO T_SUM VALUES (T.OWNER, T.TABLE_NAME, T_SUM);
END LOOP;
SELECT * FROM T_SUM ORDER BY TAB_NAME DESC;
END;