[PLSQL]當Tablespace的剩餘空間不足時，自動增加Datafile

        因為工作需要用Oracle，小弟只好自學了，因為剛開始不熟所以原廠說什就配合什，那時原廠DBA說要時常監控tablespace的剩餘空間，最好兩個小時就check一次，如果小於2GB時，就要主動增加datafile，避免單一datafile自動增長超過4G，那時想說Oracle怎麼那麼麻煩，不就一開始估計會長多大，就先分配好足夠的空間就好嗎？

        正因為不熟所以只好傻傻地照做，想說寫個Procedure來幫我做這件事好了，於是就產生的這個Procedure囉，但後來還是沒用到，但還是放上來給有需要的人參考，不過話說PLSQL跟T-SQL差好多啊

CREATE OR REPLACE PROCEDURE SP_AUTO_ADD_DATAFILE

IS

   datafilecount      NUMBER;

   adddatafileno      NUMBER;

   v_datafileno       VARCHAR2 (10);

   v_file_name        VARCHAR2 (128);

   v_file_part_name   VARCHAR2 (128);

   v_add_file_name    VARCHAR2 (128);

   v_size             VARCHAR2 (10);

   v_next_size        VARCHAR2 (10);

   v_max_size         VARCHAR2 (10);

   sql_stmt           VARCHAR2 (256);

   CURSOR c_table

   IS

      SELECT tablespace_name

        FROM (  SELECT tablespace_name,

                       ROUND (SUM (bytes / (1024 * 1024 * 1024)), 2) free

                  FROM SYS.dba_free_space

              GROUP BY tablespace_name)

       WHERE free < 2.5;

BEGIN

   FOR r_table IN c_table

   LOOP

      --DBMS_OUTPUT.PUT_LINE (r_table.tablespace_name);

      SELECT COUNT (file_name)

        INTO datafilecount

        FROM dba_data_files

       WHERE tablespace_name = r_table.tablespace_name;

      adddatafileno := datafilecount + 1;

      SELECT file_name

        INTO v_file_name

        FROM dba_data_files

       WHERE tablespace_name = r_table.tablespace_name AND ROWNUM = 1;

      --dbms_output.put_line (v_file_name);

      IF r_table.tablespace_name IN ('SYSAUX',

                                     'SYSTEM',

                                     'USERS',

                                     'UNDOTBS')

      THEN

         IF adddatafileno <= 9

         THEN

            v_datafileno := '0' || adddatafileno;

         ELSE

            v_datafileno := adddatafileno;

         END IF;

         v_file_part_name :=

            SUBSTR (v_file_name, 1, INSTR (v_file_name, '.') - 1 - 2);

      ELSE

         IF adddatafileno <= 9

         THEN

            v_datafileno := '00' || adddatafileno;

         ELSIF adddatafileno <= 99

         THEN

            v_datafileno := '0' || adddatafileno;

         ELSE

            v_datafileno := adddatafileno;

         END IF;

         v_file_part_name :=

            SUBSTR (v_file_name, 1, INSTR (v_file_name, '.') - 1 - 3);

      END IF;

      --dbms_output.put_line (v_datafileno);

      --dbms_output.put_line (v_file_part_name);

      v_add_file_name := v_file_part_name || v_datafileno || '.dbf';

      --dbms_output.put_line (v_add_file_name);

      /*

      alter tablespace mva_d10_a

      add datafile

      '/home/oracle/dbspace/mva_d10_a_005.dbf'

      size

      4000m

      autoextend on

      next 100m

      maxsize 8000m  ;

      */

      SELECT ROUND (d.bytes / 1024 / 1024, 2),

             ROUND (d.increment_by * (d.bytes / d.BLOCKS) / 1024 / 1024, 2),

             ROUND (d.maxbytes / 1024 / 1024, 2)

        INTO v_size, v_next_size, v_max_size

        FROM dba_data_files d INNER JOIN v$datafile v ON d.file_id = v.file#

       WHERE d.tablespace_name = r_table.tablespace_name AND ROWNUM = 1;

      --dbms_output.put_line (v_size || ' ' || v_next_size || ' ' || v_max_size );

      sql_stmt :=

            'alter tablespace '

         || r_table.tablespace_name

         || ' add datafile '

         || CHR (39)

         || v_add_file_name

         || CHR (39)

         || ' size '

         || v_size

         || 'm'

         || ' autoextend on next '

         || v_next_size

         || 'm'

         || ' maxsize '

         || v_max_size

         || 'm';

      --dbms_output.put_line(sql_stmt);

      EXECUTE IMMEDIATE sql_stmt;

   END LOOP;

END;

/