OS환경 : Oracle Linux6.8(64bit)
DB 환경 : Oracle Database 11.2.0.4
쿼리 : 오라클 datafile size 줄이기
컬럼 설정
1 2 3 4 5 6 7 | set verify off column file_name format a50 word_wrapped column smallest format 999,990 heading "Smallest|Size|Poss." column currsize format 999,990 heading "Current|Size" column savings format 999,990 heading "Poss.|Savings" break on report compute sum of savings on report |
db block size 확인
1 2 3 4 5 6 7 8 | column value new_val blksize column value for a10 select value from v$parameter where name = 'db_block_size' / VALUE ---------- 8192 |
데이터 파일 당 할당된 size와 사용중인 size 확인
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 | select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) / Smallest Size Current Poss. FILE_NAME Poss. Size Savings -------------------------------------------------- -------- -------- -------- /dev/raw/raw6 497 699 202 /dev/raw/raw7 699 699 0 /dev/raw/raw10 145 199 54 /dev/raw/raw11 522 699 177 /dev/raw/raw8 213 699 486 /oracle/app/oracle/oradata/ts02.dbf 322 1,024 702 /oracle/app/oracle/oradata/movets02.dbf 1 1,024 1,023 -------- sum 2,644 |
위 결과에 따라 사이즈 줄이는 쿼리 생성
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | column cmd format a75 word_wrapped select 'alter database datafile ''' || file_name || ''' resize ' || ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd from dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0 / CMD --------------------------------------------------------------------------- alter database datafile '/dev/raw/raw6' resize 497m; alter database datafile '/dev/raw/raw10' resize 145m; alter database datafile '/dev/raw/raw11' resize 522m; alter database datafile '/dev/raw/raw8' resize 213m; alter database datafile '/oracle/app/oracle/oradata/pymts02.dbf' resize 322m; alter database datafile '/oracle/app/oracle/oradata/movets02.dbf' resize 1m; |
참조 : https://cafe.naver.com/prodba/7130
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:766625833673
'ORACLE > Sql' 카테고리의 다른 글
오라클에서 서버정보 확인하기 OS 정보 확인 쿼리 (0) | 2019.01.14 |
---|---|
오라클 insert문 반복 방법 for문 (0) | 2019.01.08 |
LOB 들어간 테이블 용량 조회(SEGMENTS) (0) | 2018.12.27 |
오라클 begin backup 중인 tablespace 확인(hot backup) (3) | 2018.12.17 |
dbms_metadata로 프로시저 생성문 뽑기 (0) | 2018.12.13 |