十、安全管理

10.1 用户管理

-- 创建用户
CREATE USER app_user IDENTIFIED BY password
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users;

-- 创建profile
CREATE PROFILE app_profile LIMIT
    SESSIONS_PER_USER 10
    IDLE_TIME 30
    CONNECT_TIME 480
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LOCK_TIME 1
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 180
    PASSWORD_REUSE_MAX 5;

-- 应用profile
ALTER USER app_user PROFILE app_profile;

-- 修改用户
ALTER USER app_user IDENTIFIED BY new_password;
ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user QUOTA 500M ON users;

-- 删除用户
DROP USER app_user CASCADE;

10.2 权限管理

-- 系统权限
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;
GRANT CREATE PROCEDURE TO app_user;
GRANT UNLIMITED TABLESPACE TO app_user;

-- 对象权限
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_user;
GRANT EXECUTE ON emp_pkg TO app_user;
GRANT SELECT ON employees TO app_user WITH GRANT OPTION;

-- 角色
CREATE ROLE app_role;
GRANT SELECT ON employees TO app_role;
GRANT CREATE SESSION TO app_role;
GRANT app_role TO app_user;

-- 查看权限
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;
SELECT * FROM user_role_privs;

-- 撤销权限
REVOKE DELETE ON employees FROM app_user;
REVOKE app_role FROM app_user;

10.3 审计

-- 启用审计
ALTER SYSTEM SET audit_trail=DB,EXTENDED SCOPE=SPFILE;
-- 重启数据库

-- 标准审计
AUDIT SELECT, INSERT, UPDATE, DELETE ON employees BY ACCESS;
AUDIT CREATE TABLE BY app_user BY ACCESS;
AUDIT SESSION BY app_user;

-- 细粒度审计（FGA）
BEGIN
    DBMS_FGA.ADD_POLICY(
        object_schema => 'SCOTT',
        object_name => 'EMPLOYEES',
        policy_name => 'EMP_SALARY_AUDIT',
        audit_condition => 'salary > 10000',
        audit_column => 'SALARY',
        handler_schema => 'SCOTT',
        handler_module => 'LOG_SALARY_ACCESS',
        enable => TRUE
    );
END;
/

-- 查看审计记录
SELECT * FROM dba_audit_trail;
SELECT * FROM dba_fga_audit_trail;

-- 关闭审计
NOAUDIT SELECT ON employees;

十一、性能诊断工具

11.1 AWR（自动工作负载库）

-- 手动生成快照
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

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

-- 查看AWR配置
SELECT * FROM dba_hist_wr_control;

-- 修改快照间隔
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 43200,  -- 30天
    interval => 60       -- 60分钟
);

11.2 ADDM（自动数据库诊断监视器）

-- 运行ADDM
DECLARE
    v_task_name VARCHAR2(30);
BEGIN
    v_task_name := DBMS_ADDM.ANALYZE_DB(
        begin_snapshot => 100,
        end_snapshot => 110,
        dbid => (SELECT dbid FROM v$database)
    );
END;
/

-- 查看ADDM报告
SELECT DBMS_ADDM.GET_REPORT(v_task_name) FROM dual;

11.3 SQL Tuning Advisor

-- 创建优化任务
DECLARE
    v_task_name VARCHAR2(30);
BEGIN
    v_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
        sql_id => 'xxxxxx',
        scope => 'COMPREHENSIVE',
        time_limit => 120,
        task_name => 'tune_emp_query'
    );
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tune_emp_query');
END;
/

-- 查看优化建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_emp_query') FROM dual;

-- 删除任务
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('tune_emp_query');

十二、常见问题排查

12.1 性能问题

-- 查询当前最耗时的SQL
SELECT 
    sql_id,
    sql_text,
    elapsed_time/1000000 AS elapsed_seconds,
    cpu_time/1000000 AS cpu_seconds,
    buffer_gets,
    disk_reads,
    executions
FROM v$sql
WHERE elapsed_time > 10000000  -- 超过10秒
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

-- 查询等待事件
SELECT 
    event,
    total_waits,
    time_waited/100 AS time_waited_seconds,
    average_wait/100 AS avg_wait_seconds
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC;

-- 查询会话等待
SELECT 
    s.sid,
    s.serial#,
    s.username,
    w.event,
    w.wait_time_micro/1000000 AS wait_seconds,
    w.state
FROM v$session s
JOIN v$session_wait w ON s.sid = w.sid
WHERE w.event NOT LIKE '%Idle%'
ORDER BY wait_seconds DESC;

12.2 空间问题

-- 查看表空间使用率
SELECT 
    tablespace_name,
    ROUND(SUM(bytes)/1024/1024, 2) AS total_mb,
    ROUND(SUM(SUM(bytes)) OVER ()/1024/1024, 2) AS total_db_mb,
    ROUND(SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END)/1024/1024, 2) AS max_mb,
    ROUND(SUM(bytes)/1024/1024, 2) AS used_mb,
    ROUND((SUM(bytes)/SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END))*100, 2) AS usage_pct
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY usage_pct DESC;

-- 添加数据文件
ALTER TABLESPACE users ADD DATAFILE '/u01/app/oracle/oradata/orcl/users02.dbf' SIZE 10G AUTOEXTEND ON NEXT 100M MAXSIZE 32G;

-- 收缩表
ALTER TABLE employees ENABLE ROW MOVEMENT;
ALTER TABLE employees SHRINK SPACE CASCADE;

-- 重建索引
ALTER INDEX idx_emp_name REBUILD ONLINE;

Oracle数据库作为企业级数据库的标杆，其功能之强大、体系之完善令人叹为观止。从基础概念到高级特性，从日常操作到性能调优，系统性地梳理了Oracle数据库的核心知识点。掌握这些内容，你不仅能熟练使用Oracle数据库，更能深入理解其内部原理，应对各种复杂的企业级应用场景。
来源：https://app-ag9255nx2k8x.appmiaoda.com