Building Tablespace Growth Report from Grid Data

Use SQL Query below at Grid sysman database to locate growth of tablespaces over time. The returned results data could be used under EXCEL to generate graphs.

SELECT   target_name,
 KEY_VALUE,
 ROLLUP_TIMESTAMP,
 METRIC_COLUMN,
 sample_count,
 ROUND (AVERAGE / 1000) Allocted_GB
 FROM   sysman.MGMT$METRIC_DAILY
 WHERE       metric_name = 'tbspAllocation'
 AND LOWER (TARGET_NAME) LIKE '%Oracle_db_name%'
 AND KEY_VALUE = 'DATA'
 AND LOWER (TARGET_TYPE) = 'oracle_database'
 AND Metric_column = 'spaceAllocated'
ORDER BY   ROLLUP_TIMESTAMP DESC;

TARGET_NAME    KEY_VALUE    ROLLUP_TIMESTAMP    METRIC_COLUMN    SAMPLE_COUNT    ALLOCTED_GB
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    31/10/2010    spaceAllocated    1    395
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    19/10/2010    spaceAllocated    1    394
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    18/10/2010    spaceAllocated    1    394
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    21/09/2010    spaceAllocated    1    378
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    09/08/2010    spaceAllocated    1    371
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    25/08/2010    spaceAllocated    1    365
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    14/08/2010    spaceAllocated    1    357
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    08/07/2010    spaceAllocated    1    353
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    31/07/2010    spaceAllocated    1    349
Prod.oracle.db.uk       PROD_ARCHIVE_DATA_001    26/06/2010    spaceAllocated    1    330

Following attribute_types are available for target_type=’host’

CPUUsage
DiskActivity
EMDUploadStats
FileMonitoring
Filesystems
Load
Network
PagingActivity
ProcessInfo
ProgramResourceUtilization
Response
Swap_Area_Status
ThreadCPUUsageSummary
TotalDiskUsage
host_storage_history
proc_zombie

Following attribute_types are available for target_type=’oracle_database’

DATABASE_SIZE
Database_Resource_Usage
DeferredTrans
OCMInstrumentation
Recovery_Area
Response
UserAudit
UserBlock
UserLocks
alertLogStatus
all_invalid_objects
archFull
dbjob_status
dumpFull
ha_flashrecovery
ha_recovery
instance_efficiency
instance_throughput
invalid_objects
problemTbsp
segment_advisor_count
service
sga_pool_wastage
sql_response
streams_processes_count
system_response_time_per_call
tbspAllocation
wait_bottlenecks
wait_sess_cls