Skip to content

Instantly share code, notes, and snippets.

@na0AaooQ

na0AaooQ/file0.txt

Last active September 22, 2016 12:34
Show Gist options
  • Save na0AaooQ/e265199d249eafcb8c17 to your computer and use it in GitHub Desktop.
Save na0AaooQ/e265199d249eafcb8c17 to your computer and use it in GitHub Desktop.
Download ZIP
Oracle 11g 各表領域(テーブルスペース)の使用サイズと空き容量 + TEMP表領域のディスクソート回数を表示するスクリプト ref: http://qiita.com/na0AaooQ/items/5ab2b826254631cab708
Raw
file0.txt
[oracle@example-oracle-11g-active ~]$ vi show_oracle_tablespace.sh
Raw
file2.txt
[oracle@example-oracle-11g-active ~]$ chmod 755 show_oracle_tablespace.sh
Raw
file3.txt
[oracle@example-oracle-11g-active ~]$ vi /任意のディレクトリ/show_oracle_tablespace.sql
Raw
file5.txt
[oracle@example-oracle-11g-active ~]$ chmod 644 /任意のディレクトリ/show_oracle_tablespace.sql
Raw
file6.txt
[oracle@example-oracle-11g-active ~]$ ./show_oracle_tablespace.sh
SQL> set colsep |
SQL> set pages 1000
SQL> set lines 1000
SQL>
SQL> column TEMP_SIZE(MB) format a15
SQL> column TEMP_HWM(MB) format a15
SQL> column TEMP_HWM(%) format a15
SQL> column TEMP_USED(MB) format a15
SQL> column TEMP_USING(%) format a15
SQL>
SQL> ----- TEMP TABLESPACE USED
SQL> SELECT
2 d.tablespace_name,
3 round(現サイズ) "SIZE(MB)",
4 round(最大使用量) "USED(MB)",
5 round(((最大使用量/現サイズ))*100) "USE(%)",
6 round(現サイズ-最大使用量) "AVAIL(MB)"
7 FROM
8 (SELECT tablespace_name, SUM(bytes)/(1024*1024) "現サイズ" FROM dba_temp_files GROUP BY tablespace_name) d,
9 (SELECT tablespace_name, SUM(bytes_used)/(1024*1024) "最大使用量" FROM v$temp_extent_pool GROUP BY tablespace_name) f,
10 dba_tablespaces t
11 WHERE
12 (d.tablespace_name = f.tablespace_name) AND (d.tablespace_name = t.tablespace_name )
13 ORDER BY
14 d.tablespace_name
15 ;
TABLESPACE_NAME | SIZE(MB)| USED(MB)| USE(%)| AVAIL(MB)
------------------------------|----------|----------|----------|----------
TEMP | 5040| 0| 0| 5040
TEMP_EXAMPLE1 | 12000| 0| 0| 12000
TEMP_EXAMPLE2 | 200| 0| 0| 200
SQL>
SQL> ----- TEMP TABLESPACE HWM
SQL> SELECT
2 d.tablespace_name,
3 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "TEMP_SIZE(MB)",
4 TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "TEMP_HWM(MB)",
5 TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "TEMP_HWM(%)" ,
6 TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "TEMP_USED(MB)",
7 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "TEMP_USING(%)"
8 FROM
9 sys.dba_tablespaces d,
10 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
11 (select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
12 WHERE d.tablespace_name = a.tablespace_name(+)
13 AND d.tablespace_name = t.tablespace_name(+)
14 AND d.extent_management like 'LOCAL'
15 AND d.contents like 'TEMPORARY'
16 ;
TABLESPACE_NAME |TEMP_SIZE(MB) |TEMP_HWM(MB) |TEMP_HWM(%) |TEMP_USED(MB) |TEMP_USING(%)
------------------------------|---------------|---------------|---------------|---------------|---------------
TEMP | 5,040.000| 2519.000 | 49.98 | .000 | 0.00
TEMP_EXAMPLE1 | 12,000.000| 11931.000 | 99.43 | .000 | 0.00
TEMP_EXAMPLE2 | 200.000| 72.000 | 36.00 | .000 | 0.00
SQL>
SQL> ----- DISK SORT INCIDENCE
SQL> col "SORT_INCIDENCE(%)" format 990.99
SQL> SELECT
2 tablespace_name, ( used_blocks / total_blocks ) * 100 "SORT_INCIDENCE(%)"
3 FROM
4 v$sort_segment
5 WHERE
6 tablespace_name = 'TEMP' OR tablespace_name = 'TEMP_EXAMPLE1' OR tablespace_name = 'TEMP_EXAMPLE2'
7 ORDER BY
8 tablespace_name
9 ;
TABLESPACE_NAME |SORT_INCIDENCE(%)
-------------------------------|-----------------
TEMP | 0.00
TEMP_EXAMPLE1 | 0.00
TEMP_EXAMPLE2 | 0.00
SQL>
SQL> ----- DISK SORT COUNT
SQL> SELECT
2 name, value "SORT_FREQUENCY"
3 FROM
4 v$sysstat
5 WHERE
6 name like '%sort%'
7 ORDER BY
8 name
9 ;
NAME |SORT_FREQUENCY
----------------------------------------------------------------|--------------
sorts (disk) | 190
sorts (memory) | 1881695505
sorts (rows) | 1.5694E+11
SQL>
SQL> ----- TABLESPACE USED
SQL> SELECT
2 d.tablespace_name,
3 現サイズ "SIZE(MB)",
4 round(現サイズ-空き容量) "USED(MB)",
5 round((1 - (空き容量/現サイズ))*100) "USE(%)",
6 空き容量 "AVAIL(MB)"
7 FROM
8 (SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "現サイズ" FROM dba_data_files GROUP BY tablespace_name) d,
9 (SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "空き容量" FROM dba_free_space GROUP BY tablespace_name) f
10 WHERE
11 d.tablespace_name = f.tablespace_name
12 ORDER BY
13 d.tablespace_name
14 ;
TABLESPACE_NAME | SIZE(MB)| USED(MB)| USE(%)| AVAIL(MB)
------------------------------|----------|----------|----------|----------
INDX_EXAMPLE1 | 15900| 9800| 62| 6100
INDX_EXAMPLE2 | 3600| 2700| 75| 900
　　(省略)
SYSAUX | 2048| 1677| 82| 371
SYSTEM | 500| 459| 92| 41
TOOLS | 500| 154| 31| 346
TS_EXAMPLE1 | 12000| 8200| 68| 3800
TS_EXAMPLE1 | 3600| 2700| 75| 900
　　(省略)
UNDOTBS2 | 46820| 11305| 24| 35515
USERS | 10| 1| 10| 9
XXX rows selected.
SQL>
SQL> exit;
[oracle@example-oracle-11g-active ~]$
Raw
show_oracle_tablespace.sh
#!/bin/sh
export NLS_DATE_FORMAT="YYYY/MM/DD HH24:MI:SS"
SQL_TEXT="/任意のディレクトリ/show_oracle_tablespace.sql"
if [ -r "${SQL_TEXT}" ] && [ -f "sqlplus" ] ; then
sqlplus / as sysdba @${SQL_TEXT}
fi
Raw
show_oracle_tablespace.sql
set echo on
set colsep |
set pages 1000
set lines 1000
column TEMP_SIZE(MB) format a15
column TEMP_HWM(MB) format a15
column TEMP_HWM(%) format a15
column TEMP_USED(MB) format a15
column TEMP_USING(%) format a15
----- TEMP TABLESPACE USED
SELECT
d.tablespace_name,
round(現サイズ) "SIZE(MB)",
round(最大使用量) "USED(MB)",
round(((最大使用量/現サイズ))*100) "USE(%)",
round(現サイズ-最大使用量) "AVAIL(MB)"
FROM
(SELECT tablespace_name, SUM(bytes)/(1024*1024) "現サイズ" FROM dba_temp_files GROUP BY tablespace_name) d,
(SELECT tablespace_name, SUM(bytes_used)/(1024*1024) "最大使用量" FROM v$temp_extent_pool GROUP BY tablespace_name) f,
dba_tablespaces t
WHERE
(d.tablespace_name = f.tablespace_name) AND (d.tablespace_name = t.tablespace_name )
ORDER BY
d.tablespace_name
;
----- TEMP TABLESPACE HWM
SELECT
d.tablespace_name,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "TEMP_SIZE(MB)",
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "TEMP_HWM(MB)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "TEMP_HWM(%)" ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "TEMP_USED(MB)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "TEMP_USING(%)"
FROM
sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
;
----- DISK SORT INCIDENCE
col "SORT_INCIDENCE(%)" format 990.99
SELECT
tablespace_name, ( used_blocks / total_blocks ) * 100 "SORT_INCIDENCE(%)"
FROM
v$sort_segment
WHERE
tablespace_name = 'TEMP' OR tablespace_name = 'TEMP_EXAMPLE1' OR tablespace_name = 'TEMP_EXAMPLE2'
ORDER BY
tablespace_name
;
----- DISK SORT COUNT
SELECT
name, value "SORT_FREQUENCY"
FROM
v$sysstat
WHERE
name like '%sort%'
ORDER BY
name
;
----- TABLESPACE USED
SELECT
d.tablespace_name,
現サイズ "SIZE(MB)",
round(現サイズ-空き容量) "USED(MB)",
round((1 - (空き容量/現サイズ))*100) "USE(%)",
空き容量 "AVAIL(MB)"
FROM
(SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "現サイズ" FROM dba_data_files GROUP BY tablespace_name) d,
(SELECT tablespace_name, round(SUM(bytes)/(1024*1024)) "空き容量" FROM dba_free_space GROUP BY tablespace_name) f
WHERE
d.tablespace_name = f.tablespace_name
ORDER BY
d.tablespace_name
;
exit;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment