Step : 1 Calculate total Size of tablespace
SELECT sum(bytes)/1024/1024 "TOTAL SIZE (MB)"
FROM dba_Data_files
Step : 2 Calculate Free Space in Tablespace
SELECT sum(bytes)/1024/1024 "FREE SPACE (MB)"
FROM dba_free_space
Step : 3 Calculate total size , free space and used space in tablespace
SELECT t2.total "TOTAL SIZE",
t1.free "FREE SPACE",
(t1.free/t2.total)*100 "FREE (%)" ,
(1-t1.free/t2.total)*100 "USED (%)"
FROM (SELECT sum(bytes)/1024/1024 free FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total FROM dba_Data_files) t2
Step : 4 Create table which is store all free/use space related information of tablespace
CREATE TABLE db_growth
AS
SELECT *
FROM(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE% "
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)
Step : 5 Insert free space information in DB_GROWTH table (if you want to populate data Manually)
INSERT INTO db_growth
SELECT *
FROM (
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)
Step : 6 Create View on DB_GROWTH based table ( This Steps is Required if you want to populate data automatically)
CREATE VIEW v_db_growth
AS SELECT *
FROM
(
SELECT sysdate,
t2.total "TOTAL_SIZE",
t1.free "FREE_SPACE",
(t1.free/t2.total)*100 "FREE%"
FROM
(SELECT sum(bytes)/1024/1024 free
FROM dba_free_space) t1 ,
(SELECT sum(bytes)/1024/1024 total
FROM dba_Data_files) t2
)
Step : 7 Insert data into DB_GROWTH table from V_DD_GROWTH view
INSERT INTO db_growth select *
FROM v_db_growth
Step : 8 Check everything goes fine.
SELECT * FROM db_growth;
Check Result
Step : 9 Execute following SQL for more time stamp information
ALTER SESSION SET nls_date_format ='dd-mon-yyyy hh24:mi:ss' ;
Session altered.
Step : 10 Create a DBMS jobs which execute after 24 hours
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.submit(
jobno, 'BEGIN INSERT INTO db_growth SELECT * FROM v_db_growth;COMMIT:END;', sysdate, 'SYSDATE+ 24', TRUE);
COMMIT;
END;
PL/SQL procedure successfully completed.
Step: 11 View your dbms jobs and it's other information
SELECT * FROM user_jobs;
TIPS: If you want to execute dbms jobs manually execute following command other wise jobs is executing automatically
EXEC DBMS_JOB.run(ENTER_JOB_NUMBER)
PL/SQL procedure successfully completed.
Step: 12 Finally all data populated in db_growth table
SELECT * FROM db_growth
