Tự động add datafile vào Tablespace nếu dung lượng dưới 80%

DECLARE

    — tạo con trỏ duyệt qua các bản ghi nếu tồn tại khi thực hiện truy vấn bên dưới

CURSOR c_tablespace IS

— truy vấn nếu dung lượng sử dụng gần đạt dung lượng tổng thì list ra: 80%

— tablespace phải ở chế độ AUTOEXTEND

— DATABASE phải đặt ở chế độ OMF để không cần thiết lập tên và đường dẫn của datafile bên dưới nhé

SELECT

df.tablespace_name,

df.Max_size AS DUNG_LUONG_TONG,

df.total_space_mb DUNG_LUONG_DA_CAP,

NVL(fs.free_space_mb, 0) AS DUNG_LUONG_CHUA_SU_DUNG,

df.total_space_mb – NVL(fs.free_space_mb, 0) AS DUNG_LUONG_DA_SU_DUNG,

ROUND(((df.total_space_mb – NVL(fs.free_space_mb, 0)) / df.total_space_mb) * 100, 2) AS pct_used,

ROUND(((df.total_space_mb – NVL(fs.free_space_mb, 0)) / df.Max_size) * 100, 2) AS pct_used_Total

FROM

(SELECT

tablespace_name,

ROUND(SUM(bytes) / 1024 / 1024,0) AS total_space_mb,

ROUND(sum(maxbytes/1024/1024),0) AS Max_size

FROM

dba_data_files

GROUP BY

tablespace_name) df

LEFT JOIN

(SELECT

tablespace_name,

ROUND(SUM(bytes) / 1024 / 1024,0) AS free_space_mb

FROM

dba_free_space

GROUP BY

tablespace_name) fs

ON

df.tablespace_name = fs.tablespace_name

WHERE     ROUND(((df.total_space_mb – NVL(fs.free_space_mb, 0)) / df.Max_size) * 100, 2) >=80

ORDER BY

pct_used DESC;

— biến tham số cho viêc add thêm datafile

v_tablespace_name VARCHAR2(30);

    –v_new_datafile_name VARCHAR2(200);

    v_sql VARCHAR2(1000);

BEGIN

    FOR ts IN c_tablespace LOOP

        v_tablespace_name := ts.tablespace_name;

—        v_new_datafile_name := ‘/path/to/datafiles/’ || v_tablespace_name || ‘_’ || TO_CHAR(SYSDATE, ‘YYYYMMDDHH24MISS’) || ‘.dbf’;

        v_sql := ‘ALTER TABLESPACE ‘ || v_tablespace_name || ‘ ADD DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED’;

        EXECUTE IMMEDIATE v_sql;

        DBMS_OUTPUT.PUT_LINE(‘Added new datafile to tablespace ‘ || v_tablespace_name);

    END LOOP;

END;

/

#!/bin/bash

ORACLE_SID=your_sid

ORACLE_HOME=/path/to/your/oracle_home

export ORACLE_SID ORACLE_HOME

sqlplus -s / as sysdba <<EOF

@/path/to/add_datafile_if_needed.sql

EXIT

EOF

0 * * * * /path/to/add_datafile_auto80.sh