【记各类高级sql使用记录】

Oracle归档日志查看

SELECT
	A . NAME AS "日志存储位置",
	A .SPACE_LIMIT / 1024 / 1024 / 1024 AS "最大可存储空间(GB)",
	CAST (
		A .SPACE_USED / 1024 / 1024 AS DECIMAL (20, 2)
	) AS "已使用空间(MB)"
FROM
	(
		SELECT
			*
		FROM
			v$recovery_file_dest
	) A

Oracle查看正在运行的Sql

select sl.INST_ID,
       x.sid,
       x.USERNAME,
       x.SQL_ID,
       X.EVENT,
       x.session_time "会话小时",
       x.etime || 's' "etime",
       decode(TRUNC(sl.sofar / sl.totalwork * 100, 2),
              null,
              null,
              '****' || TRUNC(sl.sofar / sl.totalwork * 100, 2) || '%') "事物进度",
       decode(ROUND(sl.elapsed_seconds * (sl.totalwork - sl.sofar) /
                    sl.sofar),
              null,
              null,
              ROUND(sl.elapsed_seconds * (sl.totalwork - sl.sofar) /
                    sl.sofar) || 's') "剩余时间",
       o.object_name       "被锁的对象",
       avg_cputime         "平均cpu时间",
       avg_buffer          "平均逻辑读",
       avg_disk            "平均物理读",
       avg_sorts           "平均排序",
       EXECUTIONS          "执行次数",
       SQL_TEXT,
       sl.OPNAME             "实时操作",
       sl.TARGET             "操作对象",
       文件,
       块,
       更新的行,
       DISK_READS,
       BUFFER_GETS,
       CPU_TIME,
       LAST_LOAD_TIME       "开始时间",
       LAST_ACTIVE_TIME     "最近活动时间",
       LOGON_TIME            "登录时间",
       MACHINE,
       PROGRAM,
       MODULE
  from (SELECT B.SID sid,
               B.USERNAME USERNAME,
               A.SQL_ID SQL_ID,
               B.EVENT,
               ROUND(B.LAST_CALL_ET / 3600) session_time,
               TRUNC(((A.ELAPSED_TIME / DECODE(EXECUTIONS, 0, 1, EXECUTIONS)) /
                     1000000),
                     2) etime,
               TRUNC((CPU_TIME / DECODE(EXECUTIONS, 0, 1, EXECUTIONS)) /
                     1000000) avg_cputime,
               ROUND(A.BUFFER_GETS /
                     DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) avg_buffer,
               ROUND(A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS)) avg_disk,
               ROUND(A.SORTS / decode(a.executions, 0, 1, a.executions)) avg_sorts,
               A.EXECUTIONS EXECUTIONS,
               A.SQL_TEXT,
               A.DISK_READS,
               A.BUFFER_GETS,
               A.CPU_TIME,
               A.LAST_LOAD_TIME,
               A.LAST_ACTIVE_TIME,
               B.LOGON_TIME,
               B.MACHINE,
               B.PROGRAM,
               A.MODULE,
               b.ROW_WAIT_FILE# "文件",
               b.ROW_WAIT_BLOCK# "块",
               b.ROW_WAIT_ROW# "更新的行"
          FROM gV$SQLAREA A, gV$SESSION B
         WHERE EXECUTIONS >= 0
           AND B.STATUS = 'ACTIVE'
           AND A.HASH_VALUE = B.SQL_HASH_VALUE
           AND A.SQL_ID = B.SQL_ID
         ORDER BY avg_cputime   DESC,
                  A.BUFFER_GETS DESC,
                  A.EXECUTIONS  DESC,
                  A.SQL_ID) x
  left join gv$session_longops sl
    on x.sid = sl.sid
   and sl.sofar != sl.totalwork
  left join v$locked_object lo
    on x.SID = lo.session_id
  left join dba_objects o
on o.object_id = lo.object_id;

Oracle 杀死卡住的sql

SELECT b.sid oracleID,
       b.username 所属用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       b.machine 计算机名称
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value; 


alter system kill session 'sid,serial#';--杀死进程

Oracle ORA-02290: 违反检查约束条件

SELECT * from USER_CONS_COLUMNS WHERE TABLE_NAME=TBNAME;--查询所有约束 
ALTER TABLE ZGSM_MAIL DROP CONSTRAINT SYS_C0012948--删除对应约束