SELECT a.OWNER, a.TABLE_NAME, a.COLUMN_NAME, b.COMMENTS, a.data_type || '(' || a.data_length || ')' AS COLUMN_TYPE FROM dba_tab_columns a, dba_col_comments b WHERE a.owner = 'OPS$USR001' AND a.table_name = 'tbUser' AND b.owner = 'OPS$USR001' AND b.table_name = 'tbUser' AND a.owner = b.owner AND a.table_name = b.table_name AND a.column_name = b.column_name ;
--owner는 조회하고자 하는 테이블 정보의 소유자 정보를 넣으면 되고 table_name에서는 조회하고자 하는 테이블 명을 입력한다.
--user 권한으로 원하는 테이블의 컬럼 정보를 조회한다.
SELECT TABLE_NAME , COLUMN_NAME , Replace(COMMENTS,' ','') FROM USER_COL_COMMENTS WHERE TABLE_NAME = ''tbUser';
ORA-165X error는 tablespace에 space가 부족해서 table이나 rollback segment extent가 할당되지 못해서 발생하는 error이다. 다음의 에러들은 tablespace에 space가 부족해서 발생하는 사항들이다. 01652, 00000, 'unable to extend temp segment by %s in tablespace %s' 01653, 00000, 'unable to extend table %s.%s by %s in tablespace %s' 01654, 00000, 'unable to extend index %s.%s by %s in tablespace %s' 01655, 00000, 'unable to extend cluster %s.%s by %s in tablespace %s'
ORA-01652, 00000, 'unable to extend temp segment by 6144 in tablespace VESSEL' 테이블 스페이스 VESSEL 에 남아있는 가장 큰 연속된 공간을 확인해 보면
SQL> SELECT MAX(blocks), MAX(bytes) 2> FROM DBA_FREE_SPACE 3> WHERE TABLESPACE_NAME = 'VESSEL';
blocks bytes 6143 12,580,864
위의 결과를 보면 현재 VESSEL 에 남아있는 가장 큰 연속된 공간은 6143 블록인데 오라클은 6144 블럭이 사용하려다 이를 할당받지 못하여 에러가 발생하게 된 것이다.
2.tablespace space 부족현상의 조치 tablespace에 space가 부족해서 에러가 발생하는 경우 아래의 몇 가지 방법을 이용해 조치가 가능하다.
(1) 데이타 화일을 추가하여 테이블스페이스의 크기를 확장한다.
SVRMGR> ALTER TABLESPACE data ADD DATAFILE '/usr/../oracle/data2.dbf' SIZE 100M;
이때의 tablespace 가 SYSTEM 일 경우는 user 의 default tablespace가 잡혀있지 않기 때문에 근본적인 해결이 필요하다. 이 경우는 무작정 tablespsace 를 늘리지 말고 user의 default tablespace를 create 후 user에게 할당해 주도록 한다.
예) SVRMGR> CREATE TABLESPACE tablespace_name datafile '......' size 100m; SVRMGR> ALTER USER user_name IDENTIFIED BY passwd > DEFAULT TABLESPACE tablespace_name > TEMPORARY TABLESPACE temp ;
(2) 테이블(rollback segment)의 storage parameter를 조정하여 현재 남아있는 영역에 들어갈 수 있도록 한다.
SQLDBA> ALTER TABLE emp STORAGE(NEXT 1M);
이 경우 tablespace가 fragmentation이 심한 경우가 아니면 효과적이지 못하다.
(3) 테이블스페이스가 fragmentation이 심한 상태이면 exp/imp를 이용하여 테이블 스페이스를 재구성 한다.
3. V7.1에서의 ORA-1652 에러 테이블이나 인덱스 등을 만들 때 자신의 TEMP TABLESPACE가 아닌 곳에서 ORA-1652(temp tablespace가 부족함) 에러가 발생하는 경우가 있다. 이와 같은 문제는 V7.1에서만 발생하는데 V7.1에서는 테이블, 인덱스 등을 병렬로 생성할 수 있다. 이를 위하여 실제로 테이블 등이 생성될 공간에 Temporary Segment를 만들게 되는데 이 과정에서 Temporary Segment를 만들 공간이 부족하게 되면 실제의 테이블이 생성되는 테이블 스페이스에 대하여 ORA-1652 에러가 발생하게 되는 것이다. 이 에러를 해결하는 방법은 에러메시지에서 보여주는 대로 해당 테이블스페이스에 Temporary Segment가 생성 될 만한 연속된 공간을 마련하여 주는 것이다.
아침에 출근해서 담배한대 피고 있는데.. 고객이 전화와서 데이터가 한개도 안올라 온다고.. 쩝.. 확인해 보니 이런 에러가..
어딘가 시스템이 트랜젝션이 제대로 안먹고 있나본데.. 쯧..
시스템을 첨부터 일일이 뜯어볼수도 없고.. 젠장.. 쯧...
"> 다른 database를 이용하지 않는 local transaction이, 비정상 종료시 자동으로 rollback되는 것과는 달리, 분산 트랜잭션의 경우 2 phase commit수행 단계중에 fail이 발생하게 되면 관여된 일부 database에서는 rollback 혹은 commit이 되고, 일부는 distributed lock이 걸린 상태로 계속 지속될 수 있다.
이렇게 pending된 transaction에 대해서는 기본적으로 Oracle의 background process인 RECO process가 자동으로 정리하여 주나, 경우에 따라 자동으로 정리가 되지 못하는 상황이 발생할 수 있다.
이렇게 정리가 되지 않아, distributed lock이 걸린 경우에는, 이후 관계된 table을 조회나 변경시 ora-1591 오류가 발생할 수 있으므로, distributed transaction이 실패한 경우 db admin이 관여하여 pending된 transaction을 정리하여 줄 필요가 있다.
distributed transaction이 오류가 발생하거나, 혹은 이후에 ora-1591이 발생하는 경우, 조치 방법을 9단계의 STEP으로 정리하였다.
*** distributed transaction의 2 phase commit에 대한 개념 및 자세한 절차는 를 참조한다.
[참고 1] 문서의 이해를 위해서 분산 환경에 포함된 node를 V817LOC와 V817REM으로 예를 들고, V817LOC node에서 transaction을 수행하였다고 가정한다.
[참고 2] 아래에 언급되는 dbms_transaction package는 기본적으로 catproc.sql script에 의해 생성되나 만약 존재하지 않는다면, cd $ORACLE_HOME/rdbms/admin directory의 dbmsutil.sql, prvtutil.plb script를 sys user에서 수행하도록 한다. (svrmgrl에서 connect internal에서 수행하는것이 일반적)
그리고 이 package는 항상 transaction의 맨 처음에 수행되어야 한다. 즉, 새로 session을 연결하여 수행하거나, 혹은 앞에 dml에 있었다면, commit이나 rollback을 수행 후 이 package를 수행하여야 한다.
아래의 STEP중 STEP 1 ~ 3까지는 문제 해결을 위해 필수적인 단계는 아니므로 바로 문제를 시급히 해결해야 하는 경우 4번부터 확인하도록 한다.
STEP 1: alert.log file을 check한다.
bdump directory의 alert.log에는 분산 트랜잭션 fail시 관계된 오류 메시지등 log가 항상 남게 된다. 예를 들면 다음과 같은 형태인데, rollback/commit되었는지, in-doubt 상태인지와 그 외에 transaction id등 정보를 확인할 수 있다.
Tue Dec 12 16:23:25 2000 ORA-02054: transaction 1.8.238 in-doubt ORA-02063: preceding line from V817REM Tue Dec 12 16:23:25 2000 DISTRIB TRAN V817LOC.WORLD.89f6eafb.1.8.238 is local tran 1.8.238 (hex=01.08.ee) insert pending prepared tran, scn=194671 (hex=0.0002f86f)
STEP 2: network 환경을 확인한다.
listener가 떠 있는지, database link가 모두 정상적인지 확인해 본다.
STEP 3: RECO process가 떠 있는지 확인한다.
os상에서 RECO process가 떠 있는지 확인하려면 다음과 같이 한다.
os> ps -ef | grep reco
RECO process는 db가 startup되면서 자동으로 구동되는 background process로 distributed recovery를 disable시키면 사라지게 된다. distributed recovery를 enable/disable시키는 방법은 아래와 같다.
SQL>alter system enable distributed recovery; SQL>alter system disable distributed recovery;
아래의 조치사항 중에서 STEP 9번을 제외하고는 기본적으로 RECO process가 자동으로 처리하는 작업과 동일하다. 그러나 여러가지 문제로 인해 RECO가 자동으로 정리하지 못한 경우 이 문서의 방법대로 manual하게 정리하여 주어야 한다.
이 조회로 인해 여러개의 row가 나오는 경우 ora-1591이나 distributed fail에 관련된 오류시 나타나는 local transaction id값과 return된 LOCAL_TRAN_ID값을 비교하여 일치하는 row를 확인하면 된다. 이때 LOCAL_TRAN_ID 값과 GLOBAL_TRAN_ID의 뒷부분의 숫자가 동일하다면 이것은 이 node가 global coordinator임을 의미한다.
STEP 5: DBA_2PC_NEIGHBORS view를 조회해 본다.
sqlplus system/manager SQL>select local_id, in_out, database, dbuser_owner, interface from dba_2pc_neighbors;
여기에 나타난 row들이 해당 분산 트랜잭션에 관여한 database 정보이다. 이때 DATABASE column 부분이 null로 나타나는 것은 현재 조회하고 있는 local database를 의미하며, IN_OUT이 OUT으로 나타나는 경우 참조하는 node정보인데, DATABASE 컬럼의 값이 해당 database를 가리키는 database link name이 된다.
이 database link name을 이용하여 다음과 같이 remote db의 DBA_2PC_PENDING을 다시 조사하여 관계된 node들의 상태를 확인할 수 있다.
SQL>select local_tran_id, global_tran_id, state, mixed, host, commit# from dba_2pc_pending@v817rem;
각 node의 DBA_2PC_PENDING의 return된 row들이 같은 분산 트랜잭션에 포함된 정보인지는 GLOBAL_TRAN_ID 값을 이용하여 확인할 수 있다.
STEP 6: commit point site를 확인한다.
commit point site에 대해서는 을 참조한다. 이 예의 경우 COMMIT_POINT_STRENGTH를 지정하지 않았기 때문에 default로 global coordinator가 아닌 V817REM이 commit point site가 된다. 일반적으로 commit point site는 global coordinator의 DBA_2PC_NEIGHBORS의 IN_OUT field가 OUT으로 나타나고 INTERFACE 부분이 C로 나타나게 된다.
commit point site가 중요한 이유는 이 node의 local transaction부분은 prepared상태를 거치지 않아 in-doubt 상태가 되는 일이 없고, 그러므로 distributed lock에 의해 조회나 DML시 오류가 발생하는 없게 된다.
이러한 이유로 제일 중요한 data를 포함하는 중심이 되는 node를 commit point site로 지정하는 것이 바람직하다.
STEP 7: DBA_2PC_PENDING의 MIXED column을 확인한다.
- MIXED값이 NO인 경우 : STEP 8 수행 - MIXED값이 YES인 경우: STEP 9 수행
DBA_2PC_PENDING에서 MIXED column을 YES나 NO의 값으로 지정하는 것은 RECO process가 결정하여 변경하게 된다. MIXED가 YES가 되는 대표적인 경우는, commit point site가 이미 commit을 수행한 상태에서 분산 트랜잭션이 fail된 경우, non-commit point site에서 prepared 상태의 transaction을 rollback force시켜 분산 트랜잭션의 consistency가 깨진 상태이다.
(STATE column의 경우 commit point site는 COMMITTED로 non-commit point site는 FORCED ROLLBACK으로 나타난다)
[참고] commit point site가 아직 commit을 수행하기 전에 분산 트랜잭션이 fail되어 commit point site가 rollback된 경우, non-commit point site에서 prepared 상태의 transaction을 commit force 하면 이것도 논리적으로는 consistency가 지켜지지 않은 것은 동일하나 이때는 MIXED column이 no인 상태가 된다. 그 이유는 commit point site가 rollback되어 DBA_2PC_PENDING view에 entry가 남지 않기 때문에 RECO가 명시적으로 mixed 상태로 인식하는 것이 불가능하기 때문으로 파악된다.
STEP 8: DBA_2PC_PENDING의 STATE column의 값을 확인한다.
CASE 8-1: STATE field값이 COMMITTED인 경우
만약 STATE가 COMMITTED인 경우는 이 local database(V817LOC)에서는 transaction이 성공적으로 commit 되었음을 나타내므로, 이 node에서는 어떠한 작업도 수행할 필요가 없다. 이 entry는 RECO process에 의해 자동으로 지워질 것이며, 만약 RECO가 어떠한 이유로 이 row를 지우지 못했다면 다음과 같이 db admin이 직접 지워도 된다. 괄호 안의 값은 local_tran_id값이다.
이렇게 V817LOC의 STATE부분이 COMMITTED인 경우는, 이미 commit point site인 V817REM은 commit된 후임을 나타낸다. 그러므로 V817REM은 STATE가 COMMITTED로 나타나거나 아니면 commit후 이미 정보가 지워져 DBA_2PC_PENDING에 정보가 나타나지 않을 수 있다. 그러므로 V817REM에 대해서는 필요한 경우 V817LOC의 앞의 조치 방법과 동일하게 DBA_2PC_PENDING의 내용만 정리하여 주면 된다.
만약 V817REM이 아닌 별도의 다른 node가 분산 트랜잭션에 관여했다고 가정하고 V817LOC가 COMMITTED인 상태에서 그 node의 STATE가 PREPARED로 나타난다면 그 node에 대해서는 아래의 CASE 8-2를 참조하여 해결하도록 한다.
CASE 8-2: STATE field값이 PREPARED인 경우
STATE값이 PREPARED인 경우는 이 node(V817LOC)에서 변경된 data가 속한 block에 distributed lock이 걸린 상태이며, 이런 경우 변경된 data가 있는 block에 대한 모든 read/write가 ora-1591을 발생시키므로 trouble shooting에서 제일 중요한 부분이라 할 수 있다.
먼저 STEP 4와 STEP 5를 참조하여 관계된 모든 node들의 DBA_2PC_PENDING view를 조회하여 본다. 이때 다른 node(V817REM)의 DBA_2PC_PENDING에 정보가 없다면 V817REM이 commit point site이고 이미 data는 rollback되었음을 나타낸다.
이때는 V817LOC의 prepared 상태의 transaction을 다음과 rollback force 시켜준다.
즉, V817LOC에서,
SQL>rollback force '1.8.238';
만약 V817REM node에 해당 정보가 있고 상태가 COMMITTED라면 V817LOC도 다음과 같이 commit을 해 주어야 한다.
SQL>commit force '1.8.238';
이때 local_tran_id 뒤에 SCN을 지정할 수 있는데 이것은 관여된 node 중 제일 큰 SCN을 지정하도록 한다. 이 SCN 값은 DBA_2PC_PENDING의 COMMIT# field에서 값을 확인할 수 있으며 이렇게 하는 이유는 이후 분산 database중 한 database에서 incomplete recovery가 필요한 경우, 다른 database 들도 일관성을 맞추기 위해 incomplete recovery를 이용할 수 있게 하기 위한 것이다.
SQL>commit force '1.8.238', '194671'
CASE 8-3: STATE field값이 COLLECTING인 경우
STATE field가 collecting인 경우는 아직 distributed lock을 걸기 전단계에서 transaction이 비정상 종료됨을 나타내며, 이 단계에서는 distributed lock이 걸리기 전이어서 변경된 data는 이미 rollback된 상태이다. 이 경우는 DBA_2PC_PENDING에서 해당 entry를 지워 주면 된다.
CASE 8-4: STATE field값이 FORCED ROLLBACK/FORCED COMMIT 인 경우
이미 RECO나 db admin이 rollback force나 commit force 명령을 시도하여 STATE가 FORCED ROLLBACK이나 FORCED COMMIT으로 변경된 경우는 추가적으로 수행할 작업은 없으며, RECO가 자동으로 이 entry를 지워 줄 것이다. 그러나 RECO가 작업하기를 기다리지 않고 다음과 같이 직접 삭제할 수 있다.
어떠한 경우에 DBA_2PC_PENDING의 MIXED column이 YES가 되는지는 이미 STEP 7 에서 설명하였다. 이렇게 잘못된 조치에 의해 STEP 7에서 설명한 것과 같은 분산 데이타베이스간의 불일치가 발생한 경우는 간단한 operation을 통해 일치성을 맞추는 것은 불가능하다.
분산 트랜잭션의 consistency가 무엇보다 중요한 경우라면 관계된 node의 database를 모두 문제의 분산 트랜잭션이 수행되기 이전 상태로, incomplete recovery를 수행하거나 할 수 있다. 분산 데이타베이스간의 일관성을 위한 incomplete recovery에 대해서는 여기에서는 다루지 않는다. 한가지 언급한 말한 것은 앞에서 설명한 분산 트랜잭션의 commit시 이용하는 commit SCN을 관계된 모든 node들의 최대 SCN으로 이용하는 것이 바로 이러한 recovery를 위한 것이다.
이렇게 일부 database에서 SCN값이 이전 SCN에서 1씩 증가하는 것이 아니라 큰 값으로 건너뛰어 다른 database와 같은 SCN을 유지하게 함으로써, 이후에 incomplete recovery시에 관계된 node들이 서로 동일한 SCN으로 recovery를 수행하면, 모두 분산 트랜잭션 적용 이전이 되거나 혹은 모두 이후가 되어 일관성을 유지할 수 있도록 해준다.
MIXED가 YES인 상태에서, inconsistency를 받아들이고 DBA_2PC_PENDING view를 정리하려면 다음과 같이 수행한다.
예전에 우리 회사 대리가 밑에 개발자들 참고하라고 살짝 간단히 정리해 준건데.. 살짝 코멘트를 달아 보았다.
참고하면 괜찮을듯..
다 아시는 내용이지만 Procedure 사용에서 락 발생 다수 시 조치 사항입니다
1. TEMP 테이블 사용을 제한 - TEMP테이블의 경우 부하가 많은경우 락 발생이 급격하게 늘어나기 때문에 사용은 최소화 한다. -> 사실 그닥 좋은 방법이 아님은 누구나 다 알것이다. 다량의 DB작업을 하면서 임시로 쓰는것은 뭐 괜찮지만. 프로시져나 프로그램 내에서 Temp 테이블을 사용하는것은 결코 좋은 방법이 아니다.
2. 다이나믹 쿼리의 제한 - 차라리 조회 조건에서 select 을 전부 써버리는 것이 더 났습니다. -> 이 부분은 노코멘트.. 사실 뭐가 성능향상에 좋은지 잘 모르겠다. 실상 테이블명이나 필드명등의 객체명이 유동적일때 다이나믹 쿼리를 자주 사용하는데.. 걍 select를 다 해줘버려라.. 훔.... 말했던대로 노코멘트.
3. 함수를 사용할 경우 다수의 값을 받아오는 경우에는 함수를 사용하기보다도 JOIN사용이 더 낫습니다.
함수를 사용할 경우 2건 이상인 경우에는 1건 1건씩 리턴값을 주기 때문에 리턴값이 늘어날 경우 점점 더 느려지게 됩니다. -> 예를 들어서 사번을 가지고 이름을 가져오고 메일주소를 가져올때.. 보통 사번과 이름, 이메일 주소는 하나의 테이블에 있을것이다. 사원테이블 조인해서 이메일 주소와 이름을 가져오면 되는데. 함수를 사용하면 사원테이블에 접속해서 이름을 가져오고, 또 사원테이블에 접속해서 이메일 주소를 가져와야 한다. 두번 접속하게 된다. 당근.. 성능에는 치명적이다.
4. 잘 쓰지 않고 건수가 적더라도 테이블에는 반드시 인덱스를 잡는다. 실 예로 8건 밖에 안되는 코드성 테이블에 인덱스 잡는 것으로 전체 시스템 성능이 5-6배가 된 경우도 있습니다. -> 뭐 다들 알테니깐..
select * from all_tables - 모든 테이블 조회 select * from user_tables - 로그인된 user의 테이블 조회 (= select * from tab)
Column 정보 조회하기
select * from user_tab_columns
이거 찾아서 한참 헤맸다는.. 쿨럭... -_-;;
예를 들어서..
여러테이블에 특정 필드의 값을 수정해야 하는 프로시져를 작성한다면..
다음과 같이 작성할수 있지 않을까..
CREATE OR REPLACE Procedure sp_field_change ( R_TEXT OUT VARCHAR2 ,V_F_1 IN VARCHAR2 --변경대상 값 ,V_T_2 IN VARCHAR2 --변경할 값 ) IS V_SQL VARCHAR2(1000); T_RET VARCHAR2(5000);
begin
DECLARE CURSOR TAB_INFO IS SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE FROM USER_TAB_COLUMNS A, TAB B WHERE A.TABLE_NAME = B.TNAME AND B.TABTYPE = 'TABLE'; BEGIN
T_RET := '';
FOR TAB_INFO_VAL IN TAB_INFO LOOP IF TAB_INFO_VAL.DATA_TYPE = 'VARCHAR2' THEN -- 변경 대상 값과 데이터 타입이 같은것만 체크 V_SQL := 'UPDATE ' || TAB_INFO_VAL.TABLE_NAME || ' SET ' || TAB_INFO_VAL.COLUMN_NAME || ' = ''' || V_T_2 || ''''; V_SQL := V_SQL || ' WHERE ' || TAB_INFO_VAL.COLUMN_NAME || ' = ''' || V_F_1 || '''';
EXECUTE IMMEDIATE V_SQL; IF SQL%FOUND THEN T_RET := T_RET || '_' || V_SQL; END IF;
END IF; END LOOP; END; IF T_RET = '' THEN R_TEXT := V_F_1; ELSE R_TEXT := T_RET; COMMIT; END IF; EXCEPTION WHEN OTHERS THEN R_TEXT := 'ERROR => ' || SQLERRM; end sp_field_change;