반응형
Oracle 테이블스페이스 관리 방법
작업 환경
OS: Oracle Linux 8.9
DB: Oracle 19c
오라클 설치 후 테이블스페이스를 따로 관리하지 않고 초기 설정으로 사용하면 데이터가 증가될때 특정 DATAFILE
사이즈가 자동으로 증가되는 현상이 발생합니다.<
DATAFILE은 10G, 20G 사이즈로 지정하고 AUTOEXTEND OFF로 관리하는게 좋아요.
테이블스페이스 관리는 데이터 증가량에 따라 달라지는데 급격하게 증가되는 특수한 경우를 빼면 70~80% 사용량일때
DATAFILE을 추가하는걸 권장합니다.
SQL 스크립트(테이블스페이스 데이터파일 상태 확인):
| COL TABLESPACE_NAME FOR A20 COL FILE_NAME FOR A50 COL AUTOEXTENSIBLE FOR A20 SELECT TABLESPACE_NAME, FILE_NAME, ROUND(BYTES/1024/1024/1024) AS "SIZE_GB", AUTOEXTENSIBLE FROM DBA_DATA_FILES UNION ALL SELECT TABLESPACE_NAME, FILE_NAME, ROUND(BYTES/1024/1024/1024), AUTOEXTENSIBLE FROM DBA_TEMP_FILES ORDER BY 1,2; |
SQL(AUTOEXTEND OFF 스크립트)
- DB 전체 DATAFILE, TEMPFILE AUTOEXTEND OFF 변경 스크립트
- 테이블스페이스 사용량 70~80% 사용하면 DATAFILE을 추가하고 작업 진행하는걸 추천합니다.
- SCRIPT 컬럼을 복사해서 붙여서 실행해주시면 완료
| SELECT TABLESPACE_NAME, FILE_NAME, ROUND(BYTES/1024/1024/1024) AS "SIZE_GB", AUTOEXTENSIBLE, 'ALTER DATABASE DATAFILE '''||FILE_NAME||''' AUTOEXTEND OFF;' AS "SCRIPT" FROM DBA_DATA_FILES UNION ALL SELECT TABLESPACE_NAME, FILE_NAME, ROUND(BYTES/1024/1024/1024) AS "SIZE_GB", AUTOEXTENSIBLE, 'ALTER DATABASE TEMPFILE '''||FILE_NAME||''' AUTOEXTEND OFF;' AS "SCRIPT" FROM DBA_TEMP_FILES; -- sqlplus 실행 결과 SQL> ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/STR/system01.dbf' AUTOEXTEND OFF; Database altered. SQL> ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/STR/sysaux01.dbf' AUTOEXTEND OFF; Database altered. SQL> ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/STR/users01.dbf' AUTOEXTEND OFF; Database altered. SQL> ALTER DATABASE DATAFILE '/oracle/app/oracle/oradata/STR/undotbs01.dbf' AUTOEXTEND OFF; Database altered. SQL> ALTER DATABASE TEMPFILE '/oracle/app/oracle/oradata/STR/temp01.dbf' AUTOEXTEND OFF; Database altered. |
SQL(오라클 테이블스페이스 사용량 분석)
- SQL Developer에서 (F9) 실행하면 USED 컬럼이 그래픽으로 출력
| SELECT /*+ all_rows use_concat */ 'SQLDEV:LINK{#;#}'||USER||'#;#DATAFILE#;#'||ddf.file_name||'#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink' as "File Name", ddf.tablespace_name as "Tablespace", ddf.online_status as "Status", TO_CHAR(NVL(ddf.bytes / 1024 / 1024, 0), '99999990.000') as "Size (MB)", TO_CHAR(DECODE(NVL(u.bytes/1024/1024, 0), 0, NVL((ddf.bytes - NVL(s.bytes, 0))/1024/1024, 0), NVL(u.bytes/1024/1024, 0)), '99999999.999') as "Used (MB)", CASE when ddf.online_status = 'OFFLINE' then 'OFFLINE' when ddf.online_status = 'RECOVER' then 'RECOVER' else 'SQLDEV:GAUGE:0:100:0:0:'|| TRIM(TO_CHAR(DECODE((NVL(u.bytes, 0) / ddf.bytes * 100), 0, NVL((ddf.bytes - NVL(s.bytes, 0)) / ddf.bytes * 100, 0), (NVL(u.bytes, 0) / ddf.bytes * 100)), '990')) end as "Used (Proportion)", TO_CHAR(DECODE((NVL(u.bytes, 0) / ddf.bytes * 100), 0, NVL((ddf.bytes - NVL(s.bytes, 0)) / ddf.bytes * 100, 0), (NVL(u.bytes, 0) / ddf.bytes * 100)), '990.99') as "Used (%)", ddf.autoextensible as "Auto Extend" FROM sys.dba_data_files ddf, ( SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id ) s, ( SELECT file_id, SUM(bytes) bytes FROM sys.dba_undo_extents WHERE status <> 'EXPIRED' GROUP BY file_id ) u WHERE (ddf.file_id = s.file_id(+) and ddf.file_id=u.file_id(+)) UNION SELECT 'SQLDEV:LINK{#;#}'||USER||'#;#DATAFILE#;#'||v.name||'#;#oracle.dbtools.raptor.dba.navigator.Drill.DBADrillLink' as "File Name", dtf.tablespace_name as "Tablespace", dtf.status as "Status", TO_CHAR(NVL(dtf.bytes / 1024 / 1024, 0), '99999990.000') as "Size (MB)", TO_CHAR(NVL(t.bytes_used/1024/1024, 0), '99999990.000') as "Used (MB)", CASE when dtf.status = 'OFFLINE' then 'OFFLINE' else 'SQLDEV:GAUGE:0:100:0:0:'|| TRIM(TO_CHAR(NVL(t.bytes_used / dtf.bytes * 100, 0), '990.99')) end as "Used (Proportion)", TO_CHAR(NVL(t.bytes_used / dtf.bytes * 100, 0), '990') as "Used (%)", dtf.autoextensible as "Auto Extend" FROM sys.dba_temp_files dtf, sys.v_$tempfile v, v$temp_extent_pool t WHERE (dtf.file_name = v.name or dtf.file_id = v.file#) and dtf.file_id = t.file_id(+) ORDER BY 1; |
반응형
'Oracle Database > Oracle Admin' 카테고리의 다른 글
| [오라클] UNDO 사용량 증가 SQL 확인 방법 (2) | 2023.12.06 |
|---|---|
| [오라클] Oracle Version 확인 방법 (0) | 2023.12.06 |
| [오라클] CPU 사용량 분석 SQL (4) | 2023.12.06 |
| [오라클] AWR 설정(수집 주기 변경) (0) | 2020.10.15 |
| [오라클] Oracle 11g RAC PC로 구성하기 2 (RAC용) (0) | 2012.05.10 |
