### 서브파티션 테이블에서 DBA_PART_TABLES.DEF_SUBPARTITION_COUNT 가 정상적으로 보이지 않는 경우의 해결 방법
아래 스크립트는 저희가 보통 오렌지 등 툴로 직접 스크립트를 추출하게 되면 아래 처럼 보일겁니다.
이렇게 보이면 정상적으로 파티션에 대한 정보를 모두 보이므로 스크립트 상에 문제가 없는것을 확인 할 수 있고
아마 아래 스크립트로 생성을 하기도 할겁니다.
여기서 문제가 하나 발견 되었습니다.
DBA_PART_TABLES.DEF_SUBPARTITION_COUNT 가 정상적으로 보이지 않는 것을 확인 했습니다.
1. 테스트 스크립트 ( 오렌지등 툴로 추출한 스크립트..)
CREATE TABLE SHWOO.TKT_BKTSEAT_T
(
CO_CD VARCHAR2 (4) NOT NULL,
BKT_SALE_NO VARCHAR2 (16) NOT NULL,
SEQ NUMBER (6) NOT NULL,
REG_K_ID VARCHAR2 (14),
REG_DT DATE,
UPD_K_ID VARCHAR2 (14),
UPD_DT DATE
)
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (CO_CD, BKT_SALE_NO)
SUBPARTITION BY HASH (BKT_SALE_NO)
(
PARTITION PRH_PRM_201010 VALUES LESS THAN ('A416', '201011')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201010_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201011 VALUES LESS THAN ('A416', '201012')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201011_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201012 VALUES LESS THAN ('A416', '201101')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201012_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S8
TABLESPACE USERS
)
)
ENABLE ROW MOVEMENT ;
-- 생성 완료..
2. DBA_PART_TABLES 딕셔너리 확인 결과 이상 현상 발견..
( DEF_SUBPARTITION_COUNT 값이 1로 표시 )
set line 200
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT
FROM DBA_PART_TABLES
WHERE TABLE_NAME='TKT_BKTSEAT_T';
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------------------------------ --------- --------- --------------- ----------------------
SHWOO TKT_BKTSEAT_T RANGE HASH 3 1
Elapsed: 00:00:00.00
>
########################################################
#### 위의 해결 방법은 아래와 같습니다.
########################################################
1. 테이블 삭제 후 재 생성 수행 ( 주석 반드시 확인)
DROP TABLE SHWOO.TKT_BKTSEAT_T ;
CREATE TABLE SHWOO.TKT_BKTSEAT_T
(
CO_CD VARCHAR2 (4) NOT NULL,
BKT_SALE_NO VARCHAR2 (16) NOT NULL,
SEQ NUMBER (6) NOT NULL,
REG_K_ID VARCHAR2 (14),
REG_DT DATE,
UPD_K_ID VARCHAR2 (14),
UPD_DT DATE
)
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
BUFFER_POOL DEFAULT
)
PARTITION BY RANGE (CO_CD, BKT_SALE_NO)
SUBPARTITION BY HASH (BKT_SALE_NO)
SUBPARTITION 8 -- 반드시 서브파티션을 몇개로 나누어 졌는지 여부를 표시 해 준다. (기본은 무조건 1로 표시 합니다.)
(
PARTITION PRH_PRM_201010 VALUES LESS THAN ('A416', '201011')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201010_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201010_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201011 VALUES LESS THAN ('A416', '201012')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201011_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201011_S8
TABLESPACE USERS
)
,
PARTITION PRH_PRM_201012 VALUES LESS THAN ('A416', '201101')
TABLESPACE USERS
PCTFREE 10
INITRANS 10
MAXTRANS 255
STORAGE
(
INITIAL 2097152
NEXT 2097152
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
LOGGING
NOCOMPRESS
(
SUBPARTITION PRH_PRM_201012_S1
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S2
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S3
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S4
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S5
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S6
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S7
TABLESPACE USERS
,
SUBPARTITION PRH_PRM_201012_S8
TABLESPACE USERS
)
)
ENABLE ROW MOVEMENT ;
2. DBA_PART_TABLES 딕셔너리 확인 결과
( DEF_SUBPARTITION_COUNT 값이 정상적으로 8 로 설정 됨)
>
SELECT OWNER, TABLE_NAME, PARTITIONING_TYPE, SUBPARTITIONING_TYPE, PARTITION_COUNT, DEF_SUBPARTITION_COUNT
FROM DBA_PART_TABLES
WHERE TABLE_NAME='TKT_BKTSEAT_T';
OWNER TABLE_NAME PARTITION SUBPARTIT PARTITION_COUNT DEF_SUBPARTITION_COUNT
------------------------------ ------------------------------ --------- --------- --------------- ----------------------
SHWOO TKT_BKTSEAT_T RANGE HASH 3 8
Elapsed: 00:00:00.01
>
>
################################################################################
######## 또는 아래 처럼 반드시 확인 후 처리 요망..
################################################################################
1. PLSQL 패키지를 이용하여 사전 DDL 문 확인 하기.
SELECT DBMS_METADATA.GET_DDL('TABLE','TKT_BKTSEAT_T','SHWOO') FROM DUAL;
2. DDL 문을 확인 후 테이블을 생성 해 주시면 됩니다.
CREATE TABLE "SHWOO"."TKT_BKTSEAT_T"
( "CO_CD" VARCHAR2(4) NOT NULL ENABLE,
"BKT_SALE_NO" VARCHAR2(16) NOT NULL ENABLE,
"SEQ" NUMBER(6,0) NOT NULL ENABLE,
"REG_K_ID" VARCHAR2(14),
"REG_DT" DATE,
"UPD_K_ID" VARCHAR2(14),
"UPD_DT" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("CO_CD","BKT_SALE_NO")
SUBPARTITION BY HASH ("BKT_SALE_NO")
SUBPARTITIONS 8 -- 이부분 값이 정상 적이지 않으면 수정 후 생성 해 주시면 됩니다.
(PARTITION "PRH_PRM_201010" VALUES LESS THAN ('A416', '201011')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201010_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201010_S8"
TABLESPACE "USERS" NOCOMPRESS ) ,
PARTITION "PRH_PRM_201011" VALUES LESS THAN ('A416', '201012')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201011_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201011_S8"
TABLESPACE "USERS" NOCOMPRESS ) ,
PARTITION "PRH_PRM_201012" VALUES LESS THAN ('A416', '201101')
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
STORAGE(INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" NOCOMPRESS LOGGING
( SUBPARTITION "PRH_PRM_201012_S1"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S2"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S3"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S4"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S5"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S6"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S7"
TABLESPACE "USERS" NOCOMPRESS ,
SUBPARTITION "PRH_PRM_201012_S8"
TABLESPACE "USERS" NOCOMPRESS ) ) ENABLE ROW MOVEMENT
;
댓글 없음:
댓글 쓰기