# 1. 当前存储使用情况
SQL> SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024/1024, 2) size_gb,
ROUND(SUM(maxbytes)/1024/1024/1024, 2) max_gb,
ROUND((SUM(bytes) – SUM(free_bytes))/1024/1024/1024, 2) used_gb,
ROUND((1 – SUM(free_bytes)/SUM(bytes)) * 100, 2) used_pct
FROM (
SELECT tablespace_name, bytes,
NVL(maxbytes, bytes) maxbytes,
(SELECT SUM(bytes) FROM dba_free_space f
WHERE f.tablespace_name = d.tablespace_name) free_bytes
FROM dba_data_files d
)
GROUP BY tablespace_name
ORDER BY used_pct DESC;

# 2. 数据增长趋势
SQL> SELECT snap_id,
TO_CHAR(end_interval_time, ‘YYYY-MM-DD’) snap_date,
ROUND(space_used/1024/1024/1024, 2) used_gb,
ROUND(space_used – LAG(space_used) OVER (ORDER BY snap_id))/1024/1024/1024 growth_gb
FROM (
SELECT s.snap_id, s.end_interval_time,
SUM(t.space_used) space_used
FROM dba_hist_snapshot s, dba_hist_tbspc_space_usage t
WHERE s.snap_id = t.snap_id
AND s.end_interval_time > SYSDATE – 30
GROUP BY s.snap_id, s.end_interval_time
)
ORDER BY snap_id;

# 3. 表增长分析
SQL> SELECT owner, segment_name, segment_type,
ROUND(bytes/1024/1024, 2) size_mb,
blocks
FROM dba_segments
WHERE owner = ‘FGEDU’
ORDER BY bytes DESC;

# 4. 索引增长分析
SQL> SELECT owner, index_name, table_name,
ROUND(bytes/1024/1024, 2) size_mb
FROM dba_segments
WHERE segment_type = ‘INDEX’
AND owner = ‘FGEDU’
ORDER BY bytes DESC;

# 1. CPU使用分析
SQL> SELECT snap_id,
TO_CHAR(end_interval_time, ‘YYYY-MM-DD HH24:MI’) snap_time,
ROUND(avg_cpu_usage, 2) cpu_usage_pct
FROM (
SELECT s.snap_id, s.end_interval_time,
AVG(e.value) avg_cpu_usage
FROM dba_hist_snapshot s, dba_hist_sysstat e
WHERE s.snap_id = e.snap_id
AND e.stat_name = ‘CPU used by this session’
AND s.end_interval_time > SYSDATE – 7
GROUP BY s.snap_id, s.end_interval_time
)
ORDER BY snap_id;

# 2. 内存使用分析
SQL> SELECT component,
ROUND(current_size/1024/1024, 2) size_mb,
ROUND(min_size/1024/1024, 2) min_mb,
ROUND(max_size/1024/1024, 2) max_mb
FROM v$sga_dynamic_components;

# 3. IO性能分析
SQL> SELECT name,
ROUND(phyrds/1024, 2) reads_k,
ROUND(phywrts/1024, 2) writes_k,
ROUND(phyblkrd/1024, 2) blks_read_k,
ROUND(phyblkwrt/1024, 2) blks_write_k
FROM v$filestat f, v$datafile d
WHERE f.file# = d.file#
ORDER BY phyrds + phywrts DESC;

# 4. 会话并发分析
SQL> SELECT TO_CHAR(sample_time, ‘YYYY-MM-DD HH24’) hour,
COUNT(DISTINCT session_id) max_sessions
FROM v$active_session_history
WHERE sample_time > SYSDATE – 7
GROUP BY TO_CHAR(sample_time, ‘YYYY-MM-DD HH24’)
ORDER BY max_sessions DESC;

# 1. 计算日均增长量
SQL> SELECT ROUND((end_size – start_size) / days, 2) daily_growth_mb
FROM (
SELECT MIN(space_used) start_size,
MAX(space_used) end_size,
MAX(snap_id) – MIN(snap_id) days
FROM (
SELECT s.snap_id, SUM(t.space_used)/1024/1024 space_used
FROM dba_hist_snapshot s, dba_hist_tbspc_space_usage t
WHERE s.snap_id = t.snap_id
AND s.end_interval_time > SYSDATE – 30
GROUP BY s.snap_id
)
);

# 2. 预测未来容量需求
SQL> SELECT
ROUND(current_size + (daily_growth * 30), 2) month_1_gb,
ROUND(current_size + (daily_growth * 90), 2) month_3_gb,
ROUND(current_size + (daily_growth * 180), 2) month_6_gb,
ROUND(current_size + (daily_growth * 365), 2) year_1_gb
FROM (
SELECT
SUM(bytes)/1024/1024/1024 current_size,
(SELECT daily_growth FROM daily_growth_table) daily_growth
FROM dba_data_files
);

# 3. 预测空间耗尽时间
SQL> SELECT tablespace_name,
ROUND(free_space/1024/1024, 2) free_mb,
ROUND(daily_growth, 2) daily_growth_mb,
ROUND(free_space/daily_growth, 0) days_until_full
FROM (
SELECT tablespace_name,
SUM(bytes) – (SELECT SUM(bytes) FROM dba_free_space f
WHERE f.tablespace_name = d.tablespace_name) free_space,
(SELECT avg_daily_growth FROM growth_history
WHERE tablespace_name = d.tablespace_name) daily_growth
FROM dba_data_files d
GROUP BY tablespace_name
)
WHERE daily_growth > 0
ORDER BY days_until_full;

# 1. CPU使用趋势预测
SQL> SELECT
ROUND(current_cpu + (cpu_growth * 30), 2) month_1_cpu,
ROUND(current_cpu + (cpu_growth * 90), 2) month_3_cpu,
ROUND(current_cpu + (cpu_growth * 180), 2) month_6_cpu
FROM (
SELECT
AVG(cpu_usage) current_cpu,
(SELECT avg_daily_growth FROM cpu_growth_table) cpu_growth
FROM cpu_usage_history
WHERE sample_time > SYSDATE – 7
);

# 2. 会话增长预测
SQL> SELECT
ROUND(current_sessions + (session_growth * 30), 0) month_1_sessions,
ROUND(current_sessions + (session_growth * 90), 0) month_3_sessions,
ROUND(current_sessions + (session_growth * 180), 0) month_6_sessions
FROM (
SELECT
MAX(session_count) current_sessions,
(SELECT avg_daily_growth FROM session_growth_table) session_growth
FROM session_history
WHERE sample_time > SYSDATE – 7
);

# 3. IO增长预测
SQL> SELECT
ROUND(current_iops + (iops_growth * 30), 0) month_1_iops,
ROUND(current_iops + (iops_growth * 90), 0) month_3_iops,
ROUND(current_iops + (iops_growth * 180), 0) month_6_iops
FROM (
SELECT
AVG(iops) current_iops,
(SELECT avg_daily_growth FROM iops_growth_table) iops_growth
FROM iops_history
WHERE sample_time > SYSDATE – 7
);

# 1. 存储扩容
# 添加数据文件
SQL> ALTER TABLESPACE fgedu_data
ADD DATAFILE ‘/oradata/fgedudb/fgedu_data02.dbf’
SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

# 扩展现有数据文件
SQL> ALTER DATABASE DATAFILE ‘/oradata/fgedudb/fgedu_data01.dbf’
RESIZE 50G;

# 2. 内存扩容
SQL> ALTER SYSTEM SET sga_target = 8G SCOPE = SPFILE;
SQL> ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE = SPFILE;

# 3. 会话扩容
SQL> ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;
SQL> ALTER SYSTEM SET sessions = 800 SCOPE = SPFILE;

# 4. 表空间自动扩展
SQL> ALTER DATABASE DATAFILE ‘/oradata/fgedudb/fgedu_data01.dbf’
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;