TRẦN VĂN BÌNH MASTER: Tự động báo cáo hiện trạng hàng ngày, hàng giờ của Oracle Database (Bức tranh của Database)

Ngày hôm nay tôi sẽ chia sẻ với các bạn 1 báo cáo rất KINH ĐIỂN mà nhìn vào đó bạn có thể biết ngay được hiện trạng của cơ sở dữ liệu của mình, có thể bạn chưa HIỂU được script này và có thể bạn cần nhiều mục khác nữa, bạn bổ sung thêm vào nhé. Hãy copy về và nghiền ngẫm, sử dụng trước khi nó KHÔNG còn được PUBLIC rộng rãi.

1.TẠO BẢNG BINHTV.DBAMF_LOG_JOBS

--DROP TABLE BINHTV.DBAMF_LOG_JOBS CASCADE CONSTRAINTS;

create user binhtv identified by Binhtv$123; -- Thay doi ten cho phu hop

grant connect, resource, dba to binhtv;

select * from BINHTV.DBAMF_LOG_JOBS;

CREATE TABLE BINHTV.DBAMF_LOG_JOBS

(

ID NUMBER,

NAME VARCHAR2(4000 BYTE),

STATUS VARCHAR2(50 BYTE),

EVENT_DATE DATE DEFAULT sysdate,

NOTE VARCHAR2(2000 BYTE)

)

TABLESPACE USERS

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

--DROP SEQUENCE BINHTV.DBAMF_LOG_JOBS_SEQ;

CREATE SEQUENCE BINHTV.DBAMF_LOG_JOBS_SEQ

START WITH 1

MAXVALUE 9999999999999999999999999999

MINVALUE 1

NOCYCLE

CACHE 20

NOORDER;

2.TẠO THỦ TỤC GỬI EMAIL

-- 1.Tao thu tuc gui email

create or replace PROCEDURE send_email_html_clob (str_to IN VARCHAR2, str_subject IN VARCHAR2, str_body IN CLOB)

l_mail_conn UTL_SMTP.connection;

p_str_to VARCHAR2(4000) := trim(str_to);

pos number(10,0) := 0;

i number(10,0) := 0;

L_OFFSET number := 1;

L_AMMOUNT number := 1900;

BEGIN

l_mail_conn := UTL_SMTP.open_connection ('10.10.10.10', 25);

--10.3.12.25

UTL_SMTP.helo (l_mail_conn, '10.10.10.10');

UTL_SMTP.command (l_mail_conn, 'AUTH LOGIN');

UTL_SMTP.command (

l_mail_conn,

UTL_RAW.cast_to_varchar2 (

UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('Binhtv$123'))));

UTL_SMTP.command (

l_mail_conn,

UTL_RAW.cast_to_varchar2 (

UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw ('abcd%1234'))));

UTL_SMTP.mail (l_mail_conn, 'admin@tranvanbinh.vn');

pos := INSTR (p_str_to, ';', 1, 1);

IF pos = 0

THEN

UTL_SMTP.rcpt (l_mail_conn, p_str_to);

END IF;

-- while there are chunks left, loop

WHILE (pos != 0)

LOOP

UTL_SMTP.rcpt (l_mail_conn, SUBSTR (p_str_to, 1, pos-1));

p_str_to := SUBSTR (p_str_to, pos + 1, LENGTH (p_str_to));

pos := INSTR (p_str_to, ';', 1, 1);

IF pos = 0

THEN

UTL_SMTP.rcpt (l_mail_conn, p_str_to);

END IF;

END LOOP;

UTL_SMTP.open_data (l_mail_conn);

UTL_SMTP.write_data (

l_mail_conn,

'Subject: =?UTF-8?Q?'

|| UTL_RAW.cast_to_varchar2(UTL_ENCODE.quoted_printable_encode (

UTL_RAW.cast_to_raw (str_subject)))

|| '?='

|| UTL_TCP.crlf);

UTL_SMTP.write_data (l_mail_conn, 'MIME-version: 1.0' || UTL_TCP.crlf);

UTL_SMTP.write_data (

l_mail_conn,

'Content-Type: text/html;charset=utf-8' || UTL_TCP.crlf);

UTL_SMTP.write_data (

l_mail_conn,

'Content-Transfer-Encoding: quoted-printable ' || UTL_TCP.crlf);

UTL_SMTP.write_data (

l_mail_conn,

'Date: '

|| TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')

|| UTL_TCP.crlf);

UTL_SMTP.write_data (l_mail_conn, 'To: ' || str_to || UTL_TCP.crlf);

UTL_SMTP.write_data (

l_mail_conn,

'From: ' || 'admin@tranvanbinh.vn' || UTL_TCP.crlf);

UTL_SMTP.write_data (

l_mail_conn,

'Reply-To: '

|| 'admin@tranvanbinh.vn'

|| UTL_TCP.crlf

|| UTL_TCP.crlf);

WHILE L_OFFSET < DBMS_LOB.GETLENGTH(str_body) LOOP

UTL_SMTP.write_raw_data (

l_mail_conn,

UTL_ENCODE.quoted_printable_encode (UTL_RAW.cast_to_raw (DBMS_LOB.SUBSTR(str_body,L_AMMOUNT,L_OFFSET))));

/*UTL_SMTP.WRITE_DATA(l_mail_conn,

DBMS_LOB.SUBSTR(str_body,L_AMMOUNT,L_OFFSET));*/

L_OFFSET := L_OFFSET + L_AMMOUNT ;

L_AMMOUNT := LEAST(1900,DBMS_LOB.GETLENGTH(str_body) - L_AMMOUNT);

END LOOP;

UTL_SMTP.close_data (l_mail_conn);

UTL_SMTP.quit (l_mail_conn);

END send_email_html_clob;

-- 2.Test

exec send_email_html_clob ('binhtv@tranvanbinh.vn','Test from DBAViet','Test');

3. TẠO THỦ TỤC RP_ALL

-- Tao thu tuc tren user SYS

--1.Tao thu tuc

create or replace procedure hc_all

tAll varchar2(32700):='';

tAll_New varchar2(10000):='';

tDBName varchar2(20):='';

v_err varchar2(1000):='';

--1.Tai DB

nActiveSession number;

nTotalActiveSession number; -- Ca background

nTotalInactiveSession number;

nTotalSession number;

nLock number;

nLockDBLink number;

CURSOR cTotalUserSession

select /* count , status*/ username,status, count(*) num from gv$session group by username,status

having (count(*)>200 and status='INACTIVE') or (count(*)>15 and status='ACTIVE')

order by status,count(*) desc,username;

tTotalUserSession varchar2(30000):='';

/* cursor c_cpu_ram

select event_date, duration, server_name, cpu_idle, round(ram_free_k/1024/1024,0) "GB"

from BINHTV.dbamf_ct_vmstat

where event_date > sysdate-1/24

and (cpu_idle <= 30 or round(ram_free_k/1024/1024,0) <=5)

order by event_date desc;

v_cpu_ram varchar2(30000):='';

--2.Backup

CURSOR cBackup

select command_id, start_time, end_time, status,INPUT_TYPE, input_bytes_display, output_bytes_display, time_taken_display, round(compression_ratio,0) RATIO , input_bytes_per_sec_display, output_bytes_per_sec_display

from v$rman_backup_job_details

where trunc(end_time)>=trunc(sysdate-1)

order by end_time desc;

tBackup varchar2(30000):='';

--3.Storage

cursor cASM is

select name, state, type, round(total_mb/1024) total_gb, round(usable_file_mb/1024) usable_file_gb from v$asm_diskgroup;

tASM varchar2(30000) := '';

cursor cTBS is

SELECT a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) pct_usage, ROUND(a.bytes_alloc / 1024 / 1024/1024) size_gb, ROUND (NVL (b.bytes_free, 0) / 1024 / 1024/1024) free_gb,

(ROUND (a.bytes_alloc / 1024 / 1024/1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024/1024)) used_gb, ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) pct_free, ROUND (maxbytes / 1048576/1024) max_gb,

ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) as pct_used_max

FROM ( SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes

FROM dba_data_files f

GROUP BY tablespace_name) a,

( SELECT f.tablespace_name, SUM (f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name(+)

--and a.tablespace_name in ('DATA','INDX')

and ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) > 85

order by pct_used_max desc;

tTBS varchar2(30000) := '';

cursor c_user_usage is

select owner,round(sum(bytes)/1024/1024/1024,0) "GB" from dba_segments

group by owner

having round(sum(bytes)/1024/1024/1024,0) > 50

order by "GB" desc;

v_user_usage varchar2(30000) := '';

cursor c_object_usage is

select owner,segment_name,round(sum(bytes)/1024/1024/1024,0) "GB" from dba_segments

group by owner, segment_name

having round(sum(bytes)/1024/1024/1024,0) > 20

order by "GB" desc;

v_object_usage varchar2(3000) := '';

-- Cac tablespace Read Only:Chay lau

/* cursor cTBS_RO is

select tablespace_name, round(sum(bytes)/1024/1024/1024,0) "GB"

from dba_segments

where tablespace_name in (select name from v$tablespace where ts# in (select ts# from v$datafile where enabled='READ ONLY'))

group by tablespace_name

order by tablespace_name ;

tStorage varchar2(4000):='';*/

/* cursor c_du

select * from binhtv.dbamf_ct_disk_usage

where event_date>sysdate-1

and nvl(substr(disk_percen,-3,2),0)>70

order by disk_percen desc;*/

v_du varchar2(30000):='';

--4.Object invalid

nInvalidObject number;

--5.Index Non-partition, parttion Unuable

nUnuableIndNonPar number;

nUnuableIndPar number;

--6.Archive log

-- Theo doi archived log sinh ra

cursor cTotalArchivedLog is

select trunc(completion_time), round(sum(blocks*block_size)/1024/1024/1024,0) "Archived Log GB" from V$ARCHIVED_LOG

where trunc(completion_time) >= trunc(sysdate-1)

--and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')

and dest_id=1

group by trunc(completion_time)

order by trunc(completion_time) desc;

-- Archived log sinh ra theo gio

cursor cHourArchivedLog is

select to_char(next_time,'YYYY-MM-DD hh24') Hour, round(sum(size_in_byte)/1024/1024,0) as size_in_mb, count(*) log_switch from (

select thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE as size_in_byte, next_time

from v$archived_log where name is not null and completion_time>sysdate-1 group by thread# ,sequence#, FIRST_CHANGE#,blocks*BLOCK_SIZE, next_time)

group by to_char(next_time,'YYYY-MM-DD hh24') order by 1 desc;

tArchivedLog varchar2(30000):='';

--7.Gather

tGather varchar2(4000):='';

tGatherTabPar varchar2(100) :='';

tGatherIndPar varchar2(100) :='';

tGatherTabNonPar varchar2(100) :='';

tGatherIndNonPar varchar2(100) :='';

BEGIN

insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)

values(binhtv.dbamf_log_jobs_seq.nextval,'Starting sys.dba_rp.hc_all',1,sysdate,'sys.dba_rp.hc_all');

commit;

--dbms_output.put_line('Before if');

--if (to_char(sysdate,'hh24') in ('00','08','13')) then

--dbms_output.put_line('After if');

select name into tDBName from v$database;

tAll := tAll ||'<h2>BAO CAO CSDL ' || tDBName ||' NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') ||'</h2>';

--1.TAI DB

select count(*) into nActiveSession from gv$session where username NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE') and status='ACTIVE';

select count(*) into nTotalActiveSession from gv$session where status='ACTIVE';

select count(*) into nTotalInactiveSession from gv$session where status='INACTIVE';

select count(*) into nTotalSession from gv$session;

SELECT COUNT(*) INTO nLock FROM gv$session WHERE blocking_session IS NOT NULL;

sELECT COUNT ( * ) INTO nLockDBLink FROM dba_2pc_pending where (retry_time-fail_time)*24*60>2;

tAll := tAll|| '<br><b>SUMMARY SESSION:</b><table width="600" border ="1" cellspacing="1" cellpadding="1"><tr bgcolor="#FFF1D9" style="font-weight:bold"><td>nActiveSession</td><td>nTotalActiveSession</td><td>nTotalInactiveSession</td><td>nTotalSession</td><td>nLock</td><td>nLockDBLink</td></tr>';

if nActiveSession > 200 then

tAll := tAll||'<tr>

<td><font color="red"><b>'||nActiveSession||'</b></font></td>

<td>'||nTotalActiveSession||'</td>

<td>'||nTotalInactiveSession||'</td>

<td>'||nTotalSession||'</td>

<td>'||nLock||'</td>

<td>'||nLockDBLink||'</td>

</tr>'||'</table>';

else

tAll := tAll||'<tr>

<td>'||nActiveSession||'</td>

<td>'||nTotalActiveSession||'</td>

<td>'||nTotalInactiveSession||'</td>

<td>'||nTotalSession||'</td>

<td>'||nLock||'</td>

<td>'||nLockDBLink||'</td>

</tr>'||'</table>';

end if;

--1.3.Total session theo tung user

tTotalUserSession :=tTotalUserSession||'

<br><b>USER SESSIONS DETAIL</b>:

<td>UserName</td>

<td>Status</td>

<td>Total Sesion</td>

</tr>';

FOR r1 IN cTotalUserSession

LOOP

tTotalUserSession:=tTotalUserSession||'<tr>

<td>'||r1.username||'</td>

<td>'||r1.status||' </td>

</tr>';

END LOOP;

tTotalUserSession:=tTotalUserSession||'</table>';

tAll :=tAll || tTotalUserSession;

--send_email_html_clob_m('binhtv@mobifone.vn;binh.tranvan@mobifone.vn','DBA_BILL_DBA MONITOR_' || sysdate, tAll);

dbms_output.put_line('1');

--CPU,RAM

v_cpu_ram:=v_cpu_ram||'<br><b>CPU, RAM USAGE:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td> event_date</td>

<td> duration</td>

<td> server_name</td>

</tr>';

/* for r11 in c_cpu_ram loop

v_cpu_ram:=v_cpu_ram||'<tr>

<td>'||to_char(r11.event_date,'dd/mm/yyyy hh24:mm:ss')||'</td>

<td>'||r11.duration||'</td>

<td>'||r11.server_name||'</td>

</tr>' ;

end loop;*/

v_cpu_ram := v_cpu_ram || '</table>';

tAll := tAll || v_cpu_ram;

--dbms_output.put_line('2');

--2.BACKUP: Lay 1 tuan

tBackup:=tBackup||'<br><b>SUMMARY BACKUP:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td>command_id</td>

<td>start_time</td>

<td>status</td>

<td>INPUT_TYPE</td>

<td>input_bytes_display</td>

<td>output_bytes_display</td>

<td>time_taken_display</td>

<td>RATIO</td>

<td>input_bytes_per_sec_display</td>

<td>output_bytes_per_sec_display</td>

</tr>';

for r1 in cBackup loop

tBackup:=tBackup||'<tr>

<td>'||r1.command_id||'</td>

<td>'||to_char(r1.start_time,'dd/mm/yyyy hh24:mm:ss')||'</td>

<td>'||r1.status||' </td>

<td>'||r1.INPUT_TYPE||'</td>

<td>'||r1.input_bytes_display||'</td>

<td>'||r1.output_bytes_display||'</td>

<td>'||r1.time_taken_display||'</td>

<td>'||r1.RATIO||'</td>

<td>'||r1.input_bytes_per_sec_display||'</td>

<td>'||r1.output_bytes_per_sec_display||'</td>

</tr>' ;

end loop;

tBackup := tBackup || '</table>';

tAll := tAll || tBackup;

--dbms_output.put_line('2');

--3.STORAGE

tASM :='<br><b>ASM DISKGROUP:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td>state</td>

<td>total_gb</td>

<td>usable_file_gb</td>

</tr>';

for r2 in cASM loop

tASM:=tASM||'<tr>

<td>'||r2.state||'</td>

<td>'||r2.total_gb||'</td>

<td>'||r2.usable_file_gb||'</td>

</tr>' ;

end loop;

tASM := tASM || '</table><br>';

tAll := tAll || tASM;

--dbms_output.put_line('3.tASM');

tTBS :='<br><b>DUNG LUONG TABLESPACE:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td> Tablespace Name </td>

<td> %Usage </td>

</tr>';

--dbms_output.put_line('0');

for r3 in cTBS loop

if r3.pct_used_max > 90 then

tTBS:=tTBS||'<tr>

<td><font color="red"><b>'||r3.tablespace_name||'</b></font></td>

<td>'||r3.pct_usage||'</td>

</tr>' ;

else

tTBS:=tTBS||'<tr>

<td>'||r3.tablespace_name||'</td>

<td>'||r3.pct_usage||'</td>

</tr>' ;

end if;

end loop;

--dbms_output.put_line('1:' || length(tTBS));

tTBS := tTBS || '</table><br>';

tAll := tAll || tTBS;

--dbms_output.put_line('3.TBS');

v_user_usage :='<br><b>DUNG LUONG THEO USER:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

</tr>';

--dbms_output.put_line('for r31 in c_user_usage loop');

for r31 in c_user_usage loop

if r31."GB" > 100 then

v_user_usage:=v_user_usage||'<tr>

<td><font color="red"><b>'||r31.owner||'</b></font></td>

</tr>' ;

else

v_user_usage:=v_user_usage||'<tr>

<td>'||r31.owner||'</td>

</tr>' ;

end if;

end loop;

--dbms_output.put_line('end loop;');

v_user_usage := v_user_usage || '</table>';

tAll := tAll || v_user_usage;

--dbms_output.put_line('3.DUNG LUONG THEO USER');

v_object_usage :='<br><b>DUNG LUONG THEO OBJECT:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td> Segment_name/td>

</tr>';

for r32 in c_object_usage loop

if r32."GB" > 1000 then

v_object_usage:=v_object_usage||'<tr>

<td><font color="red"><b>'||r32.owner||'</b></font></td>

<td><font color="red"><b>'||r32.segment_name||'</b></font></td>

</tr>' ;

else

v_object_usage:=v_object_usage||'<tr>

<td>'||r32.owner||'</td>

<td>'||r32.segment_name||'</td>

</tr>';

end if;

end loop;

v_object_usage := v_object_usage || '</table>';

tAll := tAll || v_object_usage;

dbms_output.put_line('3.DUNG LUONG THEO OBJECT');

--dbms_output.put_line('3');

v_du :='<br><b>DUNG LUONG CAC PHAN VUNG OS:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td> Percent </td>

<td> Mount Point </td>

<td> Event_Date </td>

</tr>';

--dbms_output.put_line('v_object_usage');

/* for r33 in c_du loop

if(substr(r33.Disk_percen,-3,0) > 80) then

v_du:=v_du||'<tr>

<td><font color="red"><b>'||r33.Disk_percen||'</b></font></td>

<td><font color="red"><b>'||r33.Disk_mount||'</b></font></td>

<td><font color="red"><b>'||to_char(r33.event_date,'dd/mm/yyyy hh24:mm:ss')||'</b></font></td>

</tr>' ;

else

v_du:=v_du||'<tr>

<td>'||r33.Disk_percen||'</td>

<td>'||r33.Disk_mount||'</td>

<td>'||to_char(r33.event_date,'dd/mm/yyyy hh24:mm:ss')||'</td>

</tr>' ;

end if;

end loop;*/

v_du := v_du || '</table>';

tAll := tAll || v_du;

--dbms_output.put_line('3.DUNG LUONG THEO OS');

--dbms_output.put_line('3.Completed');

--4.Object invalid

select count(*) into nInvalidObject from (select 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects

where object_type in ('PROCEDURE','FUNCTION','TRIGGER','PACKAGE') and status like 'INVALID' and OWNER in ('BINH_OWNER')

UNION ALL

select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;' from dba_objects

where object_type in ('PACKAGE BODY') and status like 'INVALID' and OWNER like 'BINH_OWNER');

tAll := tAll||'<br><b>OBJECT INVALID:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td> So luong Object Invalid </td>

<td> So luong Index Partition UNUSABLE </td>

<td> So luong Index Non-Partition UNUSABLE </td>

</tr>';

--dbms_output.put_line('tAll: ' || length(tAll));

tAll := tAll||'<tr>

<td>'||nInvalidObject||'</td>' ;

--dbms_output.put_line('tAll: ' || length(tAll));

--Index invalid: Partition, non-partition

select count(*) into nUnuableIndNonPar from dba_indexes where status='UNUSABLE';

select count(*) into nUnuableIndPar from dba_ind_partitions where status='UNUSABLE';

tAll := tAll||'

<td>'||nUnuableIndPar||'</td>

<td>'||nUnuableIndNonPar||'</td>

</tr>' ;

tAll := tAll || '</table>';

--dbms_output.put_line('4');

--5.Archive log trong 1 ngay hien tai

tAll := tAll||'<br><b>ARCHIVE LOG TRONG 7 NGAY:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td> Completion_time </td>

<td> Archived_Log_GB </td>

</tr>';

for r1 in (select trunc(completion_time) completion_time, round(sum(blocks*block_size)/1024/1024/1024,0) as archived_log_gb from V$ARCHIVED_LOG

where trunc(completion_time) >= trunc(sysdate-7)

--and trunc(completion_time)>= to_date(trunc(sysdate),'dd/mm/yyyy')

and dest_id=1

group by trunc(completion_time)

order by trunc(completion_time) desc) loop

if r1.archived_log_gb > 1000 then

tAll:=tAll||'<tr>

<td><font color="red"><b>'||r1.completion_time||'</b></font></td>

<td><font color="red"><b>'||r1.archived_log_gb||'</b></font></td>

</tr>' ;

else

tAll:=tAll||'<tr>

<td>'||r1.completion_time||'</td>

<td>'||r1.archived_log_gb||'</td>

</tr>' ;

end if;

end loop;

tAll:=tAll||'</table>';

--dbms_output.put_line('5');

--6.Gather

tAll := tAll||'<br><b>GATHER:</b><table width="600" border ="1" cellspacing="1" cellpadding="1">

<td> tGatherTabPar </td>

<td> tGatherIndPar </td>

<td> tGatherTabNonPar </td>

<td> tGatherIndNonPar </td>

</tr>';

select to_date(max(last_analyzed),'dd/mm/yyyy') into tGatherTabPar from dba_tab_partitions

where table_owner in ('BINH_OWNER') and last_analyzed<sysdate and last_analyzed>sysdate-7;

select to_date(max(last_analyzed),'dd/mm/yyyy') into tGatherIndPar from dba_ind_partitions

where index_owner in ('BINH_OWNER') and last_analyzed<sysdate and last_analyzed>sysdate-7;

-- Non-partition

select to_date(max(last_analyzed),'dd/mm/yyyy') into tGatherTabNonPar from dba_tables

where owner in ('BINH_OWNER') and last_analyzed<sysdate and last_analyzed>sysdate-7 ;

select to_date(max(last_analyzed),'dd/mm/yyyy') into tGatherIndNonPar from dba_indexes

where owner in ('BINH_OWNER') and last_analyzed<sysdate and last_analyzed>sysdate-7;

tAll:=tAll||'<tr>

<td>'||tGatherTabPar||'</td>

<td>'||tGatherIndPar||'</td>

<td>'||tGatherTabNonPar||'</td>

<td>'||tGatherIndNonPar||'</td>

</tr>' ;

tAll := tAll || '</table>';

--dbms_output.put_line('6');

--dbms_output.put_line('tAll: '||length(tAll));

--execute immediate 'truncate table tc_monior_lob';

--insert into tc_monior_lob(text) values(tAll);

--commit;

--select text into tAll_New from tc_monior_lob;

IF nActiveSession >= 50 then

--send_email_html_clob('binh.tranvan@tranvanbinh.vn','HIGH - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') , tAll);

send_email_html_clob('admin@tranvanbinh.vn','HIGH - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') , tAll);

ELSIF nActiveSession >= 20 and nActiveSession < 50 then

--send_email_html_clob('binh.tranvan@tranvanbinh.vn','MID - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') , tAll);

send_email_html_clob('admin@tranvanbinh.vn','MID - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') , tAll);

ELSE

--send_email_html_clob('binh.tranvan@tranvanbinh.vn','LOW - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') , tAll);

send_email_html_clob('admin@tranvanbinh.vn','LOW - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss') , tAll);

END IF;

--send_email_html_clob('admin@mobifone.vn','HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss'), tAll);

--send_sms_binhtv('sys.dba_rp.hc');

--end if;

insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)

values(binhtv.dbamf_log_jobs_seq.nextval,'Completed sys.dba_rp.hc_all',1,sysdate,'sys.dba_rp.hc_all');

commit;

EXCEPTION

WHEN others THEN

--send_sms_binhtv('Error sys.dba_rp.hc: ' || SQLERRM);

v_err := SUBSTR(SQLERRM, 1, 200);

insert into binhtv.dbamf_log_jobs (id,name,status,event_date, note)

values(binhtv.dbamf_log_jobs_seq.nextval,'Error sys.dba_rp.hc_all',-1,sysdate,'Error sys.dba_rp.hc_all, '||v_err);

commit;

END;

--2.Test

exec hc_all;

4.TẠO SCHEDULER JOB

(Định kỳ chạy 4h 1 lần hoặc DB nào quan trọng thì đặt job 30p-1h chạy 1 lần)

BEGIN

SYS.DBMS_SCHEDULER.DROP_JOB

(job_name => 'SYS.DBAMF_HC_ALL_DAILY');

END;

BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB

(

job_name => 'SYS.DBAMF_HC_ALL_DAILY'

,start_date => TO_TIMESTAMP_TZ('2021/04/12 20:00:00.971704 Asia/Saigon','yyyy/mm/dd hh24:mi:ss.ff tzr')

,repeat_interval => 'FREQ=HOURLY;INTERVAL=4;BYMINUTE=0;BYSECOND=0'

,end_date => NULL

,job_class => 'DEFAULT_JOB_CLASS'

,job_type => 'STORED_PROCEDURE'

,job_action => 'SYS.HC_ALL'

,comments => NULL

);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE