Maybe...

:REGDATE - 20160117

SELECT :REGDATE,
    TO_CHAR( TO_DATE(:REGDATE,'YYYYMMDD') ,'YYYY-IW') AS YW
  , TO_CHAR( TO_DATE(:REGDATE,'YYYYMMDD') +( 1 - TO_CHAR(TO_DATE(:REGDATE,'YYYYMMDD') - 1, 'D') ), 'YYYY-MM-DD') AS START_DATE
  , TO_CHAR( TO_DATE(:REGDATE,'YYYYMMDD') +( 1 + 6 - TO_CHAR(TO_DATE(:REGDATE,'YYYYMMDD') - 1, 'D') ), 'YYYY-MM-DD') AS END_DATE
FROM DUAL

## 생년월일로 나이 가져오기

TRUNC(MONTHS_BETWEEN(sysdate, TO_DATE(birth,'yyyymmdd'))/12)

## 첫번째 띄워쓰기까지 가져오기.

 regexp_substr(addr1, '[^ ]+',1,1)

출처 : http://www.gurubee.net/m/lecture/1021

Outer Join 이란?

• - Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 테이터를 반환하지 못한다.
• - 동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용 한다.
• - Outer Join 연산자는 "(+)" 이다.
• - 조인시 값이 없는 조인측에 "(+)"를 위치 시킨다.
• - Outer Join 연산자는 표현식의 한 편에만 올 수 있다.

Outer Join 예제

Equi Join과 Outer Join의 비교.

Outer Join을 사용하는 테이블에 추가로 조건절이 있다면 (+)연산자를 모두 해야 한다.

LEFT, RIGHT, FULL Outer Join

Oracle9i 부터는 ANSI/ISO SQL 표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 한다.

LEFT OUTER JOIN

LEFT OUTERL JOIN은 오른쪽 테이블(아래 예제에서 emp테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

RIGHT OUTER JOIN

RIGHT OUTERL JOIN은 왼쪽 테이블(아래 예제에서 emp테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

FULL OUTER JOIN

FULL OUTERL JOIN은 양쪽 테이블 모두 Outer Join걸어야 하는 경우 사용 한다.

문서에 대하여

• - 강좌 URL : http://www.gurubee.com/m/lecture/1021
• - 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• - 모든 강좌는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

출처 : https://community.oracle.com/thread/938686

1. 각세션별

 

SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||

nvl(lower(ssn.machine), ins.host_name) "SESSION",

to_char(prc.spid, '999999999') "PID/THREAD",

to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' "CURRENT_SIZE",

to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' "MAXIMUM_SIZE"

FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,

v$instance ins, v$statname stat1, v$statname stat2

WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'

AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'

AND se1.sid = ssn.sid

AND se2.sid = ssn.sid

AND ssn.paddr = bgp.paddr (+)

AND ssn.paddr = prc.addr (+)

order by CURRENT_SIZE desc 

 

2. 전체 사용량 

 

SELECT 

to_char(( sum( se1.value)/1024)/1024, '999G999G990D00') || ' MB' "CURRENT_SIZE",

to_char(( sum(se2.value)/1024)/1024, '999G999G990D00') || ' MB' "MAXIMUM_SIZE"

FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,

v$instance ins, v$statname stat1, v$statname stat2

WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'

AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'

AND se1.sid = ssn.sid

AND se2.sid = ssn.sid

AND ssn.paddr = bgp.paddr (+)

AND ssn.paddr = prc.addr (+)

order by CURRENT_SIZE desc 

## 실행계획 보기

explain plan for
select COUNT(*) from TEST
;

select * from table (DBMS_XPLAN.DISPLAY);

## 통계정보생성

exec dbms_stats.gather_table_stats('my','emp');

출처 : http://jhroom.co.kr/db_oracle/23481

본 연재에서는 지난 "Partitioned Table의 Index" 연재에 이어 Partitioned Table 및 Index의 관리에 대해서 사례별로 알아보고자 한다.

파티션을 사용하는 경우에는 이전 연재에서 살펴본 바와 같이 업무의 성격과 관리하려는 데이터의 성격, 데이터의 엑세스 방식에 따라서 파티션의 종류와 파티션키의 선정 및 파티션 인덱스의 선정이 결정되어 진다.

지금까지 파티션 및 파티션 인덱스의 개념과 특성에 대해서 알아 보았으므로 이제 파티션과 파티션 인덱스의 관리적인 부분에 대해서 알아보자.

파티션은 파티션 테이블 전체가 하나의 오브젝트로 등록되어 관리되며 개별 파티션도 역시 하나의 오브젝트로 관리됨으로 일반  테이블과 인덱스와는 약간 다른 처리가 발생 하게 된다.

파티션 테이블의 일반적인 백업과 복구에서는 파티션 특성을 제외하면 일반 테이블의 백업과 복구와 특별한 차이가 없다.

하지만 파티션 인덱스에서는 로컬인덱스와 글로벌 인덱스에 따라서 다른 처리가 발생하게 되며 관리상의 부하도 클 수 있으므로 주의해야 한다.

파티션의 관리는 크게 다음과 같이 나눌 수 있다.

• 파티션 추가/삭제/병합
• 파티션 변환(파티션 테이블과 일반테이블의 변환)
• 기타 관리

파티션의 종류에 따라 가능한 작업이 정해져 있으며 이는 종류에 따른 파티션의 특성에 기인한다. 파티션의 종류별로 가능한 ALTER 명령은 오라클 매뉴얼을 참고하기 바란다.

(http://otn.oracle.com/docs/products/oracle9i/
doc_library/901_doc/server.901/a90117/partiti.htm#1311)

파티션의 관리는 실제적인 방법과 명령의 사례가 필요하므로 테스트를 위해서 오라클 9i에서 추가된 데모 데이터베이스 Sales History를 예로 든다.

테스트 환경 설정

오라클 9i에 접속 가능한 경우에는 sh 유저의 데이터 베이스에서 확인이 가능한다. 없는 경우를 위해서 간단히 다음과 같이 작업한다.

테스트를 위해서 먼저 partition table을 다음과 같이 생성한다. 테스트를 위해서 파티션의 데이터 타입을 DD-MON-YYYY 에서 YYYYMMDD로 하였다.

CREATE TABLE sales
    (prod_id       NUMBER(6)    CONSTRAINT sales_product_nn  NOT NULL,
     cust_id       NUMBER       CONSTRAINT sales_customer_nn NOT NULL,
     time_id       DATE         CONSTRAINT sales_time_nn     NOT NULL,
     channel_id    CHAR(1)      CONSTRAINT sales_channel_nn  NOT NULL,
     promo_id      NUMBER(6)   ,
     quantity_sold NUMBER(3)    CONSTRAINT sales_quantity_nn NOT NULL,
     amount        NUMBER(10,2) CONSTRAINT sales_amount_nn   NOT NULL,
     cost          NUMBER(10,2) CONSTRAINT sales_cost_nn     NOT NULL)
   PARTITION BY RANGE (time_id)
   (PARTITION Q1_1998 VALUES LESS THAN (TO_DATE('19980401','YYYYMMDD')),
    PARTITION Q2_1998 VALUES LESS THAN (TO_DATE('19980701','YYYYMMDD')),
    PARTITION Q3_1998 VALUES LESS THAN (TO_DATE('19981001','YYYYMMDD')),
    PARTITION Q4_1998 VALUES LESS THAN (TO_DATE('19990101','YYYYMMDD')),
    PARTITION Q1_1999 VALUES LESS THAN (TO_DATE('19990401','YYYYMMDD')),
    PARTITION Q2_1999 VALUES LESS THAN (TO_DATE('19990701','YYYYMMDD')),
    PARTITION Q3_1999 VALUES LESS THAN (TO_DATE('19991001','YYYYMMDD')),
    PARTITION Q4_1999 VALUES LESS THAN (TO_DATE('20000101','YYYYMMDD')),
    PARTITION Q1_2000 VALUES LESS THAN (TO_DATE('20000401','YYYYMMDD')),
    PARTITION Q2_2000 VALUES LESS THAN (TO_DATE('20000701','YYYYMMDD')),
    PARTITION Q3_2000 VALUES LESS THAN (TO_DATE('20001001','YYYYMMDD')),
    PARTITION Q4_9999 VALUES LESS THAN (MAXVALUE));

INSERT INTO SALES
SELECT PROD_ID, CUST_ID,
       ADD_MONTHS(TIME_ID,(ROWNUM-1)*3)+1 TIME_ID,
       CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD, ROWNUM
 FROM SH.SALES
 WHERE ROWNUM <= 20 ;

COMMIT ;


 

위 테이블은 time_id를 partition key로 한 range partition table이다. 다음엔 이 sales table에 SQL*Loader를 이용하여 데이터를 loading한다. 해당 control 화일 및 데이터 화일은 다음 디렉토리에 있다. 이 디렉토리로 이동해서 작업을 하면 된다.

$ORACLE_HOME/demo/schema/sales_history

Note :

Oracle 9i부터는 $ORACLE_HOME/demo/schema 서브 디렉토리에 HR(human resource), OE(order entry), PM(product media), SH(sales history), QS(Queued Shipping) 등의 샘플 스키마 및 데이터들이 있다.

가장 일반적인 RANGE PARTITION을 기준으로 설명하며 COMPOSITE PARTITION의 경우 서브파티션만 HASH로 추가된 경우이므로 RANGE PARTITITON 과 같다고 보면 되며 HASH와 LIST 파티션의 경우에는 따로 설명하였다.

1. 파티션의 추가/삭제/분할/병합

1.1. 추가

기존에 존재하는 파티션에 새로운 파티션을 추가하려는 경우 (예:리스트 파티션에서 2001년 1분기 데이터를 추가하려는 경우 등) 예제처럼 MAXVALUE로 지정한 파티션처럼 기존 파티션안에 대상이 포함되어 있다면 아래의 분할(SPLIT)을 이용하여 처리하고 MAXVALUE가 없는 경우처럼 기존 파티션의 범위 밖으로 새로이 추가되는 경우라면 다음과 같이 한다.

SQL>alter table sales add partition Q1_2001
values less than ('20010101') tablespace pts_2001Q1 ;

MAXVALUE로 지정한 파티션이 있는데 위와 같이 추가(add)하면 다음과 같은 에러가 발생한다.

"ORA-14120:DATE열에 파티션 바운드가 불완전하게 지정되었습니다."

1.2. 삭제

해당 단위 파티션을 삭제하는 경우 1998년 1분기 및 이전 데이터를 더 이상 관리하지 않아 해당 파티션 Q1_1998을 없애고 싶은 경우는 다음과 같이 실행한다.

SQL>alter table sales drop partition Q1_1998 ;

해당 파티션 및 파티션 안의 모든 데이터가 같이 삭제 된다. drop된 후에 새로 1998년 1분기 및 이전 데이타가 입력되면 Q2_1998 partition이 less then ('19980701') 으로 되어 있으므로 Q2_1998 partition에 저장된다.

1.3. 분할

하나의 파티션을 분할하여 여러 개의 파티션으로 재 생성시 MAXVALUE를 이용한 경우에 경계값으로 새로 추가되는 경우, MAXVALUE로 바운드를 지정하지 않은경우 새로운 최대값을 지정하는, 기존 하나의 파티션을 둘 이상의 파티션으로 나누는 경우 등이 해당된다.

새로 추가될 데이터 2001년 1분기 데이터를 저장할 파티션을 추가하려는 경우 기존 MAXVALUE 파티션에 이 조건이 포함되어 있으므로 이 파티션을 분할하면 된다.

SQL>alter table sales split partition Q4_9999 at (TO_DATE('20010101','YYYYMMDD'))
into (partition Q4_2000 tablespace pts_2000Q4, partition Q4_9999 tablespace pts_9999Q4)

분할되는 기존 파티션을 그대로 사용할 경우에는 기존 파티션 정보를 그대로 기술해 주면 된다.

SPLIT PARTITION은 매우 큰 partition table이나 view를 handling 하는데 유용하다.

1.4. 병합

파티션을 병합하는 방법은 MERGE명령을 이용하는 방법과 SQL로 임시테이블에 옮기는 방법과 Export/Import를 이용하는 방법이 있다.

MERGE를 이용하는 방법이 빠르고 단순하게 처리 가능한다.

• MERGE를 이용하는 방법

SQL> ALTER TABLE SALES MERGE PARTITIONS Q2_1999, Q3_1999 INTO PARTITION Q3_1999 ;

1.5. 이름 변경

partition name 을 바꾸고 싶다면 다음과 같이 실행한다.

SQL>alter table sales rename partition Q4_9999 to Q4_Max ;

1.6. 테이블 스페이스 이동

partition Q4_9999을 저장하는 tablespace를 pts_9999Q4 에서 pts_maxQ4로 바꾸고 싶은 경우아래와 같은 command를 사용한다.

SQL>alter table sales move partition Q4_9999 tablespace pts_maxQ4 nologging ;

1.7. Truncate

partition의 data를 모두 삭제하려면 truncate하는 방법을 사용할 수 있는 데, truncate는 rollback 이 불가능하며 특정 partition 전체를 삭제하므로 주의하여 사용해야 한다.

SQL>alter table sales truncate partition Q4_9999 ;

1.8. 속성 변경

partition table은 특정 partition의 속성만 변경할 수도 있고, table의 속성을 변경 하여 전체 partition에 대해 동일한 변경을 할 수 있다.

SQL>alter table sales storage(next 10M);

sales 의 모든 partition의 next 값이 변경된다.

SQL>alter table part_tbl modify partition Q4_9999 storage(maxextents 100);

Q4_9999 partition의 maxextents 값만 변경한다.

1.9. 인덱스 관리

파티션은 파티션별로 RowID를 다르게 갖게 됨으로 파티션의 변경이 발생하게 되면 RowID의 변경이 발생하게 된다.

그래서 실제 파티션 테이블의 RowID와 변경된 파티션 로컬 인덱스와 글로벌 인덱스의  RowID와 가 일치하지 않게 되어 인덱스 사용 중지 즉, IU상태(INDEX UNUSABLE)가 발생하게 되어 인덱스의 사용 불가 및 재 성성에 따른 운영 및 관리상의 부하 및 재 생성에 따른 시스템의 부하가 발생하게 된다.

partition index가 IU 상태가 되었을때 그 partition index을 사용 하게 되면 다음과 같은 에러가 발생하게 된다.

SQL>SELECT /*+ INDEX(SALES SALES_GPNK1) */ *
FROM SALES WHERE PROD_ID >= 10000

ORA-01502: 인덱스 'SALES_GPNK1'또는 인덱스 분할영역은 사용할 수없는 상태이다

이와 같은 에러가 발생시 해당 파티션을 해당 인덱스를 통해서 엑세스 할 수 없게 됨으로 해당 어플리케이션의 수행을 유지 할 수 없는 상황이 발생하게 된다.

물론 인덱스를 사용하지 않고 해당 파티션을 TABLE FULL SCAN 하면 에러가 발생 하지 않는다.

하지만 옵티마이져가 실행계획 수립시에 INDEX UNUSABLE 상태 여부를 체크하지 않으므로 해당 인덱스를 사용하는 실행계획이 수립되고 실제 수행시 오류가 발생하게 됨으로 이미 운용중인 어플리케이션이나 SQL이 정상 수행되지 않는다.

이런 경우에는 skip_unusable_indexes = TRUE 파라메터를 지정하면 수행시에 해당 인덱스가 UNUSABLE이면 이를 사용하지 않고 TABLE FULL SCAN 등을 한다. 경우에 따라서 유용하게 사용 가능하므로 고려 해 볼 수 있다.

어떤 partition이 IU 상태가 되면 그 partition을 사용하기 전에 재생성(rebuild)해야 한다. 그러나 IU partition을 제외한 다른 partition만을 읽거나 DML을 수행하는 작업은 IU partition을 access하지 않는 한 오류가 발생하지 않는다. 단, IU partition을 rebuild하기 전에 split이나 rename이 가능하며, IU상태인 global index를 drop하는 것도 가능하다. 관리상으로 IU상태인 인덱스를 다시 수행 가능하게 하는 작업은 경우에 따라 업무적인 부하가 큰 경우가 발생 할 수 있다.

파티션은 원래 대용량 데이터에 대한 처리이므로 인덱스 재생성 작업은 부하가 큰 작업인 경우가 많다. 또한 로컬파티션 인덱스의 경우에는 해당 로컬인덱스만 재 생성해주면 되지만 글로벌 파티션 인덱스의 경우 글로벌 인덱스를 한번에 재 생성할 수 없고 개별 파티션 단위로만 재생성이 가능하므로 글로벌 인덱스의 파티션 수 만큼 나누어서 지정하여 재생성 해야 한다.

이와 같은 관리상의 문제점을 해결하기 위한 여러가지 방법이 있으며 가장 큰 원인은 파티션의 변경과 파티션 데이터의 RowID가 변경되는 경우이다.

RowID가 변경되는 경우는 자주 발생하지 않고 어쩔수 없는 경우가 대부분일 것이므로 그렇다고 하여도 파티션의 변경은 가능한 발생하지 않게 유도할 수 있다.

특정 파티션의 데이터를 파티션에서 제외시키는(백업으로 이동 등) 경우에는 실제 데이터를 로우 단위로 Delete 하는것이 아니라면 파티션에 대한 명령에서는 실제 데이터의 RowID 변경이 일어난다면 어떤 방법을 써도 Global Index의 Unusable을 피할 수는 없다.

Partition Index를 Unusable상태로 만들 수 있는 명령.(Oracle9i 기준).

(표를 보면 알겠지만 Unusable상태가 되는 것은 실제 파티션 테이블 데이터의 RowID를 변경시킨 경우(파티션 단위의 변경이 발생시에도 해당 파티션에 실제 로우 데이터가 있어서 RowID 변경이 발생된 경우)에 해당된다.)

•  인덱스 재생성(Rebuild)

partition table관련 작업을 한 후에는 table에 걸려 있는 local(partitioned) index 나 global index를 반드시 rebuild해 주어야 한다.

특정 partition의 index를 rebuild 하려면

SQL>alter index idx01_sales rebuild partition idx_Q4_9999 ;

global index를 rebuild하려면 non-partition index 인덱스인 경우는

SQL>alter index sales_idx01 rebuild;

와 같이 처리하면 되지만 global partition index 인덱스는 인덱스 전체를 한번에 재생성 할 수 없다.

SQL>alter index SALES_GPNK1 rebuild

"ORA-14086:분할영역된 인덱스는 전체를 다시 만들 수 없습니다." 와 같은 에러를 발생하게 된다.

SQL>alter index SALES_GPNK1 rebuild partition SALES_GPI01 ;

위와 같이 인덱스 파티션 단위로 재 생성 해주어야만 한다.

global partition index의 경우 non-partition index 이든 partition index 이든 파티션 테이블 관련 작업인 경우 재 생성이 필수 이므로 파티션 테이블별로 global index의 rebuild 작업용의 스크립트를 작성해 놓아야 빠른 시간안에 관련 인덱스를 재 생성 할 수 있다.

1.10. Index Unusable 상태 예방

특히 Range Partition의 경우 대개 날짜를 키로 하여 파티션되며 시간의 경과에 따라 관리하는 데이터의 날짜값도 증가하여 새로운 파티션으로 관리해야 하는 경우가 많다.

오라클에서 NULL 데이터는 정렬에서 마지막에 위치하게 되어 파티션 키의 값이 NULL인 경우 MAXVALUE 파티션에 존재하게 된다.

즉 키에 NULL인 데이터가 존재시에는 MAXVALUE에 실제로 데이터가 위치하게 되며 이 경우 최신의 데이터를 위해서 파티션을 추가시 MAXVALUE를 SPLIT하게 되면 실제 데이터 로우가 있는 파티션에서 변경 작업이 일어났으므로 Global Index 의 Unusable를 피할 수 없게 된다.

이를 방지 하기 위해서는 실제 운영데이터의 최종 파티션과 MAXVALUE 파티션 사이에 dummy 파티션을 하나 두어 NULL값이 위치한 MAXVALUE 파티션을 분리하는 것이 좋다.

예를 들어 날짜가 키인 경우 '20021001'인 현재 파티션과 MAXVALUE 사이에 '21000101' 이나 '99990101' 과 같은 dummy 파티션을 유지 하여 실무에서 유용하게 사용하는 방법이다.

1.11. 파티션 키 데이터의 변경

파티션을 운영 중에 키 컬럼의 데이터가 변경되어 파티션을 이동 시에 다음과 같은 에러를 발생시킨다.

"ORA-14402: 분할영역 키 열을 수정하는 것은 분할영역 변경이 생깁니다."

이 문제를 해결하기 위해 잘못하면  프로그램 상에서 Delete & Insert 방식으로 코딩을 하기도 하지만 Oracle8i부터 SQL의 Syntax에 이 문제에 대한 옵션이 존재한다.

ENABLE ROW MOVEMENT 또는 DISABLE ROW MOVEMENT

디폴트로 DISABLE ROW MOVEMENT 가 셋팅 되어 있다. 즉 파티션간 로우의 이동이 금지되어 있었기 때문에 위와 같은 에러를 만나게 된 것이다.

CREATE TABLE시 이 옵션을 주거나, ALTER TABLE 명령어로 설정 값을 바꿀 수 있다.

ALTER TABLE SALES ENABLE ROW MOVEMENT;

2. 파티션 테이블의 변환 (파티션과 일반테이블의 변환)

파티션 테이블과 일반 테이블을 직접 변환시키는 명령은 EXCHANGE 명령이 있으며 다른 데이터베이스에의 이전이라면 Export/Import를 이용하는 방법이 있다.

EXCHANGE 명령은 특정 partition을 non partitioned table로, non partitioned table을 특정 partition으로 변환시켜 주는 역할을 한다.

내부적으로는 data(index) segment를 교환(data dictionary의 변경)하는 것이지 실제로 각각의 row들을 옮기는 것은 아니다.

Export/Import를 이하는 방법은 다른 데이터 베이스에서의 이전 등이나 재생성 등에 유용하며 일반 테이블의 Import와 동일한 절차로 수행된다.

table-level import시 우선 table creation 문장을 수행하고 row insert문을 수행 하는 것과 마찬가지로, partition-level import도 우선 partitioned table의 생성문장을 수행하고 row insert문을 수행하게 된다.

따라서 ignore=y option등을 적절히 사용하면, non-partitioned table과 partitioned table간의 변경, partitioned table의 구조 변경 등을 수행할 수 있게 된다.

2.1. EXCHANGE 이용 시

• 파티션 => 일반 테이블의 변환

table을 변환하기 위해 dummy table을 생성하고, alter table EXCHANGE PARTITION 명령어를 통해 수행한다. 이 명령어는 매우 빨리 data dictionary 를 update 시킨다

i) 변경하려는 파티션과 동일한 구조를 같는 테이블을 생성한다.

SQL>create table sales_EXCH as select * from sales where 1 = 2 ;

ii) alter table EXCHANGE을 이용하여 변경한다.

SQL>ALTER TABLE sales EXCHANGE PARTITION Q4_9999 WITH TABLE
sales_EXCH WITHOUT VALIDATION ;

EXCHANGE 으로 변경시에 파티션 테이블의 해당 파티션의 구조는 그대로 유지된다.(기존 파티션은 데이터 로우는 없는 빈 파티션이 된다) 즉 EXCHANGE를 사용하면 실제 데이터가 파티션에서는 더 이상 유지되지 못하므로 해당 경우에만 사용할 수 있는 단점이 있다.

WITHOUT VALIDATION 을 사용하면 유일성 조건 등의 기존 테이블에 걸린 제약 조건을 체크하지 않으므로 변환 전에 미리 체크 하는 것이 좋다.

• 일반 테이블 => 파티션 의 변환

alter table EXCHANGE을 이용하여 변경한다.

SQL>ALTER TABLE sales EXCHANGE PARTITION Q3_1999 WITH TABLE
sales_EXCH WITHOUT VALIDATION ;

변환될 파티션테이블의 인덱스와 완전히 매치되는 인덱스 구조를 가지고 있다면 INCLUDING INDEX 옵션을 추가하여 로컬인덱스 생성에 대한 시간을 줄일 수 있다.

EXCHANGE는 전체적으로 가장 빠르게 파티션 관련 데이터를 변경할 수 있는 방법이다.

2.2. CTAS 이용 시

파티션 테이블의 특정 테이블을 일반 테이블로 변경시에 EXCHANGE를 사용시에는 실 데이터의 변경이 발생하므로 또 다른 방법으로 CTAS(CREATE TABLE .. NOLOGGING AS SELECT ..)가 있다.

적당한 SORT_AREA_SIZE의 지정과 NOLOGGING옵션으로 원하는 성능을 얻을 수 있다.

3. 기타 관리

파티션의 관리에서 발생하게 되는 몇 가지 중요한 실례를 살펴보기로 한다.

3.1. 파티션 데이터 파일 삭제시의 처리

Partitioned table의 partition이 포함되어 있는 datafile이 OS level에서 삭제된 경우, 해당 datafile을 offline drop하고 open한 후 해당 table을 access하고자 하는 경우, 또는 해당 TableSpace를 Drop하고자 하면 에러가 발생한다. 다음과 같은 error가 유발된다.

ORA-00376: (File cannot be read at this time) ,
ORA-14404: (Partitioned table contains partitions in a different tablespace)

파일 삭제에 대한 처리는 일반 데이터 파일 삭제의 처리와 같다.

이때 파티션의 복구에 대해서는 다음과 같이 3가지로 조치 가능 한다.

1) 해당 partitioned table전체를 drop한 후 recreate한다.

• drop table sales ;
• drop tablespace pts_9999Q4 including contents ;
• tablespace recreate.
• table rebuild .

문제점은 전체 partitioned table을 복구해야 하므로 시간이 오래  소요된다.

2) 해당 partition만을 drop후 재 생성 한다.

• alter table sales drop partition Q4_9999 ;
• drop tablespace pts_9999Q4 including contents ;
• tablespace recreate.
• add partition or split partition.
• 해당 partition만의 data를 reload한다.

partitioned table전체 data를 reload하는 것 보다 짧은 시간이 소요된다.

3) temporary table을 생성하여 exchange한다.

• sales table과 동일한 구조의 dummy table을 다른 tablespace에 생성한다.
  • create table dummy_sales as select * from sales where 1=2;
• 이 table을 문제의 partition과 exchange한다.
  • alter table sales exchange partition Q4_9999 with table dummy_sales without validation ;
• drop tablespace pts_9999Q4 including contents ;
• 새로운 partition에 필요한 data를 load한다.

3.2. INDEX UNUSABLE 상태 예방 (9iR1 기준)

Oracle 9i에서는 base table에 대한 DDL 수행 시 UPDATE GLOBAL INDEXES라는 option을 사용해서 global index에 대해서도 ONLINE으로 update할 수 있는 기능을 제공한다.

이 기능으로 Global index가 base table과 동시에 변경이 되어 Global index에 대한 rebuild를 관리하지 않아도 된다.

• 가능한 DDL 명령

: SPLIT, MERGE, ADD, MOVE, COALESCE, DROP, TRUNCATE, EXCHANGE UPDATE GLOBAL INDEXES라는 option을 partition specification 바로 뒤, 그리고 PARALLEL clause 바로 앞에 기술해 두면 ONLINE으로 base table에 대한 DDL을 수행 하면서 global index를 그에 맞춰 변경시킬 수 있다.

SQL>ALTER TABLE sales DROP PARTITION sales1 UPDATE GLOBAL INDEXES
PARALLEL (DEGREE 4);

OnLine 변경은 전체적인 처리의 부하가 Rebuild보다 크며 트랜젝션에 대한 처리 시간이 증가 하므로 이 방법은 처리해야 할 row의 양이 적을 때 유용하다.

변경하려는 데이터에 대한 처리와 재생성의 부하를 비교하여 적정한 방법을 선택하는 것이 좋다. (즉 전체 글로벌 인덱스를 재 생성하는 시간보다 ONLINE update 하는 시간이 적을 때 유리하며 다량의 데이터의 경우에는 오히려 전체 시간이 더 걸릴 수 도 있으므로 테스트하여 운영 시스템마다 적정선의 처리 한계를 정하는 것이 좋다.)

3.3. 파티션 추가시 로컬인덱스의 속성 관리

ALTER TABLE ADD PARTITION을 할 경우 table partition의 경우 table partition, tablespace, physical storage attribute를 지정할 수 있으나, local index partition의 경우 관련 syntax를 지원하지 않는다.

오라클은 자동으로 index partition, tablespace, physical storage attribute를 지정한다. (ALTER TABLE SPLIT PARTITION도 마찬가지)

new index partition의 이름은 new table partition과 같은 이름이 할당되고, tablespace, physical storage attribute는 local index에 지정된 default값이 사용되고, local index에 지정된 default값이 없으면 user의 default tablespace가 사용된다. 그러므로 PARTITION을 추가하여 사용하는 경우 index partition 관리에 주의가 요구된다.

지금까지 파티션 테이블 및 인덱스의 관리에서 발생할 수 있는 문제점들을 살펴보았다.

실무에서 파티션의 INDEX UNUSABLE 상태를 일으킬 수 있는 상황들에 대해서 특히 주의 깊게 보고 운영 버전별로 테스트 해보아 각 시스템에 맞는 관리 지침을 수립 하여 숙지 하는 것이 중요하다.

이상으로 Partition table 및 인덱스의 관리에 대한 일반적인 사례 중심으로 알아보았으며 다음 연재에서는 Partition table 활용 및 제한 사항에 대하여 다루게 될 것이다.

출처 : http://shadou.tistory.com/127

No. 12208

ADD PARTITION시 NEW LOCAL INDEX PARTITION에 관하여
==================================================

개   요
=======
ALTER TABLE ADD PARTITION을 할 경우 table partition의 경우 table partition,
tablespace, physical storage attribute를 지정할 수 있으나, 
local index partition의 경우 관련 syntax를 지원하지 않는다 (8.1.6 현재)

오라클은 자동으로 index partition, tablespace, physical storage attribute를 지정한다. 
(ALTER TABLE SPLIT PARTITION도 마찬가지)

new index partition의 이름은 new table partition과 같은 이름이 할당되고,
tablespace, physical storage attribute는 local index에 지정된 default값이 사용되고,
local index에 지정된 default값이 없으면 user의 default tablespace가 사용된다.

그러므로 PARTITION 추가하여 사용하는 경우 index partition 관리에 주의가 요구된다. 
다음은 ALTER TABLE ADD PARTITION한 이후에 index partition을 관리하는 예제이다.

예   제
=======
-- Create partition table --
CREATE TABLE tbl_emp (
  empno NUMBER,
  ename VARCHAR2(20),
  job   VARCHAR2(20)
)
PARTITION BY RANGE ( empno ) (
  PARTITION pt_tbl_emp01 VALUES LESS THAN( 1000 ) TABLESPACE data_emp01,
  PARTITION pt_tbl_emp02 VALUES LESS THAN( 2000 ) TABLESPACE data_emp02,
  PARTITION pt_tbl_emp03 VALUES LESS THAN( 3000 ) TABLESPACE data_emp03
);

-- Create local index --
CREATE INDEX idx_emp ON tbl_emp( empno )
  TABLESPACE users --> 여기서 local index default tablespace 지정
  LOCAL (
    PARTITION pt_idx_emp01 TABLESPACE indx_emp01,
    PARTITION pt_idx_emp02 TABLESPACE indx_emp02,
    PARTITION pt_idx_emp03 TABLESPACE indx_emp03
  );

-- PK 설정 --
ALTER TABLE tbl_emp ADD CONSTRAINT pk_emp PRIMARY KEY ( empno );

-- Local Index를 PK index로 사용함을 확인 --
SELECT index_name FROM  user_indexes WHERE table_name = 'TBL_EMP';

INDEX_NAME
------------------------------
IDX_EMP

DROP INDEX idx_emp
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

-- Add partition --
ALTER TABLE tbl_emp  
  ADD PARTITION pt_tbl_emp04 VALUES LESS THAN( 4000 ) 
  TABLESPACE data_emp04;

-- 추가된 Local index partition의 이름, Tablespace를 확인한다 --
SELECT partition_name, tablespace_name, status
FROM  user_ind_partitions
ORDER BY 1;

PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
PT_IDX_EMP01                   INDX_EMP01                     USABLE
PT_IDX_EMP02                   INDX_EMP02                     USABLE
PT_IDX_EMP03                   INDX_EMP03                     USABLE
PT_TBL_EMP04                   USERS                          USABLE

-- Tablespace는 local index의 default tablespace에 생긴다 --
SELECT index_name, def_tablespace_name
FROM user_part_indexes;

INDEX_NAME                     DEF_TABLESPACE_NAME
------------------------------ ------------------------------
IDX_EMP                        USERS

--< Local index partition name 변경 >--
ALTER INDEX idx_emp
RENAME PARTITION pt_tbl_emp04 TO pt_idx_emp04;

--< Local index tablespace 변경 >--
ALTER INDEX idx_emp
REBUILD PARTITION pt_idx_emp04 TABLESPACE indx_emp04;

SELECT partition_name, tablespace_name, status
FROM  user_ind_partitions
ORDER BY 1;

-- 변경된 Local index partition의 이름, Tablespace를 확인한다 --
PARTITION_NAME                 TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ --------
PT_IDX_EMP01                   INDX_EMP01                     USABLE
PT_IDX_EMP02                   INDX_EMP02                     USABLE
PT_IDX_EMP03                   INDX_EMP03                     USABLE
PT_IDX_EMP04                   INDX_EMP04                     USABLE

* 다음과 같이 local index의 default tablespace를 변경할 수도 있다.
alter index <indexname> 
modify default attributes tablespace <new tablespace name>;

[출처] http://show.ideatree.kr/pages/index.php3?board_content_idx=1003

[해결방법] ORA-01461: LONG 값은 LONG 열에만 입력할 수 있습니다

1.OJDBC 버전이 Release 2 인지 확인
2.CLOB 처리시 PreparedStatement를 사용할 경우,
2-1.
먼저 대상 clob field를 INSERT INTO TEST_TABLE(CLOBFIELD)VALUES(empty_clob()) 혹은
UPDATE TEST_TABLE SET CLOBFIELD = empty_clob WHERE idx=999 와 같이 empty_clob()으로
초기화 시킨다.
2-2.
다른 사용자가 CLOB UPDATE 중간에 변경을 못하도록
CLOB Row를 먼저 SELECT~FOR UPDATE 로 잠그고,
SELECT ~FOR UPDATE로 부터 Clob Field값을 얻어서 java.io.Writer 객체를 생성한다.
clobField = rs.getClob(0);
Writer writer = clobField.getCharacterOutputStream();
2-3.
그리고, 입력할 clob데이타로 부터 java.io.Reader 객체를 생성한다.
Reader reader = new CharArrayReader(clobInput.toCharArray());
2-4.
버퍼를 만들어서 reader를 읽어가면서 writer에 읽은만큼 써준다.
char[] buffer = new char[1024];
while( (read = reader.read(buffer, 0, 1024) ) != -1 )
{
 writer.write(buffer, 0, read); //reader를 죽~ 읽어가면서 써준다.
}
reader.close();
writer.close();

with a as (
    select '제주특별자치도 서귀포시 광평로 105 ( 안덕면, 통나무힐스 )' bigo from dual
    union all
    select '부산광역시 강서구 대저1동 4302-22' from dual
)
select  bigo
    , regexp_substr(bigo, '[^ ]+',1,1) a
    , regexp_substr(bigo, '[^ ]+',1,2) b
    , regexp_substr(bigo, '[^ ]+',1,3) c
    , regexp_substr(bigo, '[^ ]+',1,4) d
    , regexp_substr(bigo, '[^ ]+',1,5) e
from a

윈도우7에 오라클 11g 클라이언트를 설치해보겠습니다.

오라클은 서버와 클라이언트로 나뉘어지는건 다들 아시죠?

서버는 누군가 다른 사람들이 접속할 수 있는 장소를 만드는 곳이고

클라이언트는 다른 서버로 접속하기 위한 것입니다.

따라서 클라이언트를 설치한다면

데이터베이스에 접속하는 것은 가능하나

데이터베이스를 생성하는 것은 안됩니다.

데이터베이스를 생성할 목적이라면 서버를 설치하시면 되겠습니다.

설치 방법은 간단합니다.

​http://www.oracle.com/technetwork/topics/winsoft-085727.html

홈페이지로 이동하여 밑에 두 파일을 다운로드 받습니다.

두 파일을 같은 폴더에 압축을 풀어 놓습니다.

tnsname 파일을 위치하기 위해 다운받은 폴더 밑에 network - admin 폴더를 생성하고

tnsname 파일을 위치시킵니다.

여기까지 하면 설치는 끝났습니다.

마지막으로 패스만 설정하면 설치가 완료됩니다.

<내컴퓨터>  <속성>  <고급 시스템 설정>  <환경변수>  <시스템 변수> 로 들어가

환경변수를 설정합니다.

1. ORACLE_HOME -> 다운받은 오라클 폴더(ex. C:\instantclient_11_2)

2. TNS_ADMIN -> tnsname파일이 위치한 폴더

3. path -> 다운받은 오라클 폴더(ex. C:\instantclient_11_2)

원격 접속지로 접속이 되는지 확인합니다.

시작프로그램 - 실행 - cmd 입력하여 cmd창으로 이동합니다.

sqlplus 아이디/패스워드@원격지tnsname

정상적으로 접속이 되면 설치가 완료되었습니다.

접속이 되지 않으면 tnsname 파일을 확인하여 정보가 맞는지 확인합니다. 

윈도우 환경변수 등록 (시스템 환경변수)

변수명 - NLS_LANG

변수값 - KOREAN_KOREA.KO16MSWIN949