DB2_收縮表空間
目的：
測試DB2收縮表空間，本指令碼摘錄自DB2安裝目錄admin_scripts/tbsreduce.db2。
版本：Windows DB2 Express-C V9.7
操作步驟：
使用"db2cmd db2 -t"進入互動模式,執行後續操作。
-- Set auto-commit of SQL statements to OFF.
UPDATE COMMAND OPTIONS USING c OFF;
-- Create database.
!db2start;
CREATE DB testdb1;
-- Connect to database.
CONNECT TO testdb1;
1,自動儲存管理表空間未使用空間回收:
-- Create table space 'tbsp_auto' managed by AUTOMATIC STORAGE.
CREATE TABLESPACE tbsp_auto PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 2 AUTORESIZE NO INITIALSIZE 107K;
-- Create table 'tab_auto1' in table space 'tbsp_auto'.
CREATE TABLE tab_auto1 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_auto;
-- Populate table 'tab_auto1' with the following data.
INSERT INTO tab_auto1 VALUES ('a', 'b', 'c', 'd', 'e',
'f', 'g', 'h', 'i', 'j' );
-- Create table 'tab_auto2' in table space 'tbsp_auto'.
CREATE TABLE tab_auto2 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_auto;
-- Populate table 'tab_auto2' with data so that the table space is full.
-------------------------------------------------------------------------------
-- Table space 'tbsp_auto' will be full after the five INSERT statements shown
-- below. Creation of new tables in table space 'tbsp_auto' will fail with an
-- error: "Unable to allocate new pages in table space".
-------------------------------------------------------------------------------
INSERT INTO tab_auto2 VALUES ('a', 'b', 'c', 'd', 'e',
'f', 'g', 'h', 'i', 'j' );
INSERT INTO tab_auto2 (SELECT * FROM tab_auto2);
INSERT INTO tab_auto2 (SELECT * FROM tab_auto2);
INSERT INTO tab_auto2 (SELECT * FROM tab_auto1);
INSERT INTO tab_auto2 (SELECT * FROM tab_auto2);
-- Create table 'tab_auto3' in table space 'tbsp_auto'. Table creation
-- will fail with the following error:
-- "Unable to allocate new pages in table space".
CREATE TABLE tab_auto3 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_auto;
! echo "Above error is expected !";
-- Take a snapshot of the table space. This will give details such as:
-- table space name, high water mark, extent size, used pages, free pages,
-- pending free pages for table space 'tbsp_auto'.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM TABLE (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_AUTO';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_AUTO 24 2 22 0 2
-- Drop tables 'tab_auto1' and 'tab_auto2'.
-- Take a table space snapshot to show the HWM hasn't changed after performing a
-- drop. The above and below snapshots show the same value for the HWM.
DROP TABLE tab_auto2;
DROP TABLE tab_auto1;
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM TABLE (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_AUTO';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_AUTO 24 2 6 0 18
-- Try to create new table 'tab_auto3' in the table space 'tbsp_auto'.
-- This will fail with the error:
-- "Unable to allocate new pages in table space" because there isn't any
-- free space available to accomodate the new table.
CREATE TABLE tab_auto3 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_auto;
! echo "Above error is expected !";
-- Perform. REDUCE on 'tbsp_auto'. This will fail with an error:
-- "The table space could not be reduced in size".
-- Table space cannot be reduced in size until the transaction containing the
-- DROP TABLE statements is committed.
-- After the transaction is committed, the space can be reused and new
-- tables can be created.
ALTER TABLESPACE tbsp_auto REDUCE;
-->SQL1348W 未能減小表空間大小。
! echo "Above error is expected !";
-- Perform. a COMMIT to permanently free extents used by the dropped tables and
-- allow the table space to be reduced in size.
-- The COMMIT will not reduce the HWM. The HWM will remain the same until a
-- REDUCE is performed on the table space.
COMMIT;
-- Take a table space snapshot to show the HWM hasn't changed after
-- performing COMMIT.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM TABLE (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_AUTO';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_AUTO 24 2 6 0 18
-- Perform. REDUCE on table space 'tbsp_auto' to reduce HWM.
ALTER TABLESPACE tbsp_auto REDUCE;
-- Take a table space snapshot to show the HWM has been reduced after
-- performing REDUCE on table space 'tbsp_auto'.
-- Pending free pages will be freed after REDUCE. After this is done,
-- creation of a new table in the table space will be successful.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM TABLE (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_AUTO';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_AUTO 6 2 6 4 0
1 條記錄已選擇。
-- New tables can now be created in the table space 'tbsp_auto' since
-- space has been reclaimed by the REDUCE operation.
-- Create table 'tab_auto3'. The size of the new table should be either
-- less than, or almost the same size as, the dropped tables.
-- Table creation will now be successful.
CREATE TABLE tab_auto3 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_auto;
-- Take a table space snapshot to show that the HWM has changed after
-- the new table was created.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM table (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_AUTO';
-- Drop table 'tab_auto3'.
DROP TABLE tab_auto3;
-- Drop table space 'tbsp_auto'.
DROP TABLESPACE tbsp_auto;
2,資料庫管理表空間未使用空間回收:
-- Create table space 'tbsp_dms' managed by database.
CREATE TABLESPACE tbsp_dms PAGESIZE 4096
MANAGED BY database using (file 'mycontainer' 28) EXTENTSIZE 2 AUTORESIZE NO;
-- Create table 'tab_1' in the table space 'tbsp_dms'.
CREATE TABLE tab_1 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_dms;
-- Populate the table 'tab_1' with the following data.
INSERT INTO tab_1 VALUES ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j' );
INSERT INTO tab_1 (SELECT * FROM tab_1);
INSERT INTO tab_1 (SELECT * FROM tab_1);
INSERT INTO tab_1 (SELECT * FROM tab_1);
-- Create table 'tab_2' in table space 'tbsp_auto'.
CREATE TABLE tab_2 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_dms;
-- Populate the table with data so that the container is full. Creation of new
-- tables in the table space will fail with the error:
-- "Unable to allocate new pages in table space".
INSERT INTO tab_2 (select * from tab_1);
-- Try creating new table 'tab_3'. It will fail with the error:
-- "Unable to allocate new pages in table space".
CREATE TABLE tab_3 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_dms;
! echo "Above error is expected !";
-- Commit the transaction.
COMMIT;
-- Take a snapshot of the table space. This gives details such as:
-- table space name, high water mark, extent size, used pages, free pages,
-- pending free pages for table space 'tbsp_dms'.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM TABLE (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_DMS';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_DMS 26 2 26 0 0
-- Drop table 'tab_2' and take a table space snapshot to show that the HWM
-- hasn't changed after dropping the table. The above and below snapshots show
-- the same value for HWM.
DROP TABLE tab_2;
SELECT SUBSTR (tablespace_name, 1, 10) as TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM table (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_DMS';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_DMS 26 2 16 0 10
-- Try creating new table 'tab_3'. It will fail with the error:
-- "Unable to allocate new pages in table space".
CREATE TABLE tab_3 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_dms;
! echo "Above error is expected !";
-- Perform. REDUCE on table space 'tbsp_dms'. This will result in the error:
-- "There is no enough space in the table space "TBSP_DMS". The amount of
-- space being removed is greater than the amount of space above the
-- high-water mark". The error occurs because the transaction has not been
-- committed and there isn't any free space on which to perform. a REDUCE
-- statement.
ALTER TABLESPACE tbsp_dms REDUCE (FILE 'mycontainer' 3);
--SQL20170N 表空間 "TBSP_DMS" 中沒有足夠的空間來執行指定的操作。原因碼："1"。
--SQLSTATE=57059
! echo "Above error is expected !";
-- Perform. COMMIT to make the free space available and allow the table
-- space to be reduced in size.
-- COMMIT will not reduce the HWM. The HWM will remain the same until a
-- REDUCE is performed on the table space.
COMMIT;
-- Take a table space snapshot to show the HWM hasn't changed after
-- performing COMMIT.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM table (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_DMS';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_DMS 26 2 16 0 10
-- Perform. REDUCE on the table space 'tbsp_dms' to reduce the HWM and
-- to free pending pages.
ALTER TABLESPACE tbsp_dms REDUCE (FILE 'mycontainer' 3);
-- Take a table space snapshot to show the HWM has been reduceed after
-- performing a REDUCE on table space 'tbsp_dms'.
-- Pending free pages will be freed after a REDUCE. After this is done,
-- creation of a new table in the table space will be successful.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM table (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_DMS';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_DMS 16 2 16 6 0
-- Create table 'tab_3'. The size of the table should be either
-- less than, or almost the same size as, the dropped table.
-- Table creation will now be successful.
CREATE TABLE tab_3 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
c10 char( 250 ) )
IN tbsp_dms;
-- Take a table space snapshot to show that the HWM has changed after
-- the new table was created.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM table (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_DMS';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_DMS 20 2 20 2 0
-- Drop table 'tab_1'
DROP TABLE tab_1;
-- Commit the transaction.
COMMIT;
-- Perform. REDUCE on table space 'tbsp_dms'.
ALTER TABLESPACE tbsp_dms REDUCE (FILE 'mycontainer' 3);
-- Take a table space snapshot to show that the HWM has not been reduced and
-- is the same as in the previous table space snapshot. The HWM will not be
-- reduced because it is held static by table 'tab_3'. So before table 'tab_1'
-- is dropped, table 'tab_3' needs to be dropped.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM table (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_DMS';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_DMS 20 2 10 10 0
-- Drop table 'tab_3'.
DROP TABLE tab_3;
-- Commit the transaction.
COMMIT;
-- Perform. REDUCE on table space 'tbsp_dms'.
ALTER TABLESPACE tbsp_dms REDUCE (FILE 'mycontainer' 3);
-- Take a table space snapshot to show that the HWM has been reduced after table
-- 'tab_3' was dropped.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
high_water_mark, extent_size, used_pages,
free_pages, pending_free_pages
FROM table (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
WHERE tablespace_name='TBSP_DMS';
--TABLESPACE_NAME HIGH_WATER_MARK EXTENT_SIZE USED_PAGES FREE_PAGES PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_DMS 6 2 6 10 0
-- Drop table space 'tbsp_dms'.
DROP TABLESPACE tbsp_dms;
-- Disconnect from database.
CONNECT RESET;
-- Drop database.
DROP DB testdb1;
TERMINATE;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-702311/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle表空間收縮方案Oracle
- mysql收縮共享表空間MySql
- 【RESIZE】Oracle收縮表空間主要命令Oracle
- 表空間（資料檔案shrink）收縮示例
- mysql共享表空間擴容,收縮,遷移MySql
- MySQL 5.7新特性之線上收縮undo表空間MySql
- lvm收縮邏輯卷空間LVM
- Oracle案例11——Oracle表空間資料庫檔案收縮Oracle資料庫
- 16、表空間 建立表空間
- Oracle 19c 線上縮減 UNDO 表空間 容量Oracle
- Oracle表空間Oracle
- oracle 表空間Oracle
- PostgreSQL 表空間SQL
- PostgreSQL：表空間SQL
- 當使用者無限制使用表空間配額且表空間有足夠空間時出現超出表空間的空間限額
- 表空間利用率及表空間的補充
- UNDO表空間空間回收及切換
- undo表空間容量
- 增加oracle表空間Oracle
- Configure innodb 表空間
- 表空間限額
- 3.2. 表空間
- 只讀表空間
- oracle temp 表空間Oracle
- KingbaseES的表空間
- oracle 表移動表空間Oracle
- Oracle表移動表空間Oracle
- oracle建立臨時表空間和資料表空間以及刪除Oracle
- 臨時表空間和回滾表空間使用率查詢
- MySQL 中的共享表空間與獨立表空間如何選擇MySql
- PostgreSQL:表空間-->資料庫-->表SQL資料庫
- mysql臨時表，臨時表空間，ibtmp1表空間暴增原因初探MySql
- Oracle臨時表空間檢視、新增臨時表空間資料檔案、修改預設臨時表空間 方法！Oracle
- 獲取表空間DDL
- Innodb：Undo 表空間巨大
- Tablespace表空間刪除
- MySQL InnoDB表空間加密MySql加密
- oracle表空間的整理Oracle
- Oracle 批量建表空間Oracle