Oracle经典SQL语句

-- 把SELECT出来的结果导到一个文本文件中 SPOOL C:\WEBSITE.TXT; SELECT * FROM OADMIN.WEBSITE ORDER BY ID ASC; SPOOL OFF; -- 查询新建用户 SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN ('TEXT', 'RMAN_USER', 'TEST', 'SCOTT', 'TSMSYS', 'MDDATA', 'DIP', 'DBSNMP', 'SYSMAN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS', 'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA', 'OLAPSYS', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'OUTLN'); -- 查询那些用户，操纵了那些表造成了锁机 SELECT S.USERNAME, DECODE(L.TYPE,'TM','TABLE LOCK', 'TX','ROW LOCK', NULL) LOCK_LEVEL, O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE, S.SID,S.SERIAL#,S.TERMINAL,S.MACHINE,S.PROGRAM,S.OSUSER FROM V$SESSION S,V$LOCK L,ALL_OBJECTS O WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID(+) AND S.USERNAME IS NOT NULL 其中 TM 为表锁定 TX 为行锁定 --看锁阻塞的方法 SELECT (SELECT USERNAME FROM V$SESSION WHERE SID=A.SID) BLOCKER, A.SID, 'IS BLOCKING', (SELECT USERNAME FROM V$SESSION WHERE SID=B.SID) BLOCKEE, B.SID FROM V$LOCK A, V$LOCK B WHERE A.BLOCK = 1 AND B.REQUEST > 0 AND A.ID1 = B.ID1 AND A.ID2 = B.ID2 --ORACLE获得系统信息 SELECT SYS_CONTEXT('USERENV','TERMINAL') TERMINAL, SYS_CONTEXT('USERENV','LANGUAGE') LANGUAGE, SYS_CONTEXT('USERENV','SESSIONID') SESSIONID, SYS_CONTEXT('USERENV','INSTANCE') INSTANCE, SYS_CONTEXT('USERENV','ENTRYID') ENTRYID, SYS_CONTEXT('USERENV','ISDBA') ISDBA, SYS_CONTEXT('USERENV','NLS_TERRITORY') NLS_TERRITORY, SYS_CONTEXT('USERENV','NLS_CURRENCY') NLS_CURRENCY, SYS_CONTEXT('USERENV','NLS_CALENDAR') NLS_CALENDAR, SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') NLS_DATE_FORMAT, SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE, SYS_CONTEXT('USERENV','NLS_SORT') NLS_SORT, SYS_CONTEXT('USERENV','CURRENT_USER') CURRENT_USER, SYS_CONTEXT('USERENV','CURRENT_USERID') CURRENT_USERID, SYS_CONTEXT('USERENV','SESSION_USER') SESSION_USER, SYS_CONTEXT('USERENV','SESSION_USERID') SESSION_USERID, SYS_CONTEXT('USERENV','PROXY_USER') PROXY_USER, SYS_CONTEXT('USERENV','PROXY_USERID') PROXY_USERID, SYS_CONTEXT('USERENV','DB_DOMAIN') DB_DOMAIN, SYS_CONTEXT('USERENV','DB_NAME') DB_NAME, SYS_CONTEXT('USERENV','HOST') HOST, SYS_CONTEXT('USERENV','OS_USER') OS_USER, SYS_CONTEXT('USERENV','EXTERNAL_NAME') EXTERNAL_NAME, SYS_CONTEXT('USERENV','IP_ADDRESS') IP_ADDRESS, SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') NETWORK_PROTOCOL, SYS_CONTEXT('USERENV','BG_JOB_ID') BG_JOB_ID, SYS_CONTEXT('USERENV','FG_JOB_ID') FG_JOB_ID, SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') AUTHENTICATION_TYPE, SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') AUTHENTICATION_DATA FROM DUAL