常用的oracle基本语句

最新推荐文章于 2024-10-16 11:14:53 发布

cixin2014

最新推荐文章于 2024-10-16 11:14:53 发布

阅读量72

点赞数

文章标签：数据库

1.--查看正在执行的SQL和SESSION
select SID, SERIAL#,LOGON_TIME,MACHINE,USERNAME,STATUS, terminal,OSUSER, program, action, V.*
from v$session V
WHERE USERNAME = 'ECC_OMS'
AND TERMINAL = 'TAOITBB05320'
2.--杀死正在执行的SESSION
--ALTER SYSTEM KILL SESSION 'SID,SERIAL#'
alter system kill session '616,26803' --615 11160

3.查询正在执行的sql
SELECT osuser, username, sql_text,b.* from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;

4:如何定位重要(消耗资源多)的SQL

select sql_text
from v$sql
where disk_reads > 1000 or (executions > 0 and buffer_gets/executions > 30000);

5.删除重复记录

DELETE FROM ECC_CUSTOMER.CUSTOMER_MANAGER_INFO B
--SELECT * FROM ECC_CUSTOMER.CUSTOMER_MANAGER_INFO B
WHERE ROWID NOT IN(
SELECT MAX(A.ROWID) FROM ECC_CUSTOMER.CUSTOMER_MANAGER_INFO A
WHERE A.CUSTOMER_ID=B.CUSTOMER_ID AND A.REGISTER_ID=B.REGISTER_ID

)

.6.创建DBLINK

create PUBLIC database link OMSTEST--连接名称
connect to oms_read identified by oms_read--登录数据库的用户名，和密码
using 'OMSTEST';-- --oracle文件tnsnames.ora中配置的连接

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24778843/viewspace-688207/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24778843/viewspace-688207/