CREATE TABLE LMPH_DB_AGEING
(
DT VARCHAR2
(40),
IP VARCHAR2(20),
DBNAME VARCHAR2
(10),
DB_SIZE VARCHAR2
(20),
USE_SPC VARCHAR2 (20),
USE_SPCP VARCHAR2(20),
FREE_SPC VARCHAR2 (20),
FREE_SPCP VARCHAR2
(20),
GRO_DAY VARCHAR2(20),
GRO_DAYP VARCHAR2(20),
GRO_WEEK VARCHAR2(20),
GRO_WEEKP VARCHAR2
(20)
);
-------------------------------------------------------------------------------------------------------------------
#=============== ANONYMOUS BLOCK DATA FATCHING
================#
-------------------------------------------------------------------------------------------------------------------
DECLARE
V_DT VARCHAR2(40);
V_IP VARCHAR2(20);
V_DBNAME VARCHAR2(10);
V_DB_SIZE VARCHAR2(20);
V_USE_SPC VARCHAR2(20);
V_USE_SPCP VARCHAR2(20);
V_FREE_SPC VARCHAR2(20);
V_FREE_SPCP VARCHAR2(20);
V_GRO_DAY VARCHAR2(20);
V_GRO_DAYP VARCHAR2(20);
V_GRO_WEEK VARCHAR2(20);
V_GRO_WEEKP VARCHAR2(20);
BEGIN
SELECT
----SYSTIMESTAMP INTO V_DT
SYSDATE INTO V_DT FROM DUAL;
SELECT UTL_INADDR.get_host_address INTO V_IP FROM DUAL;
SELECT
(select name from v$database) ,
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' ,
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P /
1024 / 1024 ),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' ,
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) -
((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024
)))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from
v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' ,
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from
v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB'
INTO
V_DBNAME,
V_DB_SIZE,
V_USE_SPC,
V_USE_SPCP,
V_FREE_SPC,
V_FREE_SPCP,
V_GRO_DAY,
V_GRO_DAYP,
V_GRO_WEEK,
V_GRO_WEEKP
FROM (SELECT
BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
DBMS_OUTPUT.PUT_LINE('==================================='||CHR(10));
DBMS_OUTPUT.PUT_LINE('DATE AND TIME........ '||V_DT);
DBMS_OUTPUT.PUT_LINE('SYSTEM IP ADD........ '||V_IP);
DBMS_OUTPUT.PUT_LINE('DATABASE NAME........ '||V_DBNAME);
DBMS_OUTPUT.PUT_LINE('DATABASE SIZE........
'||V_DB_SIZE);
DBMS_OUTPUT.PUT_LINE('USE SPACE............
'||V_USE_SPC);
DBMS_OUTPUT.PUT_LINE('USE SPACE IN %.......
'||V_USE_SPCP);
DBMS_OUTPUT.PUT_LINE('FREE SPACE...........
'||V_FREE_SPC);
DBMS_OUTPUT.PUT_LINE('FREE SPACE IN %......
'||V_FREE_SPCP);
DBMS_OUTPUT.PUT_LINE('GROWTH PER DAY.......
'||V_GRO_DAY);
DBMS_OUTPUT.PUT_LINE('GROWH PER DAY IN %...
'||V_GRO_DAYP);
DBMS_OUTPUT.PUT_LINE('GROWTH PER WEEK......
'||V_GRO_WEEK);
DBMS_OUTPUT.PUT_LINE('GROWTH PER WEEK IN %. '||V_GRO_WEEKP);
DBMS_OUTPUT.PUT_LINE('==================================='||CHR(10));
END;
/
-------------------------------------------------------------------------------------------------------------------
#============== ANONYMOUS BLOCK DATA INSERTION ================#
-------------------------------------------------------------------------------------------------------------------
DECLARE
V_DT VARCHAR2(40);
V_IP VARCHAR2(20);
V_DBNAME VARCHAR2(10);
V_DB_SIZE VARCHAR2(20);
V_USE_SPC VARCHAR2(20);
V_USE_SPCP VARCHAR2(20);
V_FREE_SPC VARCHAR2(20);
V_FREE_SPCP VARCHAR2(20);
V_GRO_DAY VARCHAR2(20);
V_GRO_DAYP VARCHAR2(20);
V_GRO_WEEK VARCHAR2(20);
V_GRO_WEEKP VARCHAR2(20);
BEGIN
SELECT
----SYSTIMESTAMP INTO V_DT
SYSDATE INTO V_DT FROM DUAL;
SELECT UTL_INADDR.get_host_address INTO V_IP FROM DUAL;
SELECT
(select name from v$database) ,
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' ,
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P /
1024 / 1024 ),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' ,
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) -
((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024
)))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from
v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' ,
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from
v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB'
INTO V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP
FROM (SELECT
BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
INSERT INTO LMPH_DB_AGEING
VALUES(V_DT,V_IP,V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP);
COMMIT;
END;
/
-------------------------------------------------------------------------------------------------------------------------
#=============== CREATE PROCEDURE FOR DATA INSERTION
==============#
-------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE LMPH_DB_AGEING_P AS
V_DT VARCHAR2(40);
V_IP VARCHAR2(20);
V_DBNAME VARCHAR2(10);
V_DB_SIZE VARCHAR2(20);
V_USE_SPC VARCHAR2(20);
V_USE_SPCP VARCHAR2(20);
V_FREE_SPC VARCHAR2(20);
V_FREE_SPCP VARCHAR2(20);
V_GRO_DAY VARCHAR2(20);
V_GRO_DAYP VARCHAR2(20);
V_GRO_WEEK VARCHAR2(20);
V_GRO_WEEKP VARCHAR2(20);
BEGIN
SELECT
----SYSTIMESTAMP INTO V_DT
SYSDATE INTO V_DT FROM DUAL;
SELECT UTL_INADDR.get_host_address INTO V_IP FROM DUAL;
SELECT
(select name from v$database) ,
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' ,
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P /
1024 / 1024 ),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' ,
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) -
((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024
)))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from
v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' ,
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' ,
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024
/ 1024 ))/(select sysdate-min(creation_time) from
v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB'
INTO
V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP
FROM (SELECT
BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
INSERT INTO LMPH_DB_AGEING
VALUES(V_DT,V_IP,V_DBNAME,V_DB_SIZE,V_USE_SPC,V_USE_SPCP,V_FREE_SPC,V_FREE_SPCP,V_GRO_DAY,V_GRO_DAYP,V_GRO_WEEK,V_GRO_WEEKP);
COMMIT;
END;
/
-------------------------------------------------------------------------------------------------------------------------
#================= CREATE JOB SCHEDULER FOR INSERTION =============#
-------------------------------------------------------------------------------------------------------------------------
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'LMPH_DB_AGEING_SCH',
job_type => 'STORED_PROCEDURE',
job_action => 'LMPH_DB_AGEING_P',
start_date => '06-OCT-2017 09:15:00
PM',
repeat_interval =>
'FREQ=DAILY',
enabled => TRUE
);
END;
-------------------------------------------------------------------------------------------------------------------------
#================== CREATE JOB RUN FOR TESTING
=====================#
-------------------------------------------------------------------------------------------------------------------------
begin
dbms_scheduler.run_job (job_name => 'LMPH_DB_AGEING_SCH');
end;
-----------------------------------------------------------------------------------------------------
#========================= END
===========================#
-----------------------------------------------------------------------------------------------------
No comments:
Post a Comment