以下是一些常見的Oracle數據庫運維任務和對應的語句腳本示例：
-
檢查數據庫實例狀態：
SELECT instance_name, status, startup_time FROM v$instance;
-
查看數據庫版本和補丁級別：
SELECT * FROM v$version; SELECT patch_id, action, status FROM dba_registry_sqlpatch;
-
查看表空間使用情況：
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_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 4 DESC -
查看數據文件大小和路徑：
SELECT file_name, bytes/1024/1024 AS size_mb FROM dba_data_files;
-
擴展表空間大小：
ALTER TABLESPACE tablespace_name ADD DATAFILE 'file_path' SIZE 100M;
-
查看數據文件增長趨勢：
SELECT file_name, bytes/1024/1024 AS "Current Size (MB)",autoextensible, maxbytes/1024/1024 AS "Max Size (MB)" FROM dba_data_files;
-
查看數據庫對象大小：
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS "Size (MB)" FROM dba_segments ORDER BY bytes DESC;
-
優化索引并收集統計信息：
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME', estimate_percent => 50, cascade => TRUE); -
執行備份和恢復操作：
-- 執行全量備份 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;
-
監控數據庫性能指標：
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;
-
查看會話和鎖信息：
SELECT sid, serial#, username, status, machine, program FROM v$session; SELECT * FROM v$locked_object;
-
執行數據庫定期維護：
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;
-
會話和鎖定：
-
查看當前會話：
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#';
-
-
連接和用戶：
-
查看數據庫連接數：
SELECT COUNT(*) FROM v$session;
-
查看當前用戶列表：
SELECT username, account_status FROM dba_users;
-
重置用戶密碼：
ALTER USER username IDENTIFIED BY new_password;
-
-
數據庫備份和恢復：
-
執行邏輯備份（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;
-
-
性能優化和統計信息：
-
更新統計信息：
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;
-
-
日志和故障排查：
-
查看警告日志：
SELECT message FROM v$datbase;
-
查看錯誤日志：
SELECT message FROM v$datbase WHERE message_type = 'ORA';
-
查看數據庫故障信息：
SELECT * FROM v$diag_info;
-
。
