SQL> exec dbms_stats.gather_table_stats('scott','t_test_partition_1');

PL/SQL 過程已成功完成。

SQL> select initial_extent From user_tables where table_name='T_TEST_PARTITION_1
';

INITIAL_EXTENT
--------------

問題:為何初始區無值呢

-----------建立測試表
SQL> create table t_hwm(a int);

表已建立。

--------查詢測試表對應段的分配資訊,說明此時未給測試表分配空間
SQL> select segment_name,blocks,retention,freelists,buffer_pool,flash_cache from
user_segments where segment_name='T_HWM';

未選定行

SQL> desc user_tables;
名稱是否為空? 型別
----------------------------------------- -------- ----------------------------

TABLE_NAME                                NOT NULL VARCHAR2(30)
TABLESPACE_NAME                                    VARCHAR2(30)
CLUSTER_NAME                                       VARCHAR2(30)
IOT_NAME                                           VARCHAR2(30)
STATUS                                             VARCHAR2(8)
PCT_FREE                                           NUMBER
PCT_USED                                           NUMBER
INI_TRANS                                          NUMBER
MAX_TRANS                                          NUMBER
INITIAL_EXTENT                                     NUMBER
NEXT_EXTENT                                        NUMBER
MIN_EXTENTS                                        NUMBER
MAX_EXTENTS                                        NUMBER
PCT_INCREASE                                       NUMBER
FREELISTS                                          NUMBER
FREELIST_GROUPS                                    NUMBER
LOGGING                                            VARCHAR2(3)
BACKED_UP                                          VARCHAR2(1)
NUM_ROWS                                           NUMBER
BLOCKS                                             NUMBER
EMPTY_BLOCKS                                       NUMBER
AVG_SPACE                                          NUMBER
CHAIN_CNT                                          NUMBER
AVG_ROW_LEN                                        NUMBER
AVG_SPACE_FREELIST_BLOCKS                          NUMBER
NUM_FREELIST_BLOCKS                                NUMBER
DEGREE                                             VARCHAR2(20)
INSTANCES                                          VARCHAR2(20)
CACHE                                              VARCHAR2(10)
TABLE_LOCK                                         VARCHAR2(8)
SAMPLE_SIZE                                        NUMBER
LAST_ANALYZED                                      DATE
PARTITIONED                                        VARCHAR2(3)
IOT_TYPE                                           VARCHAR2(12)
TEMPORARY                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)
NESTED                                             VARCHAR2(3)
BUFFER_POOL                                        VARCHAR2(7)
FLASH_CACHE                                        VARCHAR2(7)
CELL_FLASH_CACHE                                   VARCHAR2(7)
ROW_MOVEMENT                                       VARCHAR2(8)
GLOBAL_STATS                                       VARCHAR2(3)
USER_STATS                                         VARCHAR2(3)
DURATION                                           VARCHAR2(15)
SKIP_CORRUPT                                       VARCHAR2(8)
MONITORING                                         VARCHAR2(3)
CLUSTER_OWNER                                      VARCHAR2(30)
DEPENDENCIES                                       VARCHAR2(8)
COMPRESSION                                        VARCHAR2(8)
COMPRESS_FOR                                       VARCHAR2(12)
DROPPED                                            VARCHAR2(3)
READ_ONLY                                          VARCHAR2(3)
SEGMENT_CREATED                                    VARCHAR2(3)
RESULT_CACHE                                       VARCHAR2(7)

---查詢測試的相關資訊,
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM

----插入資料至測試表
SQL> insert into t_hwm select level from dual connect by level<1e4;

已建立9999行。

SQL> commit;

提交完成。

---查詢測試表資訊,無資訊因為未分析表
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 過程已成功完成。

----分析表再次查詢即有資料
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM 20 0 9999

SQL> select segment_name,blocks from user_segments where segment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS
----------
T_HWM
24

----查詢測試表的區分配資訊
SQL> select segment_name,segment_type,extent_id,blocks from user_extents where s
egment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE EXTENT_ID BLOCKS
------------------ ---------- ----------
T_HWM
TABLE 0 8

T_HWM
TABLE 1 8

T_HWM
TABLE 2 8

---------查詢測試表資料
SQL> select count(*) from t_hwm;

COUNT(*)
----------
9999

----刪除測試表部分資料
SQL> delete from t_hwm where rownum<=1000;

已刪除1000行。

SQL> commit;

提交完成。

---------刪除部分資料後查詢測試表的區分配情況,未發生變化
SQL> select segment_name,segment_type,extent_id,blocks from user_extents where s
egment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE EXTENT_ID BLOCKS
------------------ ---------- ----------
T_HWM
TABLE 0 8

T_HWM
TABLE 1 8

T_HWM
TABLE 2 8

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 過程已成功完成。

---分析表後查詢測試表段未發生變化
SQL> select segment_name,blocks from user_segments where segment_name='T_HWM';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS
----------
T_HWM
24

----分析表後查詢表的相關資訊,未發生變化
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM 20 0 8999

SQL>

---運用oracle10g shrink space子句對hwm之下已刪除的空間進行釋放
----shrink space要開啟表的行移功功能,因為行的rowid會發生變更
SQL> alter table t_hwm shrink space;
alter table t_hwm shrink space
*
第 1 行出現錯誤:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table t_hwm enable row movement;

表已更改。

SQL> alter table t_hwm shrink space;

表已更改。

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 過程已成功完成。

-----如下說明shrink space操作生效,blocks由原20變更為14,釋放了6個block

SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM 14 0 8999

------查詢現在測試表的記錄數
SQL> select count(*) from t_hwm;

COUNT(*)
----------
8999

---刪除測試表的部分資料
SQL> delete from t_hwm where rownum<1000;

已刪除999行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 過程已成功完成。

----刪除資料後的表佔用blocks仍為14,未釋放出來
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM 14 0 8000

SQL>

-------測試shrink space compact選項的用法
SQL> alter table t_hwm shrink space compact;

表已更改。

SQL> exec dbms_stats.gather_table_stats('scott','t_hwm');

PL/SQL 過程已成功完成。

-----執行shrink及選項compact後表的blocks仍為14,
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM 14 0 8000

----再次執行shrink子句
SQL> alter table t_hwm shrink space;

表已更改。

--此處略去分析表語句,blocks反而由原14增加為21,怪怪了?原因何在?
SQL> select table_name,blocks,empty_blocks,num_rows from user_tables where table
_name='T_HWM';

TABLE_NAME BLOCKS EMPTY_BLOCKS NUM_ROWS
------------------------------ ---------- ------------ ----------
T_HWM 21 0 8000

-------執行insert多次插入資料至測試
SQL> insert into t_hwm select level from dual connect by level<=2e6;

已建立2000000行。

SQL> commit
2 ;

提交完成。

SQL> insert into t_hwm select level from dual connect by level<=2e6;

已建立2000000行。

SQL> commit;

提交完成。

SQL> select count(*) from t_hwm;

COUNT(*)
----------
4008000

-----刪除測試表部分資料
SQL> delete from t_hwm where rownum<=200000;

已刪除200000行。

SQL> commit;

提交完成。

---如下分別在不同會話執行shrink space子句,並在另一個會話監控操作期間持鎖情況,以便分析對於此表併發dml的影響
SQL> alter table t_hwm shrink space;

表已更改。

SQL> select object_id from dba_objects where object_name='T_HWM';

OBJECT_ID
----------
135014

------未執行shrink之前的持鎖情形
sysdba_session>select sid,type,id1,id2,lmode,request from v$lock where sid=193;

SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
193 AE 100 0 4 0

----執行shrink space之後的持鎖情形,經對比,操作期間會對錶持3級行級排它鎖tm和事務鎖tx
sysdba_session>/

       SID TY        ID1        ID2      LMODE    REQUEST
---------- -- ---------- ---------- ---------- ----------
       193 AE        100          0          4          0
       193 OD     135014          0          3          0
       193 SK          4   17078010          6          0
       193 TM     135014          0          3          0
       193 TX     393223      53462          6          0
       193 TX     393219      53461          6          0

已選擇6行。

-------測試shrink compact持鎖情形
測試語句略去,經測試同shrink子句的持鎖一樣

----小結:1,shrink子句僅適用於assm管理的表
         2,shrink馬上釋放高水位線
         3,shrink compact僅壓實表段,為以後釋放空間作好準備,但並不馬上釋放空間;
         4,shrink compact適用於分兩步釋放表空間,而不會像alter table shrink space消耗過多的時間
         5,cascade選項,會同時把依賴於表的相關物件進行回收空間
        補充:shrink可適用於表,分割槽表,子分割槽,索引,分割槽索引,iot,物化檢視,lob segment

---------shrink功能的幾條限制:

---如果包含long列的表,不能使用此功能,測試如下:
----新增long列
SQL> alter table t_hwm add b long;

表已更改。

SQL> update t_hwm set b=rowid;

已更新3608000行。

SQL> commit;

提交完成。

SQL> desc t_hwm;
名稱是否為空? 型別
----------------------------------------- -------- ----------------------------

A NUMBER(38)
B LONG

SQL> select count(*) from t_hwm;

COUNT(*)
----------
3608000

SQL>

SQL> delete from t_hwm where rownum<=200000;

已刪除200000行。

SQL> commit;

提交完成。

---瞧,明確提示測試表有long列,不能回收空間了
SQL> alter table t_hwm shrink space;
alter table t_hwm shrink space
*
第 1 行出現錯誤:
ORA-10662: Segment has long columns

---刪除列b
SQL> alter table t_hwm drop column b;

表已更改。

---clob及blob列的表可以回收空間,看下面的測試

SQL> alter table t_hwm add b clob;

表已更改。

SQL> update t_hwm set b='ab' where rownum<=2000;

已更新2000行。

SQL> commit;

提交完成。

SQL> delete from t_hwm where rownum<=100;

已刪除100行。

SQL> commit;

提交完成。

---clob列可以進行回收空間,blob也同理,略去程式碼
--注:clob列的回收很是消耗時間和資源,有空要研究下clob列的儲存和最佳化問題
SQL> alter table t_hwm shrink space;

表已更改。

SQL>

---oracle官方手冊講,對於函式索引及點陣圖連線索引的表,不能進行回收,真是如此嗎,見測試
00:00:46 SQL> desc t_hwm;
名稱是否為空? 型別
----------------------------------------- -------- ---------------------------

A NUMBER(38)

00:01:21 SQL> create index idx_t_hwm_func on t_hwm(length(a));

索引已建立。

已用時間: 00: 00: 04.16
00:02:31 SQL> select count(*) from t_hwm;

COUNT(*)
----------
3407900

已用時間: 00: 00: 00.53
00:02:42 SQL> delete from t_hwm where rownum<=20;

已刪除20行。

已用時間: 00: 00: 00.03
00:02:53 SQL> commit;

提交完成。

已用時間: 00: 00: 00.00

---如果表列建有函式索引,不能進行回收空間
00:02:55 SQL> alter table t_hwm shrink space;
alter table t_hwm shrink space
*
第 1 行出現錯誤:
ORA-10631: SHRINK clause should not be specified for this object

----------下面是3條關於回收子句的限制,不再一一測試,記錄於此,供備查所用
does not shrink mapping tables of index-organized tables, even if you specify CASCADE.

You cannot specify this clause for a compressed table.

You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.

已用時間: 00: 00: 00.02
00:03:06 SQL>

oracle10g_alter table shrink space_compact_cascade回收空間測試(一)

相關文章