Oracle查询常用SQL

查询所有定时任务

SELECT * FROM ALL_JOBS;

SQL被阻塞查询

SELECT '节点 ' || A.INST_ID || ' SESSION ' || A.SID || ',' || A_S.SERIAL# ||       ' 阻塞了 节点 ' || B.INST_ID || ' SESSION ' || B.SID || ',' || B_S.SERIAL# BLOCKINFO,       A.INST_ID,       A_S.SID,       A_S.SCHEMANAME,       A_S.MODULE,       A_S.STATUS,       A.TYPE LOCK_TYPE,       A.ID1,       A.ID2,       DECODE(A.LMODE,              0,              'NONE',              1,              NULL,              2,              'ROW-S (SS)',              3,              'ROW-X (SX)',              4,              'SHARE (S)',              5,              'S/ROW-X (SSX)',              6,              'EXCLUSIVE (X)') LOCK_MODE,       '后为被阻塞信息' ,       B.INST_ID BLOCKED_INST_ID,       B_S.SID BLOCKED_SID,       B.TYPE BLOCKED_LOCK_TYPE,       DECODE(B.REQUEST,              0,              'NONE',              1,              NULL,              2,              'ROW-S (SS)',              3,              'ROW-X (SX)',              4,              'SHARE (S)',              5,              'S/ROW-X (SSX)',              6,              'EXCLUSIVE (X)') BLOCKED_LOCK_REQUEST,       B_S.SCHEMANAME BLOCKED_SCHEMANAME,       B_S.MODULE BLOCKED_MODULE,       B_S.STATUS BLOCKED_STATUS,       B_S.SQL_ID BLOCKED_SQL_ID,       OBJ.OWNER BLOCKED_OWNER,       OBJ.OBJECT_NAME BLOCKED_OBJECT_NAME,       OBJ.OBJECT_TYPE BLOCKED_OBJECT_TYPE,       CASE          WHEN B_S.ROW_WAIT_OBJ# <> -1 THEN             DBMS_ROWID.ROWID_CREATE(1,                                     OBJ.DATA_OBJECT_ID,                                     B_S.ROW_WAIT_FILE#,                                     B_S.ROW_WAIT_BLOCK#,                                     B_S.ROW_WAIT_ROW#)           ELSE             '-1'           END BLOCKED_ROWID, --THE BLOCKED ROWID       DECODE(OBJ.OBJECT_TYPE,              'TABLE',              'SELECT * FROM ' || OBJ.OWNER || '.' || OBJ.OBJECT_NAME ||              ' WHERE ROWID=''' ||              DBMS_ROWID.ROWID_CREATE(1,                                      OBJ.DATA_OBJECT_ID,                                      B_S.ROW_WAIT_FILE#,                                      B_S.ROW_WAIT_BLOCK#,                                      B_S.ROW_WAIT_ROW#) || '''',              NULL)  BLOCKED_DATA_QUERYSQL  FROM GV$LOCK     A,       GV$LOCK     B,       GV$SESSION  A_S,       GV$SESSION  B_S,       DBA_OBJECTS OBJ WHERE A.ID1 = B.ID1   AND A.ID2 = B.ID2   AND A.BLOCK > 0 --BLOCK THE OTHER SQL   AND B.REQUEST > 0   AND ((A.INST_ID = B.INST_ID AND A.SID <> B.SID) OR       (A.INST_ID <> B.INST_ID))   AND A.SID = A_S.SID   AND A.INST_ID = A_S.INST_ID   AND B.SID = B_S.SID   AND B.INST_ID = B_S.INST_ID   AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+) ORDER BY A.INST_ID,A.SID;

表的所有外键约束

SELECT /*+RULE*/ D.CONSTRAINT_NAME PK_NAME,                 D.TABLE_NAME                  || '.'                  || D.COLUMN_NAME  PK_COLUMN,                 A.CONSTRAINT_TYPE,                 B.CONSTRAINT_NAME FK_NAME,                 B.TABLE_NAME                  || '.'                  || B.COLUMN_NAME  FK_COLUMNFROM   DBA_CONSTRAINTS A        JOIN DBA_CONS_COLUMNS B          ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME            AND A.OWNER = B.OWNER       JOIN DBA_CONSTRAINTS C          ON A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME            AND A.R_OWNER = C.OWNER       JOIN DBA_CONS_COLUMNS D          ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME            AND C.OWNER = D.OWNERWHERE  D.TABLE_NAME = 'INV_LOCATION_PALLETS'

或者

SELECT * FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='PK_INV_LOCATION_PALLETS'

某表的触发器查询

SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='INV_LOCATION_PALLETS'

其它推荐：
Delete the data on the table very slow(删除数据慢)
http://www.anbob.com/archives/1962.html/comment-page-1