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