高水位 HWM

来源:互联网 发布:淘宝大连六本木黑店 编辑:程序博客网 时间:2024/06/11 01:59

高水位实验

 

]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Nov 14 14:09:20 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> conn oss03/oss03@oss2

Connected.

--设置下环境

SQL> set linesize 1000

SQL> set pagesize 100;

SQL> set timing on;

-- 删除要测试的表

SQL> drop table zfk_test;

Table dropped.

Elapsed: 00:00:00.64

--创建测试表的结构

SQL> create table zfk_test as select * from dba_objects where rownum<=0;

Table created.

Elapsed: 00:00:00.76

 

--表结构信息

-- Create table

create table ZFK_TEST

(

  OWNER          VARCHAR2(30),

  OBJECT_NAME    VARCHAR2(128),

  SUBOBJECT_NAME VARCHAR2(30),

  OBJECT_ID      NUMBER,

  DATA_OBJECT_ID NUMBER,

  OBJECT_TYPE    VARCHAR2(19),

  CREATED        DATE,

  LAST_DDL_TIME  DATE,

  TIMESTAMP      VARCHAR2(19),

  STATUS         VARCHAR2(7),

  TEMPORARY      VARCHAR2(1),

  GENERATED      VARCHAR2(1),

  SECONDARY      VARCHAR2(1)

)

tablespace OSS03_DATA

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64K

    minextents 1

    maxextents unlimited

  );

 

--分析表

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.74

--查看表信息 默认有8个空块

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

         0            8          0

Elapsed: 00:00:00.02

--查看段信息 占了8个块

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                       8

Elapsed: 00:00:00.25

 

--查看源表ID值

SQL> select min(object_id),max(object_id),count(object_id) from dba_objects;

MIN(OBJECT_ID) MAX(OBJECT_ID) COUNT(OBJECT_ID)

-------------- -------------- ----------------

             2         504463           139255

Elapsed: 00:00:01.29

 

--插入100行数据

SQL> insert into zfk_test select * from dba_objects where object_id <=2+100;

101 rows created.

Elapsed: 00:00:00.08

SQL> commit;

 

SQL> col object_name for a30

SQL> col object_id for 99999999

SQL> select object_name,object_id from zfk_test where rownum<=10;

OBJECT_NAME                    OBJECT_ID

------------------------------ ---------

C_USER#                               10

I_FILE#_BLOCK#                         9

C_FILE#_BLOCK#                         8

I_TS#                                  7

C_TS#                                  6

CLU$                                   5

TAB$                                   4

I_OBJ#                                 3

C_OBJ#                                 2

I_SEQ1                               102

 

10 rows selected.

Elapsed: 00:00:00.00

 

--没有分析,表空块还是那么多

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

         0            8          0

Elapsed: 00:00:00.03

-- 段空间没增大

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                       8

Elapsed: 00:00:00.24

--再次分析看看

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.26

--表段没有变大

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                       8

Elapsed: 00:00:00.15

 

--表信息出现了 占用了5个块,3个空块,101行数据

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

         5            3        101

Elapsed: 00:00:00.01

  BLOCKS 列代表该表中曾经使用过得数据库块的数目,即水线。EMPTY_BLOCKS 代表分配给该表,但是在水线以上的数据库块,即从来没有使用的数据块.

 

SQL> insert into zfk_test select * from dba_objects where object_id <=102+1000;

1056 rows created.

Elapsed: 00:00:00.05

 

--先看看两个信息表

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

         5            3        101

Elapsed: 00:00:00.00

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                      24

Elapsed: 00:00:00.14

 

--段的空间会自动扩大

 

--分析下看看

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.43

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                      24

Elapsed: 00:00:00.15

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

 

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

        20            4       1157

Elapsed: 00:00:00.00

 

--结论是 新追加数据是从表空间获取块或者说EXTENT区来分配给表段.没有经过分析就不会把高水位反应到表信息中去.

 问题是如果查入数据或者APPEND方式高水位会自动改变吗?

 

--现在删除数据看看

SQL> delete zfk_test;

1157 rows deleted.

Elapsed: 00:00:00.02

 

SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

        20            4       1157

Elapsed: 00:00:00.02

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                      24

Elapsed: 00:00:00.24

 

--一切都没有变化 再分析下看

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.49

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                      24

Elapsed: 00:00:00.15

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

        20            4          0

Elapsed: 00:00:00.01

 

--删除数据不会降低高水位,分析后也不会,段空间也不会减少

 

SQL> alter table zfk_test deallocate unused;

Table altered.

Elapsed: 00:00:00.15

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

        20            4          0

Elapsed: 00:00:00.00

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                      24

 

--一个区控制8个块

SQL> select extent_id,block_id,blocks from dba_extents where segment_name='ZFK_TEST';

 

 EXTENT_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         0    1966321          8

         1    1966329          8

         2    1966337          8

 

Elapsed: 00:00:00.55

--deallocate unused 释放的是 HWM 之上的数据块,但是还是要保留你整个表空间中extent的完整性

--因此4个空块不足以被释放

 

--再插巨量数据后

SQL> select count(*) from zfk_test;

  COUNT(*)

----------

    148218

Elapsed: 00:00:00.08

--删除

SQL> delete zfk_test where object_Id>1000;

147244 rows deleted.

Elapsed: 00:00:09.67

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

--查看段信息有4736个块

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    4736

Elapsed: 00:00:00.09

--释放未使用的块

SQL> alter table zfk_test deallocate unused;

Table altered.

Elapsed: 00:00:00.98

--释放了些块从段信息中

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    4648

Elapsed: 00:00:00.08

--表信息原来有95个空块

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      4641           95     145950

Elapsed: 00:00:00.01

 

--分析后块少了88个空块 位于高水位上的

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.37

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      4641            7         29

Elapsed: 00:00:00.00

--查看区信息 最后个区从128块减少到40块.

SQL> select extent_id,block_id,blocks from dba_extents where segment_name='ZFK_TEST';

 EXTENT_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         0    1966321          8

         1    1966329          8

         2    1966337          8

         3    1966753          8

         4    1966761          8

         5    1966769          8

         6    1966777          8

         7    1966785          8

         8    1966793          8

         9    1966801          8

        10    1966809          8

        11    1966817          8

        12    1966825          8

        13    1966833          8

        14    1966841          8

        15    1966849          8

        16    1966345        128

        17    1966473        128

        18    1966601        128

        19    1966857        128

        20    1966985        128

        21    1967113        128

        22    1967241        128

        23    1967369        128

        24    1967497        128

        25    1967625        128

        26    1967753        128

        27    1967881        128

        28    1968009        128

        29    1968137        128

        30    1968265        128

        31    1968393        128

        32    1968521        128

        33    1968649        128

        34    1968777        128

        35    1969033        128

        36    1969161        128

        37    1969289        128

        38    1969417        128

        39    1969545        128

        40    1969673        128

        41    1969801        128

        42    1969929        128

        43    1970057        128

        44    1970185        128

        45    1970313        128

        46    1970441        128

        47    1970569        128

        48    1978249        128

        49    1964425        128

        50    1964553        128

        51    1964681         40

52 rows selected.

Elapsed: 00:00:00.79

--接下来再删,全部删除也无法释放了.因为高水位没有降低

 

--采用SHRINK方法释放

SQL> alter table zfk_test enable row movement;

Table altered.

Elapsed: 00:00:00.80

 

SQL> alter table zfk_test shrink space;

Table altered.

Elapsed: 00:00:00.36

 

SQL> select extent_id,block_id,blocks from dba_extents where segment_name='ZFK_TEST';

 EXTENT_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         0    1966321          8

Elapsed: 00:00:00.89

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

        20            4          0

Elapsed: 00:00:00.02

 

--段空间得到了释放,表的统计信息还是老样子的

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.18

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

         1            7          0

Elapsed: 00:00:00.01

 

SQL> insert into zfk_test  select * from dba_objects;

139866 rows created.

Elapsed: 00:00:07.19

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

         1            7          0

Elapsed: 00:00:00.00

 

-- 可以看到区16的时候管辖的块突然变成了128块

SQL> select extent_id,block_id,blocks from dba_extents where segment_name='ZFK_TEST';

 EXTENT_ID   BLOCK_ID     BLOCKS

---------- ---------- ----------

         0    1966321          8

         1    1966329          8

         2    1966337          8

         3    1966345          8

         4    1966353          8

         5    1966361          8

         6    1966369          8

         7    1966377          8

         8    1966385          8

         9    1966393          8

        10    1966401          8

        11    1966409          8

        12    1966417          8

        13    1966425          8

        14    1966433          8

        15    1966441          8

        16    1966473        128

        17    1966601        128

        18    1966857        128

        19    1966985        128

        20    1967113        128

        21    1967241        128

        22    1967369        128

        23    1967497        128

        24    1967625        128

        25    1967753        128

        26    1967881        128

        27    1968009        128

        28    1968137        128

        29    1968265        128

        30    1968393        128

        31    1968521        128

        32    1968649        128

        33    1968777        128

34 rows selected.

Elapsed: 00:00:00.14

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    2432

Elapsed: 00:00:00.23

 

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:04.18

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      2386           46     139866

Elapsed: 00:00:00.02

 

--有46个空块,当达不到EXTENT 128个块.

 

--TRUNCATE TABLE

SQL> truncate table zfk_test;

Table truncated.

Elapsed: 00:00:01.20

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

 

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      2386           46     139866

Elapsed: 00:00:00.03

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                       8

Elapsed: 00:00:00.25

 

--TRUNCATE TABLE 会缩小段的空间

 

--删除再插入数据

SQL> insert into zfk_test  select * from dba_objects;

139825 rows created.

Elapsed: 00:00:01.21

 

SQL> commit;

Commit complete.

Elapsed: 00:00:00.01

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    2432

Elapsed: 00:00:00.13

 

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:02.93

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      2386           46     139825

Elapsed: 00:00:00.00

 

SQL> delete  zfk_test where object_id >10000;

130222 rows deleted.

Elapsed: 00:00:05.64

 

SQL> commit;

Commit complete.

Elapsed: 00:00:00.05

--删除13,0222万 后段和表信息没有变化

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      2386           46     139825

Elapsed: 00:00:00.00

 

SQL>  select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    2432

Elapsed: 00:00:00.07

-- 分析后 表信息中的行数才剩9千条,当块和空块没有变化. 说明高水位下有大量的空块和空闲块

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.24

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      2386           46       9603

Elapsed: 00:00:00.00

 

--再插入回数据去

SQL> insert into zfk_test select * from dba_objects where object_id>10000;

130222 rows created.

Elapsed: 00:00:00.84

 

SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

--查看下段和表信息 都没有发生变化

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      2386           46       9603

Elapsed: 00:00:00.03

 

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    2432

Elapsed: 00:00:00.26

--分析后 表的行数变化了

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:03.74

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      2386           46     139825

 

Elapsed: 00:00:00.00

--说明插入到高水位下面的空闲块里了

 

---APPEND

SQL> insert /*+append*/ into zfk_test select * from dba_objects where object_id>10000;

 

130224 rows created.

 

Elapsed: 00:00:27.13

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

 

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    4736

 

Elapsed: 00:00:00.24

 

--- Append 删除居然会报错 这个问题待日后研究下

SQL> delete zfk_test where object_id>10000;

delete zfk_test where object_id>10000

       *

ERROR at line 1:

ORA-12838: cannot read/modify an object after modifying it in parallel

 

insert /*+append*/ 如果不commit的话,会对该表加6级锁,也就是说,即使此时select这个表都会报错。
因此append提示的语句首先不能是业务表,其次要尽快提交

 

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:04.19

 

SQL> delete zfk_test where object_id>10000;

130224 rows deleted.

Elapsed: 00:00:04.00

--- 这方式的APPEND 是普通方式插入的

SQL> insert into /*+append*/ zfk_test select * from dba_objects where object_id >10000;

129986 rows created.

Elapsed: 00:00:01.18

SQL> commit;

Commit complete.

Elapsed: 00:00:00.04

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    4736

Elapsed: 00:00:00.14

 

--删除所有数据 使用下面查看表段具体使用多少个块,就是非空闲块有多少?

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)|| dbms_rowid.rowid_relative_fno(rowid)) "used" from zfk_test;

      used

----------

         0

Elapsed: 00:00:00.10

 --插入1万条进去

SQL> insert into zfk_test select * from dba_objects where object_id <10000;

9582 rows created.

Elapsed: 00:00:00.12

 

SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

-- 显示使用了119个块

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)|| dbms_rowid.rowid_relative_fno(rowid)) "used" from zfk_test;

      used

----------

       119

Elapsed: 00:00:00.37

-- 分析后 段和表信息都没有反应,还是那么多块.

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:01.02

 

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                    4648

Elapsed: 00:00:00.25

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

      4641            7       9582

Elapsed: 00:00:00.03

 

-- Shrink下

SQL> alter table zfk_test enable row movement;

Table altered.

Elapsed: 00:00:00.18

 

SQL> alter table zfk_test shrink space;

Table altered.

Elapsed: 00:00:19.25

 

SQL> select segment_name,segment_type,blocks from dba_segments where segment_name='ZFK_TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS

------------------------------ ------------------ ----------

ZFK_TEST                       TABLE                     128

Elapsed: 00:00:00.14

 

SQL> analyze table zfk_test compute statistics;

Table analyzed.

Elapsed: 00:00:00.35

 

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='ZFK_TEST';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS

---------- ------------ ----------

       113           15       9582

Elapsed: 00:00:00.01

-- Shrink后 分析所得 从原来的119块使用变成了113个块,也就是说Shrink 不仅降低高水位,释放空块,还整理碎块功效!

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)|| dbms_rowid.rowid_relative_fno(rowid)) "used" from zfk_test;

      used

----------

       113

Elapsed: 00:00:00.25

 

总结

1 高水位随着数据增多而向上扩展,这个是自动的.

2 如果块不够用了,就分配1个新的区给段,这个区含有的块可以指定,可以ORACLE自动分配.

3 高水位上面的空间 是未使用过的空块,当这些空块数量不会超过一个区EXTENT

4 段的大小随者数据增大而得到反映在信息表中,

5 表信息表不会随者数据的变化而得到反应,除非通过分析获得.

6 alter table shrink space;和truncate talbe 才可以降低高水位标志,并且释放段的空间

7 Alter table table_name deallocate unused 只能释放高水位上未使用的空块,无法降低高水位

8 查看表段使用多少个块

select count(distinct dbms_rowid.rowid_block_number(rowid)|| dbms_rowid.rowid_relative_fno(rowid)) "used" from table_name

 

原创粉丝点击