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

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

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

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

 

 

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

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

 

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