Grant select on v_$datafile,dba_free_space,v_$tablespace to user;
CREATE OR REPLACE
PROCEDURE
TABLESPACE_GROWTH
AS
stmt VARCHAR2(2000);
stmt2 VARCHAR2(2000);
stmt3 VARCHAR2(2000);
v_date varchar2(30);
h_exist NUMBER;
CURSOR get_size IS
--select * from test1;
SELECT UPPER(vts.NAME) AS TABLESPACE ,
UPPER(df.NAME) AS DATAFILE,
df.bytes / 1024 / 1024 AS allocated_mb,
ROUND (((df.bytes/ 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)),3) as used_mb
FROM v$datafile df,
dba_free_space dfs,
v$tablespace vts
WHERE df.file# = dfs.file_id(+)
AND vts.ts# = df.ts#
GROUP BY vts.NAME, df.NAME, df.bytes / 1024 / 1024
ORDER BY vts.NAME;
c1 get_size%rowtype;
BEGIN
--get desire v_date variable from sysdate
select to_char(sysdate,'DD-MON-YY@HH24:MI:SS') into v_date from dual;
dbms_output.put_line(v_date);
-- create column in table tbs_Growth
stmt := ' ALTER TABLE tbs_growth add "' || v_date || '" float ';
dbms_output.put_line('Executing ''' || stmt || '''');
EXECUTE IMMEDIATE stmt;
OPEN get_size;
loop
fetch get_size INTO c1 ;
exit WHEN get_size%NOTFOUND;
-- check datafile with tbs_growth datafile
select nvl((select 1 from tbs_growth where lower(datafile) = lower(c1.datafile) ) ,0) into h_exist from dual;
IF h_exist = 1
THEN
-- dbms_output.put_line ( v_date ||',' ||c1.datafile||','|| h_exist);
-- dbms_output.put_line( v_date ||'" ,"'||c1.tablespace ||'" ,"'||c1.datafile||'",'|| c1.allocated_mb || ',' ||c1.used_mb );
---update allocated_column and used_mb column with current data
stmt3 := ' Update TBS_GROWTH set allocated_mb=' || c1.allocated_mb || ',"' || v_date || '"='||c1.used_mb ||' where lower(DATAFILE)=lower('''||c1.datafile||''') ' ;
dbms_output.put_line('Executing ''' || stmt3 || '''');
EXECUTE IMMEDIATE stmt3;
ELSE
-- if not exist
--- Insert all data in new row and (used_mb with today's column)
stmt2 := ' Insert INTO TBS_GROWTH (tablespace,datafile,allocated_mb ,"' || v_date || '" ) values ('''||c1.tablespace ||''' ,'''||c1.datafile||''','|| c1.allocated_mb || ',' ||c1.used_mb||' ) ' ;
dbms_output.put_line('Executing ''' || stmt2 || '''');
EXECUTE IMMEDIATE stmt2;
end if;
END loop;
close get_size;
IF get_size%ISOPEN then
CLOSE get_size;
END IF;
END TABLESPACE_GROWTH;
