每一名DBA都有可能遇到因表、索引及LOB欄位均存放在同一個表空間而帶來的效能問題。最簡單的解決方法就是將其分門別類的存放。例如，將資料庫中的表單獨存放在特定的表空間中，我們暫且稱之為資料表空間；將資料庫中的索引單獨存放在單獨的表空間中，暫且稱之為索引表空間；如果存在LOB欄位，同樣需要將其單獨存放在特定的表空間中，暫且稱其為LOB表空間。

為達到批次自動化的完成此類維護任務，我這裡編寫了一個儲存過程輔助完成之。供參考。

我們來模擬一下這個快速維護過程。

1.建立三個獨立的表空間，分別用來存放資料、索引以及LOB欄位。
sys@ora10g> create tablespace TBS_SEC_D datafile '/oracle/oradata/ora10g/tbs_sec_d.dbf' size 10m autoextend on;
sys@ora10g> create tablespace TBS_SEC_I datafile '/oracle/oradata/ora10g/tbs_sec_i.dbf' size 10m autoextend on;
sys@ora10g> create tablespace TBS_SEC_LOB datafile '/oracle/oradata/ora10g/tbs_sec_lob.dbf' size 10m autoextend on;

2.建立測試使用者sec，並授予基本許可權。
sys@ora10g> create user sec identified by sec default tablespace tbs_sec_d;

User created.

sys@ora10g> grant connect, resource to sec;

Grant succeeded.

3.在sec使用者下建立一些常見的資料庫物件
sec@ora10g> create table t (x number, constraint pk_t primary key(x), y varchar2(10), z clob);

Table created.

sec@ora10g> create index i_t on t (y);

Index created.

4.檢視當前使用者下所有資料庫物件對應的表空間資訊
sec@ora10g> select segment_name, segment_type, tablespace_name from user_segments order by 2,1;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ----------------------
I_T                            INDEX              TBS_SEC_D
PK_T                           INDEX              TBS_SEC_D
SYS_IL0000080090C00003$$       LOBINDEX           TBS_SEC_D
SYS_LOB0000080090C00003$$      LOBSEGMENT         TBS_SEC_D
T                              TABLE              TBS_SEC_D

可見我們建立的所有資料庫物件都存放在TBS_SEC_D這個預設表空間中。
當此表空間存有大量資料時，就會因表空間爭用激烈而出現效能瓶頸。

5.即將發揮威力的儲存過程內容如下
create or replace procedure adjust_tablespace (
   data_tablespace    in varchar2,
   lob_tablespace     in varchar2,
   index_tablespace   in varchar2
)
as
   cursor c_table_name
   is
      select table_name from user_tables;

   cursor c_lob
   is
      select table_name, column_name
        from user_tab_cols t
       where t.data_type like '%LOB%';

   cursor c_index_name
   is
      select index_name, tablespace_name from user_indexes;
begin
   --修改當前使用者所有表的表空間
   for f_table_name in c_table_name
   loop
      DBMS_OUTPUT.put_line (f_table_name.table_name);

      execute immediate   'alter table '
                       || f_table_name.table_name
                       || ' move tablespace '
                       || data_tablespace;
   end loop;

   DBMS_OUTPUT.put_line ('');

   --修改當前使用者所有LOB欄位的表空間
   for f_lob in c_lob
   loop
      DBMS_OUTPUT.put_line (f_lob.table_name || '.' || f_lob.column_name);

      execute immediate   'ALTER TABLE '
                       || f_lob.table_name
                       || ' MOVE LOB('
                       || f_lob.column_name
                       || ') STORE AS(TABLESPACE '
                       || lob_tablespace
                       || ')';
   end loop;

   DBMS_OUTPUT.put_line ('');

   --重建索引，修改當前使用者中所有索引的索引表空間
   for f_index_name in c_index_name
   loop
      if f_index_name.tablespace_name <> UPPER (lob_tablespace)
      then
         DBMS_OUTPUT.put_line (f_index_name.index_name);

         execute immediate   'alter index '
                          || f_index_name.index_name
                          || ' rebuild tablespace '
                          || index_tablespace;
      end if;
   end loop;
end;
/

6.在SQL*PLus中建立上面的儲存過程

Procedure created.

7.使用儲存過程adjust_tablespace完成我們的調整任務
sec@ora10g> exec adjust_tablespace('TBS_SEC_D','TBS_SEC_LOB','TBS_SEC_I');
T

T.Z

I_T
PK_T

PL/SQL procedure successfully completed.

8.最後我們來看一下調整之後的勝利果實，perfect！
sec@ora10g> select segment_name, segment_type, tablespace_name from user_segments order by 2,1;

SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ -----------------------
I_T                            INDEX              TBS_SEC_I
PK_T                           INDEX              TBS_SEC_I
SYS_IL0000080090C00003$$       LOBINDEX           TBS_SEC_LOB
SYS_LOB0000080090C00003$$      LOBSEGMENT         TBS_SEC_LOB
T                              TABLE              TBS_SEC_D

9.小結
自動化是延長DBA生命的有效手段，應該熟練掌握各種自動化手段以備不時之需。
不過這裡需要提醒各位的是，雖然自動化可以比較方便快捷地完成我們的既定任務，不過同樣需要在評估場合後再加以利用。在海量資料和特殊場景下有時自動化反而會帶來些許的麻煩。
文中提到的方法不建議在海量資料環境下使用。

Good luck.

secooler
10.01.24

-- The End --

【自動化】使用PL/SQL輔助完成表空間的分類調整

相關文章