OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
방법 : 테이블 스페이스 용량확인
용량확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
SQL>
col col0 format a18 heading 'TS Name'
col col1 format 9,999,999.99 heading 'Size(MB)'
col col2 format 9,999,999.99 heading 'Used(MB)'
col col3 format 9,999,999.99 heading 'Free(MB)'
col col4 format 9,999,999.99 heading '% Free'
col col5 format 9,999,999.99 heading 'Max(MB)'
select col0, col1, col3, col2, col5, col4
from (
select a.tablespace_name col0,
b.bytes/1024/1024 col1,
a.bytes/1024/1024 col3,
b.max/1024/1024 col5,
(b.bytes - a.bytes)/1024/1024 col2,
(a.bytes/ b.bytes) * 100 col4
from (
select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) a,
(
select tablespace_name,
sum(bytes) bytes,
sum(maxbytes) max
from dba_data_files
group by tablespace_name) b
where b.tablespace_name=a.tablespace_name)
order by col4 asc;
TS Name Size(MB) Free(MB) Used(MB) Max(MB) % Free
------------------ ------------- ------------- ------------- ------------- -------------
SYSAUX 2,048.00 1,011.94 1,036.06 .00 49.41
SYSTEM 700.00 400.81 299.19 .00 57.26
USERS 2,048.00 1,468.13 579.88 .00 71.69
UNDOTBS1 1,000.00 966.13 33.88 1,000.00 96.61
|
단순히 할당 용량만 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
col tsname for a20
col file_name for a60
select tablespace_name tsname, file_name, bytes/1024/1024 mb
from dba_data_files
order by tablespace_name;
TSNAME FILE_NAME MB
-------------------- ------------------------------------------------------------ ----------
SYSAUX /oracle/app/oracle/oradata/ORCL11/sysaux01.dbf 2048
SYSTEM /oracle/app/oracle/oradata/ORCL11/system01.dbf 700
UNDOTBS1 /oracle/app/oracle/oradata/ORCL11/undotbs01.dbf 1000
USERS /oracle/app/oracle/oradata/ORCL11/users01.dbf 2048
|
참조 :
'ORACLE > Sql' 카테고리의 다른 글
| 오라클 캐쉬 히트율 확인 (0) | 2017.05.31 |
|---|---|
| 로그마이너로 나온 결과 확인명령어 (0) | 2017.05.31 |
| 로그 스위치 및 체크포인트 발생 (0) | 2017.05.31 |
| Current 상태의 Redo Group 조회 (0) | 2017.05.31 |
| 복구가 필요한 데이터 파일 확인 (0) | 2017.05.31 |