常用SQL語句3

25、查询有哪些数据库实例在运行:

select inst_name from v$active_instances;

26、以DBA角色, 查看当前数据库里锁的情况：

SELECT OBJECT_ID,SESSION_ID,LOCKED_MODE FROM V$LOCKED_OBJECT;
SELECT T2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIME FROM V$LOCKED_OBJECT T1,V$SESSION T2 WHERE T1.SESSION_ID=T2.SID ORDER BY T2.LOGON_TIME;

27、查看表是否是分区表：
SELECT TABLE_NAME,PARTITIONED FROM USER_TABLES WHERE TABLE_NAME='LOCALUSAGE';
TABLE_NAME                     PAR
------------------------------ ---           ---------
LOCALUSAGE                     YES

28、查看分区表的分区名和相应的表空间名：
SELECT TABLE_NAME, PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME LIKE '%USAGE%';

29、查看索引是否是分区索引：
SELECT INDEX_NAME, TABLE_NAME, STATUS, PARTITIONED FROM USER_INDEXES WHERE TABLE_NAME LIKE '%USAGE';

30、如果返回的PATITIONED为YES，请再执行如下语句来查询分区索引的类型：
SELECT INDEX_NAME,TABLE_NAME,LOCALITY FROM USER_PART_INDEXES;

31、Dual是Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select中.
查看系统时间：

SELECT TO_CHAR(SYSDATE,'YY-MM-DD HH24:MI:SS') SHIJIAN FROM DUAL;

32、查看索引段中extent的数量：
SELECT SEGMENT_NAME,COUNT(*) FROM DBA_EXTENTS
WHERE SEGMENT_TYPE='INDEX' AND OWNER='SCOTT' GROUP BY SEGMENT_NAME;

33、查看系统表中的用户索引（用来检查在system表空间内其他用户索引的存在）：
SQL>SELECT COUNT(*) FROM DBA_INDEXES WHERE TABLESPACE_NAME='SYSTEM' AND OWNER NOT IN('SYS','SYSTEM');

34、查看wacos表空间内的索引的扩展情况：
SELECT SUBSTR(SEGMENT_NAME,1,20) "SEGMENT NAME",BYTES, COUNT(BYTES)
FROM DBA_EXTENTS WHERE SEGMENT_NAME IN( SELECT INDEX_NAME FROM DBA_INDEXES
WHERE TABLESPACE_NAME = 'WACOS') GROUP BY SEGMENT_NAME,BYTES ORDER BY SEGMENT_NAME;

35、查看表空间数据文件的读写性能:
SQL>SELECT NAME,PHYRDS,PHYWRTS,AVGIOTIM,MINIOTIM,MAXIOWTM,MAXIORTM FROM V$FILESTAT,V$DATAFILE WHERE V$FILESTAT.FILE#=V$DATAFILE.FILE#;

SQL>SELECT FS.NAME NAME,F.PHYRDS,F.PHYBLKRD,F.PHYWRTS,F.PHYBLKWRT ,F.READTIM,F.WRITETIM
FROM V$FILESTAT F, V$DATAFILE FS WHERE F.FILE# = FS.FILE# ORDER BY FS.NAME;
(注意：如果phyblkrd与phyrds很接近的话，则表明这个表空间中存在全表扫描的表，这些表需要调整索引或优化SQL语句)

36、转换表空间为local方式管理:
SQL> EXEC SYS.DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TBS_TEST') ;

37、查看一下哪个用户在用临时段:

SELECT USERNAME,SID,SERIAL#,SQL_ADDRESS,MACHINE,PROGRAM,TABLESPACE,SEGTYPE,
CONTENTS FROM V$SESSION SE,V$SORT_USAGE SU WHERE SE.SADDR=SU.SESSION_ADDR;     

38、查看占io较大的正在运行的session:
SELECT SE.SID,SE.SERIAL#,PR.SPID,SE.USERNAME,SE.STATUS,SE.TERMINAL,SE.PROGRAM, SE.MODULE,SE.SQL_ADDRESS,ST.EVENT,ST.P1TEXT,SI.PHYSICAL_READS,SI.BLOCK_CHANGES FROM V$SESSION SE,V$SESSION_WAIT ST,V$SESS_IO SI,V$PROCESS PR WHERE ST.SID=SE.SID AND ST.SID=SI.SID AND SE.PADDR=PR.ADDR AND SE.SID>6 AND ST.WAIT_TIME=0 AND ST.EVENT NOT LIKE '%SQL%' ORDER BY PHYSICAL_READS DESC;

39、查找前十条性能差的sql:
SELECT * FROM(SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,SQL_TEXT FROM V$SQLAREA ORDER BY DISK_READS DESC) WHERE ROWNUM<10;

40、删除用户下所有表的语句:
SELECT 'DROP TABLE '||TABLE_NAME||' CASCADE CONSTRAINTS;' FROM USER_TABLES;

41、查看LOCK，并杀掉会话：
SET LINESIZE 132 PAGESIZE 66
BREAK ON KILL ON USERNAME ON TERMINAL
COLUMN KILL HEADING 'KILL STRING' FORMAT A13
COLUMN RES HEADING 'RESOURCE TYPE' FORMAT 999
COLUMN ID1 FORMAT 9999990
COLUMN ID2 FORMAT 9999990
COLUMN LMODE BEADING 'LOCK HELD' FORMAT A20
COLUMN REQUEST HEADING 'LOCK REQUESTED' FORMAT A20
COLUMN SERIAL# FORMAT 99999
COLUMN USERNAME FORMAT A10 HEADING "USERNAME"
COLUMN TERMINAL HEADING TERM FORMAT A6
COLUMN TAB FORMAT A35 HEADING "TABLE NAME"
COLUMN OWNER FORMAT A9
COLUMN ADDRESS FORMAT A18

SELECT NVL(S.USERNAME, 'INTERNAL') USERNAME,
       NVL(S.TERMINAL, 'NONE') TERMINAL,
       L.SID || ',' || S.SERIAL# KILL,
       U1.NAME || ',' || SUBSTR(T1.NAME, 1, 20) TAB,
       DECODE(L.LMODE,
              1,'NO LOCK',
              2,'ROW SHARE',
              3,'ROW EXCLUSIVE',
              4,'SHARE',
              5,'SHARE ROW EXCLUSIVE',
              6,'EXCLUSIVE',
              NULL) LMODE,
       DECODE(L.REQUEST,
              1,'NO LOCK',
              2,'ROW SHARE',
              3,'ROW EXCLUSIVE',
              4,'SHARE',
              5,'SHARE ROW EXCLUSIVE',
              6,'EXCLUSIVE',
              NULL) REQUEST
 FROM V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1
 WHERE L.SID = S.SID
   AND T1.OBJ# = DECODE(L.ID2, 0, L.ID1, L.ID2)
   AND U1.USER# = T1.OWNER#
   AND S.TYPE != 'BACKGROUND'
 ORDER BY 1, 2, 5;

--ALTER SYSTEM KILL SESSION ' , ';
COLUMN USERNAME FORMAT A15
COLUMN SID      FORMAT 9990 HEADING SID
COLUMN TYPE     FORMAT A4
COLUMN LMODE    FORMAT 990 HEADING 'HELD'
COLUMN REQUEST FORMAT 990 HEADING 'REQ'
COLUMN ID1      FORMAT 9999990
COLUMN ID2     FORMAT 9999990
BREAK ON ID1 SKIP 1 DUP

SPOOL TFSLCKWT.LST
SELECT SN.USERNAME,M.SID,M.TYPE,
       DECODE(M.LMODE,
              0,'NONE',
              1,'NULL',
              2,'ROW SHARE',
              3,'ROW EXCL.',
              4,'SHARE',
              5,'S/ROW EXCL.',
              6,'EXCLUSIVE',
              LMODE,
              LTRIM(TO_CHAR(LMODE, '990'))) LMODE,
       DECODE(M.REQUEST,
              0,'NONE',
              1,'NULL',
              2,'ROW SHARE',
              3,'ROW EXCL.',
              4,'SHARE',
              5,'S/ROW EXCL.',
              6,'EXCLUSIVE',
              REQUEST,LTRIM(TO_CHAR(M.REQUEST, '990'))) REQUEST,
       M.ID1,M.ID2
 FROM V$SESSION SN, V$LOCK M
 WHERE (SN.SID = M.SID AND M.REQUEST != 0)
    OR (SN.SID = M.SID AND M.REQUEST = 0 AND LMODE != 4 AND
       (ID1, ID2) IN (SELECT S.ID1, S.ID2
                         FROM V$LOCK S
                        WHERE REQUEST != 0
                          AND S.ID1 = M.ID1 AND S.ID2 = M.ID2))
 ORDER BY ID1, ID2, M.REQUEST;
SPOOL OFF
CLEAR BREAKS

42.查看WACOS表空间下所有的索引:
SQL> SELECT 'ANALYZE INDEX '||SEGMENT_NAME||' VALIDATE STRUCTURE;' FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='WACOS'AND SEGMENT_TYPE='INDEX';

43. 怎样识别IO竞争和负载平衡:
COL 文件名 FORMAT A35

SELECT
DF.NAME 文件名,
FS.PHYRDS 读次数,
FS.PHYWRTS 写次数,
(FS.READTIM/DECODE(FS.PHYRDS,0,-1,FS.PHYRDS)) ,
(FS.WRITETIM/DECODE(FS.PHYWRTS,0,-1,FS.PHYWRTS))
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE DF.FILE#=FS.FILE# ORDER BY DF.NAME
/
文件名                                                                                                         读次数               写次数       读时间     写时间-------------------------------------------- ---------- ---------- ---------- ----------
C:\ORACLE\ORADATA\ORADB\DR01.DBF                   885        883          0          0
C:\ORACLE\ORADATA\ORADB\INDX01.DBF                 885        883          0          0
C:\ORACLE\ORADATA\ORADB\OEM_REPOSITORY.ORA         885        883          0          0
C:\ORACLE\ORADATA\ORADB\RBS01.DBF                  925      22306          0          0
C:\ORACLE\ORADATA\ORADB YSTEM01.DBF             50804     155025          0          0
C:\ORACLE\ORADATA\ORADB\TEMP01.DBF                 887        894          0          0
C:\ORACLE\ORADATA\ORADB\TOOLS01.DBF                886        892          0          0
C:\ORACLE\ORADATA\ORADB\USERS01.DBF                885        883          0          0
已选择8行。

其中：ORADB为数据库名，因为本例中数据库使默认安装，没有进行过优化、调整，
      所以，一直在system表空间上做操作，导致system表空间所在的数据文件SYSTEM01.DBF被读写的次数最多，
      这也说明了，尽量不要在system表空间做与系统无关的操作，应给各个用户建立单独的表空间。

44. 查看哪些session正在使用哪些回滚段: