TRẦN VĂN BÌNH MASTER: [VIP5] Báo cáo hiện trạng cơ sở dữ liệu Oracle hàng ngày

Thứ Năm, 3 tháng 3, 2022

[VIP5] Báo cáo hiện trạng cơ sở dữ liệu Oracle hàng ngày_Full

Tạo job như sau:

CREATE OR REPLACE procedure sys.pr_db_daily_full (p_db varchar2, v_recipient varchar2)

p_content varchar2(32000);

p_subject varchar2(2000);

p_phy_data_size number;

p_phy_temp_size number;

p_phy_redo_size number;

p_phy_db_size number;

p_total_segment_size number;

v_from VARCHAR2 (80) := ''tranbinh48ca@gmail.com';

v_pass varchar2(20) := 'Admin$123';

v_mail_host VARCHAR2 (30) :='8.8.8.8';

v_mail_conn UTL_SMTP.connection;

arrRecipients string_fnc.t_array;

cursor c_db is

SELECT DBID,NAME,LOG_MODE,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,REMOTE_ARCHIVE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,

PLATFORM_NAME,CURRENT_SCN,FLASHBACK_ON,DB_UNIQUE_NAME, (select version from v$instance) version FROM v$database;

cursor c_ctl is

SELECT name FROM v$controlfile;

cursor c_backup is

select start_time start_time1, to_char(start_time,'DAY dd/mm/yyyy hh24:mi:ss') start_time, to_char(end_time,'DAY dd/mm/yyyy hh24:mi:ss') end_time, output_device_type, status, input_type, round(compression_ratio,2) compression_ratio,

input_bytes_display, output_bytes_display, time_taken_display

from v$rman_backup_job_details

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

order by start_time1 desc;

cursor c_redo is

SELECT l.GROUP# group_no,l.thread# thread_no,l.sequence# sequence_no,l.BYTES/1024/1024 size_in_mb,l.MEMBERS logfile_member,l.status logfile_status,lf.member Log_member,NVL(lf.STATUS,'File is in use') log_status

FROM v$logfile lf, v$log l WHERE l.GROUP#=lf.GROUP# ORDER BY l.GROUP#;

cursor c_archive_log is

select trunc(completion_time) completion_time, round(sum(blocks*block_size)/1024/1024/1024,2) archive_log_in_gb from V$ARCHIVED_LOG

where trunc(completion_time) >= 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;

cursor c_asm is

select name, sector_size, block_size, state, type, round(total_mb/1024,2) total_gb , round(free_mb/1024,2) free_gb,

round(hot_used_mb/1024,2) hot_used_gb, round(cold_used_mb/1024,2) cold_used_gb, round(required_mirror_free_mb/1024,2) required_mirror_free_gb,

round(usable_file_mb/1024,2) usable_file_gb, voting_files

from gv$asm_diskgroup where inst_id = 1 and state not in ('CLOSED','DISMOUNTED') order by name;

cursor c_tbs is

select

a.tablespace_name,

round( a.bytes_alloc/(1024*1024*1024)) Total_in_GB,

round(a.physical_bytes/(1024*1024*1024)) Max_total_in_GB,

round(nvl(b.tot_used,0)/(1024*1024*1024)) Used_in_GB,

round((nvl(b.tot_used,0)/a.bytes_alloc)*100,2) used_percentage,

(select status from dba_tablespaces where tablespace_name = a.tablespace_name) status

from

(select

tablespace_name,

sum(bytes) physical_bytes,

sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc

from dba_data_files

group by tablespace_name ) a,

(select tablespace_name, sum(bytes) tot_used

from dba_segments

group by tablespace_name ) b

where a.tablespace_name = b.tablespace_name (+)

and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)

and a.tablespace_name not like 'UNDO%'

order by 5 desc;

cursor c_schm is

SELECT owner, size_in_gb FROM

(SELECT owner, ROUND(SUM(bytes)/1024/1024/1024) size_in_gb FROM dba_segments

WHERE owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX',

'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM',

'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')

GROUP BY owner

ORDER BY 2 DESC)

WHERE size_in_gb > 50;

cursor c_sgm is

SELECT segment_type, ROUND(SUM(bytes)/1024/1024/1024) Size_GB

FROM dba_segments

GROUP BY segment_type

ORDER BY 2 DESC;

cursor c_table is

SELECT owner, table_name,table_type, num_part, num_rows, table_size, tablespace_name,last_analyzed FROM

(SELECT owner, table_name,table_type, num_part, num_rows, table_size, tablespace_name,last_analyzed FROM

(

SELECT owner,table_name,'NON PARTITIONED' table_type, 1 num_part, nvl(num_rows,0) num_rows,

nvl(ROUND(NVL(blocks,0)*(select to_number(block_size) from dba_tablespaces where tablespace_name = a.tablespace_name)/1024/1024/1024),0) table_Size,

tablespace_name, last_analyzed

FROM dba_tables a

WHERE partitioned = 'NO' and

owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW',

'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')

union

select table_owner, table_name, 'PARTITIONED' table_type, count(1) num_part, sum(num_rows) num_rows, round(sum(part_size)/1024/1024/1024,2) table_size, '' tablespace_name,

max(last_analyzed) last_analyzed

from

(select table_owner, table_name, nvl(num_rows,0) num_Rows,

nvl(NVL(blocks,0)*(select to_number(block_size) from dba_tablespaces where tablespace_name = b.tablespace_name),0) part_size, last_analyzed

from dba_tab_Partitions b where table_owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW',

'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB'))

group by table_owner, table_name

)

ORDER BY table_Size DESC)

WHERE rownum <=20;

cursor c_index is

SELECT index_size,Ind_name,Tab_name,tablespace_name, Owner,Status,Analyzed FROM

(SELECT ROUND(sum(s.bytes)/1024/1024/1024,2) index_size, i.owner Owner, i.index_name Ind_name, i.tablespace_name, i.table_name Tab_name, i.status Status, max(i.last_analyzed) Analyzed

FROM dba_indexes i, dba_segments s

WHERE

i.owner NOT IN ('ANONYMOUS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'DMSYS', 'EXFSYS', 'HR', 'IX', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OE', 'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS', 'XDB')

AND

i.last_analyzed IS NOT NULL

AND

i.index_name=s.segment_name

AND

i.owner=s.owner

group by i.owner, i.index_name, i.tablespace_name, i.table_name, status

ORDER BY 1 DESC)

WHERE rownum<=20;

cursor c_invalid_obj is

select owner, object_name, owner || '.' ||object_name full_name, object_type, to_char(last_ddl_time,'dd/mm/yyyy hh24:mi:ss') last_ddl_time,status

From dba_objects

where status <> 'VALID' and owner in ('TEST_OWNER')

order by owner, object_name;

cursor c_hit_ratio is

SELECT a.inst_id, ROUND((1-(a.physical_reads)/((a.db_block_gets+a.consistent_gets)))*100,3) p_pool_hit_ratio,

ROUND((1-(sum(b.getmisses)/(sum(b.gets)+sum(b.getmisses)))) * 100,3) p_Dictionary_cache_hit_ratio,

ROUND((SUM(c.pins)/(SUM(c.pins)+SUM(c.reloads)))*100,3) p_Lib_Cache_Hit_Ratio , ROUND((sum(c.pinhits)/sum(c.pins))*100,3) p_Lib_Cache_Pin_Hit_Ratio

FROM gv$buffer_pool_statistics a, gv$rowcache b, gv$librarycache c

WHERE a.db_block_gets + a.consistent_gets > 0 and b.gets + b.getmisses <> 0 and a.inst_id = b.inst_id and a.inst_id = c.inst_id

group by a.inst_id,ROUND((1-(a.physical_reads)/((a.db_block_gets+a.consistent_gets)))*100,3) order by 1;

cursor c_top_cpu is

SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text

FROM

(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea

ORDER BY cpu_time DESC)

WHERE rownum <=20;

cursor c_top_buffer is

SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text

FROM

(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea

ORDER BY buffer_gets DESC)

WHERE rownum <=20;

cursor c_top_disk is

SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text

FROM

(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea

ORDER BY disk_reads DESC)

WHERE rownum <=20;

cursor c_top_exec is

SELECT cpu_time,buffer_gets,disk_reads,executions,sql_id,sql_text

FROM

(SELECT cpu_time, buffer_gets, disk_reads, executions, sql_id, dbms_lob.substr(sql_fulltext,3900,1) sql_text FROM v$sqlarea

ORDER BY executions DESC)

WHERE rownum <=20;

begin

p_subject := '[DBA] [BAO CAO DINH KY] Database ' ||p_db ||' - NGAY '||to_char(sysdate, 'dd/mm/yyyy');

v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);

UTL_SMTP.HELO (v_mail_conn, v_mail_host);

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

UTL_SMTP.command (v_mail_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (v_from))));

UTL_SMTP.command (v_mail_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (v_pass))));

UTL_SMTP.MAIL (v_mail_conn, v_from);

arrRecipients := string_fnc.split(v_recipient,',');

for i in 1..arrRecipients.count LOOP

UTL_SMTP.rcpt(v_mail_conn, arrRecipients(i));

end loop;

UTL_SMTP.OPEN_DATA( v_mail_conn );

UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );

UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );

UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '

|| p_subject || UTL_TCP.CRLF );

UTL_SMTP.WRITE_DATA ( v_mail_conn,

'Content-Type: text/html; charset=utf8'

|| UTL_TCP.CRLF || UTL_TCP.CRLF );

UTL_SMTP.WRITE_DATA ( v_mail_conn,

'<html lang="en">

<head>

<title>'||p_subject || '</title> </head>

<body>

<table style="border:1px solid black;border-collapse:collapse;width: 800px;">');

-- Thong tin chung

UTL_SMTP.WRITE_DATA (v_mail_conn, '<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin chung ve Database</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn, '<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> db_unique_name </th>

<th style="border:1px solid black;background-color:lightyellow"> Version </th>

<th style="border:1px solid black;background-color:lightyellow"> Protection_Mode </th>

<th style="border:1px solid black;background-color:lightyellow"> Protection_level </th>

<th style="border:1px solid black;background-color:lightyellow"> Database_Role </th>

<th style="border:1px solid black;background-color:lightyellow"> Switchover_Status </th>

<th style="border:1px solid black;background-color:lightyellow"> dataguard_broker </th>

<th style="border:1px solid black;background-color:lightyellow"> flashback_on </th>

</tr>');

for v_db in c_db

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,

'<tr>

<th style="border:1px solid black;"> ' ||v_db.db_unique_name ||'</th>

<th style="border:1px solid black;"> ' ||v_db.version ||'</th>

<th style="border:1px solid black;"> ' ||v_db.Protection_Mode ||'</th>

<th style="border:1px solid black;"> ' ||v_db.Protection_level ||'</th>

<th style="border:1px solid black;"> ' ||v_db.Database_Role ||'</th>

<th style="border:1px solid black;"> ' ||v_db.Switchover_Status ||'</th>

<th style="border:1px solid black;"> ' ||v_db.dataguard_broker ||'</th>

<th style="border:1px solid black;"> ' ||v_db.flashback_on ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin control file

UTL_SMTP.WRITE_DATA (v_mail_conn,

'<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin Control File</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow;"> Danh sach Control File </th>

</tr>');

for v_ctl in c_ctl

loop

UTL_SMTP.WRITE_DATA (v_mail_conn, '

<tr>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin redo file:

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin Redo file</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> GROUP# </th>

<th style="border:1px solid black;background-color:lightyellow"> Thread# </th>

<th style="border:1px solid black;background-color:lightyellow"> Sequence# </th>

<th style="border:1px solid black;background-color:lightyellow"> Logfile_MEMBERS </th>

<th style="border:1px solid black;background-color:lightyellow"> Logfile_status </th>

<th style="border:1px solid black;background-color:lightyellow"> Log_member </th>

<th style="border:1px solid black;background-color:lightyellow"> Log_status </th>

</tr>

');

for v_redo in c_redo

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_redo.group_no ||'</th>

<th style="border:1px solid black;"> ' ||v_redo.thread_no ||'</th>

<th style="border:1px solid black;"> ' ||v_redo.sequence_no ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_redo.size_in_mb,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||v_redo.Logfile_MEMBER ||'</th>

<th style="border:1px solid black;"> ' ||v_redo.Logfile_status ||'</th>

<th style="border:1px solid black;"> ' ||v_redo.Log_member ||'</th>

<th style="border:1px solid black;"> ' ||v_redo.Log_status ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin backup:

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin Backup 07 ngay gan nhat</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Start_time </th>

<th style="border:1px solid black;background-color:lightyellow"> Output_device_type </th>

<th style="border:1px solid black;background-color:lightyellow"> Status </th>

<th style="border:1px solid black;background-color:lightyellow"> Input_type </th>

<th style="border:1px solid black;background-color:lightyellow"> Compression_ratio </th>

<th style="border:1px solid black;background-color:lightyellow"> Input_bytes </th>

<th style="border:1px solid black;background-color:lightyellow"> Output_bytes </th>

<th style="border:1px solid black;background-color:lightyellow"> Time_taken </th>

</tr>

');

for v_backup in c_backup

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_backup.start_time ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.end_time ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.output_device_type ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.status ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.input_type ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.compression_ratio ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.input_bytes_display ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.output_bytes_display ||'</th>

<th style="border:1px solid black;"> ' ||v_backup.time_taken_display ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin Archive log:

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin Archive log 07 ngay gan nhat</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Completion_Time </th>

<th style="border:1px solid black;background-color:lightyellow"> Archive_log(GB)</th>

</tr>

');

for v_archive_log in c_archive_log

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_archive_log.completion_time ||'</th>

<th style="border:1px solid black;"> ' ||v_archive_log.archive_log_in_gb ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin dung luong database

SELECT round(a.data_size/1024/1024/1024), round(b.temp_size/1024/1024/1024), round(c.redo_size/1024/1024/1024),

ROUND((a.data_size+b.temp_size+c.redo_size)/(1024*1024*1024))

into p_phy_data_size, p_phy_temp_size, p_phy_redo_size, p_phy_db_size FROM

(SELECT SUM(bytes) data_size FROM dba_data_files) a,

(SELECT NVL(SUM(bytes),0) temp_size FROM dba_temp_files) b,

(SELECT NVL(SUM(bytes),0) redo_size FROM v$log ) c;

SELECT ROUND(SUM(bytes)/1024/1024/1024) into p_total_segment_size FROM dba_segments;

UTL_SMTP.WRITE_DATA (v_mail_conn, '

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin Dung luong Database</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Physical Data Size (GB)</th>

<th style="border:1px solid black;background-color:lightyellow"> Physical Temp Size (GB)</th>

<th style="border:1px solid black;background-color:lightyellow"> Physical Redo Size (GB)</th>

<th style="border:1px solid black;background-color:lightyellow"> Total Physical Size (not included Archive log) (GB)</th>

<th style="border:1px solid black;background-color:lightyellow"> Total Segment Size (GB)</th>

</tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' || to_char(p_phy_data_size,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' || to_char(p_phy_temp_size,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' || to_char(p_phy_redo_size,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' || to_char(p_phy_db_size,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' || to_char(p_total_segment_size,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

</tr>

</table>

</td>

</tr>');

-- Thong tin ASM:

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin ASM</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Disk_group_name </th>

<th style="border:1px solid black;background-color:lightyellow"> Sector size </th>

<th style="border:1px solid black;background-color:lightyellow"> Block Size </th>

<th style="border:1px solid black;background-color:lightyellow"> State </th>

<th style="border:1px solid black;background-color:lightyellow"> Total (GB) </th>

<th style="border:1px solid black;background-color:lightyellow"> Required_mirror_free (GB) </th>

<th style="border:1px solid black;background-color:lightyellow"> Usable_file (GB) </th>

<th style="border:1px solid black;background-color:lightyellow"> Voting_file </th>

</tr>');

for v_asm in c_asm

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_asm.sector_size||'</th>

<th style="border:1px solid black;"> ' ||v_asm.block_size||'</th>

<th style="border:1px solid black;"> ' ||v_asm.state||'</th>

<th style="border:1px solid black;"> ' ||to_Char(v_asm.total_gb,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_asm.free_gb,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_asm.hot_used_gb,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_asm.cold_used_gb,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_asm.required_mirror_free_gb,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_asm.usable_file_gb,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||v_asm.voting_files ||'%</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin dung luong tablespace:

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin dung luong Tablespace</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Tablespace_Name </th>

<th style="border:1px solid black;background-color:lightyellow"> Trang thai </th>

<th style="border:1px solid black;background-color:lightyellow"> Total (GB) </th>

<th style="border:1px solid black;background-color:lightyellow"> Max_total (GB) </th>

</tr>');

for v_tbs in c_tbs

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_tbs.tablespace_name||'</th>

<th style="border:1px solid black;"> ' ||v_tbs.status||'</th>

<th style="border:1px solid black;"> ' ||to_Char(v_tbs.Total_In_GB,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_tbs.Max_total_In_GB,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_tbs.Used_in_GB,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||v_tbs.Used_percentage ||'%</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin schema dung luong lon hon 50GB

UTL_SMTP.WRITE_DATA (v_mail_conn, '

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin Schema dung luong lon hon 50GB</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn, '<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Owner </th>

</tr>');

for v_schm in c_schm

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_schm.Owner||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_schm.Size_In_GB,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin dung luong cac loai segment

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin dung luong theo Segment</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn, '<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Segment_Type </th>

</tr>');

for v_sgm in c_sgm

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_sgm.segment_type||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_sgm.Size_GB,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin table dung luong lon

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Top 20 table dung luong lon</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Owner </th>

<th style="border:1px solid black;background-color:lightyellow"> Table_Name </th>

<th style="border:1px solid black;background-color:lightyellow"> Table_type </th>

<th style="border:1px solid black;background-color:lightyellow"> Tablespace_Name </th>

<th style="border:1px solid black;background-color:lightyellow"> Num_partition </th>

<th style="border:1px solid black;background-color:lightyellow"> Table_Size (GB) </th>

<th style="border:1px solid black;background-color:lightyellow"> Last_Analyzed </th>

</tr>');

for v_table in c_table

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_table.owner||'</th>

<th style="border:1px solid black;"> ' ||v_table.table_name ||'</th>

<th style="border:1px solid black;"> ' ||v_table.table_type ||'</th>

<th style="border:1px solid black;"> ' ||v_table.tablespace_name ||'</th>

<th style="border:1px solid black;"> ' ||to_Char(v_table.num_part,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_Char(v_table.num_rows,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.') ||'</th>

<th style="border:1px solid black;"> ' ||to_Char(v_table.table_size,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.')||'</th>

<th style="border:1px solid black;"> ' ||to_Char(v_table.last_analyzed,'dd/mm/yyyy') ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin index dung luong lon

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Top 20 Index dung luong lon</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Owner </th>

<th style="border:1px solid black;background-color:lightyellow"> Table_Name </th>

<th style="border:1px solid black;background-color:lightyellow"> Index_Name </th>

<th style="border:1px solid black;background-color:lightyellow"> Index_Size (GB) </th>

<th style="border:1px solid black;background-color:lightyellow"> Tablespace_Name </th>

<th style="border:1px solid black;background-color:lightyellow"> Status </th>

<th style="border:1px solid black;background-color:lightyellow"> Analyzed </th>

</tr>');

for v_index in c_index

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_index.owner||'</th>

<th style="border:1px solid black;"> ' ||v_index.tab_name ||'</th>

<th style="border:1px solid black;"> ' ||v_index.ind_name ||'</th>

<th style="border:1px solid black;"> ' ||to_char(v_index.index_size,'999G999G999G999', 'NLS_NUMERIC_CHARACTERS=,.')||'</th>

<th style="border:1px solid black;"> ' ||v_index.tablespace_name ||'</th>

<th style="border:1px solid black;"> ' ||v_index.status ||'</th>

<th style="border:1px solid black;"> ' ||to_Char(v_index.analyzed,'dd/mm/yyyy') ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Thong tin invalid object

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Invalid object</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Owner </th>

<th style="border:1px solid black;background-color:lightyellow"> Object_name </th>

<th style="border:1px solid black;background-color:lightyellow"> Object_type </th>

<th style="border:1px solid black;background-color:lightyellow"> Status </th>

</tr>');

for v_invalid_obj in c_invalid_obj

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_invalid_obj.owner||'</th>

<th style="border:1px solid black;"> ' ||v_invalid_obj.object_name ||'</th>

<th style="border:1px solid black;"> ' ||v_invalid_obj.full_name ||'</th>

<th style="border:1px solid black;"> ' ||v_invalid_obj.object_type ||'</th>

<th style="border:1px solid black;"> ' ||v_invalid_obj.last_ddl_time ||'</th>

<th style="border:1px solid black;"> ' ||v_invalid_obj.status ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Performance

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Thong tin Performance</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Instance_ID </th>

<th style="border:1px solid black;background-color:lightyellow"> Pool_Hit_Ratio </th>

<th style="border:1px solid black;background-color:lightyellow"> Dictionary_Cache_Hit_Ratio </th>

<th style="border:1px solid black;background-color:lightyellow"> Library_Cache_Hit_Ratio </th>

<th style="border:1px solid black;background-color:lightyellow"> Library_Cache_Pin_Hit_Ratio </th>

</tr>');

for v_hit_ratio in c_hit_ratio

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_hit_ratio.inst_id||'</th>

<th style="border:1px solid black;"> ' ||v_hit_ratio.p_pool_hit_ratio ||'%</th>

<th style="border:1px solid black;"> ' ||v_hit_ratio.p_dictionary_cache_hit_ratio ||'%</th>

<th style="border:1px solid black;"> ' ||v_hit_ratio.p_lib_cache_hit_ratio ||'%</th>

<th style="border:1px solid black;"> ' ||v_hit_ratio.p_lib_cache_pin_hit_ratio ||'%</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- Top Command

-- By CPU

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Top Command by CPU</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Buffer_gets </th>

<th style="border:1px solid black;background-color:lightyellow"> Disk_Reads </th>

<th style="border:1px solid black;background-color:lightyellow"> Executions </th>

</tr>');

for v_top_cpu in c_top_cpu

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_top_cpu.buffer_gets ||'</th>

<th style="border:1px solid black;"> ' ||v_top_cpu.disk_reads ||'</th>

<th style="border:1px solid black;"> ' ||v_top_cpu.Executions ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- By Buffer_gets

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Top Command by buffer gets</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Buffer_gets </th>

<th style="border:1px solid black;background-color:lightyellow"> Disk_Reads </th>

<th style="border:1px solid black;background-color:lightyellow"> Executions </th>

</tr>');

for v_top_buffer in c_top_buffer

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_top_buffer.cpu_time||'</th>

<th style="border:1px solid black;"> ' ||v_top_buffer.buffer_gets ||'</th>

<th style="border:1px solid black;"> ' ||v_top_buffer.disk_reads ||'</th>

<th style="border:1px solid black;"> ' ||v_top_buffer.Executions ||'</th>

<th style="border:1px solid black;"> ' ||v_top_buffer.sql_id ||'</th>

<th style="border:1px solid black;"> ' ||v_top_buffer.sql_text ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- By Disk_reads

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Top Command by Disk reads</th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Buffer_gets </th>

<th style="border:1px solid black;background-color:lightyellow"> Disk_Reads </th>

<th style="border:1px solid black;background-color:lightyellow"> Executions </th>

</tr>');

for v_top_disk in c_top_disk

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_top_disk.buffer_gets ||'</th>

<th style="border:1px solid black;"> ' ||v_top_disk.disk_reads ||'</th>

<th style="border:1px solid black;"> ' ||v_top_disk.Executions ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>');

-- By Executions

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;background-color:lightgreen;"> Top Command by Executions </th></tr>');

UTL_SMTP.WRITE_DATA (v_mail_conn,'<tr>

<td>

<tr>

<th style="border:1px solid black;background-color:lightyellow"> Buffer_gets </th>

<th style="border:1px solid black;background-color:lightyellow"> Disk_Reads </th>

<th style="border:1px solid black;background-color:lightyellow"> Executions </th>

</tr>');

for v_top_exec in c_top_exec

loop

UTL_SMTP.WRITE_DATA (v_mail_conn,'

<tr>

<th style="border:1px solid black;"> ' ||v_top_exec.buffer_gets ||'</th>

<th style="border:1px solid black;"> ' ||v_top_exec.disk_reads ||'</th>

<th style="border:1px solid black;"> ' ||v_top_exec.Executions ||'</th>

</tr>');

end loop;

UTL_SMTP.WRITE_DATA (v_mail_conn,

'</table>

</td>

</tr>

</table>

</body>

</html>' || UTL_TCP.CRLF);

UTL_SMTP.CLOSE_DATA (v_mail_conn);

UTL_SMTP.QUIT (v_mail_conn);

end;

BEGIN

SYS.DBMS_SCHEDULER.DROP_JOB

(job_name => 'SYS.DBAMBF_HC_FULL');

END;

BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB

(

job_name => 'SYS.DBAMBF_HC_FULL'

,start_date => TO_TIMESTAMP_TZ('2020/11/28 02:04:40.218630 Asia/Saigon','yyyy/mm/dd hh24:mi:ss.ff tzr')

,repeat_interval => 'FREQ=DAILY;INTERVAL=7'

,end_date => NULL

,job_class => 'DEFAULT_JOB_CLASS'

,job_type => 'PLSQL_BLOCK'

,job_action => 'Begin

sys.pr_db_daily_full(''DBAVIET'',''tranbinh48ca@gmail.com'');

end;

,comments => NULL

);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'RESTARTABLE'

,value => FALSE);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'LOGGING_LEVEL'

,value => SYS.DBMS_SCHEDULER.LOGGING_OFF);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'MAX_FAILURES');

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'MAX_RUNS');

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'STOP_ON_WINDOW_CLOSE'

,value => FALSE);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'JOB_PRIORITY'

,value => 3);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'SCHEDULE_LIMIT');

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

( name => 'SYS.DBAMBF_HC_FULL'

,attribute => 'AUTO_DROP'

,value => TRUE);

SYS.DBMS_SCHEDULER.ENABLE

(name => 'SYS.DBAMBF_HC_FULL');

END;

select * from dba_scheduler_jobs where owner='SYS' and job_name='DBAMBF_HC_FULL';

Begin

sys.pr_db_daily_full('DBAVIET','tranbinh48ca@gmail.com');

end;

Kết quả như sau:

Hy vọng hữu ích cho bạn.

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================

[VIP5] Báo cáo hiện trạng cơ sở dữ liệu Oracle hàng ngày_Full, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,khóa học pl/sql, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty

Thứ Năm, 3 tháng 3, 2022

[VIP5] Báo cáo hiện trạng cơ sở dữ liệu Oracle hàng ngày_Full

ĐỌC NHIỀU