每一名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 --

ITPub博客

【自动化】使用PL/SQL辅助完成表空间的分类调整

最新文章