Tom寫的show_space過程---紀錄每條紀錄及表資料大小 (zt)
關於SHOW_SPACE()工具的用法
TOM寫了個好SHOW_SPACE,這個工具對於Oracle來講其實就是個,這個儲存過程可以用來分析空間使用情況,有了此工具,就不用再透過寫SQL來看每條記錄或表佔用表空間的了,使用起來很方便。
具體使用過程如下:
首先需要建立一個儲存過程:
d:>sqlplus /nolog
SQL>connect / as sysdba
SQL>create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
as
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_last_used_block number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_LastUsedExtFileId,
last_used_extent_block_id => l_LastUsedExtBlockId,
last_used_block => l_last_used_block );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_last_used_block );
end;
/
Procedure created.
執行以上語句會在當前下生成一個procedure,當前使用者為sys使用者。
SQL>create table t as select * from all_users; (建立表T)
SQL> exec show_space('T'); (檢視錶T佔用空間大小)
Free Blocks.............................0
Total Blocks............................15
Total Bytes.............................61440
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................13
Last Used Ext BlockId...................61782
Last Used Block.........................2
結果馬上就出來了,以前必須透過SQL語句查詢dba_tables才能得到結果,可見,此工具的方便性。
另外,此工具有好幾個版本,目前上面這個版本只適合表空間為非ASSM的時候,ASSM的時候是不能用的,原因是DBMS_SPACE.FREE_BLOCKS 不允許在ASSM上操作,解決方法如下:
對於ASSM,可以使用dbms_space.space_usage ,可以在show_space中加入這一段:
select ts.segment_space_management
into t_segment_space_management
from dba_segments seg
, dba_tablespaces ts
where seg.segment_name = t_segname
and seg.owner = t_owner
and seg.tablespace_name = ts.tablespace_name
;
--
if t_segment_space_management = 'AUTO'
then
dbms_space.space_usage (
t_owner,
t_segname,
t_type,
l_unformatted_blocks,
l_unformatted_bytes,
l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes,
l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes,
l_full_blocks, l_full_bytes
);dure successfully completed.
--
p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => t_owner,
segment_name => t_segname,
segment_type => t_type,
freelist_group_id => 0,
free_blks => l_free_blks
);
--
p( 'Free Blocks', l_free_blks );
end if;
---------------------------------------------
改進後的版本:
create or replace procedure show_space
( p_segname_1 in varchar2,
p_space in varchar2 default 'MANUAL',
p_type_1 in varchar2 default 'TABLE' ,
p_analyzed in varchar2 default 'N',
p_owner_1 in varchar2 default user)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;
if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;
if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;
if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
p( 'Free Blocks', l_free_blks );
end if;
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);
end if;
end;
ASSM 型別的表
SQL> exec show_space('t','auto');
Total Blocks............................512
Total Bytes.............................4194304
Unused Blocks...........................78
Unused Bytes............................638976
Last Used Ext FileId....................9
Last Used Ext BlockId...................25608
Last Used Block.........................50
PL/SQL procedure successfully completed.
ASSM 型別的索引
SQL> exec show_space('t_index','auto','i');
Total Blocks............................80
Total Bytes.............................655360
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................9
Last Used Ext BlockId...................25312
Last Used Block.........................3
PL/SQL procedure successfully completed.
對analyze 過的segment 可以這樣
SQL> exec show_space('t','auto','T','Y');
Total Blocks............................512
Total Bytes.............................4194304
Unused Blocks...........................78
Unused Bytes............................638976
Last Used Ext FileId....................9
Last Used Ext BlockId...................25608
Last Used Block.........................50
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................418
Total bytes.............................3424256
PL/SQL proce
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/35489/viewspace-84803/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 從資料庫返回多條紀錄賦值給shell變數資料庫賦值變數
- 紀錄片《Code Rush》
- update誤操作後 通過undo記錄的scn找回原紀錄
- Caffe學習紀錄01
- 備份指令碼--紀錄指令碼
- ESSBASE 安裝全紀錄
- 《黃錄 • 大黃本紀》
- 世界紀錄模板原始碼+Wap原始碼
- 《星辰牧歌》開發程式紀錄
- 紀錄片《中國遊戲紀事》首映會順利舉辦遊戲
- 控制DDL許可權及紀錄DDL操作的Trigger
- 暑期觀影 / 紀錄片推薦
- MySQL-資料記錄大小寫轉換MySql
- mysql去除某些欄位重複的紀錄MySql
- Oracle 10g Stream 時時資料同步, 全程試驗紀錄Oracle 10g
- 資料庫擴充套件表設計過程記錄資料庫套件
- 大型紀錄片《網際網路時代》
- 【實驗】【Tom】show_space儲存過程的使用演示儲存過程
- 天合光能重新整理210 i-TOPCon電池效率紀錄,第23次創世界紀錄
- Webview清空歷史紀錄的正確姿勢WebView
- 紀錄片《軟體專利的荒謬性》
- 資料寫過程中各項觸發條件及邏輯
- 在Oracle中找出重複的紀錄的方法(轉)Oracle
- mysql表名忽略大小寫問題記錄MySql
- 最高TPM達340萬,騰訊雲SQL Server資料庫效能再破紀錄SQLServer資料庫
- 通關TPC-DS,中國資料庫領域首破紀錄誕生!資料庫
- 每秒8700萬次!雙11資料庫峰值新紀錄背後的關鍵力量資料庫
- Oracle遷移資料庫過程記錄Oracle資料庫
- 紀錄片《搶救切爾諾貝利》(Battle of Chernobyl)BAT
- Redis 運維實際經驗紀錄之一Redis運維
- 論文速讀紀錄 - 202408
- 計算資料庫中所有表的記錄條數資料庫
- 各個資料庫的取一表前N條記錄不同SQL寫法資料庫SQL
- 那些開源與科技的紀錄片(上)|Open Source Prism
- 2017年程式設計師必看的紀錄片程式設計師
- 查詢凶手:一次logmin的紀錄和分析
- 解決需求的實際過程記錄——10萬條降雨資料的處理
- 觀紀錄片《監視資本主義——智慧陷阱》有感而發AUV