sqlplus之 show_space

SHOW_PACE例程用于打印數(shù)據(jù)庫段空間利用率信息。

接口如下:

sys@ORCL>desc?show_space;
PROCEDURE?show_space
參數(shù)名稱???????????????????????類型????????????????????輸入/輸出默認(rèn)值?
------------------------------?-----------------------?------?--------
?P_SEGNAME??????????????????????VARCHAR2????????????????IN
?P_OWNER????????????????????????VARCHAR2????????????????IN?????DEFAULT
?P_TYPE?????????????????????????VARCHAR2????????????????IN?????DEFAULT
?P_PARTITION????????????????????VARCHAR2????????????????IN?????DEFAULT

P_SEGNAME? ? 段名(例如，表或者索引名)

P_OWNER? ? 默認(rèn)為當(dāng)前用戶，不過也可以使用這個(gè)例程查看另外某個(gè)模式

P_TYPE? ? 默認(rèn)為TABLE，表示查看哪種類型的對(duì)象(段).如下是合法的段類型：

sys@ORCL>select?distinct?segment_type?from?dba_segments;

SEGMENT_TYPE
------------------
LOBINDEX
INDEX?PARTITION
NESTED?TABLE
TABLE?PARTITION
ROLLBACK
LOB?PARTITION
LOBSEGMENT
TABLE
INDEX
CLUSTER
TYPE2?UNDO

已選擇11行。

P_PARTITION? ? 顯示分區(qū)對(duì)象的空間時(shí)所用的分區(qū)名。SHOW_PACE一次只顯示一個(gè)分區(qū)的空間利用率。

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

1.–建立SHOW_PACE

sys@ORCL>CREATE?OR?REPLACE?PROCEDURE?show_space(p_segname???IN?VARCHAR2,
??2?????????????????????????????????????????p_owner?????IN?VARCHAR2?DEFAULT?USER,
??3?????????????????????????????????????????p_type??????IN?VARCHAR2?DEFAULT?'TABLE',
??4?????????????????????????????????????????p_partition?IN?VARCHAR2?DEFAULT?NULL?)?AS
??5????l_free_blks??????????NUMBER;
??6????l_total_blocks???????NUMBER;
??7????l_total_bytes????????NUMBER;
??8????l_unused_blocks??????NUMBER;
??9????l_unused_bytes???????NUMBER;
?10????l_lastusedextfileid??NUMBER;
?11????l_lastusedextblockid?NUMBER;
?12????l_last_used_block????NUMBER;
?13????l_segment_space_mgmt?VARCHAR2(255);
?14????l_unformatted_blocks?NUMBER;
?15????l_unformatted_bytes??NUMBER;
?16????l_fs1_blocks?????????NUMBER;
?17????l_fs1_bytes??????????NUMBER;
?18????l_fs2_blocks?????????NUMBER;
?19????l_fs2_bytes??????????NUMBER;
?20????l_fs3_blocks?????????NUMBER;
?21????l_fs3_bytes??????????NUMBER;
?22????l_fs4_blocks?????????NUMBER;
?23????l_fs4_bytes??????????NUMBER;
?24????l_full_blocks????????NUMBER;
?25????l_full_bytes?????????NUMBER;
?26
?27????PROCEDURE?p(p_label?IN?VARCHAR2,
?28????????????????p_num???IN?NUMBER)?IS
?29????BEGIN
?30??????dbms_output.put_line(rpad(p_label,?40,?'.')?||
?31???????????????????????????to_char(p_num,?'999,999,999,999'));
?32????END;
?33??BEGIN
?34????EXECUTE?IMMEDIATE?'select?ts.segment_space_management?from?dba_segments?seg,dba_tablespaces?ts?where?seg.segment_name?=?:p_segname?and?(:p_partition?is?null?or?seg.partition_name?=?:p_partition)?and?seg.owner?=?:p_owner?and?seg.tablespace_name=ts.tablespace_name'
?35??????INTO?l_segment_space_mgmt
?36??????USING?p_segname,?p_partition,?p_partition,?p_owner;
?37
?38????--?????exception
?39????--?????????????when?too_many_rows
?40????--?????????????then
?41????--?????????????dbms_output.put_line('This?must?be?a?partitioned?table,use?p_partition?=>?');
?42????--?????????????return;
?43????--?????end;
?44

?45????IF?l_segment_space_mgmt?=?'AUTO'?THEN
?46??????dbms_space.space_usage(p_owner,?p_segname,?p_type,?l_unformatted_blocks,
?47?????????????????????????????l_unformatted_bytes,?l_fs1_blocks,?l_fs1_bytes,
?48?????????????????????????????l_fs2_blocks,?l_fs2_bytes,?l_fs3_blocks,?l_fs3_bytes,
?49?????????????????????????????l_fs4_blocks,?l_fs4_bytes,?l_full_blocks,
?50?????????????????????????????l_full_bytes,?p_partition);
?51
?52??????p('Unformatted?Blocks??',?l_unformatted_blocks);
?53??????p('FS1?Blocks?(0-25)???',?l_fs1_blocks);
?54??????p('FS2?Blocks?(25-50)??',?l_fs2_blocks);
?55??????p('FS3?Blocks?(50-75)??',?l_fs3_blocks);
?56??????p('FS4?Blocks?(75-100)?',?l_fs4_blocks);
?57??????p('Full?Blocks?????????',?l_full_blocks);
?58????ELSE
?59??????dbms_space.free_blocks(segment_owner?=>?p_owner,?segment_name?=>?p_segname,
?60?????????????????????????????segment_type?=>?p_type,?freelist_group_id?=>?0,
?61?????????????????????????????free_blks?=>?l_free_blks);
?62????END?IF;
?63
?64????dbms_space.unused_space(segment_owner?=>?p_owner,?segment_name?=>?p_segname,
?65????????????????????????????segment_type?=>?p_type,?partition_name?=>?p_partition,
?66????????????????????????????total_blocks?=>?l_total_blocks,
?67????????????????????????????total_bytes?=>?l_total_bytes,
?68????????????????????????????unused_blocks?=>?l_unused_blocks,
?69????????????????????????????unused_bytes?=>?l_unused_bytes,
?70????????????????????????????last_used_extent_file_id?=>?l_lastusedextfileid,
?71????????????????????????????last_used_extent_block_id?=>?l_lastusedextblockid,
?72????????????????????????????last_used_block?=>?l_last_used_block);
?73
?74????p('Total?Blocks?',?l_total_blocks);
?75????p('Total?Bytes??',?l_total_bytes);
?76????p('Total?MBytes?',?trunc(l_total_bytes?/?1024?/?1024));
?77????p('Unused?Blocks',?l_unused_blocks);
?78????p('Unused?Bytes?',?l_unused_bytes);
?79????p('Last?Used?Ext?FileId',?l_lastusedextfileid);
?80????p('Last?Used?Ext?BlockId',?l_lastusedextblockid);
?81????p('Last?Used?Block',?l_last_used_block);
?82??END;
?83??/

過程已創(chuàng)建。

2.用法演示

sys@ORCL>create?table?test_space
??2??AS
??3??select?*?from?dba_tables;

表已創(chuàng)建。

sys@ORCL>exec?show_space('TEST_SPACE');
Total?Blocks?...........................?????????????104
Total?Bytes??...........................?????????851,968
Total?MBytes?...........................???????????????0
Unused?Blocks...........................???????????????3
Unused?Bytes?...........................??????????24,576
Last?Used?Ext?FileId....................???????????????1
Last?Used?Ext?BlockId...................??????????87,912
Last?Used?Block.........................???????????????5

PL/SQL?過程已成功完成。

Total Blocks、Total Bytes、Total MBytes ????為所查看的段分配的總空間量，單位分別是數(shù)據(jù)庫塊、字節(jié)、兆字節(jié)

Unused Blocks、Unused Bytes????????????????? ? 表示未用空間所占的比例(未用空間量)，

????????????????????????????????????????????????????????????????????這些塊已經(jīng)分配給所查看的段，但目前在段的HWM之上

Last Used Ext FileId? ? ????????????????????????????????最后使用的文件的文件ID，該文件包含最后一個(gè)含數(shù)據(jù)的區(qū)段(extent)

Last Used Ext BlockId????????????????????????????? ? 最后一個(gè)區(qū)段開始處的塊ID；這是最后使用的文件中的塊ID

Last Used Block????????????????????????????????????? ? 最后一個(gè)區(qū)段中最后一個(gè)塊的偏移量

sys@ORCL>delete?from?test_space;
已刪除2859行。

sys@ORCL>commit;
提交完成。

sys@ORCL>exec?show_space('TEST_SPACE');
Total?Blocks?...........................?????????????104
Total?Bytes??...........................?????????851,968
Total?MBytes?...........................???????????????0
Unused?Blocks...........................???????????????3
Unused?Bytes?...........................??????????24,576
Last?Used?Ext?FileId....................???????????????1
Last?Used?Ext?BlockId...................??????????87,912
Last?Used?Block.........................???????????????5

PL/SQL?過程已成功完成。

sys@ORCL>truncate?table?test_space;
表被截?cái)唷?
sys@ORCL>exec?show_space('TEST_SPACE');
Total?Blocks?...........................???????????????8
Total?Bytes??...........................??????????65,536
Total?MBytes?...........................???????????????0
Unused?Blocks...........................???????????????7
Unused?Bytes?...........................??????????57,344
Last?Used?Ext?FileId....................???????????????1
Last?Used?Ext?BlockId...................??????????87,816
Last?Used?Block.........................???????????????1

PL/SQL?過程已成功完成。

sys@ORCL>drop?table?test_space;
表已刪除。