본문 바로가기
IT/ORACLE

오라클(Oracle) 테이블 스페이스 용량 축소 & 프로시저 생성

by 베베야 2021. 3. 16.
728x90

오늘은 오라클(ORACLE) 데이터베이스의 테이블스페이스 용량을 줄이는 방법에 대해서 알아보고 프로시저 만드는 방법도 알겠습니다. 테이블스페이스 용량을 축소한다고 하였지만 해당 테이블스페이스에 있는 테이블의 용량을 줄여주는 방법입니다. (해당 프로시저를 생성할 때 새창을 열어서 단독으로 생성해주세요.)

이론에 대한 자세한 설명은 이전 글을 참조해 주세요.

 

오라클 테이블 용량 축소(SEGMENT SHRINK) 사용법 & 예제

오라클(ORACLE) 테이블 스페이스 용량을 줄이기 위해서 데이터베이스 테이블의 데이터를 delete문을 통해 삭제하였습니다. 이후 테이블 스페이스 용량을 확인해보니 용량이 줄어들지 않았는데 이

bebeya.tistory.com

 

 

오라클 용량 축소 프로시저 생성

CREATE OR REPLACE PROCEDURE P_SHRINK
(
   P_TABLE_OWNER IN VARCHAR2, 
   P_TABLE_NAME IN VARCHAR2 
)
IS

vs_TABLESPACE_NAME VARCHAR2(100);
vs_COL1 VARCHAR2(100);
vs_COL2 VARCHAR2(100);
vs_COL3 VARCHAR2(100);
vs_COL4 VARCHAR2(100);
vs_COL5 VARCHAR2(100);

vs_enable_sql VARCHAR2(1000);
vs_disable_sql VARCHAR2(1000);
vs_shrink_sql VARCHAR2(1000);

v_cur integer;
v_result integer;

BEGIN
SELECT TABLESPACE_NAME INTO vs_TABLESPACE_NAME FROM DBA_TABLES where owner = P_TABLE_OWNER and table_name=P_TABLE_NAME;
                
dbms_output.put_line('1');

--테이블 로그 축소 전 INSERT DATA
select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%" into vs_COL1,vs_COL2,vs_COL3,vs_COL4,vs_COL5
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
having a.tablespace_name=vs_TABLESPACE_NAME
order by tablespace;

--테이블 로그 축소 전 INSERT DATA 출력
dbms_output.put_line('로그 축소 전 ' || 'TABLESPACE:'|| vs_COL1 || ' TotalMB:' || vs_COL2 || ' UsedMB:'||vs_COL3 || ' FreeMB:' || vs_COL4 || ' Used%:' || vs_COL5);

--로그 축소 시작
v_cur := dbms_sql.open_cursor;

vs_enable_sql := 'ALTER TABLE '|| P_TABLE_OWNER ||'.'|| P_TABLE_NAME ||' ENABLE ROW MOVEMENT';
vs_disable_sql := 'ALTER TABLE '|| P_TABLE_OWNER ||'.'|| P_TABLE_NAME ||' DISABLE ROW MOVEMENT';
vs_shrink_sql := 'ALTER TABLE '|| P_TABLE_OWNER ||'.'|| P_TABLE_NAME ||' SHRINK SPACE CASCADE';

--1 ROW MOVEMENT 활성화
dbms_sql.parse(v_cur,vs_enable_sql,dbms_sql.native);
v_result := dbms_sql.execute(v_cur);

--2. SEGMENT SHRINK
-- cascade 옵션 : segment shrink는 dependent한 오브젝트들(정의된 인덱스)에 대해서도 자동으로 수행됩니다.
dbms_sql.parse(v_cur,vs_shrink_sql,dbms_sql.native);
v_result := dbms_sql.execute(v_cur);

--3 ROW MOVEMENT 비활성화
dbms_sql.parse(v_cur,vs_disable_sql,dbms_sql.native);
v_result := dbms_sql.execute(v_cur);

dbms_sql.close_cursor(v_cur);
--로그 축소 종료

--테이블 로그 축소 후 INSERT DATA
select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%" into vs_COL1,vs_COL2,vs_COL3,vs_COL4,vs_COL5
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          group by tablespace_name) a
group by a.tablespace_name
having a.tablespace_name=vs_TABLESPACE_NAME
order by tablespace;

--테이블 로그 축소 후 INSERT DATA 출력
dbms_output.put_line('로그 축소 후 ' || 'TABLESPACE:'|| vs_COL1 || ' TotalMB:' || vs_COL2 || ' UsedMB:'||vs_COL3 || ' FreeMB:' || vs_COL4 || ' Used%:' || vs_COL5);

END P_SHRINK;

 

오라클 용량 축소 프로시저 실행

EXEC P_SHRINK('유저명','테이블명') 

-------------------------[Start Time: 2021/03/16 08:56:51]-------------------------
SQL> 1
로그 축소 전 TABLESPACE:BEBEYA_TP TotalMB:54961.1 UsedMB:9512.8 FreeMB:45448.3 Used%:17.31
로그 축소 후 TABLESPACE:BEBEYA_TP TotalMB:54961.1 UsedMB:9410.5 FreeMB:45550.6 Used%:17.12

 

해당 프로시저가 필요하신 분들을 위해 메모장으로 첨부합니다.

오라클 용량 축소 프로시저.txt
0.00MB

 

이상으로 오라클 테이블스페이스 용량을 줄이는 방법에 대해서 알아보았습니다.

반응형
그리드형

댓글