Oracle数据库信息收集的常用方法

-- 查看数据库版本
select * from v$version;

-- 查看实例名称和状态
select instance_name, status, host_name from v$instance;

-- 查看数据库名称和创建时间
select name, created, log_mode from v$database;

-- 查看所有参数
show parameter;

-- 查看特定参数（如内存相关）
show parameter sga;
show parameter pga;

-- 查看非默认参数
select name, value from v$parameter where isdefault = 'false';

select
    t.tablespace_name,
    round(sum(d.bytes) / 1024 / 1024, 2) as total_mb,
    round(sum(case when d.status = 'online' then d.bytes else 0 end) / 1024 / 1024, 2) as online_mb,
    round(sum(f.bytes) / 1024 / 1024, 2) as free_mb
from
    dba_tablespaces t,
    dba_data_files d,
    dba_free_space f
where
    t.tablespace_name = d.tablespace_name
    and t.tablespace_name = f.tablespace_name
group by
    t.tablespace_name;

-- 查看当前活跃会话
select
    sid, serial#, username, status,
    machine, program, sql_id
from
    v$session
where
    username is not null
order by
    status, username;

-- 查看会话等待事件
select
    sid, event, wait_class, seconds_in_wait
from
    v$session_wait
where
    wait_class != 'idle'
order by
    seconds_in_wait desc;

-- 一键收集数据库核心信息
set pagesize 100
set linesize 200
set serveroutput on

prompt ========================================
prompt 1. 数据库版本信息
prompt ========================================
select * from v$version;

prompt ========================================
prompt 2. 实例信息
prompt ========================================
select instance_name, host_name, status, version, startup_time
from v$instance;

prompt ========================================
prompt 3. 数据库基本信息
prompt ========================================
select name, db_unique_name, created, log_mode, open_mode
from v$database;

prompt ========================================
prompt 4. 内存参数
prompt ========================================
show parameter sga_target;
show parameter pga_aggregate_target;

prompt ========================================
prompt 5. 表空间使用率
prompt ========================================
select
    t.tablespace_name,
    round(sum(d.bytes) / 1024 / 1024, 2) as total_mb,
    round(sum(f.bytes) / 1024 / 1024, 2) as free_mb,
    round((1 - sum(f.bytes) / sum(d.bytes)) * 100, 2) as used_pct
from
    dba_tablespaces t,
    dba_data_files d,
    dba_free_space f
where
    t.tablespace_name = d.tablespace_name
    and t.tablespace_name = f.tablespace_name
group by
    t.tablespace_name
order by
    used_pct desc;

-- 生成 awr 报告（需要先安装 awrrpt.sql）
@?/rdbms/admin/awrrpt.sql

-- 生成 addm 报告
@?/rdbms/admin/addmrpt.sql

-- 将结果输出到文件
spool /tmp/oracle_info.txt
-- 执行收集语句
spool off