WITH OBJECT_COUNT AS ( SELECT OWNER, OBJECT_TYPE, COUNT(*) "NUM_OBJECTS" FROM DBA_OBJECTS GROUP BY OWNER,OBJECT_TYPE ), SPACE_USAGE AS ( SELECT SEGMENT_TYPE, SUM(BYTES) "BYTES" FROM DBA_SEGMENTS GROUP BY SEGMENT_TYPE ) SELECT O.OWNER,O.OBJECT_TYPE,O.NUM_OBJECTS, ROUND(U.BYTES/1024/1024) "MB" FROM OBJECT_COUNT O LEFT OUTER JOIN SPACE_USAGE U ON O.OBJECT_TYPE = U.SEGMENT_TYPE ORDER BY 1 ASC;
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
Friday, December 13, 2013
SQL statement to list all database objects and their sizes
Use the WITH clause to create two different subqueries that you can reference later in the query:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment