viatoris, 인생의 잡동사니들

환경: Oracle 9i(OS 상관없음)

증상
일반적인 테이블에 alter table .. add column .. 시 제목(ORA-22856: cannot add columns to object tables)과 같은 에러 발생.

ORA-22856 내용 : object table이라서 안된다는 내용.하지만 해당 테이블은 object table이 아니다.

솔루션
1. 해당 table(또는 table partition)이 compress된 상태일 경우 발생할 수 있다.
-- nocompress로 변경 SQL> alter table table_name nocompress; or SQL> alter table table_name move partition partition_name nocompress; -- move tablespace 수행(그렇지 않으면 동일에러 발생) SQL> alter table table_name move tablespace to tablespace_name; SQL> 2. 드물게 pctfree가 적을때도 가능하다.

결론
에러 메시지가 Oracle 9i 버그이다.

oracle sql을 튜닝하거나, 문제가 발생했을때, 세션에 대해 좀 더 세밀하게 trace 할 필요가 있을때가 있다. 이때는 oradebug을 이용해서 하도록 한다.

참고문서: http://www.oracleutilities.com/wiki/index.php?title=Oradebug

1. os pid 얻기

SQL> select username, spid from v$process;
2. trace할 pid 설정하기
SQL> oradebug setospid 22803. trace 파일 설정하기

oradebug tracefile_name c:\oratrace_file\mooracle_ora_2280.trc

4. trace level 설정

SQL> oradebug event 10046 trace name context forever, level 12

5. trace 종료

SQL> oradebug event 10046 trace name context off

특정 테이블에 수행된 DML문의 실행시간을 알아내기

SELECT  SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM TABLE_NAME;

이 방법외에 오라클의 AUDIT 기능을 활성화 시키거나(이 경우는 SELECT 문까지 체크 가능하다), TRIGGER를 활용하는 방법이 있다.

참고 URL:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1590655700346557237

 DBA가 아니라면 크게 와 닿지 않는 것이 통계정보(statistics)이다. 통계정보의 용도는 oracle의 optimizer가 올바른 query plan을 짤수 있도록 정보를 제공하는 것이다. 즉, 이 query를 1번 index를 이용해서 실행할 것인지, 2번 index를 이용할지, 그도 아니면 full table scan을 할 것인지를 결정하는데 통계정보를 활용한다. 이 말은 이 통계정보가 잘못되어 있다면 DB의 성능잇슈를 불러오게 될 것이란 말이 된다.

 일반적으로 데이터의 수가 많지 않으면, 아무리 full table scan을 하더라도 성능문제는 일어나지 않는다. 왠만큼 데이터가 쌓이면 성능문제가 일어나고 이 시점에선 적절한 index를 생성해주면 성능문제는 사라진다. 하지만 여기서 더 데이터가 쌓이게 되고, 필요한 index의 수가 많아지면 얘기가 달라진다. 잘못된 index를 선택하는 것은 full table scan을 하는 것보다 성능이 느리다. 이 때 올바른 index를 선택 할 수 있도록 정보를 제공하는 것이 통계정보의 용도이다.

 그렇다면 이 통계정보는 어떤때에 갱신되어야 할까? 대량의 정보(10g에서는 10%정도가 바뀌면 통계정보를 갱신해야 할 것으로 판단한다)가 갱신되거나(insert, delete, update), 새로운 index가 추가되었을때는 갱신할 필요가 있다(무조건 해야하는 것은 아니다. 데이터양이 많지 않다면 크게 문제되진 않는다. 0.01초나 0.001초나 산술적으론 10배차이지만 체감속도엔 큰 차이가 없다).

 통계정보는 언제나 갱신되는 것이 좋은가? 결론은 아니다. 통계정보의 갱신은 SGA안의 모든 SQL Cursor의 validation을 무너뜨린다. 그에 따라 갱신된 이후부터 들어오는 모든 SQL에 대해 hard parse를 불러오게 되고, 결과적으로 이 시점에서 DB성능의 문제를 야기한다(이 시점 - hard parse를 야기하는 - 을 넘긴다면 성능은 좋아질 수도 있다). 그렇다고 통계정보를 갱신하지 않을수도 없다.

 9i에서는 통계정보의 갱신으로 모든 SQL이 hard parse되는 사태를 막기 위해, analyze option에 no_invalidate option(true or false)를 제공했다. false는 invalidate 시키지 않고, age-out(oracle은 새로운 SQL을 SGA에 올리고, 그때가지 SGA에 들어 있는 SQL중에서 사용된지 가장 오래된 SQL을 SGA에서 제거한다. 이를 age-out이라 한다)될때가지 기다린 후에 새로 load될때 새로운 통계정보로 plan을 활용하게 되고, true는 즉시 invalidate시키고 hard parse를 일으킨다. 이런 방식으로 hard parse가 일순간에 몰리는 현상을 분산시킬 수 있지만, 이 옵션은 false로 했을때, age-out 되는 시점을 알아내기가 어렵다는 단점이 있다(즉, 그냥 기다려야 한다).

 10g에서는 유사한 문제(hard parse 문제)를 피하기 위해서, 갱신된 통계정보를 즉시 반영하지 않는다. 대신dbms_stats.auto_invalidate 옵션을 통해서 반영되는 시간을 결정할 수 있다(default  5시간). 이는 9i에서 반영시점을 DBA가 결정하지 못해서 생겼던 문제들을 위한 해결책이라 할 수 있다. DBA는 통계정보는 언제나 갱신 할 수 있고, DB의 사용시간이 적은 시간대를 통해 hard parse를 발생시킬 수 있다. 또는, 지연시간을 유연하게 분배해서 hard parse가 일어나는 시점을 적절하게 분배할 수 있다.

10g는 기본적으로 자동화된 통계수집을 제공한다(자동으로 통계정보를 갱신한다는 뜻). 이를 제어할 수 있는 방법은 다음과 같다(dbms_stats 패키지 활용).
 

1. 특정 owner나 object의 통계정보 수집 중지

EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');

EXECUTE DBMS_STATS.LOCK_SCHEMA_STATS ('owner name');

2. 통계수집 자체를 중지

exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

3. 수집된 통계정보를 즉시 반영

alter system flush shared_pool;

또, 10g에서 dbms_stats. 패키지로 갱신되는 통계정보는 history 형태로 SGA에 저장하며 mmon은 주기적으로(default 60분) 이를 스냅샷 형태로 AWR에 저장한다. 이 AWR은 SYSAUX 테이블 스페이스에 존재하며,

$ORACLE_HOME/rdbms/admin/awrrpt.sql 를 이용해서 정보를 조회할 수 있다.

그냥 감리문서 정리하면서 만들어낸 몇가지 쿼리. 더 쓸만한게 있음 댓글좀(엑셀정리가 싫어요.. ㅠ.ㅠ) ;;

1. 테이블설명과 테이블의 인덱스 목록 가져오기

select
    a.table_name,
    c.comments,
    a.tablespace_name,
    b.index_name
from
    user_tables a,
    user_tab_comments c,
    user_indexes b
where
    a.table_name = b.table_name(+)
and a.table_name = c.table_name(+)
order by a.table_name, b.index_name

2. 테이블별 컬럼 스(테이블별로 순번 매겨지는 꼼수좀 알려주셈.. ㅠ.ㅠ. 비고는 그냥 엑셀에 붙이기 편해 넣은거..;)

select
    c.comments,
    a.table_name,
    '' 순번,
    b.column_name,
    d.comments,
    b.nullable,
    b.data_type,
    b.data_length,
    '' 비고
from
    user_tables a,
    user_tab_comments c,
    user_tab_cols b,
    user_col_comments d
where
    a.table_name = b.table_name(+)
and a.table_name = c.table_name(+)
and a.table_name = d.table_name(+)
and b.column_name = d.column_name
order by a.table_name

3. 테이블별 인덱스 스펙(형식이라는 곳은 PK/UK를 넣는 것인데 네이밍 룰에 따라 적당히 조절~)

select
    a.table_name,
    b.comments,
    a.index_name,
    '' 형식,
    c.column_name,
    d.comments
from
    user_indexes a,
    user_tab_comments b,
    user_ind_columns c,
    user_col_comments d
where
    a.table_name = b.table_name(+)
and a.index_name = c.index_name
and a.table_name = d.table_name(+)
and c.column_name = d.column_name
order by a.table_name, a.index_name

이상이 어제 엑셀 노가다의 결과였슴미다...

말 그대로, 오라클에서 IMPORT하려는 테이블명과 동일한 SYNONYM이 존재하는 경우, 테이블을 생성하지 않고 해당 SYNONYM에 DATA를 IMPORT하려고 시도한다

UPDATE ACCOUNT

SET PAYAMT = (SELECT PAYAMT FROM ACCOUNT WHERE SEQNO = 111) + 10000

WHERE

SEQNO = 111;

참 멋지다고 하지 아니할수 없다..  나에게 천운이 따르는걸까? 천벌이 따르는걸까?

천재 아니면 생각이 넘 많은듯.. -_-;

지금은 서버나 클라이언트나 예전에 비해 엄청난 발전을 이루었다. 그래서 조금 잘못되고, 어플리케이션의 성능이 최선은 고사하고 좀 떨어지더라도 하드웨어의 성능이 많은 것을 메꾸어준다. 물론 그마저 사용자가 많아지면 - 흔히 말해서 임계점을 돌파하면 소프트웨어의 성능을 개선하지 않으면 해결되진 않지만 말이다. 하지만 이런 정도의 문제는 개발단계에서 테스트되지 않으므로 개발업체는 그대로 빠지고, 후에 유지보수를 맞는쪽에서 피박을 쓰게 되지만 말이다.

뭐, 오늘 하려는 말은 다름이 아니라 DB 어플리케이션의 가장 기본이 될수 있는 ERD이다. ERD를 그릴때 쉽게 범하는 실수중에 하나는 될 것 같으니깐 하는거다. 그리고, 이렇게 하면 개발이 더 쉬울 것이라고 쉽게 지레짐작하는 것이다. 지금은 어떤 이유로 사라져버린 포스팅에서 잠깐 언급했었지만, 쉽게 생각되는 꼼수는 나중에 두배 세배의 손품/발품/머리품을 팔아야 한다는 것을 명심해야 한다. 그리고 생각하기 쉬운 꼼수하나는 바로 PK의 선정을 무조건 적게 하려는 것이다.

물론 너무 많은 PK컬럼이 선정된다면 정규화나 아키텍쳐 부분에서 잘못된 것이 없는지 고민해야 한다.

여기 개발자 A씨가 있다. A씨는  3-5개정도의 PK 컬럼을 JOIN 해서 사용하는 것에 대해 불만스럽다 그래서 고안해내는 것이 Oracle Object인 SEQUENCE이다. 일련번호는 그 자체로 UNIQUE하므로 PK 및 UNIQUE Key의 역할을 충분히 해낼수 있다. 그래서 부모테이블에 SEQUENCE를 KEY로 활용하고 자식테이블에 그 KEY를 물려줄 생각을 하게 된다. 이로써 A씨는 JOIN은 한컬럼만 하고서도 충분히 구현할 수 있다고 믿었다.

A씨는 그 두개의 테이블에서 데이터를 조회하며, 충분히 만족했다. (본인의 생각에는)필요없이 많은 컬럼을 조인하지 않고도 충분히 원하는 결과를 이끌어 낼수 있었던 것이다. 그러나 A씨는 UPDATE문을 작성하면서, 해당데이터가 있는지 두건 이상 존재할수도 있다는 사실에 에러처리를 해야했다(PK에서 당연히 해줘야할 중복에러를 SEQUENCE로는 해결할 수 없으므로). INSERT를 하면서도 기존에 "실제" KEY가 있지는 않은지 체크해서 있으면 UPDATE로직으로 돌려야 했다. 원래대로라면 ORACLE의 Constraints로 해결되어야 할 문제들을 본인이 짠 로직으로 작성해야 하기 시작한 것이다. 또 DELETE문을 처리하면서 자식테이블에는 부모의 "실제" KEY가 없는 관계로 항상 부모테이블을 뒤져서 자식테이블의 부모 SEQUENCE를 찾아내야했다.

관계된 테이블의 두개일 경우 이문제는 견딜만 했다. 하지만 관계된 테이블이 3개 4개로 되면 될수록 3중 4중으로 코드량이 늘어났다.

그리고 A씨는 개발이 완료된 이후, 데이터를 마이그레이션하면서 A씨는 상심에 빠졌다. 자식테이블에 SEQUENCE로 부모의 값을 집어넣다보니 마이그레이션이 제대로 되었는지 검증하기가 어려워진 것이다. 자식테이블의 Identity를 확인하기 위해서는 항상 부모테이블을 뒤져야 했고 결국 이는 성능문제로 이어졌다.

SEQUENCE는 일련번호일 뿐이지 Identity가 아니다. 나를 숫자로 표현할 수 없듯이 테이블의 데이터도 단순한 일련번호로는 의미가 없다. 아, 물론 그렇게 처리해도 무방한 데이터가 있기도 하다. 하지만 대부분의 경우는 아니고, 다른 Key를 가지는게 훨씬 효율적일 것이다.

대부분의 개발에서 제일먼저 작성되는 것은 SELECT이고, 이를 위해 저런 생각을 하는 사람은 심심찮게 많다(진짜다). 하지만 이는 나중에 더 큰 비용을 치뤄야 한다. 꼭 자신은 아니라도 자신의 일을 물려받는 후배나, 다른 업체나 누구던지 그 문제로 더 많은 시간을 뺏기며  그렇게 설계를 한 당신을 욕하고 있을 것이다.

###원래는 적당한 그림을 추가하고 싶으나.. 귀찮은 관계로 그림은 나중에..;; ###

imp/exp를 통해 데이터를 이전하는 방법은 매우 쉽고, 속도도 백만단위의 데이터가 아니라면 그럭저럭 만족할만한 수준이 된다. 하지만 이 데이터가 수백만건이 넘어서기 시작하고 테이블의 갯수가 늘어나면, exp/imp로는 해결할 수 없을 정도로 속도상에서 문제가 생긴다. imp 의 경우 insert 문을 이용한 데이터 입력과 별다른 바가 없기 때문이다. 물론 exp/imp의 속도를 늘이는 방법은 존재하지만, 한계가 있는 것은 사실이다. 오라클 10g에서는 이 exp/imp의 속도를 향상한 expdp/impdp(DataPump)를 제공한다. 이 얘기는 다음에 기회가 되면 다루어보자.

여기서 얘기하려고 하는 것은 제목에서 쓰여있다시피, Transportable Tablespace에 관한 내용이다. 사용방법은 간단하다. export를 할때, trasportable_tablespace=y 라는 옵션을 주기만 하면 된다. 물론 그 이전에 고려할 사항들은 있다.

확인사항 1. Transportable Tablespace는 8i 이상에서 지원한다. 2. Transportable Tablespace는 동일한 OS에서 동일버전 이상의 Target로만 이동이 가능하다    단, 10g는 OS가 달라도 가능하다 3.두 DB의 block size와 charset은 동일해야 한다. 4. Target에는 동일한 이름의 Tablespace가 없어야 한다.

위의 조건이 충족한다면, 아래와 같은 몇줄의 명령으로 가능하다. 단, 파일을 카피하는 시간은 감수해야할 것이다.

내용 source에서 alter tablespace TB_NAME read only; --테이블 스페이스를 read only 상태로 만든다. exp '/as sysdba' file=TB_NAME.dmp tablespace=TB_NAME transportable_tablespace=y  -- export target에서 source에서 transport 한 dump 파일과 테이블스페이스의 datafile을 복사해온다. imp '/as sysdba'  file=TB_NAME.dmp transport_tablespace=y datafile=$DIR/DATAFILE.dbf

같은 이름의 tablespace가 있다면 에러가 나므로 백업을 받은 후 없에고 수행한다.

뜬금없이는 아니고.. os와의 characterset의 차이로 export가 안되는 경우가 종종 있다.

오라클에 sysdba로 접속하여..

두개의 캐릭터셋이 다르다면 맞춰준다.

bash일경우

잠온다.