常用SQL語句3

  23 、查询表空间使用情况 :
SELECT A.TABLESPACE_NAME " 空间名称 ", 100 -ROUND((NVL(B.BYTES_FREE, 0 )/A.BYTES_ALLOC)* 100 , 2 ) " 占用率 (%) ", ROUND(A.BYTES_ALLOC/ 1024 / 1024 , 2 ) " 容量 (M) ",
ROUND(NVL(B.BYTES_FREE, 0 )/ 1024 / 1024 , 2 ) 空闲 (M) ",
ROUND((A.BYTES_ALLOC-NVL(B.BYTES_FREE, 0 ))/ 1024 / 1024 , 2 ) " 使用 (M) ",
LARGEST " 最大扩展段 (M) ",TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS' ) " 采样时间 " FROM (SELECT F.TABLESPACE_NAME,SUM(F.BYTES) BYTES_ALLOC,SUM(DECODE(F.AUTOEXTENSIBLE, 'YES' ,F.MAXBYTES, 'NO' ,F.BYTES)) MAXBYTES FROM DBA_DATA_FILES F GROUP BY TABLESPACE_NAME) A,(SELECT F.TABLESPACE_NAME,SUM(F.BYTES) BYTES_FREE FROM DBA_FREE_SPACE F GROUP BY TABLESPACE_NAME) B,(SELECT ROUND(MAX(FF.LENGTH)* 16 / 1024 , 2 ) LARGEST,TS.NAME TABLESPACE_NAME FROM SYS.FET$ FF, SYS.FILE$ TF,SYS.TS$ TS WHERE TS.TS#=FF.TS# AND FF.FILE#=TF.RELFILE# AND TS.TS#=TF.TS# GROUP BY TS.NAME, TF.BLOCKS) C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

SELECT UPPER(F.TABLESPACE_NAME) " 表空间名 ",
       D.TOT_GROOTTE_MB " 表空间大小 (M) ",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES " 已使用空间 (M) ",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100 ,  2 ), '990.99' ) " 使用比 ",F.TOTAL_BYTES " 空闲空间 (M) ",F.MAX_BYTES " 最大块 (M) " FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / ( 1024 * 1024 ), 2 ) TOTAL_BYTES,
               ROUND(MAX(BYTES) / ( 1024 * 1024 ), 2 ) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / ( 1024 * 1024 ), 2 ) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC;

24 、查询表空间的碎片程度 :
SQL>SELECT TABLESPACE_NAME,COUNT(TABLESPACE_NAME) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME HAVING COUNT(TABLESPACE_NAME)> 10 ;
SQL>ALTER TABLESPACE NAME COALESCE;
SQL>ALTER TABLE TABLE_NAME DEALLOCATE UNUSED;
SQL>CREATE OR REPLACE VIEW TS_BLOCKS_V AS
SELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS, 'FREE SPACE' SEGMENT_NAME FROM DBA_FREE_SPACE UNION ALL
SELECT TABLESPACE_NAME,BLOCK_ID,BYTES,BLOCKS,SEGMENT_NAME FROM DBA_EXTENTS;
SQL>SELECT * FROM TS_BLOCKS_V;
SQL>SELECT TABLESPACE_NAME,SUM(BYTES),MAX(BYTES),COUNT(BLOCK_ID) FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
SQL>SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME|| ' COALESCE;'
FROM DBA_FREE_SPACE_COALESCED WHERE PERCENT_EXTENTS_COALESCED< 100
OR PERCENT_BLOCKS_COALESCED< 100 ;

由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可 用 fsfi- --FREE SPACE FRAGMENTATION INDEX （自由空间碎片索引）值来直观体现 :
FSFI= 100 *SQRT(MAX(EXTENT)/SUM(EXTENTS))* 1 /SQRT(SQRT(COUNT(EXTENTS)))
REM FSFI VALUE COMPUTE
REM FSFI.SQL
COLUMN FSFI FORMAT 999 , 99
SELECT TABLESPACE_NAME,SQRT(MAX(BLOCKS)/SUM(BLOCKS))* ( 100 /SQRT(SQRT(COUNT(BLOCKS)))) FSFI
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1 ;
SPOOL FSFI.REP;
/
SPOOL OFF;
可以看出， fsfi 的最大可能值为 100 （一个理想的单文件表空间）。随着范围的增加， fsfi 值缓慢下降，而随着最大范围尺寸的减少， fsfi 值会迅速下降。比如，在某数据库运行脚本 fsfi.sql, 得到以下 fsfi 值： TABLESPACE_NAME FSFI
------------------------------ -------
RBS 74.06
SYSTEM 100.00
TEMP 22.82
TOOLS 75.79
USERS 100.00
USER_TOOLS 100.00
YDCX_DATA 47.34
YDCX_IDX 57.19
YDJF_DATA 33.80
YDJF_IDX 75.55

---- 统计出了数据库的 fsfi 值，就可以把它作为一个可比参数。在一个有着足够
有效自由空间，且 fsfi 值超过 30 的表空间中，很少会遇见有效自由空间的问题。
当一个空间将要接近可比参数时，就需要做碎片整理了。

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正在使用哪些回滚段:

COL 回滚段名 FORMAT A10
COL SID FORMAT 9990
COL 用户名 FORMAT A10
COL 操作程序 FORMAT A80
COL STATUS FORMAT A6 TRUNC

SELECT R.NAME 回滚段名
S.SID,
S.SERIAL#,
S.USERNAME 用户名 ,
T.STATUS,
T.CR_GET,
T.PHY_IO,
T.USED_UBLK,
T.NOUNDO,
SUBSTR(S.PROGRAM, 1 , 78 ) 操作程序
FROM   SYS.V_$SESSION S,SYS.V_$TRANSACTION T,SYS.V_$ROLLNAME R
WHERE T.ADDR = S.TADDR AND T.XIDUSN = R.USN ORDER BY T.CR_GET,T.PHY_IO;

45.检查谁Lock了什么对象:
SET LINE 200
COL "O/S-USER" FORMAT A10
COL "ORA-USER" FORMAT A10
COL "OBJ LOCKED" FORMAT A30
SELECT /*+RULE*/ S.MACHINE, S.OSUSER "O/S-USER", S.USERNAME "ORA-USER", S.SID "SESSION-ID",
S.SERIAL# "SERIAL", S.PROCESS "PROCESS-ID", S.STATUS "STATUS",L.NAME "OBJ LOCKED",
L.MODE_HELD "LOCK MODE"
FROM V$SESSION S,DBA_DML_LOCKS L,V$PROCESS P
WHERE L.SESSION_ID = S.SID AND P.ADDR = S.PADDR