Database Support Engineer Oracle数据库支持工程师手册

本手册是Oracle数据库支持工程师的实战参考指南，涵盖33个常见故障排查场景的诊断脚本和处理方法。无论是内存泄漏、数据库挂起、性能问题还是备份恢复，都能在这里找到相应的诊断命令和解决思路。

使用说明：本手册中的脚本可直接在SQL*Plus或命令行中执行。建议收藏备用，遇到问题时快速定位相关章节。

---

目录

• 一、内存诊断：PGA/SGA内存问题、ORA-4030
• 二、对象与组件：无效对象、JVM状态
• 三、挂起与锁：Hanganalyze、Systemstate
• 四、跟踪与调试：10046、Errorstack、Oradebug
• 五、性能诊断：Redo生成、会话分析、AWR/Statspack
• 六、空间管理：ORA-1652、表空间扩展
• 七、网络诊断：SQL*Net跟踪、监听器
• 八、备份恢复：恢复诊断脚本
• 九、系统管理：Scheduler、Resource Manager
• 十、实用工具：CSSCAN、DBMS_METADATA

---

一、内存诊断

1.1 PGA内存使用分析

当遇到内存使用过高或ORA-4030错误时，使用以下脚本诊断：

-- =============================================
-- PGA内存诊断脚本
-- =============================================
SET PAGESIZE 999
SET LINESIZE 200
COLUMN memory FORMAT A30
COLUMN program FORMAT A40
COLUMN spid FORMAT A10

SPOOL pga_analysis.log

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS current_time FROM dual;

PROMPT
PROMPT ========== 各会话PGA内存使用 ==========
SELECT p.spid, 
       s.sid,
       SUBSTR(n.name,1,25) AS memory_type, 
       ROUND(s.value/1024/1024,2) AS mb
FROM v$sesstat s, v$statname n, v$process p, v$session vs
WHERE s.statistic# = n.statistic#
  AND n.name LIKE '%pga memory%' 
  AND s.sid = vs.sid
  AND vs.paddr = p.addr
ORDER BY s.value DESC;

PROMPT
PROMPT ========== PGA分配最大的进程 ==========
SELECT pid, spid, 
       SUBSTR(username,1,20) AS db_user,
       SUBSTR(program,1,40) AS program,
       ROUND(pga_used_mem/1024/1024,2) AS used_mb,
       ROUND(pga_alloc_mem/1024/1024,2) AS alloc_mb,
       ROUND(pga_freeable_mem/1024/1024,2) AS freeable_mb,
       ROUND(pga_max_mem/1024/1024,2) AS max_mb
FROM v$process 
WHERE pga_alloc_mem = (SELECT MAX(pga_alloc_mem) FROM v$process WHERE program NOT LIKE '%LGWR%');

PROMPT
PROMPT ========== PGA总使用量 ==========
SELECT ROUND(SUM(pga_alloc_mem)/1024/1024,2) AS total_pga_mb FROM v$process;

PROMPT
PROMPT ========== 进程PGA排名TOP 20 ==========
SELECT * FROM (
    SELECT spid, 
           SUBSTR(program,1,40) AS program,
           ROUND(pga_alloc_mem/1024/1024,2) AS pga_mb
    FROM v$process
    ORDER BY pga_alloc_mem DESC
) WHERE ROWNUM <= 20;

PROMPT
PROMPT ========== PGA统计信息 ==========
SELECT SUBSTR(name,1,40) AS name, value, unit FROM v$pgastat;

SPOOL OFF

1.2 Oracle总内存使用

-- 计算Oracle实例总内存使用（SGA + 所有会话PGA）
SELECT ROUND(SUM(bytes)/1024/1024,2) AS total_memory_mb 
FROM (
    SELECT bytes FROM v$sgastat
    UNION ALL
    SELECT value AS bytes 
    FROM v$sesstat s, v$statname n 
    WHERE n.statistic# = s.statistic# 
      AND n.name = 'session pga memory'
);

1.3 指定会话的内存详情

-- 查看特定会话的PGA使用
SELECT spid, 
       ROUND(pga_used_mem/1024/1024,2) AS used_mb,
       ROUND(pga_alloc_mem/1024/1024,2) AS alloc_mb
FROM v$process 
WHERE addr = (SELECT paddr FROM v$session WHERE sid = &sid AND serial# = &serial);

1.4 生成Heapdump（内存泄漏诊断）

-- 对特定进程生成堆转储
ORADEBUG SETOSPID <pid>
ORADEBUG DUMP HEAPDUMP 536870917
ORADEBUG TRACEFILE_NAME

相关文档：NOTE.233869.1 - Diagnosing and Resolving ORA-4030 error

---

二、对象与组件诊断

2.1 无效对象检查

-- =============================================
-- 无效对象诊断脚本
-- =============================================
SET PAGESIZE 1000
SET LINESIZE 150
COLUMN owner FORMAT A20
COLUMN object_name FORMAT A35
COLUMN object_type FORMAT A20
COLUMN comp_name FORMAT A40

SPOOL invalid_objects_report.log

PROMPT ========== 数据库名称 ==========
SELECT SYS_CONTEXT('USERENV','DB_NAME') AS db_name FROM dual;

PROMPT ========== 无效对象总数 ==========
SELECT COUNT(*) AS invalid_count FROM dba_objects WHERE status = 'INVALID';

PROMPT ========== 按类型和属主分组统计 ==========
SELECT owner, object_type, COUNT(*) AS cnt
FROM dba_objects 
WHERE status = 'INVALID' 
GROUP BY owner, object_type
ORDER BY owner, object_type;

PROMPT ========== 组件注册状态 ==========
SELECT comp_id, comp_name, version, status FROM dba_registry;

PROMPT ========== 无效对象详细列表 ==========
SELECT owner, object_name, object_type
FROM dba_objects 
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

SPOOL OFF

2.2 重新编译无效对象

-- 重新编译所有无效对象
@$ORACLE_HOME/rdbms/admin/utlrp.sql

-- 或手动重新编译
EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'SCHEMA_NAME');

相关文档：NOTE.300056.1 - Debug and Validate Invalid Objects

2.3 JVM状态检查

-- =============================================
-- Oracle JVM状态诊断脚本
-- =============================================
SET SERVEROUTPUT ON
SET PAGESIZE 500
SET LINESIZE 150
COLUMN comp_name FORMAT A40
COLUMN object_name FORMAT A50

SPOOL jvm_status.log

PROMPT ========== 组件状态 ==========
SELECT comp_name, version, status FROM dba_registry;

PROMPT ========== JAVA对象统计 ==========
SELECT owner, status, COUNT(*) AS cnt
FROM all_objects
WHERE object_type LIKE '%JAVA%' 
GROUP BY owner, status;

PROMPT ========== 无效JAVA对象 ==========
SELECT owner, object_type, COUNT(*) AS cnt
FROM all_objects
WHERE object_type LIKE '%JAVA%' 
  AND status <> 'VALID' 
GROUP BY owner, object_type;

PROMPT ========== DBMS_JAVA状态 ==========
SELECT owner, status, object_type, object_name 
FROM all_objects
WHERE object_name LIKE '%DBMS_JAVA%';

PROMPT ========== JAVA Pool使用 ==========
SELECT * FROM v$sgastat WHERE pool = 'java pool' OR name = 'free memory';

SHOW PARAMETER pool_size
SHOW PARAMETER sga

SPOOL OFF

相关文档：Note 842449.1 - Troubleshooting Oracle JVM

---

三、挂起与死锁诊断

3.1 Hanganalyze（挂起分析）

数据库挂起时，首先收集Hanganalyze：

-- 方法1：通过SQL*Plus
$ sqlplus / as sysdba

ORADEBUG SETMYPID
ORADEBUG UNLIMIT
ORADEBUG HANGANALYZE 3

-- 等待90秒后再次收集
-- （等待90秒）

ORADEBUG HANGANALYZE 3
ORADEBUG TRACEFILE_NAME

EXIT

3.2 Systemstate Dump

-- 收集Systemstate（建议收集3次，每次间隔90秒）
$ sqlplus / as sysdba

ORADEBUG SETMYPID
ORADEBUG UNLIMIT

ORADEBUG DUMP SYSTEMSTATE 10
-- 等待90秒
ORADEBUG DUMP SYSTEMSTATE 10
-- 等待90秒
ORADEBUG DUMP SYSTEMSTATE 10

ORADEBUG TRACEFILE_NAME

EXIT

提示：对于RAC环境，使用level 266可以收集集群范围的信息：ORADEBUG DUMP SYSTEMSTATE 266

3.3 Hanganalyze级别说明

相关文档：

• Note 61552.1 - Diagnosing Database Hanging Issues
• Note 215858.1 - Interpreting HANGANALYZE trace files
• Note 423153.1 - Understanding and Reading Systemstates

---

四、跟踪与调试

4.1 10046 SQL跟踪

会话级跟踪（当前会话）：

-- 启用10046跟踪
ALTER SESSION SET TRACEFILE_IDENTIFIER = '10046_trace';
ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET STATISTICS_LEVEL = ALL;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

-- 执行需要跟踪的SQL
SELECT * FROM your_table WHERE ...;

-- 关闭跟踪
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

跟踪其他会话：

-- 1. 找到目标会话的进程信息
SELECT p.pid AS ora_pid, p.spid AS os_pid, s.sid
FROM v$process p, v$session s
WHERE s.paddr = p.addr
  AND s.sid = &session_id;

-- 2. 使用ORADEBUG跟踪（通过OS PID）
CONNECT / AS SYSDBA
ORADEBUG SETOSPID <os_pid>
ORADEBUG UNLIMIT
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

-- 3. 关闭跟踪
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
ORADEBUG TRACEFILE_NAME

系统级跟踪（谨慎使用）：

-- 启用（影响所有会话）
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

-- 关闭
ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT OFF';

4.2 10046跟踪级别

4.3 Errorstack（错误堆栈）

-- 系统级：捕获特定错误的堆栈
-- 示例：捕获ORA-30926
ALTER SYSTEM SET EVENTS '30926 TRACE NAME ERRORSTACK LEVEL 3';

-- 等待错误发生后关闭
ALTER SYSTEM SET EVENTS '30926 TRACE NAME ERRORSTACK OFF';

-- 会话级
ALTER SESSION SET EVENTS '30926 TRACE NAME ERRORSTACK LEVEL 3';
ALTER SESSION SET EVENTS '30926 TRACE NAME ERRORSTACK OFF';

4.4 进程函数堆栈

-- 用户进程
SELECT p.pid AS ora_pid, p.spid AS os_pid 
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.sid = &sid;

ORADEBUG SETORAPID <ora_pid>
ORADEBUG DUMP ERRORSTACK 3
ORADEBUG TRACEFILE_NAME

-- 后台进程（如SMON）
SELECT p.spid FROM v$process p, v$bgprocess b
WHERE p.addr = b.paddr AND b.name = 'SMON';

ORADEBUG SETOSPID <smon_os_pid>
ORADEBUG DUMP ERRORSTACK 3
ORADEBUG TRACEFILE_NAME

4.5 操作系统级跟踪

---

五、性能诊断

5.1 高Redo生成会话

-- 按块变化量排序
SELECT s.sid, s.serial#, s.username, 
       SUBSTR(s.program,1,30) AS program,
       i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY i.block_changes DESC;

-- 按Undo使用量排序
SELECT s.sid, s.serial#, s.username, 
       SUBSTR(s.program,1,30) AS program,
       t.used_ublk AS undo_blocks, 
       t.used_urec AS undo_records
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY t.used_ublk DESC, t.used_urec DESC;

相关文档：NOTE.167492.1 - How to Find Sessions Generating Lots of Redo

5.2 会话等待事件分析

-- 当前会话等待
SELECT sid, event, seq#, p1, p2, p3, wait_time, seconds_in_wait
FROM v$session_wait
WHERE sid = &sid
ORDER BY sid;

-- 活动等待事件
SELECT sid, event, seq#, p1, p2, p3 
FROM v$session_wait 
WHERE wait_time = 0 
  AND event NOT LIKE '%message%';

-- 会话状态统计
SELECT status, COUNT(*) FROM v$session GROUP BY status;

5.3 会话执行的SQL

-- 查看会话正在执行的SQL
SELECT s.sid, s.status, q.sql_text
FROM v$session s, v$sqltext q
WHERE s.sql_hash_value = q.hash_value
  AND s.sql_address = q.address
  AND s.sid = &sid
ORDER BY q.piece;

-- 通过OS PID查找会话
SELECT s.sid, s.serial#, s.username, s.status, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND p.spid = '&os_pid';

5.4 AWR报告

-- 生成AWR报告（交互式）
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- 生成特定时间段的AWR报告
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

5.5 Statspack报告

-- 手动创建快照
CONNECT perfstat/perfstat
EXECUTE statspack.snap;

-- 生成报告
@$ORACLE_HOME/rdbms/admin/spreport.sql

---

六、空间管理

6.1 ORA-1652 临时表空间诊断

-- 查看临时段使用情况（10g+）
SELECT a.username, a.sid, a.serial#, a.osuser, 
       b.tablespace, b.blocks,
       ROUND(b.blocks * 8 / 1024, 2) AS mb,
       c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
  AND c.address = a.sql_address
  AND c.hash_value = a.sql_hash_value
ORDER BY b.blocks DESC;

-- 查看临时段统计
SELECT tablespace_name, extent_size, total_extents, 
       used_extents, free_extents, max_used_size
FROM v$sort_segment;

-- 设置事件捕获ORA-1652
ALTER SYSTEM SET EVENTS '1652 TRACE NAME ERRORSTACK LEVEL 3';
-- 错误发生后关闭
ALTER SYSTEM SET EVENTS '1652 TRACE NAME ERRORSTACK OFF';

相关文档：Note 793380.1 - ORA-1652 Error Troubleshooting

6.2 表空间扩展错误诊断

-- 查看表空间最大连续空间
SELECT MAX(bytes)/1024/1024 AS max_free_mb 
FROM dba_free_space 
WHERE tablespace_name = '&tablespace_name';

-- 查看段的NEXT_EXTENT大小
SELECT segment_name, segment_type, owner,
       next_extent/1024/1024 AS next_extent_mb,
       pct_increase
FROM dba_segments 
WHERE segment_name = '&segment_name';

-- 检查表空间管理类型
SELECT tablespace_name, extent_management, segment_space_management
FROM dba_tablespaces 
WHERE tablespace_name = '&tablespace_name';

-- 检查数据文件自动扩展状态
SELECT file_name, 
       ROUND(bytes/1024/1024,2) AS size_mb,
       autoextensible,
       ROUND(maxbytes/1024/1024,2) AS max_mb
FROM dba_data_files 
WHERE tablespace_name = '&tablespace_name';

-- 检查临时文件
SELECT file_name, 
       ROUND(bytes/1024/1024,2) AS size_mb,
       autoextensible,
       ROUND(maxbytes/1024/1024,2) AS max_mb
FROM dba_temp_files 
WHERE tablespace_name = '&tablespace_name';

6.3 表空间告警阈值

-- 查看当前告警
SELECT reason, metric_value, message_type,
       TO_CHAR(creation_time,'YYYY-MM-DD HH24:MI:SS') AS creation_time
FROM dba_outstanding_alerts;

-- 查看表空间阈值设置
SELECT metrics_name, 
       warning_operator, warning_value,
       critical_operator, critical_value,
       object_type, object_name, status
FROM dba_thresholds
WHERE metrics_name LIKE '%Tablespace%';

-- 查看表空间使用率
SELECT tablespace_name, 
       used_space,
       tablespace_size,
       ROUND(used_percent,2) AS used_pct
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

---

七、网络诊断

7.1 客户端SQL*Net跟踪

在客户端sqlnet.ora中添加（位于%TNS_ADMIN%或$ORACLE_HOME/network/admin）：

# 客户端跟踪配置
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client_trace
TRACE_DIRECTORY_CLIENT = /tmp/oracle_trace
TRACE_TIMESTAMP_CLIENT = ON
TRACE_UNIQUE_CLIENT = ON

# 11g客户端需要添加
DIAG_ADR_ENABLED = OFF

7.2 服务器端SQL*Net跟踪

在服务器sqlnet.ora中添加：

# 服务器端跟踪配置
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = server_trace
TRACE_DIRECTORY_SERVER = /tmp/oracle_trace
TRACE_TIMESTAMP_SERVER = ON

# 循环跟踪（用于长时间或间歇性问题）
TRACE_FILELEN_SERVER = 10240    # KB
TRACE_FILENO_SERVER = 5         # 文件数量

# 11g服务器需要添加
DIAG_ADR_ENABLED = OFF

7.3 监听器跟踪

在listener.ora中添加（将LISTENER替换为实际监听器名称）：

# 监听器跟踪配置
TRACE_FILE_LISTENER = listener_trace
TRACE_LEVEL_LISTENER = SUPPORT
TRACE_TIMESTAMP_LISTENER = TRUE
TRACE_DIRECTORY_LISTENER = /tmp/oracle_trace

# 循环跟踪
TRACE_FILELEN_LISTENER = 10240
TRACE_FILENO_LISTENER = 5

# 11g监听器
DIAG_ADR_ENABLED_LISTENER = OFF

相关文档：Note 395525.1 - How to Enable Oracle SQLNet Tracing

---

八、备份恢复诊断

8.1 恢复状态检查脚本

-- =============================================
-- 备份恢复诊断脚本
-- =============================================
SET PAGESIZE 200
SET LINESIZE 200
COLUMN name FORMAT A60
COLUMN status FORMAT A10
COLUMN recover FORMAT A10
COLUMN fuzzy FORMAT A5

SPOOL recovery_diag.log

SHOW PARAMETER pfile;
ARCHIVE LOG LIST;

PROMPT ========== 备份状态 ==========
SELECT * FROM v$backup;

PROMPT ========== 数据文件状态 ==========
SELECT file#, status, SUBSTR(name,1,60) AS name FROM v$datafile;

PROMPT ========== 检查点一致性 ==========
SELECT DISTINCT checkpoint_change# FROM v$datafile_header;

PROMPT ========== 数据文件头详情 ==========
SELECT status, resetlogs_change#, resetlogs_time,
       checkpoint_change#,
       TO_CHAR(checkpoint_time,'YYYY-MM-DD HH24:MI:SS') AS checkpoint_time,
       COUNT(*) AS file_count
FROM v$datafile_header
GROUP BY status, resetlogs_change#, resetlogs_time, 
         checkpoint_change#, checkpoint_time
ORDER BY checkpoint_change#;

PROMPT ========== 恢复状态 ==========
SELECT SUBSTR(name,1,50) AS name, recover, fuzzy, 
       checkpoint_change#, resetlogs_change#
FROM v$datafile_header;

PROMPT ========== 数据库状态 ==========
SELECT name, open_mode, checkpoint_change#, archive_change# FROM v$database;

PROMPT ========== Redo日志组 ==========
SELECT group#, thread#, sequence#, members, archived, status, first_change#
FROM v$log;

PROMPT ========== Redo日志成员 ==========
SELECT group#, SUBSTR(member,1,60) AS member FROM v$logfile;

PROMPT ========== 需要恢复的文件 ==========
SELECT * FROM v$recover_file;

PROMPT ========== 需要的归档日志 ==========
SELECT * FROM v$recovery_log;

SPOOL OFF

8.2 数据文件头信息（X$表）

-- 详细的文件头信息
SELECT hxfil AS file_no,
       SUBSTR(hxfnm,1,50) AS file_name,
       fhtyp AS type,
       hxerr AS validity,
       fhscn AS scn,
       fhtnm AS tablespace_name,
       fhsta AS status,
       fhrba_seq AS sequence
FROM x$kcvfh;

---

九、系统管理

9.1 Scheduler诊断

-- =============================================
-- 调度器诊断脚本
-- =============================================
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZR';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- 调度窗口
SELECT window_name, window_priority, enabled, resource_plan,
       next_start_date, duration
FROM dba_scheduler_windows;

-- 窗口运行历史
SELECT log_date, window_name, req_start_date, actual_start_date,
       window_duration, actual_duration
FROM dba_scheduler_window_details
WHERE ROWNUM < 20
ORDER BY log_date DESC;

-- 作业运行历史
SELECT log_date, owner, job_name, status, error#,
       req_start_date, actual_start_date
FROM dba_scheduler_job_run_details
WHERE log_date > SYSDATE - 7
ORDER BY log_date DESC;

SHOW PARAMETER job_queue_processes;

相关文档：Note 783357.1 - Troubleshooting DBMS_SCHEDULER and DBMS_JOB

9.2 Resource Manager

-- 当前活动计划
SELECT * FROM v$rsrc_plan;

-- 所有资源计划
SELECT plan, comments, status FROM dba_rsrc_plans;

-- 消费者组
SELECT consumer_group, comments, status FROM dba_rsrc_consumer_groups;

-- 计划指令
SELECT plan, group_or_subplan, type, 
       cpu_p1, cpu_p2, cpu_p3, cpu_p4,
       parallel_degree_limit_p1, status
FROM dba_rsrc_plan_directives;

9.3 会话管理

-- 按用户统计会话
SELECT username, COUNT(*) AS sessions FROM v$session GROUP BY username ORDER BY 2 DESC;

-- 按机器统计
SELECT machine, COUNT(*) AS sessions FROM v$session GROUP BY machine ORDER BY 2 DESC;

-- 按程序统计
SELECT program, COUNT(*) AS sessions FROM v$session GROUP BY program ORDER BY 2 DESC;

-- 按状态统计
SELECT status, COUNT(*) AS sessions FROM v$session GROUP BY status;

-- 孤立进程
SELECT COUNT(*) AS orphan_processes 
FROM v$process 
WHERE addr NOT IN (SELECT paddr FROM v$session);

-- 不活动会话
SELECT username, program, sid, serial# 
FROM v$session 
WHERE status = 'INACTIVE';

9.4 共享内存和信号量

# 查看共享内存段
ipcs -m

# 删除共享内存段
ipcrm -m <shmid>

# 查看信号量
ipcs -s

# 删除信号量
ipcrm -s <semid>

# 查看Oracle拥有的资源
ipcs -a | grep oracle

---

十、实用工具与参考

10.1 DBMS_METADATA导出DDL

-- 获取表DDL
SET LONG 2000000
SELECT DBMS_METADATA.GET_DDL('TABLE', '&table_name', '&owner') FROM dual;

-- 获取表空间DDL
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', '&tablespace_name') FROM dual;

-- 获取索引DDL
SELECT DBMS_METADATA.GET_DDL('INDEX', '&index_name', '&owner') FROM dual;

10.2 隐藏参数查询

-- 查询隐藏参数值
SELECT a.ksppinm AS parameter,
       b.ksppstvl AS session_value,
       c.ksppstvl AS instance_value,
       a.ksppdesc AS description
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx 
  AND a.indx = c.indx
  AND a.ksppinm LIKE '%&parameter_name%';

10.3 用户默认表空间

SELECT username, user_id, default_tablespace, temporary_tablespace
FROM dba_users
WHERE username IN ('&user1', '&user2');

10.4 Shutdown Immediate挂起诊断

-- 设置跟踪后执行shutdown
CONNECT / AS SYSDBA
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
ALTER SESSION SET EVENTS '10400 TRACE NAME CONTEXT FOREVER, LEVEL 1';  -- 每5分钟dump systemstate
SHUTDOWN IMMEDIATE;

相关文档：

• Note 164504.1 - How to Check Why Shutdown Immediate Hangs
• Note 375935.1 - What To Do When 'shutdown immediate' Hangs

10.5 重要事件参数

10.6 操作系统日志位置

10.7 Catalog/Catproc重建

CONNECT / AS SYSDBA
STARTUP MIGRATE

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql

SHUTDOWN IMMEDIATE
STARTUP

@$ORACLE_HOME/rdbms/admin/utlrp.sql

---

快速参考卡

常用诊断命令速查