SQL语句大全
来源：互联网 发布：淘宝网上超市怎么开 编辑：程序博客网 时间：2024/06/08 10:12
数据库各个表空间增长情况的检查：
SQL>SELECT A.TABLESPACE_NAME,(1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENT
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) TOTALFROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM(BYTES) TOTALFROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) BWHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME;
SQL>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_MBFROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4DESC;
查看各个表空间占用磁盘情况:
SQL>COL TABLESPACE_NAME FORMAT A20;
SQL>SELECT B.FILE_ID FILE_ID,
B.TABLESPACE_NAME TABLESPACE_NAME,
B.BYTES BYTES,
(B.BYTES-SUM(NVL(A.BYTES,0))) USED,
SUM(NVL(A.BYTES,0)) FREE,
SUM(NVL(A.BYTES,0))/(B.BYTES)*100PERCENT
FROM DBA_FREE_SPACE A,DBA_DATA_FILES B
WHERE A.FILE_ID=B.FILE_ID
GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES
ORDER BY B.FILE_ID;
数据库对象下一扩展与表空间的free扩展值的检查：
SQL>SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_TABLES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) FWHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK
UNION SELECT A.INDEX_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME
FROM ALL_INDEXES A,(SELECT TABLESPACE_NAME, MAX(BYTES) AS BIG_CHUNK
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) FWHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK;
Disk Read最高的SQL语句的获取：
SQL>SELECT SQL_TEXTFROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)
WHERE ROWNUM<=5;
查找前十条性能差的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 ;
等待时间最多的5个系统等待事件的获取：
SQL>SELECT * FROM (SELECT * FROM V$SYSTEM_EVENT WHERE EVENT NOT LIKE 'SQL%'ORDER BY TOTAL_WAITS DESC) WHERE ROWNUM<=5;
查看当前等待事件的会话:
COL USERNAME FORMAT A10
SET LINE 120
COL EVENT FORMAT A30
SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAIT
FROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAMEIS NOT NULL AND SE.SID=S.SID
AND S.STATUS='ACTIVE'AND SE.EVENT NOT LIKE '%SQL*NET%';
SELECT SID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATEFROM V$SESSION_WAIT WHERE EVENT NOT LIKE'%MESSAGE%' AND EVENT NOT LIKE'SQL*NET%' AND EVENT NOT LIKE'%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER';
找到与所连接的会话有关的当前等待事件：
SELECT SW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAIT
FROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAMEIS NOT NULL AND SW.SID=S.SID
AND SW.EVENT NOT LIKE '%SQL*NET%'ORDER BY SW.WAIT_TIME DESC;
Oracle所有回滚段状态的检查：
SQL>SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,DBA_ROLLBACK_SEGS.STATUSFROM DBA_ROLLBACK_SEGS,V$DATAFILE WHERE FILE_ID=FILE#;
Oracle回滚段扩展信息的检查:
COL NAME FORMAT A10
SET LINESIZE 140
SELECT SUBSTR(NAME,1,40)NAME,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE
FROM V$ROLLNAME RN,V$ROLLSTAT RS WHERE (RN.USN=RS.USN);
EXTENTS:回滚段中的盘区数量。
Rssize:以字节为单位的回滚段的尺寸。
optsize：为optimal参数设定的值。
Aveactive:从回滚段中删除盘区时释放的以字节为单位的平均空间的大小。
Extends:系统为回滚段增加的盘区的次数。
Shrinks:系统从回滚段中清除盘区（即回滚段收缩）的次数。回滚段每次清除盘区时，系统可能会从这个回滚段中消除一个或多个盘区。
Hwmsize:回滚段尺寸的上限，即回滚段曾经达到的最大尺寸。
(如果回滚段平均尺寸接近OPTIMAL的值，那么说明OPTIMAL的值设置正确，如果回滚段动态增长次数或收缩次数很高，那么需要提高OPTIMAL的值)
查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
SELECT S.USERNAME, U.NAME FROM V$TRANSACTION T,V$ROLLSTAT R,
V$ROLLNAME U,V$SESSION S WHERE S.TADDR=T.ADDR AND
T.XIDUSN=R.USN AND R.USN=U.USN ORDER BY S.USERNAME;
如何查看一下某个shared_server正在忙什么:
SELECT A.USERNAME,A.MACHINE,A.PROGRAM,A.SID,
A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXT
FROM V$SESSION A,V$PROCESS B,V$SQLTEXT C
WHERE B.SPID=13161AND B.ADDR=A.PADDR
AND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;
数据库共享池性能检查:
SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONSFROM V$LIBRARYCACHE WHERE NAMESPACE IN('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');
检查数据重载比率:
SELECT SUM(RELOADS)/SUM(PINS)*100 "RELOAD RATIO"FROM
V$LIBRARYCACHE;
检查数据字典的命中率:
SELECT 1-SUM(GETMISSES)/SUM(GETS) "DATA DICTIONARY HIT
RATIO" FROM V$ROWCACHE;
(对于library cache, gethitratio和pinhitratio应该大于90%,对于数据重载比率,reload ratio应该小于1%,对于数据字典的命中率,data dictionary hit ratio应该大于85%)
检查共享内存的剩余情况:
SELECT REQUEST_MISSES, REQUEST_FAILURESFROM V$SHARED_POOL_RESERVED;
(对于共享内存的剩余情况, request_misses和request_failures应该接近0)
数据高速缓冲区性能检查:
SELECT 1-P.VALUE/(B.VALUE+C.VALUE) "DB BUFFER CACHE HIT RATIO"FROM V$SYSSTAT P,V$SYSSTAT B,V$SYSSTAT C WHERE P.NAME='PHYSICAL READS'AND B.NAME='DB BLOCK GETS'AND C.NAME='CONSISTENT GETS';
检查buffer pool HIT_RATIO执行
SELECT NAME, (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "MISS_HIT_RATIO"FROM V$BUFFER_POOL_STATISTICS WHERE (DB_BLOCK_GETS+ CONSISTENT_GETS)>0;
(正常时db buffer cache hit ratio 应该大于90%,正常时buffer pool MISS_HIT_RATIO应该小于10%)
数据库回滚段性能检查:
检查Ratio执行
SELECT SUM(WAITS)* 100 /SUM(GETS) "RATIO",SUM(WAITS) "WAITS", SUM(GETS) "GETS" FROM V$ROLLSTAT;
检查count/value执行:
SELECT CLASS,COUNT FROM V$WAITSTATWHERE CLASS LIKE '%UNDO%';
SELECT VALUE FROM V$SYSSTAT WHERE NAME='CONSISTENT GETS';
(两者的value值相除)
检查average_wait执行:
SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAITFROM V$SYSTEM_EVENT WHERE EVENT LIKE'%UNDO%';
检查RBS header get ratio执行:
SELECT N.NAME,S.USN,S.WRAPS, DECODE(S.WAITS,0,1,1- S.WAITS/S.GETS)"RBS HEADER GET RATIO" FROM V$ROLLSTAT S,V$ROLLNAME NWHERE S.USN=N.USN;
(正常时Ratio应该小于1%, count/value应该小于0.01%,average_wait最好为0，该值越小越好,RBS header get ratio应该大于95%)
杀会话的脚本:
SELECT A.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME,A.LAST_CALL_ET/3600 LAST_HOUR,A.STATUS,'ORAKILL '||SID||' '||SPID HOST_COMMAND,'ALTER SYSTEM KILL SESSION '''||A.SID||','||A.SERIAL#||'''' SQL_COMMANDFROM V$SESSION A,V$PROCESS B WHERE A.PADDR=B.ADDRAND SID>6;
查看排序段的性能:
SQL>SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('SORTS (MEMORY)','SORTS (DISK)');
7、查看数据库库对象:
SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*) COUNT# FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;
8、查看数据库的版本:
SELECT * FROM V$VERSION;
9、查看数据库的创建日期和归档方式:
SELECT CREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;
10、捕捉运行很久的SQL:
COLUMN USERNAME FORMAT A12
COLUMN OPNAME FORMAT A16
COLUMN PROGRESS FORMAT A8
SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100 / TOTALWORK,0) ||'%' AS PROGRESS,TIME_REMAINING,SQL_TEXTFROM V$SESSION_LONGOPS , V$SQL WHERE TIME_REMAINING <>0 AND SQL_ADDRESS=ADDRESSAND SQL_HASH_VALUE = HASH_VALUE;
11、查看数据表的参数信息:
SELECTPARTITION_NAME, HIGH_VALUE, HIGH_VALUE_LENGTH,TABLESPACE_NAME,PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,INITIAL_EXTENT,NEXT_EXTENT, MIN_EXTENT, MAX_EXTENT, PCT_INCREASE,FREELISTS,FREELIST_GROUPS, LOGGING, BUFFER_POOL, NUM_ROWS, BLOCKS,EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, SAMPLE_SIZE,LAST_ANALYZEDFROM DBA_TAB_PARTITIONS
--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNER
ORDER BY PARTITION_POSITION;
12、查看还没提交的事务:
SELECT * FROM V$LOCKED_OBJECT;
SELECT * FROM V$TRANSACTION;
13、查找object为哪些进程所用:
SELECT P.SPID,S.SID,S.SERIAL# SERIAL_NUM,S.USERNAME USER_NAME,
A.TYPE OBJECT_TYPE,S.OSUSER OS_USER_NAME,A.OWNER,A.OBJECT OBJECT_NAME,DECODE(SIGN(48 - COMMAND),1,
TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,
P.PROGRAM ORACLE_PROCESS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,S.STATUS SESSION_STATUSFROM V$SESSION S, V$ACCESS A, V$PROCESS P WHERE S.PADDR = P.ADDRAND S.TYPE = 'USER' AND A.SID = S.SID AND A.OBJECT='SUBSCRIBER_ATTR'ORDERBY S.USERNAME, S.OSUSER;
14、查看回滚段:
SQL>COL NAME FORMAT A10
SQL>SET LINESIZE 100
SQL>SELECT ROWNUM,SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAME NAME, V$ROLLSTAT.EXTENTS EXTENTS,V$ROLLSTAT.RSSIZE SIZE_IN_BYTES, V$ROLLSTAT.XACTS XACTS,V$ROLLSTAT.GETS GETS, V$ROLLSTAT.WAITS WAITS, V$ROLLSTAT.WRITES WRITES,SYS.DBA_ROLLBACK_SEGS.STATUS STATUS FROM V$ROLLSTAT, SYS.DBA_ROLLBACK_SEGS, V$ROLLNAMEWHERE V$ROLLNAME.NAME(+) = SYS.DBA_ROLLBACK_SEGS.SEGMENT_NAMEAND V$ROLLSTAT.USN (+) = V$ROLLNAME.USN ORDER BY ROWNUM;
15、耗资源的进程(top session):
SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN(48 - COMMAND),1, TO_CHAR(COMMAND), 'ACTION CODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSER OS_USER_NAME,S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME,'[ORACLE PROCESS]') USER_NAME,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,ST.VALUE CRITERIA_VALUEFROM V$SESSTAT ST,V$SESSION S,V$PROCESS P WHERE ST.SID = S.SIDAND ST.STATISTIC# = TO_NUMBER('38') AND ('ALL'='ALL' OR S.STATUS ='ALL')AND P.ADDR=S.PADDR ORDER BY ST.VALUE DESC,P.SPIDASC,S.USERNAME ASC,S.OSUSER ASC;
根据PID查找相应的语句:
SELECT A.USERNAME, A.MACHINE,A.PROGRAM,A.SID,A.SERIAL#,A.STATUS,C.PIECE,C.SQL_TEXTFROM V$SESSION A,V$PROCESS B,V$SQLTEXT C WHERE B.SPID=SPIDAND B.ADDR=A.PADDR AND A.SQL_ADDRESS=C.ADDRESS(+)ORDER BY C.PIECE;
SQL> SELECT PRO.SPIDFROM V$SESSION SES,V$PROCESS PRO WHERE SES.SID=21AND SES.PADDR=PRO.ADDR;
监控当前数据库谁在运行什么SQL语句:
SQL>SELECT OSUSER, USERNAME, SQL_TEXTFROM V$SESSION A, V$SQLTEXT B
WHERE A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE;
如何查看数据库中某用户，正在运行什么SQL语句
SQL>SELECT SQL_TEXTFROM V$SQLTEXT T, V$SESSION S WHERE T.ADDRESS=S.SQL_ADDRESSAND T.HASH_VALUE=S.SQL_HASH_VALUE AND S.MACHINE='XXXXX'OR USERNAME='WACOS';
如何查出前台正在发出的sql语句:
SQL> SELECT USER_NAME,SQL_TEXTFROM V$OPEN_CURSOR WHERE SID IN(SELECT SIDFROM (SELECT SID,SERIAL# FROM V$SESSIONWHERE STATUS='ACTIVE'));
查询当前所执行的SQL语句：
SQL> SELECT PROGRAM ,SQL_ADDRESSFROM V$SESSION WHERE PADDR IN (SELECT ADDRFROM V$PROCESS WHERE SPID=3556);
PROGRAM SQL_ADDRESS
------------------------------------------------ ----------------
SQLPLUS@CTC20 (TNS V1-V3) 000000038FCB1A90
SQL> SELECT SQL_TEXT FROM V$SQLAREAWHERE ADDRESS='000000038FCB1A90';
找出消耗CPU最高的进程对应的SQL语句：
SET LINE 240
SET VERIFY OFF
COLUMN SID FORMAT 999
COLUMN PID FORMAT 999
COLUMN S_# FORMAT 999
COLUMN USERNAME FORMAT A9 HEADING "ORA USER"
COLUMN PROGRAM FORMAT A29
COLUMN SQL FORMAT A60
COLUMN OSNAME FORMAT A9 HEADING "OS USER"
SELECTP.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSEROSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1, 80)) SQLFROM V$PROCESS P, V$SESSION S,V$SQLAREA AWHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+)AND P.SPID LIKE '%&1%';
ENTER VALUE FOR 1: PID¡（这里输入占用CPU最高的进程对应的PID）
SET TERMOUT OFF
SPOOL MAXCPU.TXT
SELECT '++'||S.USERNAME USERNAME,RTRIM(REPLACE(A.SQL_TEXT,CHR(10),''))||';'FROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE'%&&1%';
Enter value for 1: PID（这里输入占用CPU最高的进程对应的PID）
spool off(这句放在最后执行)
CPU用率最高的2条SQL语句的获取
执行：top，通过top获得CPU占用率最高的进程的pid。
SQL>SELECT SQL_TEXT,SPID,V$SESSION.PROGRAM,PROCESSFROM V$SQLAREA,V$SESSION,V$PROCESS WHERE V$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESSAND V$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUE AND V$SESSION.PADDR=V$PROCESS.ADDRAND V$PROCESS.SPID IN (PID);
COL MACHINE FORMAT A30
COL PROGRAM FORMAT A40
SET LINE 200
SQL>SELECT SID,SERIAL# ,USERNAME,OSUSER,MACHINE,PROGRAM,PROCESS,TO_CHAR(LOGON_TIME,'YYYY/MM/DD HH24:MI:SS')FROM V$SESSION WHERE PADDR IN(SELECT ADDRFROM V$PROCESS WHERE SPID IN([$SPID]));
SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES
WHERE HASH_VALUE=(SELECT SQL_HASH_VALUE FROM V$SESSION WHERE SID=&SID)
ORDER BY PIECE;
16、查看锁（lock）情况:
SQL>SELECT /*+ RULE */
LS.OSUSER OS_USER_NAME,
LS.USERNAME USER_NAME,
DECODE(LS.TYPE,
'RW','ROW WAIT ENQUEUE LOCK',
'TM','DML ENQUEUE LOCK',
'TX','TRANSACTION ENQUEUE LOCK',
'UL','USER SUPPLIED LOCK') LOCK_TYPE,
O.OBJECT_NAME OBJECT,
DECODE(LS.LMODE,
1,NULL,
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE,
O.OWNER,
LS.SID,
LS.SERIAL# SERIAL_NUM,
LS.ID1,
LS.ID2
FROM SYS.DBA_OBJECTS O,
(SELECT S.OSUSER,
S.USERNAME,
L.TYPE,
L.LMODE,
S.SID,
S.SERIAL#,
L.ID1,
L.ID2
FROM V$SESSION S, V$LOCK L
WHERE S.SID = L.SID) LS
WHERE O.OBJECT_ID = LS.ID1
AND O.OWNER <> 'SYS'
ORDER BY O.OWNER, O.OBJECT_NAME;
SQL>SELECT SYS.V_$SESSION.OSUSER,
SYS.V_$SESSION.MACHINE,
V$LOCK.SID,
SYS.V_$SESSION.SERIAL#,
DECODE(V$LOCK.TYPE,
'MR','MEDIA RECOVERY',
'RT','REDO THREAD',
'UN','USER NAME',
'TX','TRANSACTION',
'TM','DML',
'UL','PL/SQL USER LOCK',
'DX','DISTRIBUTED XACTION',
'CF','CONTROL FILE',
'IS','INSTANCE STATE',
'FS','FILE SET',
'IR','INSTANCE RECOVERY',
'ST','DISK SPACE TRANSACTION',
'TS','TEMP SEGMENT',
'IV','LIBRARY CACHE INVALIDA-TION',
'LS','LOG START OR SWITCH',
'RW','ROW WAIT',
'SQ','SEQUENCE NUMBER',
'TE','EXTEND TABLE',
'TT','TEMP TABLE',
'UNKNOWN') LOCKTYPE,
RTRIM(OBJECT_TYPE) || ' ' || RTRIM(OWNER) ||'.' || OBJECT_NAME OBJECT_NAME,
DECODE(LMODE,
0,'NONE',
1,'NULL',
2,'ROW-S',
3,'ROW-X',
4,'SHARE',
5,'S/ROW-X',
6,'EXCLUSIVE',
'UNKNOWN') LOCKMODE,
DECODE(REQUEST,
0,'NONE',
1,'NULL',
2,'ROW-S',
3,'ROW-X',
4,'SHARE',
5,'S/ROW-X',
6,'EXCLUSIVE',
'UNKNOWN') REQUESTMODE,
CTIME,
BLOCK B
FROM V$LOCK, ALL_OBJECTS, SYS.V_$SESSION
WHERE V$LOCK.SID > 6
AND SYS.V_$SESSION.SID = V$LOCK.SID
AND V$LOCK.ID1 = ALL_OBJECTS.OBJECT_ID;
以DBA角色,查看当前数据库里锁的情况可以用如下SQL语句：
COL OWNER FOR A12
COL OBJECT_NAME FOR A16
SELECT B.OWNER,B.OBJECT_NAME,L.SESSION_ID,L.LOCKED_MODE
FROM V$LOCKED_OBJECT L, DBA_OBJECTS B
WHERE B.OBJECT_ID=L.OBJECT_ID;
SQL>SELECT T2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIMEFROM V$LOCKED_OBJECT T1,V$SESSION T2 WHERE T1.SESSION_ID=T2.SIDORDER BY T2.LOGON_TIME;
SQL>SELECT SQL_ADDRESSFROM V$SESSION WHERE SID=;
SQL>SELECT * FROM V$SQLTEXT WHERE ADDRESS=;
SQL>SELECT COMMAND_TYPE,PIECE,SQL_TEXTFROM V$SQLTEXT WHERE ADDRESS=(SELECT SQL_ADDRESSFROM V$SESSION A WHERE SID=18);
SQL>SELECT OBJECT_IDFROM V$LOCKED_OBJECT;
SQL>SELECT OBJECT_NAME,OBJECT_TYPEFROM DBA_OBJECTS WHERE OBJECT_ID='';
如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：SQL>ALTER SYSTEM KILL SESSION'SID,SERIAL#';
17、查看等待（wait）情况:
SQL>SELECT V$WAITSTAT.CLASS,V$WAITSTAT.COUNT COUNT,SUM(V$SYSSTAT.VALUE) SUM_VALUE FROM V$WAITSTAT,V$SYSSTATWHERE V$SYSSTAT.NAME IN('DB BLOCK GETS','CONSISTENT GETS')GROUP BY V$WAITSTAT.CLASS,V$WAITSTAT.COUNT;
18、查看sga情况:
SQL>SELECT NAME, BYTESFROM SYS.V_$SGASTAT ORDER BY NAME ASC;
19、查看catched object:
SQL>SELECT OWNER,NAME,DB_LINK,NAMESPACE,TYPE,SHARABLE_MEM,LOADS, EXECUTIONS,LOCKS,PINS,KEPTFROM V$DB_OBJECT_CACHE;
20、查看V$SQLAREA:
SQL>SELECT SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,
VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,
USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,
DISK_READS,BUFFER_GETS,ROWS_PROCESSED FROM V$SQLAREA;
21、查看object分类数量:
SELECT DECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER') OBJECT_TYPE ,COUNT(*) QUANTITY FROM SYS.OBJ$ O WHERE O.TYPE# >1 GROUP BY DECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER' ,4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')UNION SELECT 'COLUMN', COUNT(*)FROM SYS.COL$ UNION SELECT 'DB LINK' ,COUNT(*) FROM ALL_OBJECTS;
22、有关connection的相关信息:
1）查看有哪些用户连接
SELECT S.OSUSER OS_USER_NAME,DECODE(SIGN(48 - COMMAND),1,TO_CHAR(COMMAND),
'ACTION CODE #'|| TO_CHAR(COMMAND))ACTION,P.PROGRAM ORACLE_PROCESS, STATUSSESSION_STATUS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM, S.USERNAMEUSER_NAME,S.FIXED_TABLE_SEQUENCE ACTIVITY_METER,''QUERY,0 MEMORY,0 MAX_MEMORY,0 CPU_USAGE,S.SID,S.SERIAL# SERIAL_NUMFROM V$SESSION S,V$PROCESS P WHERE S.PADDR=P.ADDRAND S.TYPE = 'USER'ORDER BY S.USERNAME, S.OSUSER;
2）根据v.sid查看对应连接的资源占用等情况
SELECT N.NAME,V.VALUE,N.CLASS,N.STATISTIC#FROM V$STATNAME N,V$SESSTAT V WHERE V.SID=18AND V.STATISTIC# = N.STATISTIC# ORDER BY N.CLASS, N.STATISTIC#;
3）根据sid查看对应连接正在运行的sql
SELECT /*+ PUSH_SUBQ */COMMAND_TYPE,SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,SYSDATE START_TIME,SYSDATE FINISH_TIME,'>'|| ADDRESS SQL_ADDRESS,'N' STATUS FROM V$SQLAREA WHERE ADDRESS = (SELECT SQL_ADDRESSFROM V$SESSION WHERE SID=8);
根据pid查看sql语句:
SELECT SQL_TEXTFROM V$SQL WHERE ADDRESS IN (SELECT SQL_ADDRESSFROM V$SESSION WHERE SID IN (SELECT SIDFROM V$SESSION WHERE PADDR IN (SELECT ADDRFROM V$PROCESS WHERE SPID=&PID)));
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)) MAXBYTESFROM DBA_DATA_FILES F GROUP BY TABLESPACE_NAME) A,(SELECT F.TABLESPACE_NAME,SUM(F.BYTES) BYTES_FREEFROM 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) CWHERE 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 4DESC;
24、查询表空间的碎片程度:
SQL>SELECT TABLESPACE_NAME,COUNT(TABLESPACE_NAME)FROM DBA_FREE_SPACE GROUP BY
- sql sql语句大全
- SQL语句大全SQL语句大全
- SQL语句大全--语句功能
- mysql语句 SQL语句大全
- SQL语句大全
- SQL语句大全
- SQL语句大全
- SQL常用语句大全
- SQL语句大全
- SQL语句大全2
- SQL语句大全
- SQL语句大全
- SQL语句大全
- SQL语句大全
- SQL语句大全
- SQL语句大全
- sql语句大全
- SQL查询语句大全
- readv/writev系统调用
- flex报表----柱状图
- 关于socket的read和write
- 7、数组1---(定义、a，&a，&a[0]区别、以指针形式访问和以下标形式访问)(
- 制作一个自定义Dialog例子
- SQL语句大全
- 动易SiteFactory添加.Net控件，并结合VS进行编程
- 8、 关于a 和 &a区别的经典题目
- 还是网络
- 9、 定义和声明
- 项目经验积累
- 10、数组指针与指针数组(内存布局、理解、地址强制转换(题目))
- 详解Java增强的For循环（泛型） 各种用法
- 生成验证码图片
