數據庫中如何降低高水位

這篇文章主要為大家展示了“數據庫中如何降低高水位”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“數據庫中如何降低高水位”這篇文章吧。

降低高水位方法

1. move

a.move不但可以重置水位線（HWM），解決松散表帶來的 IO 浪費，還可以解決表中的行遷移問題；

b.move可以將表移動到其他表空間，也可以在原表空移動，這樣可以一定程度解決表空間碎片；

c.如果表空間上有大量表、索引被 drop（或者 truncate），導致表空間前半部分出現大量空閑空間，可以通過 move 將靠后的表移動到前面的空閑空間，從而收縮數據文件。

實驗：

sys@ORCL>conn shall/shall

Connected.

shall@ORCL>create table zhong(x int);

Table created.

shall@ORCL>begin

  2  for i in 1..100000 loop

  3  insert into zhong values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

----收集統計信息

shall@ORCL>analyze table zhong compute statistics;

Table analyzed.

shall@ORCL>select table_name,blocks,empty_blocks from user_tables where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                 244           12

----delete表zhong

shall@ORCL>delete zhong;

100000 rows deleted.

shall@ORCL>analyze table zhong compute statistics;

Table analyzed.

shall@ORCL>select table_name,blocks,empty_blocks from user_tables where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                 244           12

----move整理碎片

shall@ORCL>alter table zhong move;

Table altered.

或者 alter table zhong move tablespace hct;    ----move到hct表空間

/*

如果move到hct表空間了，可以看見表空間已經變了，如下

shall@ORCL>select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME

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

TTTT                           USERS

ZHONG                          HCT

*/

shall@ORCL>analyze table zhong compute statistics;

Table analyzed.

shall@ORCL>select table_name,blocks,empty_blocks from user_tables where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                   0            8

----高水位已經降下來了。move到該表空間，需要保證有足夠的剩余空間

----重建索引

shall@ORCL> alter index inx_t_x rebuild;

Index altered.

或 alter index inx_t_x rebuild tablespace users;

----查看索引狀態

SCOTT@test> set linesize 200

SCOTT@test> select index_name,table_name,tablespace_name,status from user_indexes;

----注意事項：

----Rebuild index

在對表進行 move 操作后，表中的 rowid 發生了改變，這樣導致索引無法定位到原來表中的數據，從而觸發了索引失效，所以需要 alter index index_name rebuild [online] 的命令進行重建。

----空間分配

alter table move操作，必須給move的表空間足夠的剩余空間，否則可能會出現 ORA-01652 告警。

----exclusive lock

move 操作相當于將表中所有數據移動，因此在move的過程中，oracle會對表放置了 exclusive lock 鎖，此時只能對它進行 select 操作。

2. shrink space

此命令為 Oracle 10g 新增功能，shrink 操作是將原本松散的數據存放結構，通過將表中靠后的行向前面的空閑塊遷移，在完成后將完全空閑的區釋放，并前置 HWM 到表中最后一個使用塊的位置，從而實現松散表重新結構緊湊。

使用條件

                自動段管理模式。只支持 ASSM 管理的表空間，如果不是會報ORA-10635: Invalid segment or tablespace type

                打開行移動  alter table table_name enable row movement

參數：

alter table TABLE_NAME shrink space [compact|cascate]

alter table TABLE_NAME shrink space; 整理碎片并回收空間

alter table TABLE_NAME shrink space compact; 只整理碎片 不回收空間

alter table TABLE_NAME shrink space cascate; 整理碎片回收空間 并連同表的級聯對象一起整理(比如索引)

使用步驟

1. alter table t1 enable ROW MOVEMENT;

2. shrink 操作

3. alter table t1 disable ROW MOVEMENT;

實驗：

----查看表空間段管理模式

sys@ORCL>select tablespace_name,block_size,extent_management,allocation_type,segment_space_management from dba_tablespaces order by segment_space_management;

TABLESPACE_NAME                BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN

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

SYSAUX                               8192 LOCAL      SYSTEM    AUTO

HCT                                  8192 LOCAL      SYSTEM    AUTO

USERS                                8192 LOCAL      SYSTEM    AUTO

EXAMPLE                              8192 LOCAL      SYSTEM    AUTO

TEMP                                 8192 LOCAL      UNIFORM   MANUAL

UNDOTBS1                             8192 LOCAL      SYSTEM    MANUAL

SYSTEM                               8192 LOCAL      SYSTEM    MANUAL

----查看shall用戶使用的默認表空間

sys@ORCL>select username,default_tablespace,temporary_tablespace from dba_users where username='SHALL';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

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

SHALL                          USERS                          TEMP

----創建表及插入數據

sys@ORCL>conn shall/shall

Connected.

shall@ORCL>create table shall(ttt int);

Table created.

sys@ORCL>begin

  2  for i in 1..1000000 loop

  3    insert into shall values(i);

  4   end loop;

  5   commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

shall@ORCL>analyze table shall compute statistics;

Table analyzed.

shall@ORCL>select table_name,blocks,empty_blocks from user_tables where table_name='SHALL';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

SHALL                                1630           34

----delete表shall

shall@ORCL>delete shall;

1000000 rows deleted.

shall@ORCL>analyze table shall compute statistics;

Table analyzed.

shall@ORCL>select table_name,blocks,empty_blocks from user_tables where table_name='SHALL';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

SHALL                                1630           34

----開始shrink整理碎片

shall@ORCL>alter table shall enable row movement;

Table altered.

shall@ORCL>alter table shall shrink space;

Table altered.

shall@ORCL>alter table shall disable row movement;

Table altered.

----為刷新統計信息之前，高水位未降

shall@ORCL>select table_name,blocks,empty_blocks from user_tables where table_name='SHALL';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

SHALL                                1630           34

shall@ORCL>analyze table shall compute statistics;

Table analyzed.

shall@ORCL>select table_name,blocks,empty_blocks from user_tables where table_name='SHALL';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

SHALL                                   1            7

使用shrink降低高水位的優點：

1）能在線進行，不影響表上的DML操作，當然，并發的DML操作在shrink結束的時刻會出現短暫的block；

2）shrink的另外一個優點是在碎片整理結束后，表上相關的index仍然enable。

                對于第二點進一步說明下，shrink在整理表碎片的時候，行的rowid已經發生改變，那為什么相關的索引還能enable呢？其實oracle在進行shrink的時候會對相應的索引進行維護，以保證index在shrink結束的時候index仍然有效。這個維護不同于索引rebuild，不會對索引的空間進行整理，shrink有cascede選項，如果在shrink的時候加上該選項，就會對表上相應的索引空間進行整理。 ALTER TABLE tablename SHRINK SPACE CASCADE;

shrink也可以分兩步進行

1）先執行ALTER TABLE tablename SHRINK SPACE compact,此時oracle會在高水位線以下將row盡量向segment的頂部移動，但不收縮高水位線，即不釋放空間。這個操作對于那些在嘗試讀取已經被釋放的塊的查詢是有益的。

2）然后在執行ALTER TABLE test SHRINK SPACE，此時第一步中的結果已經存儲到磁盤，不會重新在整理碎片，只是收縮高水位，釋放空間。第二步操作應該在系統不繁忙時候進行。

shrink的工作原理

shrink的算法是從segment的底部開始，移動row到segment的頂部，移動的過程相當于delete/insert操作的組合，在這個過程中會產生大量的undo和redo信息。

另外， 對于空間的要求，shrink不需要額外的空間，move需要兩倍的空間。

3. rename to

復制要保留的數據到臨時表t，drop原表，然后rename to臨時表t為原表

驗證：

   begin

                for i in 1..100000 loop

                  insert into t2 values(i);

                end loop;

                commit;

   end;

   /

   analyze table t2 compute statistics;

   select table_name,blocks,empty_blocks

                from dba_tables

      where table_name='T2';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

T2                                    152          103

SQL> delete t2;

100000 rows deleted.

SQL> create table t3 as select * from t2;

SQL> analyze table t2 compute statistics;

SQL> select table_name,blocks,empty_blocks

  2  from dba_tables

  3  where table_name='T2';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

T2                                    152          103

SQL> drop table t2;

SQL> alter table t3 rename to t2;

SQL> analyze table t2 compute statistics;

SQL> select table_name,blocks,empty_blocks

  2  from dba_tables

  3  where table_name='T2';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

T2                                      1            6

4. exp/imp

用EXP導出后，刪除原表/表空間，之后用IMP重新導入

實驗：

shall@ORCL>create table zhong(id int);

Table created.

shall@ORCL>begin

  2  for i in 1..1000000 loop

  3  insert into zhong values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

shall@ORCL>analyze table zhong compute statistics;

Table analyzed.

sys@ORCL> select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                1630           34

----刪除然后導出表

shall@ORCL>delete zhong where id>50000;

950000 rows deleted.

[oracle@zyx ~]$ exp \'/ as sysdba\' tables=shall.zhong file=zhong.dmp log=zhong.log

Export: Release 11.2.0.4.0 - Production on Sun May 1 18:34:39 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

Current user changed to SHALL

. . exporting table                          ZHONG      50000 rows exported

Export terminated successfully without warnings.

[oracle@zyx ~]$

----drop原表

shall@ORCL>drop table zhong;

Table dropped.

----導入表

[oracle@zyx ~]$ imp \'/ as sysdba\' tables=zhong file=zhong.dmp fromuser=shall touser=shall;

Import: Release 11.2.0.4.0 - Production on Sun May 1 18:37:44 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SHALL's objects into SHALL

. . importing table                        "ZHONG"      50000 rows imported

Import terminated successfully without warnings.

[oracle@zyx ~]$

----未刷新統計信息時

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                1630            0

----刷新統計信息后

shall@ORCL>analyze table zhong compute statistics;

Table analyzed.

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                 110         1554

---- BLOCKS 列代表該表中曾經使用過得數據庫塊的數目，即水線。EMPTY_BLOCKS 代表分配給該表，但是在水線以上的數據庫塊，即從來沒有使用的數據塊

5. deallocate unused

alter table table_name deallocate unused;

注：這證明，DEALLOCATE UNUSED為釋放HWM上面的未使用空間，但是并不會釋放HWM下面的自由空間，也不會移動HWM的位置。

truncate table 后，有可能表空間仍沒有釋放，可以使用如下語句：

            alter table 表名稱 deallocate   UNUSED KEEP 0;

例如：

alter table tablename deallocate UNUSED KEEP 0;

或者：

truncate table  tablename DROP STORAGE; 才能釋放表空間

注意:如果不加KEEP 0的話，表空間是不會釋放的。

實驗：接上面導入導出實驗

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                 110         1554

----開始整理

sys@ORCL>alter table shall.zhong deallocate unused keep 0;

Table altered.

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                 110         1554

sys@ORCL>analyze table shall.zhong compute statistics;

Table analyzed.

----整理之后

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                 110           18

6. truncate

盡量使用truncate （如：truncate t1）

實驗：接上面實驗

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                 110           18

sys@ORCL>truncate table shall.zhong;

Table truncated.

sys@ORCL>analyze table shall.zhong compute statistics;

Table analyzed.

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                   0          128

sys@ORCL>alter table shall.zhong deallocate unused keep 0;

Table altered.

sys@ORCL>analyze table shall.zhong compute statistics;

Table analyzed.

sys@ORCL>select table_name,blocks,empty_blocks from dba_tables  where table_name='ZHONG';

TABLE_NAME                         BLOCKS EMPTY_BLOCKS

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

ZHONG                                   0           24

以上是“數據庫中如何降低高水位”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！