將資料庫資訊彙總為HTML表格用瀏覽器開啟
指令碼如下：
根據網上找的改的--可以根據個人需要根據文件內格式進行定製
[oracle@bys001 ~]$ cat c.sql
-- +----------------------------------------------------------------------------+
-- | haibusuanyun |
-- | http://blog.csdn.net/q947817003 |
-- | |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2008 haibusuanyun. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : dba_snapshot_database_10g.sql |
-- | CLASS : Database Administration |
prompt
prompt +-----------------------------------------------------------------------------------------+
prompt | Snapshot Database 11g Release 2 |
prompt |-----------------------------------------------------------------------------------------+
prompt | Copyright (c) 1998-2008 haibusuanyun. All rights reserved. |
prompt +-----------------------------------------------------------------------------------------+
prompt
prompt Creating database report.
prompt This script must be run as a user with SYSDBA privileges.
prompt This process can take several minutes to complete.
prompt
define reportHeader="<font size=+3 color=darkgreen><b>Snapshot Database 11<i>g</i> Release 2</b></font><hr>Copyright (c) 1998-2008 haibusuanyun. All rights reserved. (<a target=""_blank"" href=""http://www.idevelopment.info"">http://blog.csdn.net/q947817003</a>)<p>"
-- +----------------------------------------------------------------------------+
-- | SCRIPT SETTINGS |
-- +----------------------------------------------------------------------------+
set termout off
set echo off
set feedback off
set heading off
set verify off
set wrap on
set trimspool on
set serveroutput on
set escape on
set timing off
set pagesize 50000
set linesize 175
set long 2000000000
clear buffer computes columns breaks
define fileName=health_check
define versionNumber=5.3
-- +----------------------------------------------------------------------------+
-- | GATHER DATABASE REPORT INFORMATION |
-- +----------------------------------------------------------------------------+
COLUMN tdate NEW_VALUE _date NOPRINT
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual;
COLUMN time NEW_VALUE _time NOPRINT
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual;
COLUMN date_time NEW_VALUE _date_time NOPRINT
SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual;
COLUMN date_time_timezone NEW_VALUE _date_time_timezone NOPRINT
SELECT TO_CHAR(systimestamp, 'Mon DD, YYYY (') || TRIM(TO_CHAR(systimestamp, 'Day')) || TO_CHAR(systimestamp, ') "at" HH24:MI:SS ') || TO_CHAR(systimestamp, ' "in Timezone" TZR') date_time_timezone
FROM dual;
COLUMN spool_time NEW_VALUE _spool_time NOPRINT
SELECT TO_CHAR(SYSDATE,'YYYYMMDDhh24miss') spool_time FROM dual;
COLUMN dbname NEW_VALUE _dbname NOPRINT
SELECT name dbname FROM v$database;
COLUMN dbid NEW_VALUE _dbid NOPRINT
SELECT dbid dbid FROM v$database;
COLUMN platform_id NEW_VALUE _platform_id NOPRINT
SELECT platform_id platform_id FROM v$database;
COLUMN platform_name NEW_VALUE _platform_name NOPRINT
SELECT platform_name platform_name FROM v$database;
COLUMN global_name NEW_VALUE _global_name NOPRINT
SELECT global_name global_name FROM global_name;
COLUMN blocksize NEW_VALUE _blocksize NOPRINT
SELECT value blocksize FROM v$parameter WHERE name='db_block_size';
COLUMN startup_time NEW_VALUE _startup_time NOPRINT
SELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance;
COLUMN host_name NEW_VALUE _host_name NOPRINT
SELECT host_name host_name FROM v$instance;
COLUMN instance_name NEW_VALUE _instance_name NOPRINT
SELECT instance_name instance_name FROM v$instance;
COLUMN instance_number NEW_VALUE _instance_number NOPRINT
SELECT instance_number instance_number FROM v$instance;
COLUMN thread_number NEW_VALUE _thread_number NOPRINT
SELECT thread# thread_number FROM v$instance;
COLUMN cluster_database NEW_VALUE _cluster_database NOPRINT
SELECT value cluster_database FROM v$parameter WHERE name='cluster_database';
COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT
SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances';
COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINT
SELECT user reportRunUser FROM dual;
COLUMN user_role NEW_VALUE _user_role NOPRINT
select granted_role user_role from user_role_privs;
COLUMN db_unique_name NEW_VALUE _db_unique_name NOPRINT
select db_unique_name from v$database;
COLUMN server_ip NEW_VALUE _server_ip NOPRINT
select utl_inaddr.get_host_address server_ip from dual;
COLUMN created NEW_VALUE _created NOPRINT
select to_char(created,'yyyy/mm/dd hh24:mi:ss') created from v$database;
-- +----------------------------------------------------------------------------+
-- | GATHER DATABASE REPORT INFORMATION |
-- +----------------------------------------------------------------------------+
set heading on
set markup html on spool on preformat off entmap on -
head ' -
<title>Database Report</title> -
<style type="text/css"> -
body {font:12pt Arial,Helvetica,sans-serif; color:black; background:White;} -
p {font:9pt Arial,Helvetica,sans-serif; color:black; background:White;} -
table,tr,td {font:9pt Arial,Helvetica,sans-serif; color:Black; background:#C0C0C0; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;} -
th {font:bold 9pt Arial,Helvetica,sans-serif; color:#336699; background:#cccc99; padding:0px 0px 0px 0px;} -
h1 {font:bold 12pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; border-bottom:1px solid #cccc99; margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} -
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif; color:#336699; background-color:White; margin-top:4pt; margin-bottom:0pt;} -
a {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.link {font:9pt Arial,Helvetica,sans-serif; color:#663300; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLink {font:9pt Arial,Helvetica,sans-serif; color:#663300; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkBlue {font:9pt Arial,Helvetica,sans-serif; color:#0000ff; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkDarkBlue {font:9pt Arial,Helvetica,sans-serif; color:#000099; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkRed {font:9pt Arial,Helvetica,sans-serif; color:#ff0000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkDarkRed {font:9pt Arial,Helvetica,sans-serif; color:#990000; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkGreen {font:9pt Arial,Helvetica,sans-serif; color:#00ff00; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
a.noLinkDarkGreen {font:9pt Arial,Helvetica,sans-serif; color:#009900; text-decoration: none; margin-top:0pt; margin-bottom:0pt; vertical-align:top;} -
</style>' -
body 'BGCOLOR="#C0C0C0"' -
table 'WIDTH="90%" BORDER="1"'
spool &FileName._&_dbname._&_spool_time..html
set markup html on entmap off
-- +============================================================================+
-- | |
-- | <<<<< Database and Instance Information >>>>> |
-- | |
-- +============================================================================+
prompt
prompt <center><font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#663300"><b><u>Database and Instance Information</u></b></font></center>
-- +----------------------------------------------------------------------------+
-- | - REPORT HEADER - |
-- +----------------------------------------------------------------------------+
prompt
prompt <a name="report_header"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Report Header</b></font><hr align="left" width="460">
prompt <table width="90%" border="1"> -
<tr><th align="left" width="20%">Report Name</th><td width="80%"><tt>&FileName._&_dbname._&_spool_time..html</tt></td></tr> -
<tr><th align="left" width="20%">Snapshot Database Version</th><td width="80%"><tt>&versionNumber</tt></td></tr> -
<tr><th align="left" width="20%">Created_Time</th><td width="80%"><tt>&_created</tt></td></tr> -
<tr><th align="left" width="20%">Run Date / Time / Timezone</th><td width="80%"><tt>&_date_time_timezone</tt></td></tr> -
<tr><th align="left" width="20%">Host Name</th><td width="80%"><tt>&_host_name</tt></td></tr> -
<tr><th align="left" width="20%">Server IP</th><td width="80%"><tt>&_server_ip</tt></td></tr> -
<tr><th align="left" width="20%">Database Name</th><td width="80%"><tt>&_dbname</tt></td></tr> -
<tr><th align="left" width="20%">Database UNIQUE Name</th><td width="80%"><tt>&_db_unique_name</tt></td></tr> -
<tr><th align="left" width="20%">Database ID</th><td width="80%"><tt>&_dbid</tt></td></tr> -
<tr><th align="left" width="20%">Global Database Name</th><td width="80%"><tt>&_global_name</tt></td></tr> -
<tr><th align="left" width="20%">Platform Name / ID</th><td width="80%"><tt>&_platform_name / &_platform_id</tt></td></tr> -
<tr><th align="left" width="20%">Clustered Database?</th><td width="80%"><tt>&_cluster_database</tt></td></tr> -
<tr><th align="left" width="20%">Clustered Database Instances</th><td width="80%"><tt>&_cluster_database_instances</tt></td></tr> -
<tr><th align="left" width="20%">Instance Name</th><td width="80%"><tt>&_instance_name</tt></td></tr> -
<tr><th align="left" width="20%">Instance Number</th><td width="80%"><tt>&_instance_number</tt></td></tr> -
<tr><th align="left" width="20%">Thread Number</th><td width="80%"><tt>&_thread_number</tt></td></tr> -
<tr><th align="left" width="20%">Database Startup Time</th><td width="80%"><tt>&_startup_time</tt></td></tr> -
<tr><th align="left" width="20%">Database Block Size</th><td width="80%"><tt>&_blocksize</tt></td></tr> -
<tr><th align="left" width="20%">Report Run User</th><td width="80%"><tt>&_reportRunUser</tt></td></tr> -
<tr><th align="left" width="20%">user_role</th><td width="80%"><tt>&_user_role</tt></td></tr> -
</table>
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - CONTROL FILES - |
-- +----------------------------------------------------------------------------+
prompt <a name="control_files"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>Control Files</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN name HEADING 'Controlfile Name' ENTMAP off
COLUMN status FORMAT a75 HEADING 'Status' ENTMAP off
COLUMN file_size FORMAT a75 HEADING 'File Size' ENTMAP off
SELECT
'<tt>' || c.name || '</tt>' name
, DECODE( c.status
, NULL
, '<div align="center"><b><font color="darkgreen">VALID</font></b></div>'
, '<div align="center"><b><font color="#663300">' || c.status || '</font></b></div>') status
, '<div align="right">' || TO_CHAR(block_size * file_size_blks, '999,999,999,999') || '</div>' file_size
FROM
v$controlfile c
ORDER BY
c.name;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
prompt <a name="datafile"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>datafiles</b></font><hr align="left" width="460">
CLEAR COLUMNS BREAKS COMPUTES
COLUMN file_name HEADING 'File_name' ENTMAP off
COLUMN file_id HEADING 'File_ID' ENTMAP off
COLUMN tablespace_name HEADING 'Tablespace_Name' ENTMAP off
COLUMN MB HEADING 'FILE_MB' ENTMAP off
select '<tt>' || file_name || '</tt>' file_name,'<div align="center"><b><font color="darkgreen">' ||file_id ||'</font></b></div>' file_id,'<div align="right">' ||tablespace_name|| '</div>' tablespace_name,'<div align="right">' ||bytes/1024/1024 || '</div>'
MB from dba_data_files order by file_id;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
-- +----------------------------------------------------------------------------+
-- | - END OF REPORT - |
-- +----------------------------------------------------------------------------+
SPOOL OFF
SET MARKUP HTML OFF
SET TERMOUT ON
prompt
prompt Output written to: &FileName._&_dbname._&_spool_time..html
EXIT;
在指令碼中增加一項內容的簡單解析：
prompt <a name="datafile"></a>
prompt <font size="+2" face="Arial,Helvetica,Geneva,sans-serif" color="#336699"><b>datafiles</b></font><hr align="left" width="460"> 這一句定義的是左上的此項表格名，見最下圖。
CLEAR COLUMNS BREAKS COMPUTES
COLUMN file_name HEADING 'File_name' ENTMAP off 這裡要將查詢出的列名做一下轉換，在瀏覽器的表格中顯示的名稱就是：File_name 以下三項類似。
COLUMN file_id HEADING 'File_ID' ENTMAP off
COLUMN tablespace_name HEADING 'Tablespace_Name' ENTMAP off
COLUMN MB HEADING 'FILE_MB' ENTMAP off
div align="center" 這一句是將查詢出的值在表格里居中，div align="right"在表格中靠右。
'<div align="center"><b><font color="darkgreen"> 查詢出的值在表格里居中，並顯示為darkgreen的顏色；也可以用red等顏色。
更詳細的可以參考HTML的語法了，這裡只是簡單說了下指令碼中需要用到的。
select '<tt>' || file_name || '</tt>' file_name,'<div align="center"><b><font color="darkgreen">' ||file_id ||'</font></b></div>' file_id,'<div align="right">' ||tablespace_name|| '</div>' tablespace_name,'<div align="right">' ||bytes/1024/1024 || '</div>'
MB from dba_data_files order by file_id;
prompt <center>[<a class="noLink" href="#top">Top</a>]</center><p>
這一句是回到最開始的一個按鈕。
執行情況：--輸出的檔案在執行SQLPLUS的當前目錄內
[oracle@bys001 ~]$ sqlplus bys/bys
SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 5 16:34:42 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
BYS@bys1>@c.sql
+-----------------------------------------------------------------------------------------+
| Snapshot Database 11g Release 2 |
|-----------------------------------------------------------------------------------------+
| Copyright (c) 1998-2008 haibusuanyun. All rights reserved. |
+-----------------------------------------------------------------------------------------+
Creating database report.
This script must be run as a user with SYSDBA privileges.
This process can take several minutes to complete.
Output written to: health_check_BYS1_20131005163443.html
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bys001 ~]$ ls health_check_BYS1_20131005163443.html
health_check_BYS1_20131005163443.html
將health_check_BYS1_20131005163443.html用IE或火狐等瀏覽器開啟，如下：
相關文章
- CSS瀏覽器相容彙總CSS瀏覽器
- 為什麼谷歌瀏覽器開啟是2345瀏覽器 谷歌瀏覽器一開啟是2345介面怎麼辦谷歌瀏覽器
- 將資料庫中資料匯出為excel表格資料庫Excel
- 瀏覽器資料庫 IndexedDB（一） 概述瀏覽器資料庫Index
- Mechanize庫，用於模擬瀏覽器行為瀏覽器
- 瀏覽器開啟md文件瀏覽器
- ASM資訊資料彙總ASM
- 怎麼用谷歌瀏覽器開啟jupyter谷歌瀏覽器
- Monetate：瀏覽器大戰–資料資訊圖瀏覽器
- 使用chrome瀏覽器驅動自動開啟瀏覽器Chrome瀏覽器
- VScode如何在瀏覽器中開啟html檔案VSCode瀏覽器HTML
- 瀏覽器資料庫 IndexedDB 入門教程瀏覽器資料庫Index
- Win10開啟IE瀏覽器方法 Win10怎麼用IE瀏覽器?Win10瀏覽器
- python用selenium開啟瀏覽器後瀏覽器關閉---解決辦法Python瀏覽器
- ie瀏覽器開啟變成別的瀏覽器怎麼辦 開啟ie瀏覽器變成360怎麼改瀏覽器
- Javascript開啟瀏覽器全屏模式JavaScript瀏覽器模式
- win10為什麼開啟瀏覽器就當機 win10開啟瀏覽器就當機的方法Win10瀏覽器
- Oracle資料庫啟動問題彙總(一)Oracle資料庫
- 瀏覽器攔截開啟新視窗情況總結瀏覽器
- 在搜狗瀏覽器中啟用WebGL瀏覽器Web
- 前端面試總結——http、html和瀏覽器篇前端面試HTTPHTML瀏覽器
- 水稻資料庫彙總資料庫
- 直接通過瀏覽器開啟Android App 應用瀏覽器AndroidAPP
- 【瀏覽器開啟匯出的excel】瀏覽器Excel
- 瀏覽器開發者工具開啟檢測瀏覽器
- uc 瀏覽器不能開啟網頁瀏覽器網頁
- 將excel表格匯入資料庫Excel資料庫
- shellinabox 可以用瀏覽器開啟的 SSH瀏覽器
- win10系統下edge瀏覽器如何開啟flash_win10edge瀏覽器啟用flash的步驟Win10瀏覽器
- android典型程式碼系列(二十七)------鎖屏開啟瀏覽器傳資料,解屏最小化瀏覽器Android瀏覽器
- 【彙總】語料庫資源
- javascript 獲取瀏覽器資訊JavaScript瀏覽器
- 如何重用瀏覽器tab開啟頁面瀏覽器
- 谷歌瀏覽器開啟實驗性功能谷歌瀏覽器
- 替代Edge瀏覽器？微軟開發新的瀏覽器：採用Chrome核心瀏覽器微軟Chrome
- ie瀏覽器開啟怎麼是360導航 ie每次開啟都是360瀏覽器怎麼解決瀏覽器
- java HTML5 學習資料彙總JavaHTML
- win10系統設定HTML檔案指定瀏覽器開啟的方法Win10HTML瀏覽器