|
|
本人学习研究sybase已经n多年了，从刚开始什么都不会，到现在自诩为的"专家"，感受也颇深。我都会sybase什么东西呢？sybase是一个软件公司，其中包括...查看全文
博客简介
本人学习研究sybase已经n多年了，从刚开始什么都不会，到现在自诩为的"专家"，感受也颇深。我都会sybase什么东西呢？sybase是一个软件公司，其中包括了ase，asa，iq，rs，ma，pb，pd等等等等。ase我应该算是精通了，我开发了ase的灾难恢复软件powersybedit，开发了存储过程调试器，开发了日志查看器等软件。asa方面，也能够做一些数据的恢复，还有密码的破解（可能有人认为很简单，但事实上在加密条件下，dba用户被修改等情况下的破解，嘿嘿，我还是有办法的）。iq么，一些皮毛，准备入手学习。rs么基本上没有什么问题，做过大型的复制系统，双向复制等等。pb么，代码总归看得懂，用起来也没有问题。pd么，呵呵，仅仅会用而已，画画流程图表结构设计等。
|
 |
| |
| 今天浏览了一下sybase官方网站，没想到上面居然有我的一篇文章 |
|
摘录如下
Sybase：直接查询段的空间使用情况
问：使用sp_helpsegment可以看到段的空间使用情况，但是显示结果中还有段上的所有表和索引信息，对于一个建有很多表的段来说，这是很恐怖的。想直接看到所关心的段的空间使用情况，请高手指点一下?
答：
/*Author Hobbylu*/
/*Reference from sybase procedures*/
/*2005-07-01*/
create procedure sp_helpsegment_me
@segname varchar(30) = NULL /* segment name */
as
declare @segbit int, /* this is the bit version of the segment # */
@segment int, /* the segment number of the segment */
@free_pages int, /* unused pages in segment */
@factor float, /* conversion factor to convert to MB */
@clr_pages int, /* Space reserved for CLRs */
@total_pages int, /* total allocatable log space */
@used_pages int, /* allocated log space */
@ismixedlog int /* mixed log & data database ? */
if @@trancount = 0
begin
set chained off
end
set transaction isolation level 1
set nocount on
/*
** If no segment name given, get 'em all.
*/
if @segname is null
begin
--Adaptive Server has expanded all '*' elements in the following statement
select syssegments.segment, syssegments.name, syssegments.status
from syssegments order by segment
return (0)
end
/*
** Make sure the segment exists
*/
if not exists (select *
from syssegments
where name = @segname)
begin
/* 17520, "There is no such segment as '%1!'." */
raiserror 17520, @segname
return (1)
end
/*
** Show the syssegment entry, then the fragments and size it is on,
** then any dependent objects in the database.
*/
--Adaptive Server has expanded all '*' elements in the following statement
select syssegments.segment, syssegments.name, syssegments.status
from syssegments
where name = @segname
/*
** Set the bit position for the segment.
*/
select @segment = segment
from syssegments
where name = @segname
/*
** Now set the segments on @devname sysusages.
*/
if (@segment < 31)
select @segbit = power(2, @segment)
else
/*
** Since this is segment 31, power(2, 31) will overflow
** since segmap is an int. We'll grab the machine-dependent
** bit mask from spt_values to set the right bit.
*/
select @segbit = low
from master.dbo.spt_values
where type = "E"
and number = 2
/*
** Get factor for conversion of pages to megabytes from spt_values
*/
select @factor = convert(float, low) / 1048576.0
from master.dbo.spt_values
where number = 1 and type = "E"
select @total_pages = sum(u.size)
from master.dbo.sysusages u
where u.segmap & @segbit = @segbit
and u.dbid = db_id()
select @ismixedlog = status2 & 32768
from master.dbo.sysdatabases where dbid = db_id()
/*
** Select the sizes of the segments
*/
if (@segbit = 4)
begin
select device = d.name,
size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB"
from master.dbo.sysusages u, master.dbo.sysdevices d
where u.segmap & @segbit = @segbit
and u.dbid = db_id()
and d.status & 2 = 2
and u.vstart between d.low and d.high
group by d.name order by d.name
select @clr_pages = lct_admin("reserved_for_rollbacks", db_id())
select @free_pages = lct_admin("logsegment_freepages", db_id())
- @clr_pages
select free_pages = @free_pages
if(@ismixedlog = 32768)
begin
/*
** For a mixed log and data database, we cannot
** deduce the log used space from the total space
** as it is mixed with data. So we take the expensive
** way by scanning syslogs.
*/
select @used_pages = lct_admin("num_logpages", db_id())
/* Account allocation pages as used pages */
select @used_pages = @used_pages + (@total_pages / 256)
end
else
begin
/* Dedicated log database */
select @used_pages = @total_pages - @free_pages
- @clr_pages
end
end
else
begin
select device = d.name,
size = convert(varchar(20), round((sum(u.size) * @factor), 0)) + "MB",
free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
from master.dbo.sysusages u, master.dbo.sysdevices d
where u.segmap & @segbit = @segbit
and u.dbid = db_id()
and d.status & 2 = 2
and u.vstart between d.low and d.high
group by d.name order by d.name
select @free_pages = sum(curunreservedpgs(db_id(), u.lstart, u.unreservedpgs))
from master.dbo.sysusages u
where u.segmap & @segbit = @segbit
and u.dbid = db_id()
select @used_pages = @total_pages - @free_pages
select @clr_pages = 0
end
/*
** Select the dependent objects
*/
/*
** Print total_size, total_pages, free_pages, used_pages and reserved_pages
*/
select total_size = convert(varchar(15),
round(@total_pages * @factor, 0)) + "MB",
total_pages = convert(char(15), @total_pages),
free_pages = convert(char(15), @free_pages),
used_pages = convert(char(15), @used_pages),
reserved_pages = convert(char(15), @clr_pages)
return (0
 http://www.sybase.com.cn/gvswse/site/china/content.jsp?_doc_id=1831 |
| 标签: |
作者 hobbylu 评论() | 人气()
| 引用(0) | 推荐 | 保存日志 | 问题日志 | 收藏到网摘 | 返回首页
|
| | |
 | |
