Thứ Năm, 24 tháng 11, 2022

Gói vận hành tự động DBA_OPERATIONS cho Oracle Database nhỏ

Mục đích: Gói DBA_OPERATIONS vận hành tự động cho Oracle Database nhỏ

1. Tạo gói

-- Start of DDL Script for Package SYS.DBA_OPERATIONS

-- Drop the old instance of DBA_OPERATIONS

--DROP PACKAGE sys.dba_operations

CREATE OR REPLACE

PACKAGE sys.dba_operations

PROCEDURE extend_space;

procedure truncate_table(table_owner varchar2, table_name varchar2);

PROCEDURE TBS_WARNING;

PROCEDURE SYS.AUTO_DROP_PAR

procedure gather_table_nonpart

procedure gather_table_part

-- Cần hàm nào tự động thì bổ sung thêm vào

END; -- Package spec

CREATE OR REPLACE

PACKAGE BODY sys.dba_operations

free_space_low_level NUMBER := 50000;

PROCEDURE extend_space

CURSOR c_free_space -- get tablespace free left 200MB.

SELECT

a.tablespace_name,

ROUND (a.bytes_alloc / 1024 / 1024) megs_alloc,

ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) megs_free,

ROUND (maxbytes / 1048576) MAX,

ROUND (maxbytes / 1048576)-ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024) free_tbs

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 (LOWER (a.tablespace_name) IN

('indx','dump_data',

'data','indx' || to_char(sysdate,'YYYY'),

--'undotbs1','undotbs2','undotbs3','undotbs4',

'indx' || to_char(sysdate,'YYYYMM'),

'indx' || to_char(sysdate-30,'YYYYMM'),

'data' || to_char(sysdate,'YYYY'),

'data' || to_char(sysdate,'YYYYMM'),

'data' || to_char(sysdate-30,'YYYYMM'),

'logs' || to_char(sysdate,'YYYY'),

'data_lob'

)

AND ROUND (maxbytes / 1048576)-ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024) < free_space_low_level

order by ROUND (maxbytes / 1048576)-ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024) desc;

/* SELECT tablespace_name, SUM (BYTES) / 1024 / 1024

FROM SYS.dba_free_space

WHERE (lower(tablespace_name) in ('indx','dump_data',

'data','indx' || to_char(sysdate,'YYYY'),

--'undotbs1','undotbs2','undotbs3','undotbs4',

'indx' || to_char(sysdate,'YYYYMM'),

'indx' || to_char(sysdate-30,'YYYYMM'),

'data' || to_char(sysdate,'YYYY'),

'data' || to_char(sysdate,'YYYYMM'),

'data' || to_char(sysdate-30,'YYYYMM'),

'logs' || to_char(sysdate,'YYYY'),

'data_lob', 'ccgw_clob'

))

GROUP BY tablespace_name

HAVING SUM (BYTES) / 1024 / 1024 < free_space_low_level;*/

v_sql VARCHAR2 (2000);

msg VARCHAR2 (1000);

BEGIN

FOR v_free_space IN c_free_space

LOOP

if (v_free_space.tablespace_name !='DATA_LOB' and v_free_space.tablespace_name!='CCGW_CLOB') then

begin

v_sql:='ALTER TABLESPACE '

|| v_free_space.tablespace_name

|| ' ADD DATAFILE ''+DATA_GOLD'' size 1G autoextend on next 100m;';

EXECUTE IMMEDIATE v_sql;

insert into tc_monitor (msg, type, note) values (v_sql, 'DF','Add datafile');

commit;

--dbms_output.put_line('sql: ' || v_sql);

/* EXCEPTION

WHEN OTHERS

THEN

msg :='TBS AutoExtend: '|| SQLERRM;

END;

elsif (v_free_space.tablespace_name ='DATA_LOB' or v_free_space.tablespace_name='NGHIEPVU2_CLOB') then

begin

v_sql:='ALTER TABLESPACE '

|| v_free_space.tablespace_name

|| ' ADD DATAFILE ''+DATA'' size 1g autoextend on next 100m;';

EXECUTE IMMEDIATE v_sql;

--dbms_output.put_line('sql: ' || v_sql);

end;

end if;

--CLOSE c_free_space;

END LOOP;

END;

procedure truncate_table(table_owner varchar2, table_name varchar2)

l_sql varchar2(2500);

begin

l_sql := 'TRUNCATE TABLE '||upper(table_owner)||'.'||upper(table_name);

execute immediate l_sql;

insert into tc_monitor (msg, type, note) values (l_sql, 'truncate','Truncate table');

commit;

end;

CREATE OR REPLACE PROCEDURE SYS.AUTO_DROP_PAR

CURSOR c_partition

SELECT table_owner,table_name, partition_name

FROM dba_tab_partitions

where sysdate - to_date(SUBSTR(partition_name,4-length(partition_name)),'yyyy/mm/dd') > 15

and length(partition_name)=12

and table_name ='TRANS_LOG'

order by table_owner, table_name, partition_name;

v_sql_command VARCHAR2 (2400);

BEGIN

-- add partitions

FOR v_data IN c_partition

LOOP

BEGIN

EXECUTE IMMEDIATE ' Alter table ' || v_data.table_owner ||'.'

|| v_data.table_name

|| ' drop partition '

|| v_data.partition_name;

--dbms_output.put_line('Droped partition ' || v_data.table_name || '.' ||v_data.partition_name);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.put_line (SQLERRM);

END;

END LOOP;

END;

PROCEDURE TBS_WARNING

msg VARCHAR2(1000):='';

CURSOR c1

SELECT a.tablespace_name , ROUND (b.BYTES / 1024 / 1024, 2) AS mb_free

FROM (SELECT tablespace_name, SUM (BYTES) BYTES

FROM dba_data_files

GROUP BY tablespace_name) a,

(SELECT tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) largest

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

AND (lower(a.tablespace_name) in ('data','indx','undotbs1','undotbs2','regb_req_data01','regb_req_data02','regb_req_data03','regb_req_data04','regb_req_data05','regb_req_data06','data','indx' || to_char(sysdate,'YYYYMM'),'data' || to_char(sysdate,'YYYYMM'),'logs' || to_char(sysdate,'YYYYMM')))

AND (ROUND (b.BYTES / 1024 / 1024, 2) < 500)

ORDER BY ((a.BYTES - b.BYTES) / a.BYTES) DESC;

TYPE tbs_type IS RECORD (tablespace_name dba_data_files.tablespace_name%TYPE,

mb_free dba_free_space.bytes%TYPE);

rec tbs_type;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO rec.tablespace_name, rec.mb_free;

EXIT WHEN c1%NOTFOUND;

--msg:=msg || ';' || rec.tablespace_name ||':'|| rec.mb_free;

--dbms_output.put_line(rec.tablespace_name ||':'|| rec.mb_free);

END loop;

CLOSE c1;

END;

procedure gather_table_part

p_gather_date_par date:=SYSDATE;

p_month date := trunc(sysdate);

CURSOR c_partitioned_tables

SELECT owner, table_name, partition_name, last_analyzed,stale_stats,num_rows,

'begin

dbms_stats.gather_table_stats

(ownname=>''' || OWNER || ''',

tabname=>''' || table_name || ''',

partname=>''' || partition_name || ''',

granularity=>''partition'',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => ''FOR ALL COLUMNS SIZE AUTO'',

cascade=>true,

degree=>8);

end;

script

FROM dba_tab_statistics where owner in ('APP_OWNER')

and table_name not like 'XXX%' and table_name not like 'TMP%' and table_name not like '%$%'

--and table_name in ('TAB1')

--and partition_name like '%2021%'

and ((length(partition_name)=12 and to_date(substr(partition_name,5,8),'YYYYMMDD')>sysdate-30 and to_date(substr(partition_name,5,8),'YYYYMMDD')<trunc(sysdate)-1)

or (length(partition_name)=10 and to_date(substr(partition_name,5,6),'YYYYMM')<trunc(sysdate) and to_date(substr(partition_name,5,6),'YYYYMM')>trunc(sysdate)-30)

or (length(partition_name)=8 and substr(partition_name,5,6)= TO_CHAR(sysdate, 'YYYY'))

)

--and ( (partition_name LIKE 'DATA' || TO_CHAR (trunc(sysdate), 'YYYYMM') || '%')

--OR (partition_name LIKE 'DATA'|| TO_CHAR (trunc(sysdate) + 30, 'YYYYMM')|| '%')

--OR (partition_name LIKE'DATA'|| TO_CHAR (trunc(sysdate) + 60, 'YYYYMM')|| '%')

--AND (table_name IN ('TAB1', 'TAB2'))

-- )

-- (stale_stats is null or stale_stats = 'YES')

and (last_analyzed is NULL OR NUM_ROWS=0 or stale_stats is null or stale_stats = 'YES')

--and (last_analyzed is NULL)

and object_type = 'PARTITION'

ORDER BY partition_name desc, owner, table_name, partition_name;

v_table_name VARCHAR2 (100);

v_par_name VARCHAR2 (100);

v_err varchar2(500);

BEGIN

DBMS_OUTPUT.ENABLE (buffer_size => NULL);

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

values(binhtv.dbamf_log_jobs_seq.nextval,'Starting sys.dba_op.gather_par_tables',1,sysdate,'binhtv.dbamf_log_jobs');*/

--commit;

dbms_output.put_line('Before Loop');

FOR v_partitioned_tables IN c_partitioned_tables

LOOP

dbms_output.put_line('Starting gather_par_tables: ' ||v_partitioned_tables.owner||':'||v_table_name||':'||v_partitioned_tables.partition_name);

v_table_name := v_partitioned_tables.table_name;

v_par_name := v_partitioned_tables.partition_name;

EXECUTE IMMEDIATE v_partitioned_tables.script;

dbms_output.put_line('Completed gather_par_tables: ' ||v_partitioned_tables.owner||':'||v_table_name||':'||v_partitioned_tables.partition_name);

END LOOP;

dbms_output.put_line('End Loop');

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

values(binhtv.dbamf_log_jobs_seq.nextval,'Completed sys.dba_op.gather_par_tables',1,sysdate,'binhtv.dbamf_log_jobs');*/

commit;

--dbms_output.put_line('In_C');

EXCEPTION

WHEN OTHERS

THEN

/* p_error :='Error while gathering statistics for ' || v_table_name || ': ' || v_par_name || ': ' || SQLERRM;*/

--INSERT INTO binhtv.log_gather_stats (errormsg, errorsql) VALUES ('error', p_error);

--COMMIT;

/* 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_op.gather_par_tables',-1,sysdate,'Error sys.dba_op.gather_par_tables, '|| v_err);

commit;*/

dbms_output.put_line('Loi gather report partition table: ' || v_err);

--sys.send_sms_binhtv('Loi gather report partition table: ' || v_err);

END;

procedure gather_table_nonpart

p_gather_date date:=SYSDATE-5;

CURSOR c_tables

SELECT table_name,last_analyzed, num_rows,stale_stats,partition_name,partition_position,

'begin

dbms_stats.gather_table_stats

(ownname => '''|| owner || ''',

tabname => ''' || table_name || ''',

cascade => true,

estimate_percent => 4,

degree => 8);

end; '

script

from dba_tab_statistics a

WHERE owner in ('APP_OWNER')

and table_name in (select table_name from dba_tab_statistics a WHERE owner in ('APP_OWNER') group by table_name having count(*) =1)

--and partitioned = 'NO'

AND table_name NOT LIKE '%$%' and table_name not like '%XX%' and table_name not like '%ZZ%' and table_name not like 'TMP%' and table_name not like 'TEMP%' and table_name not like '%TEST%'

--and table_name not in ('DUMP_20210501_HN','DUMP_20210601_HN'')

--and table_name in ('THREAD')

and object_type = 'TABLE'

--AND nvl(last_analyzed,sysdate- 15) < SYSDATE

and (stale_stats is null or stale_stats = 'YES' or num_rows=0 or last_analyzed is null)

ORDER BY owner,table_name;

v_table_name VARCHAR2 (100);

v_err varchar2(1000);

BEGIN

DBMS_OUTPUT.ENABLE (buffer_size => NULL);

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

values(binhtv.dbamf_log_jobs_seq.nextval,'Starting sys.dba_op.gather_unpar_tables',1,sysdate,'binhtv.dbamf_log_jobs');

commit;*/

FOR v_tables IN c_tables

LOOP

v_table_name := v_tables.table_name;

--dbms_output.put_line('unpar table: ' ||v_table_name);

EXECUTE IMMEDIATE v_tables.script;

---dbms_output.put_line('unpar table: ' ||v_table_name);

END LOOP;

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

values(binhtv.dbamf_log_jobs_seq.nextval,'Completed sys.dba_op.gather_unpar_tables',1,sysdate,'binhtv.dbamf_log_jobs');

commit;*/

EXCEPTION

WHEN OTHERS

THEN

/* p_error :=

'Error while gathering statistics for '

|| v_table_name

|| ': '

|| SQLERRM;

INSERT INTO binhtv.log_gather_stats (

errormsg, errorsql

)

VALUES (

'error', p_error

);

COMMIT;*/

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

dbms_output.put_line('Loi gather bang non-partition Report: '||v_err);

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

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

commit;*/

END;

-- End of DDL Script for Package SYS.DBA_OPERATIONS

2. Tạo job:

Sau đó dặt các job dùng giao diện hoặc câu lệnh tương tự như sau:

BƯỚC 3: Tạo job từ sched.Jobs: 10 phút chạy 1 lần

Vào giao diện hoặc chạy câu lệnh

BEGIN

SYS.DBMS_SCHEDULER.DROP_JOB

(job_name => 'SYS.DBAVIET_ADD_DF');

END;

BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB

(

job_name => 'SYS.DBAVIET_ADD_DF'

,start_date => TO_TIMESTAMP_TZ('2021/05/07 10:45:13.876967 Asia/Ho_Chi_Minh','yyyy/mm/dd hh24:mi:ss.ff tzr')

,repeat_interval => 'FREQ=MINUTELY;INTERVAL=10'

,end_date => NULL

,job_class => 'DEFAULT_JOB_CLASS'

,job_type => 'STORED_PROCEDURE'

,job_action => 'SYS.DBA_OPERATIONS.EXTEND_SPACE'

,comments => NULL

);

SYS.DBMS_SCHEDULER.SET_ATTRIBUTE