缩小oracle表空间（二）

       通过delete数据，是不能降低hwm、不能释放表空间（一般情况下都是，极端情况下除外），但可以通过drop table tbname、truncate table tbname来释放表空间，而且执行效率非常高，远远超过delete。truncate之后，表结构还存在。但是有时候只能delete数据，不能够drop和truncate。

hwm介绍:
      HWM（high water mark）,高水标记,这个概念在segment的存储内容中是比较重要的.简单来说,HWM就是一个segment中已使用和未使用的block的分界线.
在oracle的concept中对于HWM的说明是这样的：在一个segment中，HWM是使用和未使用空间的分界线。当请求新的空闲块，并且现有空闲列表中的块不能满足要求时，HWM指向的块将被标记为已使用，然后HWM将移动指向下一个未使用过的块。我们知道,在oracle中,存储数据的最小单元是block,对于一个segment(table或index),都是由很多的block组成的,这些block的状态分为已使用和未使用两种,一般来说，在HWM之下的block都是存储过数据的. 我们就能很清楚的看到，一个segment中的block的分布情况。在HWM左边的block是已使用的，或者说是可以用来存储数据的。而HWM右边的block是不能用来存储数据的。当HWM左边的block空间都使用完之后，还有新的数据需要存储，怎样处理呢？这时oracle会向右移动HWM，即把右边的没有使用的block移到HWM的左边，这时HWM左边的block就增加了，那么就有新的block空间可供使用了。

2016-2-4今天没事儿，来亲自测试一下：降低HWM普通用户即可，收缩表空间、resize datafile，需要是dba角色，job的使用普通用户即可。
1、检查表空间的情况：

select a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024 "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name;

2、在该表空间中建表

SQL> create table tab01
  2 (
  3    pid NUMBER,
  4    pname VARCHAR2(32),
  5    stime TIMESTAMP(6)
  6 )
  7 tablespace test;

Table created

SQL>

3、利用存储过程插入数据

首先建一个序列seq_yk，然后创建的存储过程，以插入行数、插入的表为参数：

create sequence SEQ_YK
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

然后创建存储过程：

create or replace procedure pro_testyk(num in number,tab_name in varchar2) is
str_sql varchar2(128);
begin
  --delete from test1;
  str_sql:='insert into ' || tab_name || ' values(seq_yk.nextval,''张三'',sysdate)';
  --str_sql:='insert into  '|| tab_name || ' values (1,''张三'')';
  for i in 1..num loop
    --insert into tab_name values(seq_yk.nextval,'张三',sysdate);
    --execute immediate 'insert into ' || tab_name || ' values(seq_yk.nextval,'张三',sysdate)';--编译不成功
    --execute immediate 'insert into ' || tab_name || ' values(seq_yk.nextval,"张三",sysdate)';--编译成功，但是执行不成功，ORA-00984: 列在此处不允许
    execute immediate str_sql;
    end loop;
    commit;
    --execute immediate 'select * from test1';
end pro_testyk;

这里有一个很关键的点，由于插入的有中文字符，要么编译不成功，要么执行不成功，后来网上看了下，再发现是要用两次单引号来引中文字符。oracle的sql中，是不存在双引号的，就算编译成功了也没用。
执行存储过程，插入100w条:

SQL> set timing on;
SQL> exec pro_testyk(1000000,'tab01');
 
PL/SQL procedure successfully completed
 
Executed in 30.966 seconds
 
SQL> select count(*) from tab01;
 
  COUNT(*)
----------
   1000010
 
Executed in 0.11 seconds
 
SQL> select max（pid) from tab01;
 
  MAX（PID)
----------
   1000000
 
Executed in 0.063 seconds
 
SQL>
SQL> select * from tab01 where pid >999990;
 
       PID PNAME                            STIME
---------- -------------------------------- --------------------------------------------------------------------------------
    999991 张三                             04-2月 -16 12.28.42.000000 下午
    999992 张三                             04-2月 -16 12.28.42.000000 下午
    999993 张三                             04-2月 -16 12.28.42.000000 下午
    999994 张三                             04-2月 -16 12.28.42.000000 下午
    999995 张三                             04-2月 -16 12.28.42.000000 下午
    999996 张三                             04-2月 -16 12.28.42.000000 下午
    999997 张三                             04-2月 -16 12.28.42.000000 下午
    999998 张三                             04-2月 -16 12.28.42.000000 下午
    999999 张三                             04-2月 -16 12.28.42.000000 下午
   1000000 张三                             04-2月 -16 12.28.42.000000 下午
 
10 rows selected
 
Executed in 0.234 seconds
 
SQL>

4、表空间的使用情况是：

增加了27MB，不够明显，在这个表空间再建一个表，再多插入一些数据试试：

SQL> create table tab02 as select * from tab01 where rownum<1;

Table created

Executed in 0.093 seconds

SQL> select t.TABLE_NAME,t.TABLESPACE_NAME from user_tables t where t.TABLE_NAME='TAB02';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TAB02                          TEST

Executed in 0.015 seconds

SQL> exec pro_testyk(4000000,'tab02');

PL/SQL procedure successfully completed

Executed in 154.176 seconds

SQL>

再看看表空间的情况：

我的test表空间是设置的autoextend的。
5、删除一些数据看看空间如何变化：
先删tab01中的全部数据：

SQL> delete from tab01;

1000010 rows deleted

Executed in 28.922 seconds

SQL>

表空间的使用情况未变化：

删除tab02一半的数据看看什么效果：

SQL> select count(*) from tab02;

  COUNT(*)
----------
   4000000

Executed in 2.45 seconds

SQL> delete tab02 where pid<2000000;

999998 rows deleted

Executed in 20.592 seconds

SQL> select count(*) from tab02;

  COUNT(*)
----------
   3000002

Executed in 0.795 seconds

SQL> delete tab02 where pid<3000000;

1000000 rows deleted

Executed in 18.174 seconds

SQL> select count(*) from tab02;

  COUNT(*)
----------
   2000002

Executed in 0.078 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL>

还是未变化：

6、使用shrink，降低HWM
alter table tab01 enable row movement;
--把表设置成enable row_movement允许修改分区键，并自动根据修改后的的值，决定记录是否被move到其它表分区。
alter table tab01 shrink space;
--收缩HWM
查看表空间的变化情况：

释放了原来的那个27MB。

再对tab02操作：
alter table tab02 enable row movement;
alter table tab02 shrink space;--执行的时间略久

通过上面可以查看，释放了更多空间。通过shrink降低HWM成功。
7、如果使用truncate删数据呢？
删除tab02另外的全部数据：

SQL> truncate table tab02;

Table truncated

Executed in 0.608 seconds

SQL>

可以看出：

操作	回滚	高水线	空间	效率
Truncate	不能	下降	回收	快
delete	可以	不变	不回收	慢

所有的Oracle表都有一个容纳数据的上限（很象一个水库历史最高的水位），我们把这个上限称为“high water mark”或HWM。这个HWM是一个标记(专门有一个数据块用来记录高水标记等)，用来说明已经有多少数据块分配给这个表. HWM通常增长的幅度为一次5个数据块。

如果有很多个表的时候，就通过这个查询，哪些大表需要shrink：
查看oracle中某个表空间下所有表的大小

select segment_name,tablespace_name,bytes B, bytes/1024 KB, bytes/1024/1024 MB from user_segments where segment_type='TABLE' and tablespace_name='USERS';

8、缩小表空间：
缩小表空间TEST，其实也就是收缩datafile（常见的操作是增大datafile，例如：），首先要知道它可以收缩到什么程度：

SQL> select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
  2 ceil(HWM * a.block_size)/1024/1024 ResizeTo,
  3 (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
  4 'alter database datafile '''||a.name||''' resize '||
  5 ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
  6 from v$datafile a,
  7 (select file_id,max(block_id+blocks-1) HWM
  8 from dba_extents where file_id in
  9 (select b.file# From v$tablespace a ,v$datafile b
 10 where a.ts#=b.ts# and a.name='TEST')
 11 group by file_id) b
 12 where a.file# = b.file_id(+)
 13 and (a.bytes - HWM *block_size)>0
 14 order by 5;

     FILE# NAME                                                                              CURRENTMB   RESIZETO  RELEASEMB RESIZECMD
---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- --------------------------------------------------------------------------------
         6 E:\DATAFILE\TEST01.DBF                                                                  160 28.9921875 131.007812 alter database datafile 'E:\DATAFILE\TEST01.DBF' resize 29M;

Executed in 0.531 seconds

SQL>

执行resize 数据文件：

SQL> alter database datafile 'E:\DATAFILE\TEST01.DBF' resize 50M;

alter database datafile 'E:\DATAFILE\TEST01.DBF' resize 50M

ORA-01031: 权限不足

SQL> conn /as sysdba;
Connected to Personal Oracle Database 11g Release 11.2.0.1.0
Connected as yangkai AS SYSDBA

SQL> alter database datafile 'E:\DATAFILE\TEST01.DBF' resize 50M;

Database altered

Executed in 0.203 seconds

SQL>

查看表空间的情况：

完成收缩表空间。