ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 段空间管理问题

oracle 段空间管理问题

原创 Linux操作系统 作者:regonly1 时间:2009-08-26 10:30:40 0 删除 编辑

 

show_space过程内容:
create or replace procedure show_space
    ( p_segname in varchar2,
      p_owner   in varchar2 default user,
      p_type    in varchar2 default 'TABLE' )
    as
       l_free_blks                 number;
       l_total_blocks              number;
       l_total_bytes               number;
       l_unused_blocks             number;
       l_unused_bytes              number;
       l_LastUsedExtFileId         number;
       l_LastUsedExtBlockId        number;
     l_LAST_USED_BLOCK           number;
       procedure p( p_label in varchar2, p_num in number )
       is
       begin
           dbms_output.put_line( rpad(p_label,40,'.') ||
                                 p_num );
       end;
   begin
       dbms_space.free_blocks
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         freelist_group_id => 0,
         free_blks         => l_free_blks );
       dbms_space.unused_space
       ( segment_owner     => p_owner,
         segment_name      => p_segname,
         segment_type      => p_type,
         total_blocks      => l_total_blocks,
         total_bytes       => l_total_bytes,
         unused_blocks     => l_unused_blocks,
         unused_bytes      => l_unused_bytes,
         LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
         LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
         LAST_USED_BLOCK => l_LAST_USED_BLOCK );

       p( 'Free Blocks', l_free_blks );
       p( 'Total Blocks', l_total_blocks );
       p( 'Total Bytes', l_total_bytes );
       p( 'Unused Blocks', l_unused_blocks );
       p( 'Unused Bytes', l_unused_bytes );
       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
       p( 'Last Used Block', l_LAST_USED_BLOCK );
end;


create table bigcol(y varchar2(2048)) nologging;

执行show_space报错:
call show_space('BIGCOL','LYON')
     *
第 1 行出现错误:
ORA-10618: Operation not allowed on this segment
ORA-06512: 在 "SYS.DBMS_SPACE", line 152
ORA-06512: 在 "SYS.SHOW_SPACE", line 21
ORA-06512: 在 line 1

发现这是由于表空间的ASSM方式引起的。
要能够执行该脚本,则需要在手动段空间管理模式下。
创建一个MSSM表空间:
SQL> create tablespace mantbs datafile 'E:\oracle\oradata\lyon\mantbs01.dbf' size 10m uniform. size 1m segment space management manual;
表空间已创建。

将用户在mantbs上的空间配额修改为不限制:
SQL> alter user lyon quota unlimited on mantbs;
用户已更改。

将表移动到该表空间下:
SQL> alter table bigcol move tablespace mantbs
  2  /
表已更改。

SQL> call show_space('BIGCOL',user);

调用完成。

SQL> show message;
SP2-0158: 未知的 SHOW 选项 "message"
SQL> set serveroutput on;
SQL> /
Free Blocks.............................0
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384
Last Used Ext FileId....................23
Last Used Ext BlockId...................137
Last Used Block.........................1

调用完成。
即可查看该表占用空间情况。

往bigcol表插入10000条数据:
SQL> insert into bigcol
  2  select dbms_random.string('X', 2048) from dual connect by rownum <= 10000;
insert into bigcol
            *
第 1 行出现错误:
ORA-01653: 表 LYON.BIGCOL 无法通过 128 (在表空间 MANTBS 中) 扩展

出现空间不够问题
修改数据文件大小,扩展表空间到100m:
SQL> alter database datafile 'E:\oracle\oradata\lyon\mantbs01.dbf' resize 100m;

数据库已更改。

SQL> insert into bigcol
  2  select dbms_random.string('X', 2048) from dual connect by rownum <= 10000;

已创建10000行。

SQL> commit;

提交完成。

查看表使用空间情况:
SQL> call show_space('BIGCOL',user);
Free Blocks.............................4
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................118
Unused Bytes............................966656
Last Used Ext FileId....................23
Last Used Ext BlockId...................3337
Last Used Block.........................10

调用完成。
总计使用block 3456,未使用118,则已使用:3456-118 = 3338,即hwm为3338
用delete删除所有记录:
SQL> delete from bigcol;
已删除10000行。
SQL> commit;
提交完成。
SQL> call show_space('BIGCOL',user);
Free Blocks.............................3337
Total Blocks............................3456
Total Bytes.............................28311552
Unused Blocks...........................118
Unused Bytes............................966656
Last Used Ext FileId....................23
Last Used Ext BlockId...................3337
Last Used Block.........................10
调用完成。

发现free blocks由原来的4个变为现在的3337。但是已使用的blocks:3456 - 118 = 3338,即hwm仍为3338。
查看执行时间:
10:24:19 SQL> select count(*) from bigcol;

  COUNT(*)
----------
         0

已用时间:  00: 00: 00.84

截断表:
10:24:25 SQL> truncate table bigcol;
表被截断。
已用时间:  00: 00: 02.81
10:24:50 SQL> call show_space('BIGCOL',user);
Free Blocks.............................0
Total Blocks............................128
Total Bytes.............................1048576
Unused Blocks...........................127
Unused Bytes............................1040384
Last Used Ext FileId....................23
Last Used Ext BlockId...................9
Last Used Block.........................1
调用完成。

发现已使用的blocks:128 - 127 = 1,即hwm收缩为1。
再次执行count查询:
10:26:28 SQL> select count(*) from bigcol;

  COUNT(*)
----------
         0

已用时间:  00: 00: 00.01
在每次执行count之前都做了buffer cache的清理,已保证每次都是从disk读取的。
发现两次查询的时间发生了明显的变化,由原来的0.84s缩小为现在的0.01s。


参考了:
http://tolywang.itpub.net/post/48/307529

引用里面对此的总结:
在9I中:

(1)如果MINEXTENT 可以使ALTER TABLE TABLENAME DEALLOCATE UNUSED将HWM以上所有没使用的空间释放
(2)如果MINEXTENT >HWM 则释放MINEXTENTS 以上的空间。如果要释放HWM以上的空间则使用KEEP 0。
ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0;
(3) TRUNCATE TABLE DROP STORAGE(缺省值)命令可以将MINEXTENT 之上的空间完全释放(交还给操作系统),并且重置HWM。

(4)如果仅是要移动HWM,而不想让表长时间锁住,可以用TRUNCATE TABLE REUSE STORAGE,仅将HWM重置。
(5)ALTER TABLE MOVE会将HWM移动,但在MOVE时需要双倍的表空间,而且如果表上有索引的话,需要重构索引

(6)DELETE表不会重置HWM,也不会释放自由的空间(也就是说DELETE空出来的空间只能给对象本身将来的INSERT/UPDATE使用,不能给其它的对象使用)

在ORACLE 10G:

(1)可以使用ALTER TABLE TEST_TAB SHRINK SPACE命令来联机移动HWM,

(2)如果要同时压缩表的索引,可以发布:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-613262/,如需转载,请注明出处,否则将追究法律责任。

下一篇: oracle rman 基础
请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1025941