Oracle 经典常用查询

一、Query tablespaces usage details

SELECT d.tablespace_name

      ,space "SUM_SPACE(M)"

       ,blocks sum_blocks

       ,space - nvl(free_space, 0) "USED_SPACE(M)"

       ,round((1 - nvl(free_space, 0) / space) * 100, 2) "USED_RATE(%)"

       ,free_space "FREE_SPACE(M)"

  FROM (SELECT tablespace_name

              ,round(SUM(bytes) / (1024 * 1024), 2) space

              ,SUM(blocks) blocks

          FROM dba_data_files

         GROUP BY tablespace_name) d

      ,(SELECT tablespace_name

              ,round(SUM(bytes) / (1024 * 1024), 2) free_space

          FROM dba_free_space

         GROUP BY tablespace_name) f

 WHERE d.tablespace_name = f.tablespace_name(+)

UNION ALL --if have tempfile

SELECT d.tablespace_name

      ,space "SUM_SPACE(M)"

       ,blocks sum_blocks

       ,used_space "USED_SPACE(M)"

       ,round(nvl(used_space, 0) / space * 100, 2) "USED_RATE(%)"

       ,nvl(free_space, 0) "FREE_SPACE(M)"

  FROM (SELECT tablespace_name

              ,round(SUM(bytes) / (1024 * 1024), 2) space

              ,SUM(blocks) blocks

          FROM dba_temp_files

         GROUP BY tablespace_name) d

      ,(SELECT tablespace_name

              ,round(SUM(bytes_used) / (1024 * 1024), 2) used_space

              ,round(SUM(bytes_free) / (1024 * 1024), 2) free_space

          FROM v$temp_space_header

         GROUP BY tablespace_name) f

 WHERE d.tablespace_name = f.tablespace_name(+)

二、Query resource,SGA,Invalid objects,

select * from v$resource_limit;

select * from v$sgastat;

select * from dba_objects t where t.status='INVALID';

三、Query request status

      SELECT R.REQUEST_ID 请求号

      ,FLV2.MEANING 请求阶段

      ,FLV.MEANING 请求状态

      ,R.REQUEST_DATE 提交日期

      --,R.REQUESTED_START_DATE 请求开始日期

      ,R.ACTUAL_START_DATE 请求开始日期

      --,R.ACTUAL_COMPLETION_DATE 请求结束时间

      ,DECODE(R.DESCRIPTION

             ,NULL

             ,PT.USER_CONCURRENT_PROGRAM_NAME

             ,R.DESCRIPTION || ' (' || PT.USER_CONCURRENT_PROGRAM_NAME || ')') 请求名称

      ,U.USER_NAME 用户帐号

      ,U.DESCRIPTION 用户说明

      ,(R.Actual_Completion_Date - R.ACTUAL_START_DATE)*24*60 Completion_Date

      --,r.request_type

  FROM FND_CONCURRENT_PROGRAMS_TL PT

      ,FND_CONCURRENT_PROGRAMS    PB

      ,FND_USER                   U

      ,FND_PRINTER_STYLES_TL      S

      ,FND_CONCURRENT_REQUESTS    R

      ,FND_LOOKUP_VALUES          FLV

      ,FND_LOOKUP_VALUES          FLV2

 WHERE PB.APPLICATION_ID = R.PROGRAM_APPLICATION_ID

   AND PB.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID

   AND PB.APPLICATION_ID = PT.APPLICATION_ID

   AND PB.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID

   AND PT.LANGUAGE = 'ZHS'

   AND U.USER_ID = R.REQUESTED_BY

   AND S.PRINTER_STYLE_NAME(+) = R.PRINT_STYLE

   AND S.LANGUAGE(+) = 'ZHS'

   AND R.Status_Code = FLV.LOOKUP_CODE

   AND FLV.LOOKUP_TYPE = 'CP_STATUS_CODE' --请求状态

   AND FLV.LANGUAGE = 'ZHS'

   AND FLV.DESCRIPTION IS NULL

   AND R.Phase_Code = FLV2.LOOKUP_CODE

   --AND R.STATUS_CODE IN ('C','I','R','Q') --正常/正常 (1)/正常 (2)/正在等待

   AND FLV2.LOOKUP_TYPE = 'CP_PHASE_CODE' --请求阶段

   AND FLV2.LANGUAGE = 'ZHS'

   AND FLV2.DESCRIPTION IS NULL

   /*AND R.PHASE_CODE = 'R' --'R' --运行中*/

   /*AND TRUNC(R.ACTUAL_START_DATE) < TRUNC(SYSDATE)*/

   AND TO_CHAR(R.ACTUAL_START_DATE,'YYYYMMDD') >= '20100101'

   AND TRUNC(R.ACTUAL_START_DATE) <= TRUNC(SYSDATE)

   AND NVL(R.REQUEST_TYPE,'X') != 'S'

   ORDER BY  (R.Actual_Completion_Date - R.ACTUAL_START_DATE)*24*60 DESC

四、查询表空间数据文件是否自动增加容量

    select file_name,autoextensible,increment_by from dba_data_files

五、Query&alter tablespaces example

  --query apps_undots1 tablespaces 

select d.FILE_NAME,d.BYTES/1024/1024 bytes

  from dba_data_files d

 where d.TABLESPACE_NAME='APPS_UNDOTS1';

--alter apps_undots1 spaces

alter database datafile '/db/prod/db/apps_st/data/undo01.dbf' resize 3000m;

六、Who use tablespaces

SELECT se.username, se.SID, se.serial#, 

se.sql_address, se.machine, se.program, 

su.TABLESPACE,su.segtype,  su.CONTENTS

FROM v$session se, v$sort_usage su

WHERE se.saddr = su.session_addr

七、Top20 segments

select dsa.owner

      ,dsa.segment_name

      ,dsa.segment_type

      ,dsa.bytes

 from (

      select ds.owner

            ,ds.segment_name

            ,ds.segment_type

            ,ds.bytes/1024/1024 bytes

        from dba_segments ds

       where ds.owner <> 'SYS'

        order by ds.bytes desc 

       ) dsa

 where  rownum <= 20

八、Query active sessions

SELECT NVL(s.username, '(oracle)') AS username,

       s.osuser,

       s.sid,

       s.serial#,

       p.spid,

       s.lockwait,

       s.status,

       s.module,

       s.machine,

       s.program,

       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time

FROM   v$session s,

       v$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'