常用的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中配置的連線