ORACLE 11 数据库管理_oracle chuck-优快云博客

本文介绍如何在Oracle数据库中获取当前trace文件路径、查询慢日志TOP10及进行表碎片整理的方法。通过SQL语句定位性能瓶颈，并通过调整表结构减少碎片，提升数据库运行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

获取当前trace目录

select u_dump.value || '/' || db_name.value || '_ora_' || v$process.spid ||
       nvl2(v$process.traceid, '_' || v$process.traceid, null) || '.trc' "Trace File"
  from v$parameter u_dump
 cross join v$parameter db_name
 cross join v$process
  join v$session
    on v$process.addr = v$session.paddr
 where u_dump.name = 'user_dump_dest'
   and db_name.name = 'db_name'
   and v$session.audsid = sys_context('userenv', 'sessionid');

获取慢日志TOP10

select *
  from (select active_session_history.sql_id,
               dba_users.username,
               sqlarea.sql_text,
               sum(active_session_history.wait_time +
                   active_session_history.time_waited) ttl_wait_time
          from v$active_session_history active_session_history,
               v$sqlarea                sqlarea,
               dba_users                dba_users
         where active_session_history.sample_time between sysdate - 1 / 24 and
               sysdate
           and active_session_history.sql_id = sqlarea.sql_id
           and active_session_history.user_id = dba_users.user_id
         group by active_session_history.sql_id,
                  sqlarea.sql_text,
                  dba_users.username
         order by 4 desc)
 where rownum <= 10;

表碎片整理

exec dbms_stats.gather_schema_stats(ownname=>'REPORT');            --重新分析表

SELECT table_name,
   ROUND((blocks * 8), 2) "高水位空间 k",
   ROUND((num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
   ROUND((blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
   ROUND((blocks * 8 - (num_rows * avg_row_len / 1024) -
         blocks * 8 * 10 / 100),
         2) "浪费空间 k"
  FROM dba_tables
 WHERE table_name = 'MIS_LINKED_MX';                              --超过25%需要整理碎片

alter table MIS_LINKED_MX enable row movement;
alter table MIS_LINKED_MX shrink space compact;                   --在线整理表碎片
alter table MIS_LINKED_MX shrink space;                           --整理表并重置高水位线