오라클 19c 파티션 테이블 high_value 컬럼 잘라서 보는 방법

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 파티션 테이블 high_value 컬럼 자르는 방법

오라클에서 파티션테이블이 존재하는 경우 해당 파티션의 high_value를 보기 위해 dba_tab_partitions를 조회함

이때 high_value 컬럼을 가공해서 보고 싶은 경우 long 타입이라 substr 함수등이 사용 할수 없음

 

 

샘플테이블 생성

SQL>
drop table ptable purge;
create table ptable
(
col1 varchar2(8),
col2 number
)
tablespace users
partition by range (col1)
(
partition p202101 values less than ('20210200'),
partition p202102 values less than ('20210300'),
partition p202103 values less than ('20210400'),
partition p202104 values less than ('20210500'),
partition p202105 values less than ('20210600'),
partition p202106 values less than ('20210700'),
partition p202107 values less than ('20210800'),
partition p202108 values less than ('20210900'),
partition p202109 values less than ('20211000'),
partition p202110 values less than ('20211100'),
partition p202111 values less than ('20211200'),
partition p202112 values less than ('20211300'),
partition p_max values less than (maxvalue)
)
;

 

 

high value 일반 조회 쿼리 실행

SQL>
set lines 200 pages 1000
col table_name for a15
col partition_name for a15
col high_value for a80
select table_name, partition_name, high_value
from dba_tab_partitions
where table_name = 'PTABLE';
 
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- ------------------------------
PTABLE          P202101         '20210200'
PTABLE          P202102         '20210300'
PTABLE          P202103         '20210400'
PTABLE          P202104         '20210500'
PTABLE          P202105         '20210600'
PTABLE          P202106         '20210700'
PTABLE          P202107         '20210800'
PTABLE          P202108         '20210900'
PTABLE          P202109         '20211000'
PTABLE          P202110         '20211100'
PTABLE          P202111         '20211200'
PTABLE          P202112         '20211300'
PTABLE          P_MAX           MAXVALUE
 
13 rows selected.

정상적으로 수행됨

 

 

high value 컬럼에 substr 을 사용해 2번째부터 6번째 자리까지 잘라서 보는 쿼리 실행

SQL>
set lines 200 pages 1000
col table_name for a15
col partition_name for a15
col high_value for a80
select table_name, partition_name, substr(high_value, 2, 6)
from dba_tab_partitions
where table_name = 'PTABLE';
 
select table_name, partition_name, substr(high_value, 2, 6)
                                          *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

에러가 발생함

 

 

이때 두가지 방식을 이용해 high_value 컬럼을 잘라서 볼수 있음

방법1. ctas 이용

아래와 같이 high_value 컬럼에 to_lob를 사용해 임시 테이블을 만들어서 substr을 이용할 수있음

임시 테이블 생성

SQL>
create table temp_high as 
select table_name, partition_name, high_value, to_lob(high_value) as hv_clob
from dba_tab_partitions
where table_name = 'PTABLE';

 

 

substr을 이용해 조회

SQL>
select table_name, partition_name, substr(hv_clob, 2, 6) as high_value
from temp_high;
 
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
--------------- --------------- --------------------------------------------------------------------------------
PTABLE          P202101         202102
PTABLE          P202102         202103
PTABLE          P202103         202104
PTABLE          P202104         202105
PTABLE          P202105         202106
PTABLE          P202106         202107
PTABLE          P202107         202108
PTABLE          P202108         202109
PTABLE          P202109         202110
PTABLE          P202110         202111
PTABLE          P202111         202112
PTABLE          P202112         202113
PTABLE          P_MAX           AXVALU
 
13 rows selected.

정상적으로 잘라서 볼수 있음

 

 

방법2. plsql 이용

원하는 컬럼을 plsql 소스에 각각 넣어서 수행하면 substr로 잘라서 볼수 있음

방법1보다는 번거롭지만 새로운 테이블을 만들지 않아도 되는 방식임

SQL>
set serveroutput on
declare
  cursor c_tab_partitions is
    select table_name, partition_name, high_value
    from dba_tab_partitions
    where table_name = 'PTABLE';
  lv_table_name varchar2(30);
  lv_partition_name varchar2(30);
  lv_high_value varchar2(10);
begin
  for rec in c_tab_partitions loop
    lv_table_name := rec.table_name;
    lv_partition_name := rec.partition_name;
    lv_high_value := substr(rec.high_value, 2, 6);
    -- 출력
    dbms_output.put_line(lv_table_name || ' ' || lv_partition_name || ' ' || lv_high_value);
  end loop;
end;
/
 
PTABLE P202101 202102
PTABLE P202102 202103
PTABLE P202103 202104
PTABLE P202104 202105
PTABLE P202105 202106
PTABLE P202106 202107
PTABLE P202107 202108
PTABLE P202108 202109
PTABLE P202109 202110
PTABLE P202110 202111
PTABLE P202111 202112
PTABLE P202112 202113
PTABLE P_MAX AXVALU
 
PL/SQL procedure successfully completed.

정상적으로 잘라서 볼수 있음

 

 

참조 : https://stackoverflow.com/questions/27523530/oracle-error-inconsistent-datatypes-expected-char-got-long

https://positivemh.tistory.com/882

오라클 19c 파티션 테이블 멀티 truncate 테스트