제가 운영하는 시스템 중 테이블을 년+월로 range 파티셔닝 하는 시스템이 있습니다.

2018년 말에도 2019년 파티셔닝 작업을 했는데 이 때 유용하게 사용한 쿼리 공유 드립니다.

포인트는 데이터 변환이 안되는 LONG type의 high_value 컬럼을 with xml 기능을 사용하여 

변환이 가능한 varchar2로 작업 테이블에 저장 후, 작업 테이블에서 필요한 데이터를 꺼내 사용하는 것입니다.

 

-1. high_value 저장 하고 가져올 테이블 생성

 
create table testuser.tb_high_value 
(
TABLE_NAME VARCHAR2(100),
PARTITION_NAME VARCHAR2(100),
HIGH_VALUE VARCHAR2(100)
)
tablespace users;

 

=> 파티션의 high_value 값은 데이터 타입이 long으로 replace 등으로 변환이 되지 않음

high_value 값을 테이블에 varchar2 값으로 저장하여 쉽게 변환 할 수 있도록 작업 테이블 생성

 

* LONG 타입 변환시 발생하는 에러

SQL> select replace(high_value,'2018','2019') from dba_tab_partitions
  2  where table_name='TB_ML_APC_CUST_D';
select replace(high_value,'2018','2019') from dba_tab_partitions
               *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
 

-2. with xml 사용하여 long type의  high_value 컬럼을 varchar2로 변경

 

INSERT INTO testuser.TB_HIGH_VALUE
with xml as (
  select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from DBA_TAB_PARTITIONS
  WHERE TABLE_NAME IN 
''TB_TEST_TABLE''
  ') as x
  from   dual
)
  select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
         extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,
         extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
  from   xml x, 
         table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws;

=> 위에서 생성한 작업용 테이블에 high_value 값을 varchar2로 저장함

 

-3. add partition 쿼리 추출

 

select 'alter table '||a.table_owner||'.'||a.table_name||' add partition  '||lower(replace(a.partition_name,'2018','2019'))||' values less than (',c.high_value,') tablespace '||replace(a.tablespace_name,'2018','2019')
||case when count(b.index_name) >= 1 then ' update indexes ('||listagg(b.owner||'.'||b.index_name||' ('||' partition '||replace(a.partition_name,'2018','2019')||' tablespace '||replace(replace(a.tablespace_name,'2018','2019'),'PD01','PX01')||')',', ')  within group (order by b.owner, b.index_name)||')' end||' ;' 
from   dba_tab_partitions a
      ,dba_indexes b, testuser.tb_high_value c
where  a.table_owner = b.table_owner(+) and a.table_name = b.table_name(+)
and    a.table_name in
(
'TB_TEST_TABLE'
)
and    (a.partition_name like 'PR_2018%')
and a.table_name=c.table_name
and a.partition_name=c.partition_name
group by a.table_owner, a.table_name, a.partition_name,a.tablespace_name,c.partition_name,c.high_value
order by a.table_owner, a.table_name, a.partition_name;