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,
2,'ROW SHARE',
3,'ROW 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',
3,'ROW EXCL.',
5,'S/ROW EXCL.',
LMODE,
LTRIM(TO_CHAR(LMODE, '990'))) LMODE,
DECODE(M.REQUEST,
0,'NONE',
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
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)