|
안녕하세요.
한동안 사랑넷이 접속이 안되서 안오게 되다가, 오랜만에 와보니 접속이 되는군요. ^^
다름이 아니고, 대용량 테이블(대략 20억건?)에서 인덱스를 생성하려고 하는데,
ORA-01652: unable to extend temp segment by 8192 in tablespace TS_MA_VALUE_IDX
라는 에러가 떨어지네요.
시스템뷰가 막혀있는게 많아서 많은 정보를 볼 수는 없지만, USER_TABLESPACES를 참조하는 것은
가능해서 테이블스페이스 정보를 봤습니다.
TS_MA_VALUE_IDX의 경우, 아래와 같이 설정되어 있었습니다.
BLOCK_SIZE : 8192
INITIAL_EXTENT : 65536
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
MIN_EXTLEN : 65536
STATUS : ONLINE
CONTENTS : PERMANENT
LOGGING : LOGGING
FORCE_LOGGING : NO
EXTENT_MANAGEMENT : LOCAL
ALLOCATION_TYPE : SYSTEM
SEGMENT_SPACE_MANAGEMENT : AUTO
DEF_TAB_COMPRESSION : DISABLED
RETENTION : NOT APPLY
BIGFILE : NO
MAX_EXTENTS는 무제한으로 create했을 경우, 2147483645(2GB)로 보인다고 알고 있는데 맞죠?
해당 스키마의 총용량은 300GB라고 들었습니다.
이 경우, 인덱스 작성시 테이블 스페이스가 부족하면, 300GB까지 스페이스가 확장되는 거 아닌가요?
다들 바쁘시겠지만, 귀중한 답변을 기다리겠습니다.
ps. DBA로부터 용량을 300GB까지 늘렸다는 말은 들었는데, 이걸 확인하려면 어떤 뷰를 봐야할까요??
----------------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX NMAPMGR.MA_FAB_VALUE_LPK
ON NMAPMGR.MA_FAB_VALUE (SHIP_ID, ITEM_ID, INFILE_DATE, COMM_ID)
LOCAL(
PARTITION RAWDATA_201011 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201012 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201101 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201102 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201103 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201104 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201105 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201106 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201107 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201108 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201109 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201110 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201111 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_201112 TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT ) ,
PARTITION RAWDATA_MAX TABLESPACE TS_MA_VALUE_IDX NOLOGGING PCTFREE 0 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 64K NEXT 0M MINEXTENTS 1 MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT )
)
----------------------------------------------------------------------------------------------------
|
