统计Oracle数据库某个用户下面的对象个数

原创已于 2023-09-20 17:14:59 修改 · 553 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle

于 2022-08-07 16:33:00 首次发布

Oracle 专栏收录该内容

33 篇文章

订阅专栏

该博客分享了一段SQL查询，用于统计特定用户在Oracle数据库中拥有的表、视图、同义词、函数、存储过程、包、触发器、索引、约束和序列的数量。通过这个查询，可以快速获取数据库对象的概况。

统计某个用户下面的对象个数

　　包括表，视图，同义词，函数，存储过程，包，触发器，索引,约束，序列。

1. sql语句

SELECT

(SELECT COUNT(*) FROM USER_TABLES) AS TABLES,

(SELECT COUNT(*) FROM USER_VIEWS)AS VIEWS,

(SELECT COUNT(*) FROM USER_SYNONYMS)AS SYNONYMS,

(SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION') AS FUNCTIONS,

(SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE='PROCEDURE') AS PROCEDURES,

(SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE='PACKAGE') AS PACKAGES,

(SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER') AS TRIGGERS,

(SELECT COUNT(*) FROM USER_INDEXES) AS INDEXES,

(SELECT COUNT(*) FROM USER_CONSTRAINTS) AS CONSTRAINTS,

(SELECT COUNT(*) FROM USER_SEQUENCES) AS SEQUENCES

FROM DUAL

2. 执行结果