One of the “Mentor Dojo” students got this request from his manager.
They wanted to estimate the growth of a all the objects of a certain tablespace or schema in one month.
My first answer was “well, let’s assume the last month growth”, but they didn’t keep this information. Also, It’s possible that you may need something like this to build an audit report of a database and give your customers information about their systems.
So, we started to build a procedure to generate a segment growth simulation in a particular Oracle database using the PL/SQL package DBMS_SPACE.
The idea is to run the segment_growth_trend procedure for all segments in a specified schema, and store it in a table, so we may query the top segments with hightest estimation growth, and also use it in the future. As it’s based on the AWR information, we may use all this information in future to see the tablespace evolution by segment.
So, the first thing to do is testing the DBMS_SPACE package to check we have all needed privileges and the OBJECT_GROWTH_TREND procedure works as expected:
set serveroutput on begin for x in (select * from table(dbms_space.OBJECT_GROWTH_TREND ('&USER','&TABLE','TABLE'))) loop dbms_output.put_line (x.TIMEPOINT||x.SPACE_USAGE|| x.SPACE_ALLOC||x.QUALITY); end loop; end; /
You just have to pick up a table of your choice.
The output, as you may see in the procedure, is an entire table of size values for the table you entered. You also may have a first line with an error like this one, but It’s something ignorable as per Oracle Support note “SELECT Statement Using DBMS_SPACE.OBJECT_GROWTH_TREND Fails with ORA-14551” (Doc ID 1094627.1), it’s a bug, unsolved, it’s purely cosmetic and can be safely ignored.
The output will be something like this:
EXCEPTION in chrow processing - code: -1031 msg: ORA-01031: privilegios insuficientes 21/12/22 15:37:20,24400018352983145728INTERPOLATED 22/12/22 15:37:20,24400018352983145728INTERPOLATED 23/12/22 15:37:20,24400018352983145728INTERPOLATED 24/12/22 15:37:20,24400018352983145728INTERPOLATED 25/12/22 15:37:20,24400018352983145728INTERPOLATED 26/12/22 15:37:20,24400018352983145728INTERPOLATED 27/12/22 15:37:20,24400018352983145728PROJECTED 28/12/22 15:37:20,24400018352983145728PROJECTED 29/12/22 15:37:20,24400018352983145728PROJECTED 30/12/22 15:37:20,24400018352983145728PROJECTED 31/12/22 15:37:20,24400018352983145728PROJECTED
PL/SQL procedure successfully completed.
So, the day I wrote this post is 26 December 2022, so the values until today are “INTERPOLATED” and the future values are “PROJECTED”. You can see also the space used and the space allocated for the segment.
Now it’s time to run the procedure that will get the initial, current and future values for this segment, and store the results in a table.
So, first step is the table creation:
CREATE TABLE CAPACITY_SEGMENTS ( OWNER VARCHAR2(30), SEGMENT_NAME VARCHAR2(30), SEGMENT_TYPE VARCHAR2(30), PARTITION_NAME VARCHAR2(30), TABLESPACE_NAME VARCHAR2(30), SEGMENT_SIZE NUMBER, CAPACITY_LAST_MONTH NUMBER, CURRENT_CAPACITY NUMBER, FUTURE_CAPACITY NUMBER, SEGMENT_PCT_OCCUPIED NUMBER, INSTANT_MEASURE DATE) SEGMENT CREATION IMMEDIATE;
Other than the objects attributes like OWNER, NAME, TYPE, TABLESPACE and SIZE, in this table we will also store interesting values to launch our estimations in the future, such as CURRENT_CAPACITY paired with INSTANT_MEASURE, and check if it fits also with FUTURE_CAPACITY over time.
Keep in mind that this projection is on 5 days from the run time, so you may extrapolate those trends to months or years as you like (multiply x6 to get a month, for example).
And then, you can run the procedure CHECK_SEGMENT_GROWTH providing the schema’s owner you want to estimate objects growth. The procedure will store the results in the CAPACITY_SEGMENTS recently created table.
All those scripts are in the Café Database GitHub here:
Here you will find three scripts:
–> Use this one to test the DBMS_SPACE.OBJECT_GROWTH_TREND procedure
–> This one creates the table CAPACITY_SEGMENTS to store the trend results
–> This is the procedure to calculate the estimated growth and store results.
And one last thing, if you find issues running the DBMS_SPACE package inside a PL/SQL block, check privileges or try to run them under a schema with full privileges over the objects you are going to analyze.
Dear Javier, I will test this three scripts on my own database 19c that i have in my own server Oracle Linux installed in my house. I couldn’t test those yet because i discovered an errors ora-19804 and ora-19809 in my DataBase …. once i’ve figured out these errors in alert, I could today solve increasing the db_recovery_file_dest_size parameter. My apologies
Well, for those ora-19804 and ora-19809 definitively you may find really useful the post about defrag an entire tablespace. Maybe you can save up some space there.
Thanks for your comment, kind regards,