Friday 29 January 2010

SP_Tablespace_Growth

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;




No comments: