Segment size collector (all segments — aggregate)


Job details

Name: Segment size collector (all segments — aggregate)
Platform: Oracle
Category: Capacity
Description: Collects size and number totals on all segment types per segment-owner and tablespace_name.
Long description: Task collects size and number total statistics on Oracle 9i for all – (aggregate) type of segments per segment-owner and tablespace_name.
Version: 2.1
Default schedule: 50 6 1,5 *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘oracle’]/.[hasengine=‘YES’ & (version like ‘8%’ version like ‘9%’)]

Parameters

Name Default value Description

Job Summary

SELECT * FROM DBA_SEGMENTS;
SELECT * FROM DBA_FREE_SPACE;
SELECT * FROM DBA_TABLESPACES;

Detailed Description and Dependencies

The job “Segment size collector (all segments – aggregate)” is implemented to gather detailed information about database segments using a variety of views and tables that are custom-created as part of the job setup. Below are notable SQL implementations and their dependencies involved:

Dependencies Overview:

SQL Implementation Specifics:

```sql
CREATE OR REPLACE PROCEDURE Segment_Size_Aggregate(taskid IN NUMBER) IS […] — Main procedure for aggregating segment size
CREATE TABLE DBW_ALL_SEGM […] — Main table holding the segment data
CREATE OR REPLACE VIEW dbv_space_used AS […] — View to calculate used space
```

Execution and Scheduling

Automated and Manual Monitoring

While dbWatch Control Center automates this job to reduce admin overhead, database administrators can still manually query related views and tables to verify data or perform ad-hoc checks based on current database performance or issues needing immediate attention.

Impact and Cleanup

Security and Access Control