sybase ase常用查询

admin

admin

1. select getdate()
   
2. go

复制代码

admin

1. select @@version
   
2. go

复制代码

admin

1. select @@servername,@@maxpagesize
   
2. go

复制代码

admin

1. select
   
2.         (select c3.name
   
3.         from master..sysconfigures c1,master..syscurconfigs c2,master..syscharsets c3
   
4.         where c1.config=c2.config
   
5.                 and  c2.value=c3.id and c3.csid=0
   
6.                 and c1.name= 'default character set id'
   
7.         ) as cs_name,
   
8.         (select cs.name
   
9.         from master..syscharsets cs
   
10.         where cs.id=cc.value and cs.csid=
    
11.                 (select t1.value
    
12.                         from master..syscurconfigs t1,master..sysconfigures t2
    
13.                         where t1.config=t2.config and t2.name='default character set id'
    
14.                 )
    
15.         ) as sort_name
    
16. from master..syscurconfigs cc, master..sysconfigures c
    
17. where cc.config=c.config and c.name in ('default sortorder id')
    
18. go

复制代码

admin

1. select @@boottime
   
2. go

复制代码

admin

1. select @@errorlog
   
2. go

复制代码

admin

1. select convert(char(16),db_name(data_segment.dbid)) DBName
   
2. ,str(round(total_data_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Total Data(MB)"
   
3. ,str(round(free_data_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Free Data(MB)"
   
4. ,str(round(total_log_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Total Log(MB)"
   
5. ,str(round(free_log_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Free Log(MB)"
   
6. ,str( round(100.0 * free_data_pages / total_data_pages ,2),10,2) "Free_Data%"
   
7. ,str( round(100.0 * free_log_pages / total_log_pages,2),10,2) "Free_Log%"
   
8. from
   
9. (select dbid,sum(size) total_log_pages,lct_admin("logsegment_freepages", dbid ) free_log_pages
   
10.   from master.dbo.sysusages
    
11.     where segmap & 4 = 4
    
12.     group by dbid
    
13. ) log_segment
    
14. ,
    
15. (select dbid,sum(size) total_data_pages ,sum(curunreservedpgs(dbid, lstart, unreservedpgs)) free_data_pages
    
16.   from master.dbo.sysusages
    
17.     where segmap <> 4
    
18.     group by dbid
    
19. ) data_segment
    
20. where data_segment.dbid = log_segment.dbid
    
21.   --and data_segment.dbid > 3 and data_segment.dbid < 31513
    
22. order by data_segment.dbid
    
23. go

复制代码

admin

1. select dbid, db_name(dbid) as DBName, sum(case when (segmap <> 4) then size else 0 end) as total_data_pages
   
2. ,sum(case when  (segmap <> 4) then curunreservedpgs(dbid, lstart, unreservedpgs) else 0 end) as free_data_pages
   
3. ,sum(case when (segmap & 4 = 4) then size else 0 end) as total_log_pages
   
4. ,lct_admin("logsegment_freepages", dbid ) as free_log_pages
   
5. into #dbspace_usages
   
6. from master..sysusages u
   
7. group by dbid order by dbid
   
8. 
   
9. select  convert(char(16),DBName) DBName
   
10. ,str(round(total_data_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Total Data(MB)"
    
11. ,str(round(free_data_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Free Data(MB)"
    
12. ,str(round(total_log_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Total Log(MB)"
    
13. ,str(round(free_log_pages / ((1024.0 * 1024) / @@maxpagesize),2),10,2) "Free Log(MB)"
    
14. ,str( round(100.0 * free_data_pages / total_data_pages ,2),10,2) "Free_Data%"
    
15. ,str( round(100.0 * free_log_pages / total_log_pages,2),10,2) "Free_Log%"
    
16. from #dbspace_usages
    
17. 
    
18. drop table #dbspace_usages
    
19. go

复制代码

admin

1. select convert(varchar(16),name) as name,convert(varchar(50),phyname) as phyname,
   
2. str((high-low+1)/1024.0/1024.0*@@pagesize,10,2) TOTAL ,
   
3. isnull(str(sum(size)/1024.0/1024.0*@@maxpagesize,10,2),'0') USED,
   
4. isnull(str((high-low+1)/1024.0/1024.0*@@pagesize - sum(size)/1024.0/1024.0*@@maxpagesize,10,2),str((high-low+1)/1024.0/1024.0*@@pagesize,10,2)) FREE,
   
5. isnull(str(convert(decimal(20,2),(high-low+1)/1024.0/1024.0*@@pagesize - sum(size)/1024.0/1024.0*@@maxpagesize) * 100.0 / convert(decimal(20,2),(high-low+1)/1024.0/1024.0*@@pagesize) ,10,2),'100.00') as FreePCT
   
6. from master.dbo.sysdevices d left outer join master.dbo.sysusages u on d.vdevno=u.vdevno
   
7. where cntrltype=0
   
8. group by d.vdevno,name
   
9. --having (high-low+1)>isnull(sum(size)*1.0/@@pagesize*@@maxpagesize,0)
   
10. go

复制代码

admin

1. declare @devname varchar(30)
   
2. declare @numpgsmb float
   
3. declare @numpgsmb2 float
   
4. 
   
5. set nocount on
   
6. 
   
7. select @numpgsmb = (1048576. / @@pagesize)
   
8. select @numpgsmb2 = (1048576. / @@maxpagesize)
   
9. select @devname='%'
   
10. 
    
11. /* total size of device */
    
12. select d.name,
    
13.     totalsizeMB = (1. + (d.high - d.low)) / @numpgsmb
    
14.   into #totalsize
    
15.     from master.dbo.sysdevices d
    
16.         where d.status & 2 = 2
    
17.             and name like @devname
    
18.         and status &  2 = 2
    
19.         group by d.name
    
20. 
    
21. /* Calculate used size in MB */
    
22. select d.name,
    
23.     usedsizeMB = isnull(sum(u.size) / @numpgsmb2,0)
    
24.   into #usedsize               
    
25.     from master.dbo.sysdevices d, master.dbo.sysusages u
    
26.         where u.vstart >= d.low and u.vstart <= d.high                          
    
27.             and d.status & 2 = 2
    
28.             and d.name like @devname
    
29.         and status &  2 = 2
    
30.         group by d.name
    
31. union
    
32. select d.name, 0.
    
33. from master.dbo.sysdevices d
    
34.   where not exists ( select 1 from master.dbo.sysusages u where u.vstart >= d.low and u.vstart <= d.high )
    
35.       and d.status & 2 = 2
    
36.       and d.name like @devname
    
37. 
    
38. /* Calculate the free size of device */
    
39. select name = convert(varchar(30),d.name) ,phyname = convert(varchar(50),d.phyname)
    
40. ,TotalSize = str(#totalsize.totalsizeMB,10,2)
    
41. ,UsedSize = str(#usedsize.usedsizeMB,10,2)
    
42. ,FreeSize = str(#totalsize.totalsizeMB - #usedsize.usedsizeMB,10,2)
    
43. ,FreePCT = convert(decimal(19,2), (#totalsize.totalsizeMB - #usedsize.usedsizeMB) * 100.0 / #totalsize.totalsizeMB )
    
44.   from master.dbo.sysdevices d, #totalsize, #usedsize
    
45.   where    d.name = #totalsize.name
    
46.     and #totalsize.name = #usedsize.name
    
47.   order by low,high
    
48. 
    
49. drop table #totalsize
    
50. drop table #usedsize
    
51. go

复制代码

admin

1. select 1 as id,'max memory' as configname
   
2. into #configure_parameters
   
3. union all select 2,'procedure cache size'
   
4. union all select 3,'max online engines'
   
5. union all select 4,'number of user connections'
   
6. union all select 5,'number of open objects'
   
7. union all select 6,'number of open indexes'
   
8. union all select 7,'number of open partitions'
   
9. union all select 8,'number of locks'
   
10. union all select 9,'number of open databases'
    
11. go
    
12. select 1 as id, 105 as confignum, null as counter1, null as counter2, "open_database_reuse_requests" as counter3, null as counter4, 0 as is_perprocess, 1 as multiplier
    
13. into #resource_monitor_tbl
    
14. union all select 2,107,null,null,"open_object_reuse_requests",null,0,1
    
15. union all select 3,146,"active_procedure_cache","hwm_procedure_cache","procedure_cache_reuse_requests",null,0,1
    
16. union all select 4,263,null,null,"open_index_reuse_requests",null,0,1
    
17. union all select 5,266,null,null,null,null,0,1
    
18. union all select 6,301,"active_dskbufs","hwm_dskbufs",null,null,0,1
    
19. union all select 7,185,null,null,null,null,1,0
    
20. union all select 8,347,null,null,"dtxp_reuse_reqs",null,0,1
    
21. union all select 9,103,"active_connections","hwm_connections",null,null,0,1
    
22. union all select 10,267,"active_worker_process","hwm_worker_process",null,null,0,1
    
23. union all select 11,242,null,null,null,null,0,1
    
24. union all select 12,116,"active_devices","hwm_devices",null,null,0,1
    
25. union all select 13,355,null,null,null,null,0,1
    
26. union all select 14,340,null,null,null,null,0,1
    
27. union all select 15,341,null,null,null,null,0,1
    
28. union all select 16,331,null,null,null,null,0,1
    
29. union all select 17,395,null,null,null,null,0,1
    
30. union all select 18,120,"active_remote_connections","hwm_remote_connections",null,null,0,1
    
31. union all select 19,118,"active_remote_logins","hwm_remote_logins",null,null,0,1
    
32. union all select 20,119,"active_remote_sites","hwm_remote_sites",null,null,0,1
    
33. union all select 21,136,null,null,null,null,0,1
    
34. union all select 22,186,"active_perm_cache_entries","hwm_perm_cache_entries","perm_cache_entries_reuse_requests",null,1,1
    
35. union all select 23,137,null,null,null,null,0,1
    
36. union all select 24,171,null,null,null,null,0,1
    
37. union all select 25,172,null,null,null,null,0,1
    
38. union all select 26,181,"active_sort_buffers","hwm_sort_buffers",null,null,0,1
    
39. union all select 27,399,null,null,"heap_mem_waits",null,1,1
    
40. union all select 28,396,"hwm_maxmem","hwm_maxmem",null,null,0,1
    
41. union all select 29,106,null,null,null,null,0,1
    
42. union all select 30,173,null,null,null,null,0,1
    
43. union all select 31,277,null,null,null,null,0,1
    
44. union all select 32,268,null,null,null,null,2,1
    
45. union all select 33,126,"active_online_engines","hwm_online_engines",null,null,0,1
    
46. union all select 34,156,"active_network_listeners","hwm_network_listeners",null,null,0,1
    
47. union all select 35,150,null,null,null,null,0,1
    
48. union all select 36,408,null,null,"open_partition_reuse_requests",null,0,1
    
49. go
    
50. select convert(varchar(30),c.name) as name
    
51. ,cc.value as config_value
    
52. ,case
    
53.     when config_admin(22, confignum, 2, 0, counter1, NULL) < 0 or (cc.value-config_admin(22, confignum, 2, 0, counter1, NULL)) < 0 then cc.value
    
54.     else    cc.value-config_admin(22, confignum, 2, 0, counter1, NULL)
    
55. end as num_free
    
56. ,case
    
57.     when config_admin(22, confignum, 2, 0, counter1, NULL) < 0 then 0
    
58.     else config_admin(22, confignum, 2, 0, counter1, NULL)
    
59. end as num_active
    
60. , case
    
61.     when config_admin(22, confignum, 3, 0, counter2, NULL)  < 0 then 0
    
62.     else config_admin(22, confignum, 3, 0, counter2, NULL)
    
63. end as max_active
    
64. ,(case
    
65.     when cc.value=0 then 0.00
    
66.     else convert(decimal(10,2), (case when config_admin(22, confignum, 2, 0, counter1, null) < 0 then 0 else config_admin(22, confignum, 2, 0, counter1, null) end) * 100.0 / cc.value)
    
67. end) as Pct_act
    
68. from  #resource_monitor_tbl a, master..sysconfigures c,master..syscurconfigs cc ,#configure_parameters t
    
69. where c.config=a.confignum and c.config=cc.config  and c.name=t.configname
    
70. go

复制代码