[Oracle] DB reorg 작업2 (table shrink)

1. table compact : 비어있는 공간으로 data 를 채워넣는것
2. table shrink : HWM 를 아래로 내리는 작업

---

[실습1] table compact와 shrink로 db reorg 작업하기

 ■ 새로운 db reorg 작업 

  1.  scott 으로 접속해서 table 을 준비한다. 

      @demo.sql

      insert   into  emp
       select  *
           from  emp;   <---------  10번 수행 

      delete  from  emp  where  deptno in (10,20);

  2. emp 테이블의 실제 사용하고 있는 block 의 갯수 

 select count(distinct dbms_rowid.rowid_block_number(rowid)) 
        as blocks
    from  emp;

  169

  3. High water mark 까지 할당된 block 의 갯수 확인

  select  blocks
    from   user_segments
    where  segment_name='EMP';

   256 

 4. 테이블 compact 작업 수행

   alter  table  emp  enable  row  movement;

   alter  table  emp  shrink  space  compact;

  5. emp 테이블의 실제 사용하고 있는 block 의 갯수 

 select count(distinct dbms_rowid.rowid_block_number(rowid)) 
        as blocks
    from  emp;

  77

  6. High water mark 까지 할당된 block 의 갯수 확인

  select  blocks
    from   user_segments
    where  segment_name='EMP';

    BLOCKS
   ----------
       256

 7.  High water mark 를 내려주는 작업 수행 
  
   alter  table  emp  shrink  space; 

  select  blocks
    from   user_segments
    where  segment_name='EMP';

    BLOCKS
  ----------
        88

---

문제1. 아래의 환경을 만들고 db reorg작업을 하는데 compact과 shrink로 하세요

 ■ 새로운 db reorg 작업 

  1.  scott 으로 접속해서 table 을 준비한다. 

      @demo.sql
      create  index  emp_sal  on  emp(sal);
      create  index  emp_empno on  emp(empno);

      insert   into  emp
       select  *
           from  emp;   <---------  10번 수행 

      delete  from  emp  where  deptno in (10,20);

  2. emp 테이블의 실제 사용하고 있는 block 의 갯수 

 select count(distinct dbms_rowid.rowid_block_number(rowid)) 
        as blocks
    from  emp;

  169

  3. High water mark 까지 할당된 block 의 갯수 확인

  select  blocks
    from   user_segments
    where  segment_name='EMP';

   256 

 4. 테이블 compact 작업 수행

   alter  table  emp  enable  row  movement;

   alter  table  emp  shrink  space  compact;

  5. emp 테이블의 실제 사용하고 있는 block 의 갯수 

 select count(distinct dbms_rowid.rowid_block_number(rowid)) 
        as blocks
    from  emp;

  77

  6. High water mark 까지 할당된 block 의 갯수 확인

  select  blocks
    from   user_segments
    where  segment_name='EMP';

    BLOCKS
   ----------
       256

 7.  High water mark 를 내려주는 작업 수행 
  
   alter  table  emp  shrink  space; 

  select  blocks
    from   user_segments
    where  segment_name='EMP';

    BLOCKS
  ----------
        88

---

문제2. emp와 관련된 인덱스가 invalid 되었는지 확인하시오

select index_name, table_name, status
 from user_indexes;

---

문제3. HR 계정의 테이블이 뭐가 있는지 조회하시오

select *
 from all_tables
 where owner='HR' and table_name !='COUNTRIES';

---

문제4. HR 계정의 테이블들을 compact 과 shrink 로 reorg작업하시오

select 'alter  table  ' || table_name || ' enable row movement;'
 from all_tables
 where owner='HR' and table_name !='COUNTRIES'
 
 union all

 select 'alter  table  ' || table_name || ' shrink space compact;'
 from all_tables
 where owner='HR' and table_name !='COUNTRIES'
 
 union all 
 

 select 'alter  table  ' || table_name || ' shrink space;'
 from all_tables
 where owner='HR' and table_name !='COUNTRIES';

.

alter  table  EMPLOYEES enable row movement;
alter  table  REGIONS enable row movement;
alter  table  LOCATIONS enable row movement;
alter  table  DEPARTMENTS enable row movement;
alter  table  JOBS enable row movement;
alter  table  JOB_HISTORY enable row movement;

alter  table  EMPLOYEES shrink space compact;
alter  table  REGIONS shrink space compact;
alter  table  LOCATIONS shrink space compact;
alter  table  DEPARTMENTS shrink space compact;
alter  table  JOBS shrink space compact;
alter  table  JOB_HISTORY shrink space compact;

alter  table  EMPLOYEES shrink space;
alter  table  REGIONS shrink space;
alter  table  LOCATIONS shrink space;
alter  table  DEPARTMENTS shrink space;
alter  table  JOBS shrink space;
alter  table  JOB_HISTORY shrink space;

---

🔸 문제5. 아래의 스크립트가 금요일밤 10시에 수행될 수 있도록 crontab 또는 오라클 스케줄 잡으로 수행하시오

alter  table  EMPLOYEES enable row movement;
alter  table  REGIONS enable row movement;
alter  table  LOCATIONS enable row movement;
alter  table  DEPARTMENTS enable row movement;
alter  table  JOBS enable row movement;
alter  table  JOB_HISTORY enable row movement;

alter  table  EMPLOYEES shrink space compact;
alter  table  REGIONS shrink space compact;
alter  table  LOCATIONS shrink space compact;
alter  table  DEPARTMENTS shrink space compact;
alter  table  JOBS shrink space compact;
alter  table  JOB_HISTORY shrink space compact;

alter  table  EMPLOYEES shrink space;
alter  table  REGIONS shrink space;
alter  table  LOCATIONS shrink space;
alter  table  DEPARTMENTS shrink space;
alter  table  JOBS shrink space;
alter  table  JOB_HISTORY shrink space;

답:

[oracle@ora19c ~]$ pwd
/home/oracle
[oracle@ora19c ~]$ vi alter_table.sql
[oracle@ora19c ~]$
[oracle@ora19c ~]$ cat alter_table.sql
# /home/oracle/alter_table.sql

alter  table  EMPLOYEES enable row movement;
alter  table  REGIONS enable row movement;
alter  table  LOCATIONS enable row movement;
alter  table  DEPARTMENTS enable row movement;
alter  table  JOBS enable row movement;
alter  table  JOB_HISTORY enable row movement;

alter  table  EMPLOYEES shrink space compact;
alter  table  REGIONS shrink space compact;
alter  table  LOCATIONS shrink space compact;
alter  table  DEPARTMENTS shrink space compact;
alter  table  JOBS shrink space compact;
alter  table  JOB_HISTORY shrink space compact;

alter  table  EMPLOYEES shrink space;
alter  table  REGIONS shrink space;
alter  table  LOCATIONS shrink space;
alter  table  DEPARTMENTS shrink space;
alter  table  JOBS shrink space;
alter  table  JOB_HISTORY shrink space;

[oracle@ora19c ~]$ pwd
/home/oracle
[oracle@ora19c ~]$ vi run_alter_table.sh
[oracle@ora19c ~]$
[oracle@ora19c ~]$ cat run_alter_table.sh
# /home/oracle/run_alter_table.sh
#!/bin/bash

export ORACLE_SID=ORA19       # DB SID
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s scott/tiger <<EOF
@/home/oracle/scripts/alter_table.sql
EOF

[oracle@ora19c ~]$ chmod +x /home/oracle/run_alter_table.sh
[oracle@ora19c ~]$
[oracle@ora19c ~]$ crontab -e
crontab: installing new crontab
[oracle@ora19c ~]$

crontab -e

---

🔸 문제6. SH 계정의 모든 테이블을 reorg 작업을 하고 관련된 인덱스들이 invalid 안되었는지 확인하기

select 'alter  table  ' || table_name || ' enable row movement;'
 from all_tables
 where owner='SH'
 
 union all

 select 'alter  table  ' || table_name || ' shrink space compact;'
 from all_tables
 where owner='SH'
 
 union all 
 

 select 'alter  table  ' || table_name || ' shrink space;'
 from all_tables
 where owner='SH';
 
alter  table  TIMES enable row movement;
alter  table  PRODUCTS enable row movement;
alter  table  CHANNELS enable row movement;
alter  table  PROMOTIONS enable row movement;
alter  table  CUSTOMERS enable row movement;
alter  table  COUNTRIES enable row movement;
alter  table  SALES enable row movement;
alter  table  COSTS enable row movement;

alter  table  TIMES shrink space compact;
alter  table  PRODUCTS shrink space compact;
alter  table  CHANNELS shrink space compact;
alter  table  PROMOTIONS shrink space compact;
alter  table  CUSTOMERS shrink space compact;
alter  table  COUNTRIES shrink space compact;
alter  table  SALES shrink space compact;
alter  table  COSTS shrink space compact;

alter  table  TIMES shrink space;
alter  table  PRODUCTS shrink space;
alter  table  CHANNELS shrink space;
alter  table  PROMOTIONS shrink space;
alter  table  CUSTOMERS shrink space;
alter  table  COUNTRIES shrink space;
alter  table  SALES shrink space;
alter  table  COSTS shrink space;


select index_name, table_name, status
from user_indexes;