-- 查看所有表空間使用率（按使用率倒序）
SELECT 
  df.tablespace_name "表空間名",
  ROUND(SUM(df.bytes) / 1024 / 1024, 2) "總大小(MB)",
  ROUND(SUM(fs.bytes) / 1024 / 1024, 2) "剩余空間(MB)",
  ROUND((SUM(df.bytes) - SUM(fs.bytes)) / SUM(df.bytes) * 100, 2) "使用率(%)",
  COUNT(*) "數(shù)據(jù)文件數(shù)"
FROM dba_data_files df
LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id
GROUP BY df.tablespace_name
ORDER BY "使用率(%)" DESC;

表空間名     | 總大小(MB) | 剩余空間(MB) | 使用率(%) | 數(shù)據(jù)文件數(shù)
-------------|------------|--------------|-----------|------------
USERS        | 10240.00   | 12.50        | 99.88     | 1
SYSTEM       | 800.00     | 156.20       | 80.48     | 1
SYSAUX       | 2048.00    | 312.40       | 84.72     | 1

-- 查看 USERS 表空間中占用前 20 的段（Segment）
SELECT 
  owner "所有者",
  segment_name "段名",
  segment_type "類型",
  ROUND(bytes / 1024 / 1024, 2) "大小(MB)",
  extents "區(qū)數(shù)",
  blocks "塊數(shù)"
FROM dba_segments 
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- 示例：檢查大表 orders 的數(shù)據(jù)新鮮度（假設(shè)含 order_date 字段）
SELECT 
  COUNT(*) "總記錄數(shù)",
  COUNT(CASE WHEN order_date >= DATE '2024-01-01' THEN 1 END) "2024年新單",
  COUNT(CASE WHEN order_date < DATE '2022-01-01' THEN 1 END) "2022年前舊單",
  ROUND(COUNT(CASE WHEN order_date < DATE '2022-01-01' THEN 1 END) * 100 / COUNT(*), 2) "舊單占比(%)"
FROM orders;

-- 查看 USERS 表空間的數(shù)據(jù)文件路徑和當(dāng)前大小
SELECT file_name, bytes/1024/1024 "當(dāng)前大小(MB)", autoextensible, maxbytes/1024/1024 "最大可擴(kuò)(MB)"
FROM dba_data_files 
WHERE tablespace_name = 'USERS';

-- ? 執(zhí)行擴(kuò)容（將 datafile01.dbf 從 10GB 擴(kuò)到 15GB）
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' RESIZE 15G;

-- ?? 如果報錯 ORA-01237：磁盤空間不足 → 檢查文件系統(tǒng)！
-- ? 更安全做法：啟用自動擴(kuò)展（設(shè)上限防失控）
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/users01.dbf' 
AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

-- ? 創(chuàng)建新數(shù)據(jù)文件（指定路徑、大小、自動擴(kuò)展）
ALTER TABLESPACE users 
ADD DATAFILE '/u02/oradata/ORCL/users02.dbf' 
SIZE 8G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

-- ? 驗(yàn)證是否生效
SELECT file_name, tablespace_name, bytes/1024/1024 "MB", status 
FROM dba_data_files 
WHERE tablespace_name = 'USERS';

-- 步驟1：創(chuàng)建專用歸檔表空間（使用較小 block size 提升壓縮比）
CREATE TABLESPACE arch_ts 
DATAFILE '/u03/oradata/ORCL/arch01.dbf' SIZE 4G 
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M 
SEGMENT SPACE MANAGEMENT AUTO;

-- 步驟2：將大日志表 move 到新表空間（在線操作，業(yè)務(wù)無感知！）
ALTER TABLE app_log MOVE TABLESPACE arch_ts;
ALTER INDEX idx_app_log_time REBUILD TABLESPACE arch_ts;

-- 步驟3：修改用戶默認(rèn)表空間（防止新對象繼續(xù)寫入 USERS）
ALTER USER app_user DEFAULT TABLESPACE arch_ts;

-- 場景：一張表 delete 了 90% 數(shù)據(jù)，但空間沒還給表空間？
-- 原因：Oracle 的 High Water Mark（HWM）不會因 DELETE 自動下降！
-- 驗(yàn)證：對比實(shí)際行數(shù) vs 段占用塊數(shù)
SELECT 
  t.table_name,
  t.num_rows "統(tǒng)計(jì)行數(shù)",
  s.blocks "段占用塊數(shù)",
  ROUND(s.bytes/1024/1024, 2) "段大小(MB)",
  ROUND(t.num_rows * t.avg_row_len / 1024 / 1024, 2) "估算數(shù)據(jù)大小(MB)"
FROM user_tables t
JOIN user_segments s ON t.table_name = s.segment_name
WHERE t.table_name = 'HUGE_ORDER_LOG';

-- 步驟1：確保表啟用行移動（ROW MOVEMENT）
ALTER TABLE huge_order_log ENABLE ROW MOVEMENT;

-- 步驟2：收縮空間（回收 HWM 以上空白區(qū)）
ALTER TABLE huge_order_log SHRINK SPACE COMPACT; -- 先整理碎片
ALTER TABLE huge_order_log SHRINK SPACE;          -- 再下移 HWM

-- ? 驗(yàn)證：執(zhí)行后再次查 segments.blocks，應(yīng)顯著下降

-- 檢測索引碎片率（邏輯刪除率 > 20% 即需重建）
ANALYZE INDEX idx_orders_status VALIDATE STRUCTURE;

SELECT 
  name "索引名",
  del_lf_rows "刪除葉塊行數(shù)",
  lf_rows "葉塊總行數(shù)",
  ROUND(del_lf_rows/lf_rows*100, 2) "碎片率(%)",
  height "B樹高度"
FROM index_stats 
WHERE name = 'IDX_ORDERS_STATUS';

ALTER INDEX idx_orders_status REBUILD ONLINE PARALLEL 4;
ALTER INDEX idx_orders_status NOPARALLEL; -- 重建后關(guān)閉并行

-- 查找最大的 LOB 段
SELECT 
  l.owner, l.table_name, l.column_name, 
  s.bytes/1024/1024 "LOB大小(MB)",
  (SELECT COUNT(*) FROM l.owner || '.' || l.table_name WHERE l.column_name IS NOT NULL) "非空行數(shù)"
FROM dba_lobs l
JOIN dba_segments s ON l.segment_name = s.segment_name
WHERE s.tablespace_name = 'USERS'
ORDER BY s.bytes DESC
FETCH FIRST 5 ROWS ONLY;

-- 檢查審計(jì)表（尤其啟用了統(tǒng)一審計(jì)的 12c+）
SELECT owner, table_name, ROUND(bytes/1024/1024, 2) "大小(MB)"
FROM dba_segments 
WHERE segment_name IN ('AUD$', 'UNIFIED_AUDIT_TRAIL', 'FGA_LOG$')
  AND owner IN ('SYS', 'AUDSYS');

-- 檢查數(shù)據(jù)庫作業(yè)日志（DBMS_SCHEDULER）
SELECT log_date, owner, job_name, status, error#, additional_info
FROM dba_scheduler_job_log 
WHERE log_date > SYSDATE - 30 
ORDER BY log_date DESC;

-- ? 統(tǒng)一審計(jì)日志：使用官方清理接口（Oracle 12c+）
BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    use_last_arch_timestamp => FALSE);
END;
/

-- ? 設(shè)置自動清理策略（保留 90 天）
BEGIN
  DBMS_AUDIT_MGMT.INIT_CLEANUP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    default_cleanup_interval => 24); -- 每24小時運(yùn)行一次
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSDATE - 90);
END;
/

-- 查看臨時表空間使用（確認(rèn)是否被大排序/哈希連接占滿）
SELECT 
  s.sid, s.serial#, s.username, s.osuser, s.program,
  u.tablespace, u.segtype, u.contents,
  ROUND(u.blocks * (SELECT value FROM v$parameter WHERE name='db_block_size') / 1024/1024, 2) "使用量(MB)"
FROM v$session s
JOIN v$sort_usage u ON s.saddr = u.session_addr
ORDER BY u.blocks DESC;

-- 殺掉占用臨時段的異常會話（謹(jǐn)慎！先確認(rèn)）
ALTER SYSTEM KILL SESSION '123,4567'; -- sid,serial#

-- ? 長期：增大 TEMP 表空間，或?yàn)榇蟛樵冎付▽Ｓ门R時表空間
ALTER USER app_user TEMPORARY TABLESPACE temp_large;

@Component
public class TablespaceHealthChecker {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    // 每 5 分鐘檢查一次 USERS 表空間使用率
    @Scheduled(fixedRate = 300_000)
    public void checkAndAlert() {
        String sql = "SELECT ROUND((SUM(bytes)-SUM(NVL(fs.bytes,0)))/SUM(bytes)*100, 2) " +
                     "FROM dba_data_files df LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id " +
                     "WHERE df.tablespace_name = 'USERS' GROUP BY df.tablespace_name";
        try {
            BigDecimal usagePercent = jdbcTemplate.queryForObject(sql, BigDecimal.class);
            if (usagePercent != null && usagePercent.compareTo(new BigDecimal("95")) >= 0) {
                log.warn("?? USERS 表空間使用率已達(dá) {}%，觸發(fā)自動清理流程", usagePercent);
                triggerCleanupJob(); // 調(diào)用清理服務(wù)
                sendAlertToOps("USERS 表空間超限，請核查");
            }
        } catch (Exception e) {
            log.error("檢查表空間失敗", e);
        }
    }
    private void triggerCleanupJob() {
        // 調(diào)用存儲過程（如：clean_old_logs）
        jdbcTemplate.update("BEGIN clean_old_logs(:days); END;", 
            Collections.singletonMap("days", 30));
    }
    private void sendAlertToOps(String message) {
        // 集成企業(yè)微信/釘釘機(jī)器人（略）
        System.out.println("?? 發(fā)送告警: " + message);
    }
}

@Service
public class FileUploadService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public UploadResult uploadVideo(MultipartFile file) throws SQLException {
        // 步驟1：預(yù)估所需空間（按 1:1.2 壓縮冗余系數(shù)）
        long estimatedSize = Math.round(file.getSize() * 1.2);
        // 步驟2：查詢 USERS 表空間剩余空間（單位：字節(jié)）
        String spaceSql = "SELECT SUM(NVL(bytes,0)) FROM dba_free_space " +
                          "WHERE tablespace_name = 'USERS'";
        Long freeBytes = jdbcTemplate.queryForObject(spaceSql, Long.class);
        if (freeBytes == null || freeBytes < estimatedSize) {
            throw new InsufficientSpaceException(
                String.format("空間不足！需 %s MB，當(dāng)前僅剩 %s MB",
                    formatBytes(estimatedSize), formatBytes(freeBytes)));
        }
        // 步驟3：執(zhí)行上傳（此處省略文件存儲邏輯）
        return saveToDatabase(file, estimatedSize);
    }
    private String formatBytes(long bytes) {
        if (bytes < 1024) return bytes + " B";
        if (bytes < 1024 * 1024) return String.format("%.1f KB", bytes / 1024.0);
        return String.format("%.2f MB", bytes / 1024.0 / 1024.0);
    }
}

<!-- MyBatis Mapper XML -->
<update id="dropOldOrderPartition" parameterType="map">
    <!-- 假設(shè) orders 表按月分區(qū)：P202201, P202202... -->
    ALTER TABLE orders DROP PARTITION 
    <bind name="partitionName" value="'P' + (String.valueOf(_parameter.get('year')) + String.format('%02d', _parameter.get('month')))" />
</update>

// Java 調(diào)用
@Mapper
public interface OrderMapper {
    void dropOldOrderPartition(@Param("year") int year, @Param("month") int month);
}
// 服務(wù)層調(diào)用（刪除 2022 年所有分區(qū)）
orderMapper.dropOldOrderPartition(2022, 1);  // P202201
orderMapper.dropOldOrderPartition(2022, 2);  // P202202
// ... 無需遍歷數(shù)據(jù)，物理刪除分區(qū)頭，毫秒級！

-- 為應(yīng)用用戶設(shè)置硬性配額（不再允許無限增長）
ALTER USER app_user QUOTA 5G ON users;
ALTER USER app_user QUOTA 0 ON system; -- 禁止寫 SYSTEM
ALTER USER app_user QUOTA UNLIMITED ON arch_ts; -- 歸檔空間不限

-- ? 驗(yàn)證配額
SELECT username, tablespace_name, bytes/1024/1024 "已用(MB)", max_bytes/1024/1024 "限額(MB)"
FROM dba_ts_quotas 
WHERE username = 'APP_USER';

-- 查詢過去 30 天 USERS 表空間每日增長量（需 AWR 權(quán)限）
SELECT 
  TRUNC(snap.begin_interval_time) "日期",
  ROUND(MAX(tablespace_size)*db.block_size/1024/1024, 2) "當(dāng)日最大大小(MB)",
  ROUND(LAG(MAX(tablespace_size)) OVER (ORDER BY TRUNC(snap.begin_interval_time)) 
        * db.block_size/1024/1024, 2) "昨日大小(MB)",
  ROUND((MAX(tablespace_size) - LAG(MAX(tablespace_size)) 
         OVER (ORDER BY TRUNC(snap.begin_interval_time))) 
        * db.block_size/1024/1024, 2) "凈增長(MB)"
FROM dba_hist_tbspc_space_usage usg
JOIN dba_hist_snapshot snap ON usg.snap_id = snap.snap_id
CROSS JOIN (SELECT value AS block_size FROM v$parameter WHERE name = 'db_block_size') db
WHERE usg.tablespace_id = (SELECT ts# FROM v$tablespace WHERE name = 'USERS')
  AND snap.begin_interval_time >= SYSDATE - 30
GROUP BY TRUNC(snap.begin_interval_time), db.block_size, snap.begin_interval_time
ORDER BY "日期";

-- tablespace_health.sql
SET LINESIZE 200
SET PAGESIZE 100
SPOOL /home/oracle/logs/ts_health_$(date +%Y%m%d).log

PROMPT === 表空間健康報告 $(date) ===
PROMPT

-- 1. 高危表空間列表
SELECT tablespace_name, 
       ROUND((SUM(bytes)-SUM(NVL(fs.bytes,0)))/SUM(bytes)*100, 2) "使用率(%)",
       ROUND(SUM(bytes)/1024/1024, 2) "總大小(MB)"
FROM dba_data_files df
LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id
GROUP BY tablespace_name
HAVING ROUND((SUM(bytes)-SUM(NVL(fs.bytes,0)))/SUM(bytes)*100, 2) > 85
ORDER BY "使用率(%)" DESC;

PROMPT
PROMPT 2. TOP5 大段（疑似僵尸）
SELECT owner, segment_name, segment_type, 
       ROUND(bytes/1024/1024, 2) "大小(MB)"
FROM (
    SELECT owner, segment_name, segment_type, bytes,
           ROW_NUMBER() OVER (ORDER BY bytes DESC) rn
    FROM dba_segments 
    WHERE tablespace_name = 'USERS'
)
WHERE rn <= 5;

PROMPT
PROMPT 3. 索引碎片率 > 20% 的索引
SELECT owner, index_name, 
       ROUND((del_lf_rows/lf_rows)*100, 2) "碎片率(%)"
FROM dba_indexes i
JOIN index_stats ist ON i.index_name = ist.name AND i.owner = ist.name
WHERE (del_lf_rows/lf_rows) > 0.2
  AND i.tablespace_name = 'USERS';

SPOOL OFF
EXIT;