Assessing Object Ownership and Storage Consumption in Oracle Database

calendar Nov 14, 2023 · 3 min read · schema space used space usage  ·
Share on: linkedin copy

Shows the distribution of objects and data across all schemas.

Which schemas are taking up all of the space?

SQL Code

 1set pages 999
 2col "size MB" format 999,999,999
 3col "Objects" format 999,999,999
 4select	obj.owner "Owner"
 5,	obj_cnt "Objects"
 6,	decode(seg_size, NULL, 0, seg_size) "size MB"
 7from 	(select owner, count(*) obj_cnt from dba_objects group by owner) obj
 8,	(select owner, ceil(sum(bytes)/1024/1024) seg_size
 9	from dba_segments group by owner) seg
10where 	obj.owner  = seg.owner(+)
11order	by 3 desc ,2 desc, 1
12/

Sample Oracle Output:

 1Owner                               Objects      size MB
 2------------------------------ ------------ ------------
 3ANDY                                    219       46,323
 4SYS                                  22,923          706
 5SYSMAN                                1,341           49
 6XDB                                     680           49
 7MDSYS                                   896           33
 8SYSTEM                                  454           23
 9OLAPSYS                                 720           16
10WMSYS                                   242            7
11CTXSYS                                  339            5
12EXFSYS                                  281            4
13DBSNMP                                   46            2
14ORDSYS                                1,720            1
15DMSYS                                   189            1
16OUTLN                                     8            1
17SCOTT                                     6            1
18TSMSYS                                    3            1
19PUBLIC                               19,990            0
20ORDPLUGINS                               10            0
21
2218 rows selected.
23
24SQL>

Purpose:

  • To investigate database space usage at the owner level, providing insights into which owners are consuming the most storage space and how many objects they own. This aids in identifying potential space issues, optimizing storage allocation, and planning for future growth.

Breakdown:

Formatting Output:

*   `set pages 999`: Adjusts display settings to accommodate potentially large results.
*   `col "size MB" format 999,999,999`, `col "Objects" format 999,999,999`: Formats columns for readability and large numbers.

Retrieving Data from Views:

*   **Owner and Object Count:** `(select owner, count(*) obj_cnt from dba_objects group by owner) obj`: Retrieves owner names and counts their associated objects.
*   **Owner and Segment Size:** `(select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) seg`: Calculates total segment space usage in megabytes for each owner.

Joining and Handling Nulls:

*   `where obj.owner = seg.owner(+)`: Outer joins the two result sets, ensuring all owners are included even if they have no segments (nulls).
*   `decode(seg_size, NULL, 0, seg_size) "size MB"`: Replaces null segment sizes with 0 for owners without segments.

Ordering Results:

*   `order by 3 desc, 2 desc, 1`: Prioritizes owners based on:
    
    *   Total segment size in descending order (highest usage first).
    *   Object count in descending order (most objects first).
    *   Owner name alphabetically.

Key Points:

  • Owner-Centric View: Focuses on space usage at the owner level, pinpointing potential storage hogs or areas for optimization.
  • Total Segment Size: Shows overall space consumption in megabytes, aiding in capacity planning and identifying potential space constraints.
  • Object Counts: Provides context for space usage by indicating the number of objects owned, helping assess data distribution and potential fragmentation.
  • Handles Owners Without Segments: Ensures inclusion of all owners, even those with zero space usage.
  • Prioritization: Orders results to highlight areas that might require attention for space management.

Insights and Explanations:

  • Space Management:

    • Helps identify owners consuming excessive space, potentially indicating opportunities for optimization or data archiving.
    • Guides storage allocation decisions and capacity planning.

  • Troubleshooting:

    • Can be used to investigate space-related issues, such as unexpected growth or storage constraints.

  • Security Auditing:

    • Helps monitor object creation and space usage patterns to detect potential unauthorized activity or data breaches.

  • Customization:

    • Can be modified to filter for specific owners, object types, or storage thresholds based on analysis needs.

  • Integration: Consider incorporating this code into regular database monitoring and reporting processes for proactive space management.

Posts in this series