![oracle database size oracle database size](https://infohub.delltechnologies.com/static/media/81261d00-b245-5aee-ad79-ffce958635ff/8b0c8486-59d5-4777-aa39-8e4ae677a433/out/2006.024.png)
Tbsp_Used_size - (LAG(Tbsp_Used_size,1,Tbsp_Used_size) OVER (PARTITION BY TBSP_NAME ORDER BY to_date(MONTH,'YYYY Month'))) AS "Growth" WHEN LAG(tbsp_name,1,0) OVER (PARTITION BY TBSP_NAME ORDER BY to_date(MONTH,'YYYY Month')) Where (col_date = LAST_DAY(col_date) or col_date=(select max(col_date) from tbsp_hist_usage)) Select to_char(col_date,'YYYY Month') Month,trunc(sum(usedsize_mb)/1024,2) Db_size Repeat_interval => 'FREQ=WEEKLY', /* every thursday at 3:00 AM */Ĭomments => 'Job that logs daily tablespace usage in the last week - runs every thursday at 3:00 AM ') Īfter about 3 months now I can run some reports on this table and collect meaningful data that can be used for capacity planning, monitor data allocations and further check for possible fragmentation, un-proper usage of space, etc.ĭB_USED_G - (LAG(DB_USED_G,1,DB_USED_G) OVER (ORDER BY COL_DATE)) AS DB_GROWTH_Gĭb_size - (LAG(Db_size,1,Db_size) OVER (ORDER BY to_date(Month,'YYYY Month'))) as Monthly_Growth_GB The procedure is scheduled to run every Thursday at 03:00 AM through a DBMS_SCHEDULER job Raise_application_error(-20001,'An error was encountered - '||SQLCODE||' ERROR '||SQLERRM) WHERE TO_CHAR(end_interval_time, 'HH24') = '00'įETCH c1 INTO tbsp_name,size_mb,usedsize_mb,col_date Procedure to insert usage data for the last 7 daysĬREATE OR REPLACE PROCEDURE InsertTbspUsageġ) size_mb, ROUND * 8192 / 1024 / 1024, 1) usedsize_mb, s.end_interval_time snap_time FROM sys.dba_hist_tbspc_space_usage tsu, sys.dba_hist_snapshot s, sys.dba_hist_tablespace tbsp WHERE 1 =1 AND tsu.snap_id = s.snap_id AND tbsp.ts# = tsu.tablespace_id AND s.snap_id IN For this I have created a table to hold the data, a procedure which a scheduled to run once a week :ĬONSTRAINT ck_tbsp_date_uniq UNIQUE (tbsp_name,col_date) This being the case I have decided to create a table and put a job to collect daily tablespace usage once a week in order to be sure that even if the database will be moved to another Grid Control repository or something else happens I will still have my data of interest at least for the future. ORDER BY TO_DATE(TO_CHAR(creation_time, 'YYYY Month'),'YYYY Month') GROUP BY TO_CHAR(creation_time, 'YYYY Month') SELECT TO_CHAR(creation_time, 'YYYY Month') "Month",
![oracle database size oracle database size](https://s3.studylib.net/store/data/008643727_1-b88a9144bc6235726707cd2186c5c762-768x994.png)
SELECT TO_CHAR(end_interval_time,'dd-mm-yyyy') snap_time, Historically daily tablespace size from dba_hist_tbspc_space_usage (AWR table)
![oracle database size oracle database size](https://docs.oracle.com/cd/B16351_01/doc/server.102/b14196/purchase_orders.gif)
I have therefore only been able to provide the usage for the latest days (AWR retention period) along with the datafile increase history over time using the following 2 statements: Well I have immediately found out that these won’t work as the tablespace allocation metric was not collected until then. Oracle Database Tablespace Monthly Space Usage Therefore the first thing I did was to check what the database already provides and taking into consideration that EM Grid Control 11g was in place I said to myself why not just execute the space reports: I have recently found myself into a new job and one of the first tasks I get is to report on the space usage of my databases as historically as I can at the point.