Oracle数据库运维大全

以下是一些常见的Oracle数据库运维任务和对应的语句脚本示例：

1. 检查数据库实例状态：

   SELECT instance_name, status, startup_time 
   FROM v$instance;

2. 查看数据库版本和补丁级别：

   SELECT * FROM v$version;
   SELECT patch_id, action, status FROM dba_registry_sqlpatch;

3. 查看表空间使用情况：

   SELECT tablespace_name, sum(bytes)/1024/1024 AS "Size (MB)",
          sum(maxbytes)/1024/1024 AS "Max Size (MB)"
   FROM dba_data_files
   GROUP BY tablespace_name;

   SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
          D.TOT_GROOTTE_MB "表空间大小(M)",
          D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
          TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                        2),
                  '990.99') "使用比",
          F.TOTAL_BYTES "空闲空间(M)",
          F.MAX_BYTES "最大块(M)"
     FROM (SELECT TABLESPACE_NAME,
                  ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
                  ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
             FROM SYS.DBA_FREE_SPACE
            GROUP BY TABLESPACE_NAME) F,
          (SELECT DD.TABLESPACE_NAME,
                  ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
             FROM SYS.DBA_DATA_FILES DD
            GROUP BY DD.TABLESPACE_NAME) D
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    ORDER BY 4 DESC

4. 查看数据文件大小和路径：

   SELECT file_name, bytes/1024/1024 AS size_mb FROM dba_data_files;

5. 扩展表空间大小：

   ALTER TABLESPACE tablespace_name ADD DATAFILE 'file_path' SIZE 100M;

6. 查看数据文件增长趋势：

   SELECT file_name, bytes/1024/1024 AS "Current Size (MB)",
          autoextensible, maxbytes/1024/1024 AS "Max Size (MB)"
   FROM dba_data_files;

7. 查看数据库对象大小：

   SELECT owner, segment_name, segment_type, bytes/1024/1024 AS "Size (MB)"
   FROM dba_segments
   ORDER BY bytes DESC;

8. 优化索引并收集统计信息：

   EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME', estimate_percent => 50, cascade => TRUE);

9. 执行备份和恢复操作：

   -- 执行全量备份
   EXPDP username/password@database DIRECTORY=backup_dir DUMPFILE=backup.dmp LOGFILE=backup.log FULL=Y;
   ​
   -- 执行表级别的导出
   EXPDP username/password@database DIRECTORY=backup_dir DUMPFILE=table_backup.dmp LOGFILE=table_backup.log TABLES=table_name;
   ​
   -- 执行恢复操作
   IMPDP username/password@database DIRECTORY=backup_dir DUMPFILE=backup.dmp LOGFILE=restore.log FULL=Y;

10. 监控数据库性能指标：

    SELECT * FROM v$sysmetric WHERE metric_name LIKE '%Metric_Name%';
    SELECT * FROM v$sysmetric_summary WHERE metric_name LIKE '%Metric_Name%';
    SELECT * FROM v$active_session_history WHERE session_type='Foreground' AND sample_time > SYSDATE-1;

11. 查看会话和锁信息：

    SELECT sid, serial#, username, status, machine, program FROM v$session;
    SELECT * FROM v$locked_object;

12. 执行数据库定期维护：

    EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => 50, cpus => 4, cascade => TRUE);
    ALTER INDEX index_name REBUILD;
    ALTER TABLE table_name MOVE PARTITION partition_name;

13. 会话和锁定：

    • 查看当前会话：

      SELECT sid, serial#, username, status FROM v$session;

    • 查看当前会话的SQL语句：

      SELECT sid, serial#, sql_id, sql_text FROM v$sql WHERE username = 'USERNAME';

    • 查看锁定的对象：

      SELECT session_id, ORA_ROWSCN, object_name, object_type FROM dba_objects WHERE object_name = 'OBJECT_NAME';

    • 解锁对象：

      ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

14. 连接和用户：

    • 查看数据库连接数：

      SELECT COUNT(*) FROM v$session;

    • 查看当前用户列表：

      SELECT username, account_status FROM dba_users;

    • 重置用户密码：

      ALTER USER username IDENTIFIED BY new_password;

15. 数据库备份和恢复：

    • 执行逻辑备份（expdp）：

      EXPDP username/password DIRECTORY=data_pump_dir DUMPFILE=dumpfile.dmp LOGFILE=log.log FULL=Y;

    • 执行逻辑恢复（impdp）：

      IMPDP username/password DIRECTORY=data_pump_dir DUMPFILE=dumpfile.dmp LOGFILE=log.log FULL=Y;

    • 执行物理备份（RMAN）：

      BACKUP AS BACKUPSET DATABASE;

    • 执行物理恢复（RMAN）：

      RECOVER DATABASE;

16. 性能优化和统计信息：

    • 更新统计信息：

      EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME', cascade=>TRUE);

    • 查看执行计划：

      EXPLAIN PLAN FOR SELECT * FROM table_name;
      SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

    • 刷新共享池：

      ALTER SYSTEM FLUSH SHARED_POOL;

    • 刷新缓冲区：

      ALTER SYSTEM FLUSH BUFFER_CACHE;

17. 日志和故障排查：

    • 查看警告日志：

      SELECT message FROM v$datbase;

    • 查看错误日志：

      SELECT message FROM v$datbase WHERE message_type = 'ORA';

    • 查看数据库故障信息：

      SELECT * FROM v$diag_info;

。