常用查询

用户名和schema对应

SELECT
B.USERNAME AS 用户名,
A.NAME AS SCHEMA名称
FROM SYSOBJECTS A
JOIN DBA_USERS B ON A.PID = B.USER_ID
WHERE A.TYPE$ = 'SCH' and B.USERNAME NOT IN ('SYS', 'SYSTEM', 'SYSAUDITOR', 'SYSSSO') -- 排除系统用户
ORDER BY B.USERNAME;

监控表空间使用情况
SELECT t.tablespace_name,
t.total_space AS "TOTAL_SPACE",
t.total_space - f.free_space AS "USED_SPACE",
f.free_space AS "FREE_SPACE",
ROUND((t.total_space - f.free_space) / t.total_space * 100, 2) || '%' AS "USED_PECENT"
FROM (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_space
FROM dba_data_files
GROUP BY tablespace_name) t
JOIN (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_space
FROM dba_free_space
GROUP BY tablespace_name) f
ON t.tablespace_name = f.tablespace_name;