TRẦN VĂN BÌNH MASTER: Hé lộ bí mật giám sát cơ sở dữ liệu chủ động

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 thủ tục

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 <= 20 or round(ram_free_k/1024/1024,0) <=70)

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-3)

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) > 100

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;

--cdr_monitor.send_email_html_m('binhtv10@gmail.com;tranbinh48ca@gmail.com','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 ('CDR_OWNER','CUS_OWNER','MC_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 'CDR_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 ('USER_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 ('USER_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 ('USER_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 ('USER_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 >= 250 then

binhtv.send_email ('binhtv@gmail.com,'HIGH - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss'), tAll);

ELSIF nActiveSession > 200 and nActiveSession < 250 then

binhtv.send_email ('binhtv@gmail.com,'MID - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss'), tAll);

ELSE

binhtv.send_email ('binhtv@gmail.com,'LOW - HC_' || tDBName ||'_ALL NGAY ' || to_char(sysdate,'dd/mm/yyyy hh24:mm:ss'), tAll);

END IF;

--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 ('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. Chạy và có thể đặt vào job

Chạy định kỳ vào 8h sáng, 10h sáng, 14h chiều, 16h chiều nhé (có thể đặt lịch dày hơn hay giãn hơn tùy bạn)

begin

hc_all;

end;

3. Kết quả

- Nhìn vào tiêu đề (HIGH, LOW, MID) có thể biết ngay hiện trạng CSDL

- Đọc chi tiết về tải Active Session Session, Total Session, session active theo user, backup, ASM Disk, tablespace có dung lượng cao cần chú ý, dung lượng theo từng user, phân vùng OS, archive sinh ra, thông tin Gather:

Phục lục: Danh sách các bảng cần tạo:

DROP TABLE BINHTV.DBAMF_LOG_JOBS CASCADE CONSTRAINTS;

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

STORAGE (

INITIAL 64K

NEXT 1M

MINEXTENTS 1

MAXEXTENTS UNLIMITED

PCTINCREASE 0

BUFFER_POOL DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

MONITORING;

-- Start of DDL Script for Table BINHTV.DBAMF_CT_TBS_SIZE

-- Generated 5-Nov-2020 16:12:10 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CT_TBS_SIZE

DROP TABLE binhtv.dbamf_ct_tbs_size

CREATE TABLE binhtv.dbamf_ct_tbs_size

(id NUMBER,

tbs_name VARCHAR2(100 BYTE),

size_gb NUMBER,

event_date DATE DEFAULT sysdate,

note VARCHAR2(1000 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

DROP SEQUENCE BINHTV.DBAMF_LOG_JOBS_SEQ;

CREATE SEQUENCE BINHTV.DBAMF_LOG_JOBS_SEQ

START WITH 2156723

MAXVALUE 9999999999999999999999999999

MINVALUE 1

NOCYCLE

CACHE 20

NOORDER;

-- End of DDL Script for Table BINHTV.DBAMF_CT_TBS_SIZE

-- Start of DDL Script for Table BINHTV.DBAMF_CT_USER_SIZE

-- Generated 5-Nov-2020 16:12:14 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CT_USER_SIZE

DROP TABLE binhtv.dbamf_ct_user_size

CREATE TABLE binhtv.dbamf_ct_user_size

(id NUMBER,

user_name VARCHAR2(100 BYTE),

size_mb NUMBER,

event_date DATE DEFAULT sysdate,

note VARCHAR2(1000 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_CT_USER_SIZE

-- Start of DDL Script for Table BINHTV.DBAMF_CT_VMSTAT

-- Generated 5-Nov-2020 16:12:18 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CT_VMSTAT

DROP TABLE binhtv.dbamf_ct_vmstat

CREATE TABLE binhtv.dbamf_ct_vmstat

(event_date DATE,

duration NUMBER,

server_name VARCHAR2(30 BYTE),

runque_waits NUMBER,

swap_in NUMBER,

swap_out NUMBER,

cpu_user NUMBER,

cpu_sys NUMBER,

cpu_idle NUMBER,

cpu_wait NUMBER,

swapd NUMBER,

ram_free_k NUMBER)

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- Grants for Table

GRANT INSERT ON binhtv.dbamf_ct_vmstat TO monitor

-- End of DDL Script for Table BINHTV.DBAMF_CT_VMSTAT

-- Start of DDL Script for Table BINHTV.DBAMF_DISK_USAGE

-- Generated 5-Nov-2020 16:12:22 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_DISK_USAGE

DROP TABLE binhtv.dbamf_disk_usage

CREATE TABLE binhtv.dbamf_disk_usage

(host_name VARCHAR2(40 BYTE),

disk_name VARCHAR2(50 BYTE),

disk_size VARCHAR2(20 BYTE),

disk_free VARCHAR2(20 BYTE),

disk_percen VARCHAR2(10 BYTE),

disk_mount VARCHAR2(50 BYTE),

db_ip VARCHAR2(20 BYTE),

event_date DATE DEFAULT sysdate)

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_DISK_USAGE

-- Start of DDL Script for Table BINHTV.DBAMF_LOG_GG

-- Generated 5-Nov-2020 16:12:26 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_LOG_GG

DROP TABLE binhtv.dbamf_log_gg

CREATE TABLE binhtv.dbamf_log_gg

(id NUMBER,

program VARCHAR2(20 BYTE),

status VARCHAR2(50 BYTE),

gg_group VARCHAR2(100 BYTE),

lag VARCHAR2(50 BYTE),

time_since_chkpt VARCHAR2(50 BYTE),

event_date DATE DEFAULT sysdate)

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_LOG_GG

-- Start of DDL Script for Table BINHTV.DBAMF_LOG_JOBS

-- Generated 5-Nov-2020 16:12:31 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_LOG_JOBS

DROP TABLE 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))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_LOG_JOBS

-- Start of DDL Script for Table BINHTV.DBAMF_LOG_OP

-- Generated 5-Nov-2020 16:12:34 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_LOG_OP

DROP TABLE binhtv.dbamf_log_op

CREATE TABLE binhtv.dbamf_log_op

(id NUMBER,

msg VARCHAR2(1000 BYTE),

event_datetime TIMESTAMP (6) DEFAULT sysdate,

type VARCHAR2(1000 BYTE),

note VARCHAR2(1000 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_LOG_OP

-- Start of DDL Script for Table BINHTV.DBAMF_LOG_WN

-- Generated 5-Nov-2020 16:12:37 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_LOG_WN

DROP TABLE binhtv.dbamf_log_wn

CREATE TABLE binhtv.dbamf_log_wn

(id NUMBER,

msg VARCHAR2(1000 BYTE),

event_date TIMESTAMP (6) DEFAULT sysdate,

type VARCHAR2(1000 BYTE),

note VARCHAR2(1000 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_LOG_WN

-- Start of DDL Script for Table BINHTV.DBAMF_TAB_CYCLE

-- Generated 5-Nov-2020 16:12:42 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_TAB_CYCLE

DROP TABLE binhtv.dbamf_tab_cycle

CREATE TABLE binhtv.dbamf_tab_cycle

(id NUMBER,

owner VARCHAR2(50 BYTE),

table_name VARCHAR2(200 BYTE),

purpose VARCHAR2(1000 BYTE),

cycle_days NUMBER,

rw_days NUMBER,

readonly_days NUMBER,

size_mb NUMBER,

num_rows NUMBER,

note VARCHAR2(1000 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_TAB_CYCLE

-- Start of DDL Script for Table BINHTV.DBAMF_CT_SQL

-- Generated 5-Nov-2020 16:12:06 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CT_SQL

DROP TABLE binhtv.dbamf_ct_sql

CREATE TABLE binhtv.dbamf_ct_sql

(inst_id NUMBER,

username VARCHAR2(30 BYTE),

sid NUMBER,

osuser VARCHAR2(30 BYTE),

machine VARCHAR2(64 BYTE),

event VARCHAR2(64 BYTE),

sql_id VARCHAR2(13 BYTE),

sql_text VARCHAR2(3000 BYTE),

logon_time DATE,

event_date DATE DEFAULT sysdate)

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_CT_SQL

-- Start of DDL Script for Table BINHTV.DBAMF_CT_SESSION

-- Generated 5-Nov-2020 16:12:01 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CT_SESSION

DROP TABLE binhtv.dbamf_ct_session

CREATE TABLE binhtv.dbamf_ct_session

(username VARCHAR2(50 BYTE),

status VARCHAR2(10 BYTE),

count_low VARCHAR2(5 BYTE),

count_avg VARCHAR2(5 BYTE),

count_high VARCHAR2(5 BYTE),

count_critical VARCHAR2(5 BYTE),

notes VARCHAR2(1000 BYTE),

event_date DATE DEFAULT sysdate,

count NUMBER)

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_CT_SESSION

-- Start of DDL Script for Table BINHTV.DBAMF_CT_OBJ_SIZE

-- Generated 5-Nov-2020 16:11:56 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CT_OBJ_SIZE

DROP TABLE binhtv.dbamf_ct_obj_size

CREATE TABLE binhtv.dbamf_ct_obj_size

(id NUMBER,

object_owner VARCHAR2(50 BYTE),

object_name VARCHAR2(200 BYTE),

size_gb NUMBER,

event_date DATE DEFAULT sysdate,

note VARCHAR2(1000 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_CT_OBJ_SIZE

-- Start of DDL Script for Table BINHTV.DBAMF_CT_DISK_USAGE

-- Generated 5-Nov-2020 16:11:52 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CT_DISK_USAGE

DROP TABLE binhtv.dbamf_ct_disk_usage

CREATE TABLE binhtv.dbamf_ct_disk_usage

(host_name VARCHAR2(40 BYTE),

disk_name VARCHAR2(50 BYTE),

disk_size VARCHAR2(20 BYTE),

disk_free VARCHAR2(20 BYTE),

disk_percen VARCHAR2(10 BYTE),

disk_mount VARCHAR2(50 BYTE),

db_ip VARCHAR2(20 BYTE),

event_date DATE DEFAULT sysdate)

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_CT_DISK_USAGE

-- Start of DDL Script for Table BINHTV.DBAMF_CPU_MEM_USAGE

-- Generated 5-Nov-2020 16:11:45 from BINHTV@DBAVIET

-- Drop the old instance of DBAMF_CPU_MEM_USAGE

DROP TABLE binhtv.dbamf_cpu_mem_usage

CREATE TABLE binhtv.dbamf_cpu_mem_usage

(id NUMBER,

pct_usage NUMBER,

type VARCHAR2(50 BYTE),

event_date DATE DEFAULT sysdate,

note VARCHAR2(100 BYTE))

PCTFREE 10

INITRANS 1

MAXTRANS 255

TABLESPACE users

STORAGE (

INITIAL 65536

NEXT 1048576

MINEXTENTS 1

MAXEXTENTS 2147483645

)

NOCACHE

MONITORING

NOPARALLEL

LOGGING

-- End of DDL Script for Table BINHTV.DBAMF_CPU_MEM_USAGE

*****

Hãy nhanh tay đăng ký khóa học "Quản trị cơ sở dữ liệu Oracle 12c cơ bản" trên Unica giành cho những bạn mới học cơ sở dữ liệu Oracle hoặc cần nâng cao kiến thức, kinh nghiệm thực tiễn trên Unica (giá gốc 900K, giá khi đăng ký HÔM NAY là 299K):

Bước 1: Truy cập Unica.vn, bấm ĐĂNG KÝ nếu chưa có tài khoản

Bước 2: Đăng nhập tài khoản học viên trên Unica.vn

Bước 3: Truy cập https://unica.vn/quan-tri-csdl-oracle-12c-co-ban?aff=291184&coupon=ORAAZ_GG

Bước 4: Bấm KÍCH HOẠT NGAYđể thanh toán và bắt đầu vào học.

*****

@ Trần Văn Bình - Founder of Oracle DBA AZ #BossData #OraAz #OracleDBAAz #OracleTutorial #Quản_trị_cơ_sở_dữ_liệu_Oracle #OracleDBA #OracleDatabaseAdministration

Thứ Năm, 9 tháng 1, 2020

Hé lộ bí mật giám sát cơ sở dữ liệu chủ động

ĐỌC NHIỀU