- 论坛徽章:
- 0
|
下面是一段代码，在程序后面输入一个db实例名，和一个表空间名就可以得出表空间的相关信息
如果表空间是ALL,则代表是所有表空间
ts_usage.sh
#!/bin/ksh
if [ $2 = "ALL" ];then
TS_STR=''
else
shift
TS=$(echo "$@" | $AWK -v OFS="," '{for(i=1;i<=NF;i++)$i="\047"$i"\047"}1')
TS_STR=" AND d.tablespace_name in ($TS) "
fi
sqlplus -s '/ as sysdba' <<- !
SELECT d.contents,
d.tablespace_name,
TO_CHAR(a.curr_bytes / 1024 / 1024, '999,999,990') SIZE_MB,
TO_CHAR((a.curr_bytes - NVL(f.bytes, 0)) / 1024 / 1024,
'999,999,990') USED_MB,
TO_CHAR(NVL(f.bytes, 0) / 1024 / 1024, '999,999,990') FREE_MB,
TO_CHAR((a.curr_bytes - NVL(f.bytes, 0)) / a.curr_bytes * 100,
'990.00') || ' %' USED_PCT,
TO_CHAR(a.max_bytes / 1024 / 1024, '999,999,990') MAX_SIZE_MB,
LPAD(TO_CHAR((a.curr_bytes - NVL(f.bytes, 0)) / a.max_bytes * 100,
'990.00') || ' %',12) USED_MAX_PCT,
DECODE(a.autoextensible, 0, 'NO', 'YES') AUTOEXT
FROM dba_tablespaces d, (SELECT tablespace_name,
SUM(GREATEST(maxbytes, bytes)) max_bytes,
SUM(bytes) curr_bytes,
SUM(DECODE(autoextensible, 'YES', 1, 0)) autoextensible
FROM dba_data_files
GROUP BY tablespace_name
UNION ALL
SELECT tablespace_name,
SUM(GREATEST(maxbytes, bytes)) max_bytes,
SUM(bytes) curr_bytes,
SUM(DECODE(autoextensible, 'YES', 1, 0)) autoextensible
FROM dba_temp_files
GROUP BY tablespace_name) a, (SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name
$TS_STR
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY d.contents, d.tablespace_name;
!
|
|
免费注册
发表于 2014-05-30 12:54
