oracle常用命令

最新推荐文章于 2024-03-14 18:33:43 发布

原创最新推荐文章于 2024-03-14 18:33:43 发布 · 317 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文提供了Oracle数据库的全面管理指南，包括表空间查询、备份详情查看、每小时日志数统计及备份执行进度监控等关键操作。通过SQL查询示例，深入理解数据库资源分配、使用情况及性能监控。

文章目录

表空间查询，包含临时表空间

--表空间查询，包含临时表空间
select a.tablespace_name,
       round(a.bytes_alloc / 1024 / 1024) megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       round(maxbytes / 1048576) Max
  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
 where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 -
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) /
             1048576) max
  from sys.v_$TEMP_SPACE_HEADER h,
       sys.v_$Temp_extent_pool  p,
       dba_temp_files           f
 where p.file_id(+) = h.file_id
   and p.tablespace_name(+) = h.tablespace_name
   and f.file_id = h.file_id
   and f.tablespace_name = h.tablespace_name
 group by h.tablespace_name
 ORDER BY 1 
/

查看备份详情

COL STATUS FORMAT a25 
COL hours    FORMAT 999.999
COL in_sec FORMAT a10 
COL out_sec FORMAT a10 
COL TIME_TAKEN_DISPLAY FORMAT a10 
COL in_size  FORMAT a10 
COL out_size FORMAT a10 
SELECT SESSION_KEY,
       INPUT_TYPE,
       STATUS,
       To_char(START_TIME, 'yyyy-mm-dd hh24:mi') start_time,
       To_char(END_TIME, 'yyyy-mm-dd hh24:mi')   end_time,
       ELAPSED_SECONDS / 3600                    hours,
       INPUT_BYTES_PER_SEC_DISPLAY               in_sec,
       OUTPUT_BYTES_PER_SEC_DISPLAY              out_sec,
       INPUT_BYTES_DISPLAY                       in_size,
       OUTPUT_BYTES_DISPLAY                      out_size
FROM   v$RMAN_BACKUP_JOB_DETAILS
ORDER  BY SESSION_KEY;

每小时日志数

column h0 format 999
column h1 format 999 
column h2 format 999  
column h3 format 999  
column h4 format 999  
column h5 format 999  
column h6 format 999  
column h7 format 999  
column h8 format 999  
column h9 format 999  
column h10 format 999  
column h11 format 999  
column h12 format 999  
column h13 format 999  
column h14 format 999  
column h15 format 999  
column h16 format 999  
column h17 format 999  
column h18 format 999  
column h19 format 999  
column h20 format 999  
column h21 format 999  
column h22 format 999  
column h23 format 999  
column avg format 999.99  
column day format a6
SELECT TRUNC(first_time) "Date",
       TO_CHAR(first_time, 'Dy') "Day",
       COUNT(1) "Total",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) h0,
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) "h1",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) "h2",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) "h3",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) "h4",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) "h5",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) "h6",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) "h7",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) "h8",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) "h9",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) "h10",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) "h11",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) "h12",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) "h13",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) "h14",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) "h15",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) "h16",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) "h17",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) "h18",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) "h19",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) "h20",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) "h21",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) "h22",
       SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) "h23",
       ROUND(COUNT(1) / 24, 2) "Avg"
  FROM gv$log_history
 WHERE first_time >= trunc(SYSDATE) - 30
   and thread# = inst_id
 GROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy')
 ORDER BY 1;

备份执行进度

SELECT opname,
       sid,
       serial#,
       context,
       sofar,
       totalwork,
       round(sofar / totalwork * 100, 2) "% Complete"
  FROM V$SESSION_LONGOPS
 WHERE opname LIKE 'RMAN:%'
   AND opname NOT LIKE 'RMAN: aggregate%'
   AND totalwork != 0;