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,'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,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_COLUMN

FROM   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.OWNER

WHERE  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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!