oracle 简单语句,常用的oracle基本语句

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中配置的连接