[Tibero] table move

[table block free status check - table move 전/후에 확인해서 table move 잘됐는지 비교]

$ vi check_freeblock;   set serveroutput on;   DECLARE unformatted_blocks NUMBER; unformatted_bytes NUMBER; fs1_blocks NUMBER; fs1_bytes NUMBER; fs2_blocks NUMBER; fs2_bytes NUMBER; fs3_blocks NUMBER; fs3_bytes NUMBER; fs4_blocks NUMBER; fs4_bytes NUMBER; full_blocks NUMBER; full_bytes NUMBER;   BEGIN DBMS_SPACE.SPACE_USAGE( '계정', '테이블이름', 'TABLE', unformatted_blocks ,unformatted_bytes ,fs1_blocks ,fs1_bytes ,fs2_blocks ,fs2_bytes ,fs3_blocks ,fs3_bytes ,fs4_blocks ,fs4_bytes ,full_blocks ,full_bytes); DBMS_OUTPUT.PUT_LINE('Space utilization (TABLES) '); DBMS_OUTPUT.PUT_LINE('unformatted_blocks: '|| TO_CHAR(unformatted_blocks) ); DBMS_OUTPUT.PUT_LINE('unformatted_bytes: '|| TO_CHAR(unformatted_bytes) ); DBMS_OUTPUT.PUT_LINE('fs1_blocks: '|| TO_CHAR(fs1_blocks) ); DBMS_OUTPUT.PUT_LINE('fs1_bytes: '|| TO_CHAR(fs1_bytes) ); DBMS_OUTPUT.PUT_LINE('fs2_blocks: '|| TO_CHAR(fs2_blocks) ); DBMS_OUTPUT.PUT_LINE('fs2_bytes: '|| TO_CHAR(fs2_bytes) ); DBMS_OUTPUT.PUT_LINE('fs3_blocks: '|| TO_CHAR(fs3_blocks) ); DBMS_OUTPUT.PUT_LINE('fs3_bytes: '|| TO_CHAR(fs3_bytes) ); DBMS_OUTPUT.PUT_LINE('fs4_blocks: '|| TO_CHAR(fs4_blocks) ); DBMS_OUTPUT.PUT_LINE('fs4_bytes: '|| TO_CHAR(fs4_bytes) ); DBMS_OUTPUT.PUT_LINE('full_blocks: '|| TO_CHAR(full_blocks) ); DBMS_OUTPUT.PUT_LINE('full_bytes: '|| TO_CHAR(full_bytes) ); END;

 

block free space check

 

[table size check - table move 전/후에 확인해서 table move 잘됐는지 비교]

 

table move 전

table move 후

 

[table move]

SQL > alter table 테이블 move tablespace 테이블스페이스명;

 

[index rebuilding]

UNUSABLE로 되기 때문에 TABLE MOVE시에는 반드시 인덱스를 rebuild 해줘야 한다.

SQL > alter index 인덱스 rebuild;