Hi,
I am trying to use the script below to analyze the data consumed for a table since I am planning the storage capacity for a datawarehouse. Using this value(for every table), I will then be able to plan data for 18 months.
I am writing a script to monitor the database usage from a specific point in time.
Ref:
https://docs.oracle.com/database/121/ARPLS/d_space.htm#ARPLS68110 Grateful if you can help with the script
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
v_object_owner VARCHAR2(30) := UPPER('&1');,
v_object_name VARCHAR2(30) := UPPER('&2');,
v_object_type VARCHAR2(30) := UPPER('&3');,
v_partition_name VARCHAR2(30) DEFAULT NULL,
v_start_time TIMESTAMP DEFAULT NULL,
v_end_time TIMESTAMP DEFAULT NULL,
v_interval DSINTERVAL_UNCONSTRAINED DEFAULT NULL,
v_skip_interpolated VARCHAR2(30) DEFAULT 'FALSE',
v_timeout_seconds NUMBER DEFAULT NULL,
v_single_datapoint_flag VARCHAR2(30) DEFAULT 'TRUE') ,
v_timepoint TIMESTAMP,
v_space_usage NUMBER,
v_space_alloc NUMBER,
v_quality VARCHAR(20))
DBMS_SPACE.OBJECT_GROWTH_TREND (
object_owner =>v_ object_owner ,
object_name =>v_object_name ,
object_type => v_ object_type ,
partition_name => v_partition_name ,
start_time => v_start_time ,
end_time => v_end_time ,
interval => v_intercal ,
skip_interpolated => v_skip_interpolated ,
timeout_seconds => v_timeout_seconds ,
single_datapoint_flag => v_single_datapoint_flag) ;
RETURN object_growth_trend_table PIPELINED;
DBMS_OUTPUT.PUT_LINE('object_name :' || v_object_name );
DBMS_OUTPUT.PUT_LINE('object_type :' || v_object_type);
DBMS_OUTPUT.PUT_LINE('object_owner :' || v_object_owner);
DBMS_OUTPUT.PUT_LINE('start time :' || v_start_time );
DBMS_OUTPUT.PUT_LINE('end_time :' || v_end_time );
DBMS_OUTPUT.PUT_LINE('The time at which the statistic was recorded :' || v_timepoint);
DBMS_OUTPUT.PUT_LINE('The space used by data :' || v_space_usage);
DBMS_OUTPUT.PUT_LINE('The size of segment including overhead and unused space :' || v_space_alloc);
TYPE object_growth_trend_row IS RECORD(
timepoint TIMESTAMP,
space_usage NUMBER,
space_alloc NUMBER,
quality VARCHAR(20));
END;
/Thanks,
Roshan
You'll be pleased to know its much easier than that.
This is a pipelined function, so you can query it like a table
SQL> select *
2 from
3 DBMS_SPACE.OBJECT_GROWTH_TREND (
4 object_owner=>'SCOTT',
5 object_name=>'EMP',
6 object_type=>'TABLE');
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- ----------
10-SEP-18 02.16.51.356000 PM 8192 65536 GOOD
Tutorial for pipelined functions here
https://livesql.oracle.com/apex/livesql/file/tutorial_GS1U5KY647O601AZ0CGDQTKJX.html