ORACLE sql查询用户/表/数据/外键/主键/索引

查询用户数量

--  查询用户数量
SELECT COUNT(*) AS user_count FROM DBA_USERS;

SELECT COUNT(*) AS user_count FROM USER_TABLES;

查询表数量

--  查询表数量
SELECT OWNER, COUNT(*) AS table_count
FROM DBA_TABLES
WHERE OWNER IN ('RBCC_ICO_ADMIN', 'DPDB_CONFIDENTIAL', 'DPDB_INTERNAL')
GROUP BY OWNER;

检查数据记录数量

--  检查数据记录数量
SELECT OWNER, SUM(NUM_ROWS) AS NUM_ROWS
FROM DBA_TABLES
WHERE OWNER IN ('xxx', 'xxx', 'xxx')
GROUP BY OWNER;

检查约束数量: 主键/外键

--  检查约束数量: 主键/外键
SELECT OWNER, COUNT(1) COUTS
FROM all_constraints
WHERE OWNER IN ('xxx', 'xxx', 'xxx')
    AND constraint_type IN ('P', 'R')
GROUP BY OWNER;

检查约束数量: 索引

--  检查约束数量: 索引
SELECT OWNER, COUNT(1) COUTS
FROM all_indexes
WHERE OWNER IN ('xxx', 'xxx', 'xxx')
GROUP BY OWNER;