投稿
  1. 首页
  2. 软件运维

如何取的SYBASE中正在执行的SQL语句

群发邮件 • 2023-5-26 • 软件运维 • 阅读 16

如何取的SYBASE中正在执行的SQL语句,第1张

经过观察发现，总是有一个进程会长时间的占用日志空间，无法释放，只能杀掉该进程，为了找到该进程是哪个客户端程序，故整理如下方法来获取该进程正在执行的SQL语句。

通过返回的SQL语句，定位了故障程序，解决了一个大bug

第一步，打开sybase central，在左边视图中，双击数据库服务器名称，然后双击“登录”文件夹，在右边的视图中选择“增加登录”。新建一个“登录”扒陪，输入登录名：ts，设置口令，然后单击下一步，直到完成。

第二步，在新建好的登录名上单击鼠标右键，选择“属性”选项。

第三步，在属性窗口中选择“角色”页，点击“增加角色”按钮。

第四步，在d出窗口中选中sa_role和sybase_ts_role两个角色（按住ctrl键来多选），点击“确定”按钮保存设置。塌悔

第六步，输入语句select * from syslogshold，如果有记录返回，则记住第三个字段（spid）的值。

第七步，输入SQL语句：dbcc traceon(3604)dbcc sqltext(上一步中第三个字段的值)执行，输出结果即为该进程正在执行的SQL语句团此正。

根据SQL Anywhere User s Guide所作小含侍结 绝大部分都可用直接于Sybase数据库

 

SELECT语句

 

SELECT *

FROM employee

 

SELECT *

FROM employee

ORDER BY emp_lname ASC

 

SELECT *

FROM employee

ORDER BY emp_lname DESC

 

SELECT emp_lname dept_id birth_date

FROM employee

 

SELECT *

FROM employee

WHERE emp_fname= John

(一定使用单引号)

 

SELECT emp_fname emp_lname birth_date

FROM employee

WHERE emp_fname = John

ORDER BY birth_date

 

SELECT emp_lname birth_date

FROM employee

　谨握　WHERE birth_date <March

(= <><= >= <>谈晌吵 加上AND与OR)

 

SELECT emp_lname emp_fname

FROM employee

WHERE emp_lname LIKE br%

(% _)

 

SELECT emp_lname emp_fname

FROM employee

WHERE SOUNDEX( emp_lname ) = SOUNDEX( Brown )

(找出英文中发音相同的记录 中文下用处不大)

 

SELECT emp_lname birth_date

FROM employee

WHERE birth_date BEEENAND

 

SELECT emp_lname emp_id

FROM employee

WHERE emp_lname IN ( yeung bucceri charlton )

 

连接表

 

SELECT *

FROM sales_order employee

WHERE sales_order sales_rep = employee emp_id

 

SELECT E emp_lname S id S order_date

FROM sales_order as S employee as E

WHERE S sales_rep = E emp_id

ORDER BY E emp_lname

 

连接两表的快捷键 KEY JOIN及NATURAL JOIN 最好用WHERE

 

SELECT emp_lname id order_date

FROM sales_order

KEY JOIN employee

(主键与外部键对应的地方 就可以用KEY JOIN)

 

SELECT pany_name

CAST( SUM(sales_order_items quantity * product unit_price) AS INTEGER) AS value

FROM customer

KEY JOIN sales_order

KEY JOIN sales_order_items

KEY JOIN product

GROUP BY pany_name

 

SELECT emp_lname dept_name

FROM employee

NATURAL JOIN department

(找出两表间有相同的字段名 进行连结)

 

集合

 

SELECT count( * )

FROM employee

 

SELECT   count( * )

min( birth_date )

max( birth_date )

FROM employee

(MIN MAX COUNT AVG SUM LIST 作为单独的一列选出)

 

SELECT sales_rep count( * )

FROM sales_order

GROUP BY sales_rep

(在使用GROUP BY时 对于GROUP BY指定的字段 其每一个不同的值都会组成一行)

 

SELECT sales_rep count( * )

FROM  sales_order

KEY JOIN employee

GROUP BY sales_rep

HAVING count( * ) >

 

更新数据库

 

INSERT

INTO department ( dept_id dept_name dept_head_id )

VALUES ( Eastern Sales )

 

INSERT

INTO department

VALUES ( Eastern Sales )

 

UPDATE employee

SET dept_id = manager_id =

WHERE emp_id =

 

DELETE

FROM employee

WHERE termination_date IS NOT NULL

 

DELETE

FROM employee

WHERE LEFT( phone ) = AND manager_id =

视图

 

CREATE VIEW emp_dept AS

SELECT emp_fname emp_lname dept_name

FROM employee

JOIN department ON department dept_id = employee dept_id

 

SELECT *

FROM emp_dept

 

(视图能自动更新状态)

 

DROP VIEW emp_dept

 

CREATE VIEW emp_dept(FirstName LastName Department) AS

SELECT emp_fname emp_lname dept_name

FROM employee JOIN department ON department dept_id = employee dept_id

 

(创建视图不能使用ORDEY BY 但使用视图可以使用)

 

SELECT LastName dept_head_id

FROM emp_dept department

WHERE emp_dept Department = department dept_name

(将视图与其他表进行进一步的连结)

 

视图权限管理

 

GRANT CONNECT TO M_Kelly

IDENTIFIED BY SalesHead

 

CREATE VIEW SalesEmployee AS

SELECT emp_id emp_lname emp_fname

FROM dba employee

WHERE dept_id =

 

GRANT SELECT ON SalesEmployee TO M_Kelly

 

CONNECT USER M_Kelly IDENTIFIED BY SalesHead

SELECT * FROM dba SalesEmployee

 

子查询

 

SELECT *

FROM sales_order_items

WHERE prod_id IN

( SELECT id

FROM product

WHERE quantity <)

ORDER BY ship_date DESC

 

SELECT *

FROM fin_data

WHERE de = ANY (  SELECT de

FROM fin_code

WHERE type = revenue )

（=ANY 相当于IN）

 

SELECT *

FROM fin_data

WHERE de <>ALL (  SELECT de

FROM fin_code

WHERE type = revenue )

(相当于NOT IN)

 

SELECT   sales_order id sales_order order_date

( SELECT pany_name

FROM customer

WHERE customer id = sales_order cust_id )

FROM   sales_order

WHERE order_date >/ /

ORDER BY order_date

(如果其他表只要求产生一个字段 就可以使用子查询来代替连接)

 

SELECT pany_name state

( SELECT MAX( id )

FROM sales_order

WHERE sales_order cust_id = customer id )

FROM customer

WHERE state = WA

 

根据SQL Anywhere User s Guide所作小结 绝大部分都可用直接于Sybase数据库

 

SELECT语句

 

SELECT *

FROM employee

 

SELECT *

FROM employee

ORDER BY emp_lname ASC

 

SELECT *

FROM employee

ORDER BY emp_lname DESC

 

SELECT emp_lname dept_id birth_date

FROM employee

 

SELECT *

FROM employee

WHERE emp_fname= John

(一定使用单引号)

 

SELECT emp_fname emp_lname birth_date

FROM employee

WHERE emp_fname = John

ORDER BY birth_date

 

SELECT emp_lname birth_date

FROM employee

WHERE birth_date <March

(= <><= >= <>加上AND与OR)

 

SELECT emp_lname emp_fname

FROM employee

WHERE emp_lname LIKE br%

(% _)

 

SELECT emp_lname emp_fname

FROM employee

WHERE SOUNDEX( emp_lname ) = SOUNDEX( Brown )

(找出英文中发音相同的记录 中文下用处不大)

 

SELECT emp_lname birth_date

FROM employee

WHERE birth_date BEEENAND

 

SELECT emp_lname emp_id

FROM employee

WHERE emp_lname IN ( yeung bucceri charlton )

连接表

 

SELECT *

FROM sales_order employee

WHERE sales_order sales_rep = employee emp_id

 

SELECT E emp_lname S id S order_date

FROM sales_order as S employee as E

WHERE S sales_rep = E emp_id

ORDER BY E emp_lname

 

连接两表的快捷键 KEY JOIN及NATURAL JOIN 最好用WHERE

 

SELECT emp_lname id order_date

FROM sales_order

KEY JOIN employee

(主键与外部键对应的地方 就可以用KEY JOIN)

 

SELECT pany_name

CAST( SUM(sales_order_items quantity * product unit_price) AS INTEGER) AS value

FROM customer

KEY JOIN sales_order

KEY JOIN sales_order_items

KEY JOIN product

GROUP BY pany_name

 

SELECT emp_lname dept_name

FROM employee

NATURAL JOIN department

(找出两表间有相同的字段名 进行连结)

集合

 

SELECT count( * )

FROM employee

 

SELECT   count( * )

min( birth_date )

max( birth_date )

FROM employee

(MIN MAX COUNT AVG SUM LIST 作为单独的一列选出)

 

SELECT sales_rep count( * )

FROM sales_order

GROUP BY sales_rep

(在使用GROUP BY时 对于GROUP BY指定的字段 其每一个不同的值都会组成一行)

 

SELECT sales_rep count( * )

FROM  sales_order

KEY JOIN employee

GROUP BY sales_rep

HAVING count( * ) >

 

更新数据库

 

INSERT

INTO department ( dept_id dept_name dept_head_id )

VALUES ( Eastern Sales )

 

INSERT

INTO department

VALUES ( Eastern Sales )

 

UPDATE employee

SET dept_id = manager_id =

WHERE emp_id =

 

DELETE

FROM employee

WHERE termination_date IS NOT NULL

 

DELETE

FROM employee

WHERE LEFT( phone ) = AND manager_id =

 

视图

 

CREATE VIEW emp_dept AS

SELECT emp_fname emp_lname dept_name

FROM employee

JOIN department ON department dept_id = employee dept_id

 

SELECT *

FROM emp_dept

 

(视图能自动更新状态)

 

DROP VIEW emp_dept

 

CREATE VIEW emp_dept(FirstName LastName Department) AS

SELECT emp_fname emp_lname dept_name

FROM employee JOIN department ON department dept_id = employee dept_id

 

(创建视图不能使用ORDEY BY 但使用视图可以使用)

 

SELECT LastName dept_head_id

FROM emp_dept department

WHERE emp_dept Department = department dept_name

(将视图与其他表进行进一步的连结)

视图权限管理

 

GRANT CONNECT TO M_Kelly

IDENTIFIED BY SalesHead

 

CREATE VIEW SalesEmployee AS

SELECT emp_id emp_lname emp_fname

FROM dba employee

WHERE dept_id =

 

GRANT SELECT ON SalesEmployee TO M_Kelly

 

CONNECT USER M_Kelly IDENTIFIED BY SalesHead

SELECT * FROM dba SalesEmployee

 

子查询

 

SELECT *

FROM sales_order_items

WHERE prod_id IN

( SELECT id

FROM product

WHERE quantity <)

ORDER BY ship_date DESC

 

SELECT *

FROM fin_data

WHERE de = ANY (  SELECT de

FROM fin_code

WHERE type = revenue )

（=ANY 相当于IN）

 

SELECT *

FROM fin_data

WHERE de <>ALL (  SELECT de

FROM fin_code

WHERE type = revenue )

(相当于NOT IN)

 

SELECT   sales_order id sales_order order_date

( SELECT pany_name

FROM customer

WHERE customer id = sales_order cust_id )

FROM   sales_order

WHERE order_date >/ /

ORDER BY order_date

(如果其他表只要求产生一个字段 就可以使用子查询来代替连接)

 

SELECT pany_name state

( SELECT MAX( id )

FROM sales_order

WHERE sales_order cust_id = customer id )

FROM customer

WHERE state = WA

 

SELECT  pany_name MAX( sales_order id ) state

FROM customer

KEY LEFT OUTER JOIN sales_order

WHERE state = WA

GROUP BY pany_name state

 

系统表

 

SYSCATALOG 查看所有的表

SYSCOLUMNS 查看表的字段属性

FROM customer

KEY LEFT OUTER JOIN sales_order

WHERE state = WA

GROUP BY pany_name state

 

系统表

 

SYSCATALOG 查看所有的表

lishixinzhi/Article/program/Oracle/201311/16984

不能知改没搭纳改小。

只能改大。

可以增加数据设备文件或者扩大已有设备文件的大小。

--

初始化

一个100m的设备test_dev

disk

init

name

='test_dev'',physname='c:\sybase\data\test_dev.dat',size='100m'

或者

--给设备test_dev扩大200m的空间

disk

resize

name='test_dev',size='200m'

为歼斗数据库增加100m的空间

alter

database

test

on

test_dev='100m'

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/yw/12538359.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
群发邮件 群发邮件 一级用户组
0 0
上一篇 2023-05-26
下一篇 2023-05-26

发表评论

登录后才能评论

评论列表（0条）

保存