文档章节

Oracle数据库SQL语句操作大全汇总

思维80
 思维80
发布于 2015/05/24 15:10
字数 3245
阅读 45
收藏 0
点赞 0
评论 0

数据库各个表空间增长情况的检查: SQL >SELECT A.TABLESPACE_NAME,( 1-(A.TOTAL)/B.TOTAL)*100 USED_PERCENTFROM (SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROM DBA_FREE_SPACE GROUP BYTABLESPACE_NAME) A,(SELECT TABLESPACE_NAME,SUM (BYTES) TOTAL FROMDBA_DATA_FILES GROUP BY TABLESPACE_NAME) B WHEREA.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 (SELECTTABLESPACE_NAME, ROUND(SUM (BYTES) / (1024 * 1024 ), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024 ), 2) MAX_BYTES FROM SYS .DBA_FREE_SPACE GROUP BYTABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME,ROUND(SUM (DD.BYTES) / ( 1024 *1024 ), 2 ) TOT_GROOTTE_MB FROM SYS .DBA_DATA_FILES DD GROUP BYDD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY4DESC ; 查看各个表空间占用磁盘情况: 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 BWHERE A.FILE_ID=B.FILE_ID GROUP BY B.TABLESPACE_NAME,B.FILE_ID,B.BYTES ORDER BYB.FILE_ID; 数据库对象下一扩展与表空间的 free扩展值的检查: SQL>SELECT A.TABLE_NAME, A.NEXT_EXTENT, A.TABLESPACE_NAME FROM ALL_TABLESA,(SELECT TABLESPACE_NAME, MAX (BYTES) AS BIG_CHUNK FROM DBA_FREE_SPACE GROUPBY TABLESPACE_NAME ) F WHERE F.TABLESPACE_NAME = A.TABLESPACE_NAME ANDA.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) ASBIG_CHUNK FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F WHEREF.TABLESPACE_NAME = A.TABLESPACE_NAME AND A.NEXT_EXTENT > F.BIG_CHUNK; DiskRead最高的SQL 语句的获取: SQL >SELECT SQL_TEXT FROM(SELECT * FROM V$SQLAREA ORDER BY DISK_READS) WHERE ROWNUM <=5 ;查找前十条性能差的 sqlSELECT * 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%'ORDERBY TOTAL_WAITS DESC ) WHERE ROWNUM <=5 ;查看当前等待事件的会话 :COL USERNAME FORMAT A10 SET LINE120 COL EVENT FORMAT A30 SELECT SE.SID,S.USERNAME,SE.EVENT,SE.TOTAL_WAITS,SE.TIME_WAITED,SE.AVERAGE_WAITFROM V$SESSION S,V$SESSION_EVENT SE WHERE S.USERNAME IS NOT NULL ANDSE.SID=S.SID AND S.STATUS= 'ACTIVE'AND SE.EVENT NOT LIKE '%SQL*NET%' ; SELECTSID, EVENT, P1, P2, P3, WAIT_TIME, SECONDS_IN_WAIT, STATE FROM V$SESSION_WAITWHERE EVENT NOT LIKE'%MESSAGE%'AND EVENT NOT LIKE 'SQL*NET%'AND EVENT NOT LIKE'%TIMER%' AND EVENT != 'WAKEUP TIME MANAGER'; 找到与所连接的会话有关的当前等待事件:

SELECTSW.SID,S.USERNAME,SW.EVENT,SW.WAIT_TIME,SW.STATE,SW.SECONDS_IN_WAIT SEC_IN_WAITFROM V$SESSION S,V$SESSION_WAIT SW WHERE S.USERNAME IS NOT NULL ANDSW.SID=S.SID AND SW.EVENT NOT LIKE '%SQL*NET%' ORDER BY SW.WAIT_TIME DESC;Oracle 所有回滚段状态的检查:

SQL>SELECTSEGMENT_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 LINESIZE140 SELECT SUBSTR(NAME , 1 , 40 ) NAME ,EXTENTS,RSSIZE,OPTSIZE,AVEACTIVE,EXTENDS,WRAPS,SHRINKS,HWMSIZE FROM V$ROLLNAMERN,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$ROLLNAMEU,V$SESSION S WHERE S.TADDR=T.ADDR AND T.XIDUSN=R.USN AND R.USN=U.USN ORDER BYS.USERNAME;如何查看一下某个 shared_server正在忙什么: SELECTA.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=13161 AND B.ADDR=A.PADDRAND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;数据库共享池性能检查 :SELECT NAMESPACE,GETS,GETHITRATIO,PINS,PINHITRATIO,RELOADS,INVALIDATIONS FROMV$LIBRARYCACHE WHERE NAMESPACE IN ('SQLAREA', 'TABLE/PROCEDURE', 'BODY' ,'TRIGGER' ); 检查数据重载比率 : SELECT SUM (RELOADS)/SUM (PINS)*100"RELOADRATIO" FROM V$LIBRARYCACHE;检查数据字典的命中率 :SELECT1-SUM (GETMISSES)/SUM (GETS) "DATA DICTIONARY HIT RATIO" FROMV$ROWCACHE;( 对于 library cache, gethitratio 和pinhitratio 应该大于 90%, 对于数据重载比率,reload ratio应该小于 1%,对于数据字典的命中率,datadictionary hit ratio应该大于 85%)

检查共享内存的剩余情况 :SELECT REQUEST_MISSES, REQUEST_FAILURES FROM V$SHARED_POOL_RESERVED;( 对于共享内存的剩余情况,request_misses和 request_failures 应该接近 0)

数据高速缓冲区性能检查 :SELECT1-P.VALUE /(B.VALUE +C.VALUE ) "DB BUFFER CACHE HIT RATIO" FROMV$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 poolHIT_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; ( 正常时dbbuffer cache hit ratio 应该大于90%, 正常时buffer poolMISS_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$WAITSTAT WHERE CLASS LIKE'%UNDO%'; SELECT VALUE FROMV$SYSSTAT WHERE NAME ='CONSISTENT GETS' ; ( 两者的 value 值相除 )检查average_wait 执行 : SELECT EVENT,TOTAL_WAITS,TIME_WAITED,AVERAGE_WAIT FROMV$SYSTEM_EVENT WHERE EVENT LIKE'%UNDO%'; 检查 RBS header getratio执行 : 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$ROLLNAMEN WHERE S.USN=N.USN;( 正常时 Ratio 应该小于 1%,count/value 应该小于 0.01%,average_wait 最好为0,该值越小越好 ,RBSheader get ratio 应该大于95%)

杀会话的脚本 :SELECTA.SID,B.SPID,A.SERIAL#,A.LOCKWAIT,A.USERNAME,A.OSUSER,A.LOGON_TIME,A.LAST_CALL_ET/3600LAST_HOUR,A.STATUS,'ORAKILL ' ||SID|| ' ' ||SPID HOST_COMMAND,'ALTER SYSTEM KILL SESSION'''||A.SID|| ','||A.SERIAL#|| ''''SQL_COMMAND FROM V$SESSION A,V$PROCESS BWHERE A.PADDR=B.ADDR AND 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 、查看数据库的创建日期和归档方式:SELECTCREATED, LOG_MODE, LOG_MODE FROM V$DATABASE;

10 、捕捉运行很久的SQL:COLUMN USERNAME FORMAT A12 COLUMN OPNAME FORMAT A16 COLUMN PROGRESS FORMATA8 SELECT USERNAME,SID,OPNAME,ROUND(SOFAR*100/ TOTALWORK, 0 ) || '%' ASPROGRESS,TIME_REMAINING,SQL_TEXT FROM V$SESSION_LONGOPS , V$SQL WHERETIME_REMAINING <>0AND SQL_ADDRESS=ADDRESS AND SQL_HASH_VALUE =HASH_VALUE; 11、查看数据表的参数信息 : SELECT PARTITION_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_ANALYZED FROMDBA_TAB_PARTITIONS--WHERE TABLE_NAME = :TNAME AND TABLE_OWNER = :TOWNERORDERBY 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.TYPEOBJECT_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.STATUSSESSION_STATUS FROM 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'ORDER BYS.USERNAME, S.OSUSER; 14、查看回滚段 : SQL >COL NAME FORMAT A10SQL >SET LINESIZE100 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.STATUSSTATUS FROM V$ROLLSTAT, SYS .DBA_ROLLBACK_SEGS, V$ROLLNAME WHEREV$ROLLNAME.NAME (+) = SYS .DBA_ROLLBACK_SEGS.SEGMENT_NAME AND V$ROLLSTAT.USN(+) = V$ROLLNAME.USN ORDER BY ROWNUM ; 15 、耗资源的进程(topsession): SELECT S.SCHEMANAME SCHEMA_NAME,DECODE(SIGN(48 - COMMAND), 1, TO_CHAR(COMMAND),'ACTIONCODE #' || TO_CHAR(COMMAND) ) ACTION,STATUS SESSION_STATUS,S.OSUSEROS_USER_NAME,S.SID,P.SPID,S.SERIAL# SERIAL_NUM,NVL(S.USERNAME,'[ORACLEPROCESS]') USER_NAME,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM,ST.VALUECRITERIA_VALUE FROM 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' ) ANDP.ADDR=S.PADDR ORDER BY ST.VALUE DESC ,P.SPID ASC ,S.USERNAME ASC ,S.OSUSER ASC;根据 PID 查找相应的语句 :

SELECTA.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=SPID AND B.ADDR=A.PADDRAND A.SQL_ADDRESS=C.ADDRESS(+) ORDER BY C.PIECE;

SQL语句大全—查看数据(三)

2007年08月16日 星期四 上午02:17

 

根据  SID 找 ORACLE 的某个进程 :  SQL > SELECT PRO.SPID FROM V$SESSION SES,V$PROCESS PRO WHERE SES.SID=21AND  SES.PADDR=PRO.ADDR; 监控当前数据库谁在运行什么SQL 语句 :  SQL >SELECT OSUSER, USERNAME, SQL_TEXT FROM V$SESSION A, V$SQLTEXT B WHERE  A.SQL_ADDRESS =B.ADDRESS ORDER BY ADDRESS, PIECE;如何查看数据库中某用户,正在运行什么  SQL 语句 SQL >SELECT SQL_TEXT FROM V$SQLTEXT T, V$SESSION S  WHERE T.ADDRESS=S.SQL_ADDRESS AND T.HASH_VALUE=S.SQL_HASH_VALUE AND  S.MACHINE='XXXXX'OR USERNAME='WACOS' ;如何查出前台正在发出的  sql 语句 : SQL > SELECT USER_NAME,SQL_TEXT FROM  V$OPEN_CURSOR WHERE SID IN (SELECT SID FROM (SELECT SID,SERIAL# FROM  V$SESSION WHERE STATUS='ACTIVE')); 查询当前所执行的  SQL 语句: SQL > SELECT PROGRAM ,SQL_ADDRESS FROM V$SESSION  WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID=3556); PROGRAM  SQL_ADDRESS ------------------------------------------------  ----------------SQLPLUS@CTC20 (TNS V1-V3) 000000038 FCB1A90 SQL >  SELECT SQL_TEXT FROM V$SQLAREA WHERE ADDRESS='000000038FCB1A90'; 找出消耗  CPU 最高的进程对应的 SQL 语句:SET LINE  240SET VERIFY OFF COLUMN SID FORMAT 999COLUMN PID FORMAT 999 COLUMN S_#  FORMAT999 COLUMN USERNAME FORMAT A9 HEADING "ORA USER" COLUMN  PROGRAM FORMAT A29 COLUMN SQL FORMAT A60 COLUMN OSNAME FORMAT A9 HEADING  "OS USER" SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME  USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,  80)) SQLFROM 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 )  SET TERMOUT OFF SPOOL MAXCPU.TXT SELECT'++' ||S.USERNAME  USERNAME,RTRIM(REPLACE (A.SQL_TEXT,CHR(10), '' ))|| ';'FROMV$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,PROCESS  FROM V$SQLAREA,V$SESSION,V$PROCESS WHERE  V$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESS AND  V$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUE AND  V$SESSION.PADDR=V$PROCESS.ADDR AND V$PROCESS.SPID IN (PID); COL MACHINE  FORMAT A30 COL PROGRAM FORMAT A40 SET LINE200 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 ADDR FROM 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_TIME FROM V$LOCKED_OBJECT T1,V$SESSION  T2 WHERE T1.SESSION_ID=T2.SID ORDER BY T2.LOGON_TIME;

SQL  >SELECT SQL_ADDRESS FROM V$SESSION WHERE SID=;

SQL  >SELECT * FROM V$SQLTEXT WHERE ADDRESS=;

SQL  >SELECT COMMAND_TYPE,PIECE,SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS=(SELECT  SQL_ADDRESS FROM V$SESSION A WHERE SID=18);

SQL  >SELECT OBJECT_ID FROM V$LOCKED_OBJECT;

SQL  >SELECT OBJECT_NAME,OBJECT_TYPE FROM 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$SYSSTAT WHERE V$SYSSTAT.NAME  IN ('DB BLOCK GETS' , 'CONSISTENT GETS' ) GROUP BY V$WAITSTAT.CLASS  ,V$WAITSTAT.COUNT ; 18、查看 sga 情况 : SQL  >SELECT NAME , BYTES FROM SYS .V_$SGASTAT ORDER BY NAME ASC ;19 、查看  catched object: SQL >SELECT OWNER,NAME ,DB_LINK,NAMESPACE,TYPE  ,SHARABLE_MEM,LOADS, EXECUTIONS,LOCKS,PINS,KEPT FROM 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 # >1GROUP 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, STATUS  SESSION_STATUS,S.TERMINAL TERMINAL,S.PROGRAM PROGRAM, S.USERNAME  USER_NAME,S.FIXED_TABLE_SEQUENCE ACTIVITY_METER,''QUERY , 0  MEMORY,0MAX_MEMORY, 0 CPU_USAGE,S.SID,S.SERIAL# SERIAL_NUM FROM V$SESSION  S,V$PROCESS P WHERE S.PADDR=P.ADDR AND 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_ADDRESS FROM V$SESSION WHERE SID=8); 根据  pid查看 sql 语句:

SELECT  SQL_TEXT FROM V$SQL WHERE ADDRESS IN (SELECT SQL_ADDRESS FROM V$SESSION WHERE  SID IN (SELECT SID FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS  WHERE SPID=&PID)));

23、查询表空间使用情况: SELECTA.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-DDHH24: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,(SELECTF.TABLESPACE_NAME,SUM (F.BYTES) BYTES_FREE FROM DBA_FREE_SPACE F GROUP BYTABLESPACE_NAME) B,(SELECT ROUND(MAX (FF.LENGTH)*16 / 1024 ,2 ) LARGEST,TS.NAMETABLESPACE_NAME FROM SYS .FET$ FF, SYS .FILE $ TF,SYS .TS$ TS WHERETS.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, (SELECTDD.TABLESPACE_NAME, ROUND(SUM (DD.BYTES) / ( 1024* 1024), 2 ) TOT_GROOTTE_MBFROM SYS .DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERED.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY4 DESC ; 24 、查询表空间的碎片程度 :SQL >SELECT TABLESPACE_NAME,COUNT (TABLESPACE_NAME) FROM DBA_FREE_SPACEGROUP BY

原文地址:http://blog.csdn.net/sutku/article/details/7900876

如需转载请注明出处,尊重他人劳动成果

 

 

本文转载自:http://blog.csdn.net/u012151556/article/details/22646335

共有 人打赏支持
思维80
粉丝 8
博文 111
码字总数 42366
作品 0
西安
程序员
ORCALE命令大全

一、ORACLE的启动和关闭 1、在单机环境下 要想启动或关闭ORACLE系统必须首先切换到ORACLE用户,如下 su - oracle a、启动ORACLE系统 oracle>svrmgrl SVRMGR>connect internal SVRMGR>startu...

空_明 ⋅ 2012/10/31 ⋅ 1

oracle小知识汇总

Oracle跟SQL Server 2005的区别? 宏观上: 1. 最大的区别在于平台,oracle可以运行在不同的平台上,sql server只能运行在windows平台上,由于windows平台的稳定性和安全性影响了sql server的...

lvzjane ⋅ 2012/11/20 ⋅ 0

分享16个关于数据库方面的学习书籍(免费下载)

分享16个关于数据库方面的学习书籍(免费下载) 1、SQL掌用实例 2、精通SQL01 3、精通SQL02 4、精通SQL03 5、精通SQL04 6、精通SQL05 7、精通SQL06 8、精通Oracle的sql语句 9、SQL语句教程 ...

邓剑彬 ⋅ 2012/12/03 ⋅ 1

Oracle数据库学习的福利来啦——最佳入门经验分享

适用人群:从事数据库技术实施或售后工程师,准备参加OCA,OCP认证考试 通过学习对Oracle数据库的相关基础知识进行梳理,最终共同提炼出必须最先掌握的那部分知识,无论你是数据库开发、管理、...

让往事随风 ⋅ 2016/04/13 ⋅ 0

Oracle DBA必备:Oracle高级管理教程和排错、优化文档

Oracle DBA必备:Oracle高级管理教程和排错、优化文档 我们经常发现这样一种情况,我们在大学里所学的数据库那点东西,到了实际工作里往往让我们手足无措。看似简单的数据库却成了我们工作、...

IT小白白 ⋅ 2012/09/10 ⋅ 1

SQL优化大全心得

目录 作者整理:周海平(kider) 概要... 1 优化的理由... 2 1.大小写对SQL语句的影响(ORACLE)... 2 2.尽量使用(NOT) EXISTS 替代( NOT)IN这样的操作... 3 3.在海量查询时尽量少用格式转换....

sxm20074428 ⋅ 2014/05/09 ⋅ 0

PLSQL Developer软件使用大全

PLSQL Developer软件使用大全 第二章 PLSQL Developer配置 2.1 记住密码 这是个有争议的功能,因为记住密码会给带来数据安全的问题。但假如是开发用的库,密码甚至可以和用户名相同,每次输入...

lhrbest ⋅ 2017/03/03 ⋅ 0

审计简介

一、审计分类 Oracle中审计总体上可分为“标准审计”和“细粒度审计”后者也称为“基于政策的审计”,在Oracle10G之后功能得到很大增强。其中标准审计可分为用户级审计和系统级审计。用户级审...

ryan_bai ⋅ 2017/11/29 ⋅ 0

linux下oracle常用命令大全

参看主机名:$hostname 打开图形化窗口: 1)Database Configuration Assistant windows (添加数据库实例) $ dbca 2)Oracle Net Configuration Assistant windows (配置监听) $ netca 3)打......

china-soft ⋅ 2015/10/02 ⋅ 0

oracle sqlplus 常用命令大全

show和set命令是两条用于维护SQL*Plus系统变量的命令 SQL> show all --查看所有68个系统变量值 SQL> show user --显示当前连接用户 SQL> show error   --显示错误 SQL> set heading off -...

随影求是 ⋅ 2012/03/30 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

idea 整合 vue 启动

刚学习Vue 搭建了一个项目 只能命令启动 Idea里面不会启动 尝试了一下修改启动的配置 如下: 1.首先你要保证你的package.json没有修改过 具体原因没有看 因为我改了这个name的值 就没办法启动...

事儿爹 ⋅ 9分钟前 ⋅ 0

数据仓库技术概述(一看就是架构师写的,对我极其有用)

ETL,是英文 Extract-Transform-Load 的缩写,用来描述将数据从来源端经过抽取(extract)、交互转换(transform)、加载(load)至目的端的过程。ETL一词较常用在数据仓库,但其对象并不限于...

gulf ⋅ 10分钟前 ⋅ 0

redis在windows环境的后台运行方法

在后台运行,首先需要安装redis服务,命令为 redis-server.exe --service-install redis.windows.conf --loglevel verbose 启动,命令为 redis-server --service-start 停止,命令为 redis-...

程序羊 ⋅ 12分钟前 ⋅ 0

比特币现金开发者提出新的交易订单规则

本周,四位比特币现金的四位开发者和研究员:Joannes Vermorel(Lokad),AmaurySéchet(比特币ABC),Shammah Chancellor(比特币ABC)和Tomas van der Wansem(Bitcrust)共同发表了一篇关...

lpy411 ⋅ 16分钟前 ⋅ 0

vue获取input输入框的数据

用惯了jQuery,突然使用vue感觉很不习惯,有很多不同的地方,感觉是两个不同的思想来写前端的代码。jQuery是使用选择器($)选取DOM对象,对其进行赋值、取值、事件绑定等操作。而Vue则是通过...

王子城 ⋅ 18分钟前 ⋅ 0

竟然这就是面向对象的游戏设计?!

从程序角度考虑,许多 JavaScript 都基于循环和大量的 if/else 语句。在本文中,我们可了解一种更聪明的做法 — 在 JavaScript 游戏中使用面向对象来设计。本文将概述原型继承和使用 JavaSc...

柳猫 ⋅ 23分钟前 ⋅ 2

git cmd git bash

刚用到了Git,看到windows环境下有两个命令输入窗口 第一个是可视化图形界面,第二个是CMD,第三个是Bash。 Git中的Bash是基于CMD的,在CMD的基础上增添一些新的命令与功能。所以建议在使用的...

东东笔记 ⋅ 25分钟前 ⋅ 0

分布式系统CAP和Base

1、分布式系统 1.1 简介 由多台计算机和通信的软件组件通过计算机网络连接(本地网络或广域网)组成。分布式系统是建立在网络之上的软件系统。正是因为软件的特性,所以分布式系统具有高度的...

xixingzhe ⋅ 36分钟前 ⋅ 0

查看磁盘占用情况

记一次jenkins构建失败的问题 Build step 'Send build artifacts over SSH' changed build result to UNSTABLE 网上查资料都没明确表明是什么错,回忆之前处理这样的问题。第一时间想到的是不...

ManderSF ⋅ 37分钟前 ⋅ 0

数据库管理提速:SQL解析的探索与应用

前言: SQL解析是一项复杂的技术,一般都是由数据库厂商来掌握,当然也有公司专门提供SQL解析的API。SQL解析与优化是属于编译器范畴,和C语言等其他语言的解析没有本质的区别。其中分为词法分...

java高级架构牛人 ⋅ 44分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部