'오라클 테이블스페이스 관리'에 해당되는 글 1건

  1. 2023.12.06 [오라클] 테이블스페이스 관리 방법

[오라클] 테이블스페이스 관리 방법

|
반응형

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;

 

 

 

반응형
And
prev | 1 | next