MSSQL编程：关于SHOW_SPACE()东西的用法

仓酷云

限制,如果WHERE子句的查询条件里有不等号（WHEREcoloum!=），MySQL将无法使用索引。类似地，如果WHERE子句的查询条件里使用了函数（WHEREDAY（column）=），MySQL也将无法使用索引。
TOM写了个好工具SHOW_SPACE,这个工具关于Oracle来说实在就是个存储历程，这个存储历程能够用来剖析空间利用情形，有了此工具，就不必再经由过程写SQL语句来看每笔记录或表占用表空间的巨细了，利用起来很便利。

详细利用历程以下：

起首必要创立一个存储历程:

d:>sqlplus/nolog

SQL>connect/assysdba

SQL>createorreplaceprocedureshow_space

(p_segnameinvarchar2,

p_ownerinvarchar2defaultuser,

p_typeinvarchar2defaultTABLE,

p_partitioninvarchar2defaultNULL)

as

l_total_blocksnumber;

l_total_bytesnumber;

l_unused_blocksnumber;

l_unused_bytesnumber;

l_LastUsedExtFileIdnumber;

l_LastUsedExtBlockIdnumber;

l_last_used_blocknumber;

procedurep(p_labelinvarchar2,p_numinnumber)

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(TotalBlocks,l_total_blocks);

p(TotalBytes,l_total_bytes);

p(UnusedBlocks,l_unused_blocks);

p(UnusedBytes,l_unused_bytes);

p(LastUsedExtFileId,l_LastUsedExtFileId);

p(LastUsedExtBlockId,l_LastUsedExtBlockId);

p(LastUsedBlock,l_last_used_block);

end;
/
Procedurecreated.

实行以上语句会在以后用户下天生一个procedure，以后用户为sys用户。

SQL>createtabletasselect*fromall_users;(创立表T)

SQL>execshow_space(T);(检察表T占用空间巨细)
FreeBlocks.............................0
TotalBlocks............................15
TotalBytes.............................61440
UnusedBlocks...........................13
UnusedBytes............................53248
LastUsedExtFileId....................13
LastUsedExtBlockId...................61782
LastUsedBlock.........................2


了局即刻就出来了，之前必需经由过程SQL语句查询dba_tables才干失掉了局，可见，此工具的便利性。

别的，此工具有好几个版本，今朝下面这个版本只合适表空间为非ASSM的时分，ASSM的时分是不克不及用的，缘故原由是DBMS_SPACE.FREE_BLOCKS不同意在ASSM上操纵，办理办法以下：

关于ASSM，可使用dbms_space.space_usage，能够在show_space中到场这一段：

selectts.segment_space_management
intot_segment_space_management
fromdba_segmentsseg
,dba_tablespacests
whereseg.segment_name=t_segname
andseg.owner=t_owner
andseg.tablespace_name=ts.tablespace_name
;
--
ift_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
);
--
p(UnformattedBlocks,l_unformatted_blocks);
p(FS1Blocks(0-25),l_fs1_blocks);
p(FS2Blocks(25-50),l_fs2_blocks);
p(FS3Blocks(50-75),l_fs3_blocks);
p(FS4Blocks(75-100),l_fs4_blocks);
p(FullBlocks,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(FreeBlocks,l_free_blks);
endif;


ITPUB上供应了该工具的良多版本，详细另有以下几个版本，也很好用，对从此的工具年夜有优点！

xzh2000供应的终极夹杂超等完整无敌版：

createorreplaceprocedureshow_space
(p_segname_1invarchar2,
p_spaceinvarchar2defaultMANUAL,
p_type_1invarchar2defaultTABLE,
p_analyzedinvarchar2defaultN,
p_owner_1invarchar2defaultuser)
as
p_segnamevarchar2(100);
p_typevarchar2(10);
p_ownervarchar2(30);

l_unformatted_blocksnumber;
l_unformatted_bytesnumber;
l_fs1_blocksnumber;
l_fs1_bytesnumber;
l_fs2_blocksnumber;
l_fs2_bytesnumber;
l_fs3_blocksnumber;
l_fs3_bytesnumber;
l_fs4_blocksnumber;
l_fs4_bytesnumber;
l_full_blocksnumber;
l_full_bytesnumber;

l_free_blksnumber;
l_total_blocksnumber;
l_total_bytesnumber;
l_unused_blocksnumber;
l_unused_bytesnumber;
l_LastUsedExtFileIdnumber;
l_LastUsedExtBlockIdnumber;
l_LAST_USED_BLOCKnumber;

procedurep(p_labelinvarchar2,p_numinnumber)
is
begin
dbms_output.put_line(rpad(p_label,40,.)||
p_num);
end;
begin
p_segname:=upper(p_segname_1);--rainychanged
p_owner:=upper(p_owner_1);
p_type:=p_type_1;

if(p_type_1=iorp_type_1=I)then--rainychanged
p_type:=INDEX;
endif;

if(p_type_1=torp_type_1=T)then--rainychanged
p_type:=TABLE;
endif;

if(p_type_1=corp_type_1=C)then--rainychanged
p_type:=CLUSTER;
endif;


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);

ifp_space=MANUALor(p_spaceautoandp_spaceAUTO)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(FreeBlocks,l_free_blks);
endif;

p(TotalBlocks,l_total_blocks);
p(TotalBytes,l_total_bytes);
p(UnusedBlocks,l_unused_blocks);
p(UnusedBytes,l_unused_bytes);
p(LastUsedExtFileId,l_LastUsedExtFileId);
p(LastUsedExtBlockId,l_LastUsedExtBlockId);
p(LastUsedBlock,l_LAST_USED_BLOCK);


/*IFthesegmentisanalyzed*/
ifp_analyzed=Ythen
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(Thesegmentisanalyzed);
p(0%--25%freespaceblocks,l_fs1_blocks);
p(0%--25%freespacebytes,l_fs1_bytes);
p(25%--50%freespaceblocks,l_fs2_blocks);
p(25%--50%freespacebytes,l_fs2_bytes);
p(50%--75%freespaceblocks,l_fs3_blocks);
p(50%--75%freespacebytes,l_fs3_bytes);
p(75%--100%freespaceblocks,l_fs4_blocks);
p(75%--100%freespacebytes,l_fs4_bytes);
p(UnusedBlocks,l_unformatted_blocks);
p(UnusedBytes,l_unformatted_bytes);
p(TotalBlocks,l_full_blocks);
p(Totalbytes,l_full_bytes);

endif;

end;


ASSM范例的表

SQL>execshow_space(t,auto);
TotalBlocks............................512
TotalBytes.............................4194304
UnusedBlocks...........................78
UnusedBytes............................638976
LastUsedExtFileId....................9
LastUsedExtBlockId...................25608
LastUsedBlock.........................50

PL/SQLproceduresuccessfullycompleted.


ASSM范例的索引


SQL>execshow_space(t_index,auto,i);
TotalBlocks............................80
TotalBytes.............................655360
UnusedBlocks...........................5
UnusedBytes............................40960
LastUsedExtFileId....................9
LastUsedExtBlockId...................25312
LastUsedBlock.........................3

PL/SQLproceduresuccessfullycompleted.


对analyze过的segment能够如许

SQL>execshow_space(t,auto,T,Y);
TotalBlocks............................512
TotalBytes.............................4194304
UnusedBlocks...........................78
UnusedBytes............................638976
LastUsedExtFileId....................9
LastUsedExtBlockId...................25608
LastUsedBlock.........................50
*************************************************
Thesegmentisanalyzed
0%--25%freespaceblocks.............0
0%--25%freespacebytes..............0
25%--50%freespaceblocks............0
25%--50%freespacebytes.............0
50%--75%freespaceblocks............0
50%--75%freespacebytes.............0
75%--100%freespaceblocks...........0
75%--100%freespacebytes............0
UnusedBlocks...........................0
UnusedBytes............................0
TotalBlocks............................418
Totalbytes.............................3424256

PL/SQLproceduresuccessfullycompleted.

支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）

仓酷云

无法深入到数据库系统层面去了解和探究

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

连做梦都在想页面结构是怎么样的，绝非虚言

然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

所以你总能得到相应的升级版本，来满足你的需求。

记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

可以动态传入参数，省却了动态SQL的拼写。