以下是一个基本的Oracle数据库巡检脚本,可以根据具体需求进行修改和优化:
```sql
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
v_total_mb NUMBER;
v_used_mb NUMBER;
v_free_mb NUMBER;
v_total_size NUMBER;
v_used_size NUMBER;
v_free_size NUMBER;
v_percent_used NUMBER;
v_percent_free NUMBER;
v_tablespace_size NUMBER;
v_tablespace_used NUMBER;
v_tablespace_free NUMBER;
v_table_count NUMBER;
v_indexes_count NUMBER;
v_today DATE := SYSDATE;
BEGIN
-- Database Information
DBMS_OUTPUT.PUT_LINE('Database Name: ' || UPPER(DBMS_STANDARD.database_name));
DBMS_OUTPUT.PUT_LINE('DB Version: ' || UPPER(DBMS_STANDARD.db_version));
DBMS_OUTPUT.PUT_LINE('Instance Name: ' || UPPER(ora_database_name));
DBMS_OUTPUT.PUT_LINE('Instance Start Time: ' || TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS'));
-- Database Size
SELECT
SUM(bytes)/1024/1024 AS total_mb,
SUM(DECODE(autoextensible, 'YES', maxbytes))/1024/1024 AS max_size_mb,
SUM(bytes - free_space)/1024/1024 AS used_mb,
SUM(free_space)/1024/1024 AS free_mb,
ROUND((SUM(bytes - free_space)/SUM(bytes))*100, 2) AS percent_used,
ROUND((SUM(free_space)/SUM(bytes))*100, 2) AS percent_free
INTO
v_total_mb,
v_total_size,
v_used_mb,
v_free_mb,
v_percent_used,
v_percent_free
FROM
dba_data_files a,
(SELECT file_id, sum(bytes) free_space FROM dba_free_space GROUP BY file_id) b
WHERE
a.file_id = b.file_id(+);
DBMS_OUTPUT.PUT_LINE('Total Database Size: ' || v_total_size || ' MB');
DBMS_OUTPUT.PUT_LINE('Max Database Size: ' || v_total_size || ' MB');
DBMS_OUTPUT.PUT_LINE('Used Database Size: ' || v_used_mb || ' MB (' || v_percent_used || '%)');
DBMS_OUTPUT.PUT_LINE('Free Database Size: ' || v_free_mb || ' MB (' || v_percent_free || '%)');
-- Tablespace Information
FOR ts IN (SELECT * FROM dba_tablespaces ORDER BY tablespace_name)
LOOP
SELECT
SUM(bytes)/1024/1024 AS ts_size,
SUM(bytes - NVL(free_space, 0))/1024/1024 AS ts_used,
SUM(NVL(free_space, 0))/1024/1024 AS ts_free,
COUNT(DISTINCT segment_name) AS table_count,
COUNT(DISTINCT index_name) AS index_count
INTO
v_tablespace_size,
v_tablespace_used,
v_tablespace_free,
v_table_count,
v_indexes_count
FROM
dba_segments a LEFT JOIN dba_free_space b ON a.tablespace_name=b.tablespace_name AND a.file_id=b.file_id AND a.block_id=b.block_id
WHERE
a.tablespace_name=ts.tablespace_name;
DBMS_OUTPUT.PUT_LINE('Tablespace Name: ' || ts.tablespace_name);
DBMS_OUTPUT.PUT_LINE('Tablespace Size: ' || v_tablespace_size || ' MB');
DBMS_OUTPUT.PUT_LINE('Tablespace Used: ' || v_tablespace_used || ' MB');
DBMS_OUTPUT.PUT_LINE('Tablespace Free: ' || v_tablespace_free || ' MB');
DBMS_OUTPUT.PUT_LINE('Table Count: ' || v_table_count);
DBMS_OUTPUT.PUT_LINE('Index Count: ' || v_indexes_count);
END LOOP;
-- Last Backup Information
SELECT
MAX(start_time) AS last_backup_time,
COUNT(*) AS backup_count
INTO
v_today,
v_total_size
FROM
v$rman_backup_job_details
WHERE
status = 'COMPLETED';
DBMS_OUTPUT.PUT_LINE('Last Backup Time: ' || TO_CHAR(v_today, 'YYYY-MM-DD HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('Total Backup Count: ' || v_total_size);
END;
/
```
该脚本主要包含以下信息:
1. 数据库基本信息,如数据库名称、版本、实例名称、启动时间等。
2. 数据库大小,包括总大小、已使用大小、空闲大小和百分比等。
3. 表空间信息,包括表空间名称、大小、已使用大小、空闲大小、表数量和索引数量等。
4. 最近备份信息,包括最近备份时间和备份次数等。
需要注意的是,该脚本只是一个基础版本,可以根据实际情况进行修改和优化。例如,可以添加更多的检查项,如系统状态、性能指标等。同时,需要注意脚本的执行权限和安全性,确保只有授权用户可以执行该脚本。