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 버그이다.

pupustory@님의 글을 참조하여 적용하였습니다.

아래 쿼리는 oracle에서 active sesseion이 어떤 작업을 수행하고 있는지 보여주는 쿼리입니다.
SELECT A.LAST_CALL_ET EL_TIME, TO_CHAR(SYSDATE, 'yyyyMMdd hh24:mi:ss') DT, A.INST_ID , A.SID, A.SERIAL# , A.EVENT, A.SECONDS_IN_WAIT, A.USERNAME , DECODE(A.MODULE,NULL,A.PROGRAM,SUBSTR(A.MODULE,1,30)) MODULE, A.SQL_ID, DECODE(A.COMMAND,3,'S',2,'I',6,'U',7,'D',COMMAND) C, A.OSUSER, B.SPID, (SELECT OBJECT_NAME FROM DBA_OBJECTS C WHERE C.OBJECT_ID = ROW_WAIT_OBJ# )||'->'|| a.row_wait_obj#||'.'|| row_wait_file#||'.'|| row_wait_block#||'.'||row_wait_row# "OBJ_NAME", DECODE(SUBSTR(A.EVENT,1,3),'enq', DECODE(CHR(BITAND(A.P1,-16777216)/16777215)||CHR(BITAND(A.P1, 16711680)/65535), 'TX', CHR(BITAND(A.P1,-16777216)/16777215)|| CHR(BITAND(A.P1, 16711680)/65535)||'-'|| TO_CHAR( BITAND(A.P1, 65535) )||','|| 'USN.SLOT:'||BITAND(A.P2, 4294901760)/65536||'.'|| BITAND(A.P2, 65535)||','||'WRAP:'||A.P3, A.P1||','||A.P2||','||A.P3), a.p1||','||a.p2||','||a.p3) PARAM FROM GV$SESSION A, GV$PROCESS B WHERE WAIT_CLASS <> 'Idle' AND STATUS ='ACTIVE' AND A.INST_ID = B.INST_ID AND A.PADDR = B.ADDR(+) ORDER BY A.LAST_CALL_ET

수행시간(ET), obj_name, event, sql_id 컬럼을 위주로 모니터링 하면, 분석에 많은 도움을 받을 수 있습니다.

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 를 이용해서 정보를 조회할 수 있다.

## LOCK이 걸린 쿼리문 가져오기

SET LINESIZE 160

COLUMN ORACLE_USER  FORMAT A10
COLUMN LOCKWAIT     FORMAT A10
COLUMN OBJECT_OWNER FORMAT A10
COLUMN OBJECT_NAME  FORMAT A20
COLUMN SQL_TEXT     FORMAT A80

SELECT
    B.USERNAME  ORACLE_USER,
    C.OWNER     OBJECT_OWNER,
    C.OBJECT    OBJECT_NAME,
    P.PID       "PROCESS_ID",
    B.SID       "SESSION_ID",
    B.SERIAL#,
    B.PROCESS,
    B.LOCKWAIT,
    A.SQL_TEXT  SQL_TEXT
FROM
    V$SQLTEXT A,
    V$SESSION B,
    V$PROCESS P,
    V$ACCESS C
WHERE
    A.ADDRESS       = B.SQL_ADDRESS
AND P.ADDR          = B.PADDR
AND A.HASH_VALUE    = B.SQL_HASH_VALUE
AND B.SID           = C.SID
AND C.OWNER IN('TMAX')
;

알아두면 유용하다.. -_-;;

해당 세션을 kill 할때는 alter system session kill 'session_id, serial#';

오라클에는 언어_지역간에 관한 몇가지 환경설정이 있다. 보통은 initdb.ora에 설정되는 것으로 되어 있지만, 개발환경이 원래 그렇게 심플하지는 않으므로, 사용하기에 따라 다른 나라의 포맷을 사용해야 할 경우가 있다.

가령 우리가 CURRENCY TYPE을 볼때, KOREA TERRITORY 변수에선 \ 으로 보일테지만 AMERICA에선 $로 표시되는 등등의 일이다.

이런 환경변수는 SESSION이 제일 우선적으로 적용되고, CLIENT, SERVER 순으로 적용된다. 즉, 최우선권은 ALTER SESSION으로 적용되는 변수이다(어떻게 보면 당연하다).

아래는 그러한 환경변수를 다루기 위한 몇가지 방법이다. 설명은 생략한다.

NLS_* 환경변수 확인하기.

select * from v$nls_parameters;

NLS_* 환경변수 변경하기

alter session set nls_language='KOREAN';

변경가능한 NLS_* 환경변수 찾기

select * from v$nls_valid_values;

2008/12/16 - [IT/DBMS] - 맨날 까먹는 것 #1 - ORACLE에서 매개변수 무시하기
2009/01/30 - [IT/DBMS] - 맨날 까먹는 것 #2 - ORACLE IMPORT시 해당테이블에 대한 SYNONYM이 걸려 있으면

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

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하려고 시도한다

오라클 sql 스크립트 적용시 주석문에 &라도 있는 날이면 입력변수를 넣으라고 요구한다. 이것을 무시하는 방법은...

근데 이렇게 써놓고 나면 꼭 안 잊어먹더라-_-;;