Oracle―deallocate unused释放高水位空间（二）

deallocate unused :仅适用于释放HWM高水位以上的空间，而无法释放高水位以下的空间；比如对表预分配的空间

使用说明和方法，官方文档有说明，如下：

Use the deallocate_unused_clause to explicitly deallocate unused space at the end of

a database object segment and make the space available for other segments in the

tablespace.

You can deallocate unused space using the following statements:

■ ALTER CLUSTER (see ALTER CLUSTER on page 10-5)

■ ALTER INDEX: to deallocate unused space from the index,an index partition,or an

index subpartition (see ALTER INDEX on page 10-78)

■ ALTER MATERIALIZED VIEW: to deallocate unused space from the overflow segment

of an index-organized materialized view (see ALTER MATERIALIZED VIEW on

page 11-3)

■ ALTER TABLE: to deallocate unused space from the table,a table partition,a table

subpartition,the mapping table of an index-organized table,the overflow segment

of an index-organized table,or a LOB storage segment (see ALTER TABLE on

page 12-2)

一、测试环境：

Oracle11.2.0.4

使用admin用户在test表空间新建一张测试分区表，并提前对子分区预分配一些空间，然后插入少量的数据，最后使用：alter table .....deallocate unused;来释放未使用的空间

二、开始测试

①：查看测试环境表空间使用情况：

sql>setlinesize2500
sql>setpagesize300
sql>selecta.tablespace_name"表空间名称",100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2)"占用率(%)",round(a.bytes_alloc/1024/1024,2)"容量(M)",round(nvl(b.bytes_free,0)/1024/1024,2)"空闲(M)",round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2)"使用(M)",to_char(sysdate,'yyyy-mm-ddhh24:mi:ss')"采样时间"from(selectf.tablespace_name,sum(f.bytes)bytes_alloc,sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes))maxbytesfromdba_data_filesfgroupbytablespace_name)a,(selectf.tablespace_name,sum(f.bytes)bytes_freefromdba_free_spacefgroupbytablespace_name)bwherea.tablespace_name=b.tablespace_nameorderby2desc;
????????????????????????(%)??????(M)??????(M)??????(M)????????????
-------------------------------------------------------------------------------------------
SYSAUX94.4151028.5481.52017-08-0717:01:20
SYSTEM93.2480054.06745.942017-08-0717:01:20
USERS26.2553.691.312017-08-0717:01:20
UNDOTBS117.97470385.5684.442017-08-0717:01:20
TEST.052048020470102017-08-0717:01:20

sql>showuser
USERis"ADMIN"----测试用的用户

②：创建测试分区表：test_emp

sql>createtabletest_emp(TMP_UPSTATE_CASEKEYchar(14),TMP_NUM_STATUS_IDnumber(38),updated_datedate)
2partitionbyrange(TMP_NUM_STATUS_ID)
3(partitionpt_1valueslessthan(1000000))nologging;
Tablecreated.

③：给pt_1分区表预分配10G的空间：

sql>altertabletest_EMPmodifyPARTITIONpt_1ALLOCATEEXTENT(size10240m);
Tablealtered.

④：收集该分区表的统计信息，并查看该表的block块占用情况

sql>execdbms_stats.gather_table_stats(ownname=>'ADMIN',tabname=>'TEST_EMP',cascade=>TRUE);
PL/sqlproceduresuccessfullycompleted.
sql>selectB.SEGMENT_NAME,B.blocks,B.blocks*8096/1024/1024,A.BLOCKS,A.blocks*8096/1024/1024,A.EMPTY_BLOCKSfromuser_tablesa,USER_SEGMENTSBWHERETABLE_NAME='TEST_EMP'ANDA.TABLE_NAME=B.SEGMENT_NAME;
SEGMENT_NAMEBLOCKSB.BLOCKS*8096/1024/1024BLOCKSA.BLOCKS*8096/1024/1024EMPTY_BLOCKS
---------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_EMP131584010159.5313000
sql>SELECTsegment_name,SUM(bytes)/1024/1024MbytesFROMdba_segmentsWHEREPARTITION_NAME='PT_1'GROUPBYsegment_name;
SEGMENT_NAMEMBYTES
-------------------------------------------------------------------------------------------
TEST_EMP10288
BIN$VibHVCPfDL/gU8gCqMDDfw==$08

---从上面可以看出，test_emp分区表的大小是10G，而占用的blocks有1315840个；

⑤：向分区表插入一些数据，并查看该表的大小

sql>createorreplaceprocedureproc_casekey_upstate
2as
3casekeychar(14);
4begin
5foriin1..10000loop
6casekey:='TMP'||lpad(i,7,0);
7insertintotest_empvalues(casekey,1,sysdate);
8endloop;
9commit;
10end;
11/
Procedurecreated.
sql>execproc_casekey_upstate;---执行存储过程插入数据
PL/sqlproceduresuccessfullycompleted.
sql>execdbms_stats.gather_table_stats(ownname=>'ADMIN',cascade=>TRUE);---收集该表的统计信息
PL/sqlproceduresuccessfullycompleted.
sql>selectB.SEGMENT_NAME,USER_SEGMENTSBWHERETABLE_NAME='TEST_EMP'ANDA.TABLE_NAME=B.SEGMENT_NAME;
SEGMENT_NAMEBLOCKSB.BLOCKS*8096/1024/1024BLOCKSA.BLOCKS*8096/1024/1024EMPTY_BLOCKS
---------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_EMP131584010159.531346.3551635740
sql>SELECTsegment_name,SUM(bytes)/1024/1024MbytesFROMdba_segmentsWHEREPARTITION_NAME='PT_1'GROUPBYsegment_name;
SEGMENT_NAMEMBYTES
-------------------------------------------------------------------------------------------
TEST_EMP10288
BIN$VibHVCPfDL/gU8gCqMDDfw==$08

--注意：从上面可以看出，该表的大小是10G，但是该表占用blocks只有46个，显然有很多空间没有被使用，

⑥：释放未被使用的空间（我对该操作过了10046事件，在实际操作中只需执行：alter table test_emp modify partition pt_1 deallocate unused; ）

sql>altersessionsettracefile_identifier='10046';
sql>altersessionsetevents'10046tracenamecontextforever,level12';---开启10046事件
Sessionaltered.
sql>
sql>altertabletest_empmodifypartitionpt_1deallocateunused;---执行该命令释放子分区未被使用的空间；

Tablealtered.
sql>altersessionsetevents'10046tracenamecontextoff';---关闭10046事件
Sessionaltered.
sql>selectvaluefromv$diag_infowherename='DefaultTraceFile';
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/dbs/dbs/trace/dbs_ora_3263_10046.trc

⑦：验证空间是否被释放：

sql>selectB.SEGMENT_NAME,USER_SEGMENTSBWHERETABLE_NAME='TEST_EMP'ANDA.TABLE_NAME=B.SEGMENT_NAME;
SEGMENT_NAMEBLOCKSB.BLOCKS*8096/1024/1024BLOCKSA.BLOCKS*8096/1024/1024EMPTY_BLOCKS
---------------------------------------------------------------------------------------------------------------------------------------------------------------
TEST_EMP10247.9062546.3551635740

sql>SELECTsegment_name,SUM(bytes)/1024/1024MbytesFROMdba_segmentsWHEREPARTITION_NAME='PT_1'GROUPBYsegment_name;
SEGMENT_NAMEMBYTES
-------------------------------------------------------------------------------------------
TEST_EMP16
BIN$VibHVCPfDL/gU8gCqMDDfw==$08
-------------------------------------------------------------------------------------------
SYSAUX94.4451028.38481.632017-08-0717:19:30
SYSTEM93.2480054.06745.942017-08-0717:19:30
USERS26.2553.691.312017-08-0717:19:30
UNDOTBS15.73470443.0626.942017-08-0717:19:30
TEST.052048020470102017-08-0717:19:30

从上面可以看出，该表的大小变成了16M，而blocks也有1024个；表未被使用的空间已经释放，而且表空间可用大小也已经变大：

Oracle―deallocate unused释放高水位空间（二）

猜你在找的Oracle相关文章