ORACLE常用命令

--日志查询命令：
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
       P.SPID || '.trc' TRACE_FILE_NAME
  FROM (SELECT P.SPID
          FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
         WHERE M.STATISTIC# = 1
           AND S.SID = M.SID
           AND P.ADDR = S.PADDR) P,
       (SELECT T.INSTANCE
          FROM V$THREAD T, V$PARAMETER V
         WHERE V.NAME = 'thread'
           AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))) I,
       (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') T


--确定数据文件可以收缩的空间及命令：
SELECT A.FILE#,
       A.NAME,
       A.BYTES / 1024 / 1024 CURRENTMB,
       CEIL(HWM * A.BLOCK_SIZE) / 1024 / 1024 RESIZETO,
       (A.BYTES - HWM * A.BLOCK_SIZE) / 1024 / 1024 RELEASEMB,
       'alter database datafile ''' || A.NAME || ''' resize ' ||
       CEIL(HWM * A.BLOCK_SIZE) / 1024 / 1024 || 'M;' RESIZECMD
  FROM V$DATAFILE A,
       (SELECT FILE_ID, MAX(BLOCK_ID + BLOCKS - 1) HWM
          FROM DBA_EXTENTS
         GROUP BY FILE_ID) B
 WHERE A.FILE# = B.FILE_ID(+)
   AND (A.BYTES - HWM * A.BLOCK_SIZE) > 0
 ORDER BY 5;


----当前数据库逻辑读最高的10个对象
SELECT *
  FROM (SELECT OBJECT_NAME, STATISTIC_NAME, VALUE
          FROM V$SEGMENT_STATISTICS
         WHERE STATISTIC_NAME = 'logical reads'
         ORDER BY 3 DESC)
 WHERE ROWNUM < 11;


--从V$SQLAREA中查询最占用资源的查询
SELECT B.USERNAME USERNAME,
       A.DISK_READS READS,
       A.EXECUTIONS EXEC,
       A.DISK_READS / DECODE(A.EXECUTIONS, 0, 1, A.EXECUTIONS) RDS_EXEC_RATIO,
       A.SQL_TEXT STATEMENT
  FROM V$SQLAREA A, DBA_USERS B
 WHERE A.PARSING_USER_ID = B.USER_ID
   AND A.DISK_READS > 10000
 ORDER BY A.DISK_READS DESC;


----使用频率最高的10个查询
SELECT SQL_TEXT, EXECUTIONS
  FROM (SELECT SQL_TEXT,
               EXECUTIONS,
               RANK() OVER(ORDER BY EXECUTIONS DESC) EXEC_RANK
          FROM V$SQL)
 WHERE EXEC_RANK <= 10;
----消耗磁盘读取最多的SQL TOP 10
SELECT DISK_READS, SQL_TEXT
  FROM (SELECT SQL_TEXT,
               DISK_READS,
               DENSE_RANK() OVER(ORDER BY DISK_READS DESC) DISK_READS_RANK
          FROM V$SQL)
 WHERE DISK_READS_RANK <= 10;


--- 二进制地址转储文件号和块号(select getbfno('') from dual):
CREATE OR REPLACE FUNCTION getbfno (p_dba IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_str   VARCHAR2 (255) DEFAULT NULL;
   l_fno   VARCHAR2 (15);
   l_bno   VARCHAR2 (15);
BEGIN
   l_fno :=
      DBMS_UTILITY.data_block_address_file (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                       'xxxxxxxx'
                                                      )
                                           );
   l_bno :=
      DBMS_UTILITY.data_block_address_block (TO_NUMBER (LTRIM (p_dba, '0x'),
                                                        'xxxxxxxx'
                                                       )
                                            );
   l_str :=
         'datafile# is:'
      || l_fno
      || CHR (10)
      || 'datablock is:'
      || l_bno
      || CHR (10)
      || 'dump command:alter system dump datafile '
      || l_fno
      || ' block '
      || l_bno
      || ';';
   RETURN l_str;
END;
/