备案控制台
开发者社区 数据库 文章 正文

SHOW_SPACE

2017-01-04 1273
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：       这是Oracle大神TOM写的一个好工具SHOW_SPACE；它实际上就是一个存储过程，这个存储过程可以很高效的分析空间使用情况，有了此工具，就不用再通过写SQL语句来看每条记录或表占用表空间的大小了，使用起来非常方便。

      这是Oracle大神TOM写的一个好工具SHOW_SPACE；它实际上就是一个存储过程，这个存储过程可以很高效的分析空间使用情况，有了此工具，就不用再通过写SQL语句来看每条记录或表占用表空间的大小了，使用起来非常方便。

一、创建存储过程

create or replace procedure show_space
( p_segname_1 in varchar2,
p_owner_1 in varchar2 default user,
p_type_1 in varchar2 default 'TABLE',
p_space in varchar2 default 'AUTO',
p_analyzed in varchar2 default 'Y'
)
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;
/

二、存储过程使用方法：


SQL> create table test01 as select * from all_objects;

Table created.

SQL> set serveroutput on;

SQL> exec show_space('TEST01');

Total Blocks............................1280
Total Bytes.............................10485760
Unused Blocks...........................38
Unused Bytes............................311296
Last Used Ext FileId....................5
Last Used Ext BlockId...................610304
Last Used Block.........................90
*************************************************
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............................1214
Total bytes.............................9945088

PL/SQL procedure successfully completed.



SQL> delete from TEST01;

85054 rows deleted.

SQL> exec show_space('TEST01');
Total Blocks............................1280
Total Bytes.............................10485760
Unused Blocks...........................38
Unused Bytes............................311296
Last Used Ext FileId....................5
Last Used Ext BlockId...................610304
Last Used Block.........................90
*************************************************
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...........1214
75% -- 100% free space bytes............9945088
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0

PL/SQL procedure successfully completed.


SQL> alter table TEST01 move;

Table altered.

SQL> exec show_space('TEST01');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................5
Last Used Ext BlockId...................609168
Last Used Block.........................3
*************************************************
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............................0
Total bytes.............................0

PL/SQL procedure successfully completed.

SQL>



文章标签：
SQL
存储
Perl
关系型数据库
Oracle
prudentwoo
目录
相关文章
即兴小索奇
|
6月前
|
前端开发 容器
什么是space-around
什么是space-around
即兴小索奇
41 0
陈言必行
|
图形学
Unity 报错之 The type or namespace name 'UI' does not exist in the namespace 'UnityEngine'
关于Unity2017 升级到 Unity2019 两个问题。 The type or namespace name 'UI' does not exist in the namespace 'UnityEngine'
陈言必行
2660 0
Unity 报错之 The type or namespace name 'UI' does not exist in the namespace 'UnityEngine'
青夜之衫
|
SQL 测试技术 索引
关于move tablespace的问题总结
青夜之衫
1313 0
ryan_bai
|
SQL Oracle 关系型数据库
MANAGE TABLESPACE
一、官档 BOOK → Database SQL Language Reference → 12 SQL Statements: ALTER TABLE to ALTER TABLESPACE → ALTER TABLESPACE 二、扩容表空间 扩大数据库的第三种方法是手工增大数据文件（datafile）的容量或使表空间（tablespace）内的数据文件容量可以随需动态地增长。
ryan_bai
1237 0
技术小哥哥
从V.C.Space抄来的
技术小哥哥
1091 0
科技小能手
|
Oracle 关系型数据库
UtilSession failed: Prerequisite check "CheckSystemSpace" space(22288172004) is not availa
科技小能手
1233 0
技术小胖子
Tablespace Report
技术小胖子
942 0
科技小能手
Script:Tablespace Report
科技小能手
799 0
科技小能手
Script to Detect Tablespace Fragmentation
科技小能手
854 0
bisal
|
存储 索引
学习show_space存储过程
今天学习和介绍一个有用的工具，来自TOM大神的show_space，其实这就是一个存储过程，用他可以统计一些段的用度，非常方便，网上流传着不同的版本。 首先我们看下原版的脚本，https://asktom.
bisal
1047 0