【诊断脚本】【SQL】得到包含关键字的表空间与数据文件信息

2020-02-03 13:32:48

1.在日常维护中，没有得心应手的“诊断脚本”是低效的，枯燥的。在这个小文儿里给大家抛个砖。请看下面是我日常维护中相对得心应手的一个查看包含某关键字的表空间及数据文件的“诊断脚本”（此脚本也是在逐步更新到现在这个样子的，以后还将继续优化）。这个脚本中有一些格式化输出和变量定义的小技巧，留给有心人发掘。
ora10g@testdb /home/oracle/sql$ cat tbs.sql
--------------------------------------------------
-- Script. Function: Query the tablespace info --
-- Script. Name:     tbs.sql                    --
-- Author:           Secooler Hou               --
-- Date:             2007.9.6                   --
--------------------------------------------------

SET verify off
DEFINE tablespace_name=&tablespace_name
COL tablespace_name format a30
COL total_Mbytes format 99999999.99
COL used_Mbytes format 99999999.99
COL free_Mbytes format 99999999.99
COL pct_free format 99999999.99

COL file_name for a66
SELECT   file_name, BYTES / 1024 / 1024 mb, tablespace_name
    FROM dba_data_files
   WHERE UPPER (tablespace_name) LIKE UPPER ('%&tablespace_name%')
ORDER BY tablespace_name, file_name;
COL file_name clear

SELECT   a.tablespace_name, a.total_mbytes,
         a.total_mbytes - NVL (b.free_mbytes, 0) used_mbytes,
         NVL (b.free_mbytes, 0) free_mbytes,
         TRUNC (NVL (b.free_mbytes, 0) / a.total_mbytes * 100, 2) pct_free
    FROM (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 total_mbytes
              FROM dba_data_files
          GROUP BY tablespace_name) a,
         (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 free_mbytes
              FROM dba_free_space
          GROUP BY tablespace_name) b
   WHERE a.tablespace_name = b.tablespace_name(+)
     AND UPPER (a.tablespace_name) LIKE UPPER ('%&tablespace_name%')
ORDER BY free_mbytes DESC
/

UNDEFINE tablespace_name
SET verify on

2.脚本演示：以包含sec关键字为例，显示所有表空间和数据文件的信息
sys@ora10g> @tbs
Enter value for tablespace_name: sec

FILE_NAME                                   MB TABLESPACE_NAME
----------------------------------------- ---- -----------------
/oracle/oradata/ora10g/tbs_sec_01.dbf          TBS_SEC
/oracle/oradata/ora10g/tbs_sec_02.dbf       10 TBS_SEC
/oracle/oradata/ora10g/tbs_sec_d01.dbf      20 TBS_SEC_D
/oracle/oradata/ora10g/tbs_sec_i01.dbf      20 TBS_SEC_I
/oracle/oradata/ora10g/tbs_user_sec01.dbf   10 TBS_USER_SEC

TABLESPACE_NAME TOTAL_MBYTES USED_MBYTES FREE_MBYTES     PCT_FREE
---------------- ------------ ------------ ------------ ------------
TBS_SEC_I               20.00          .06        19.94        99.68
TBS_SEC_D               20.00         1.50        18.50        92.50
TBS_SEC                 10.00          .06         9.94        99.37
TBS_USER_SEC            10.00          .06         9.94        99.37

OK，输出的信息包含所有包含关键字“sec”的表空间，以及他们对应的数据文件。在这个脚本的协助下，可以快速的定位数据文件的位置，对给具体的表空间添加数据文件给出了提示。
欢迎大家交流。

-- The End --

分享好友

分享这个小栈给你的朋友们，一起进步吧。