따봉 구걸남's 블로그

- 기능 설명 

 varchar 의 길이 제한은 4000 byte 이나 이것 이상 저장하려고 하면 long 이나 lob 타입으로 변경해야함.
 그러나 long 타입은 테이블 당 1개 밖에 쓸수 없고, SUBSTR 함수 같은 것의 사용이 제약됨. 
 내부적으로는 기능개선을 중단했고, clob 사용을 권장함.
 
- 사용방법

conn / as sysdba
PURGE DBA_RECYCLEBIN

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

ALTER SYSTEM SET max_string_size=extended;

@?/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;

 
-- CHECK 
show parameter max_string

alter table TEST modify varchar2(5000);

1. 트리거란 

트리거는 특정이벤트가 발생시, 그것을 기점으로 동작하는 object 이다.  

2. 트리거의 단점 

 - 업무 누락 가능성 : 트랜잭션의 흐름에 대해서는 대부분 인지하고 있지만,

 트리거에 대해서는 따로 신경쓰지 않으면 인지하기 어려워 누락되기 쉽고 장애 또는 로직의 누수로 이어질수 있다. 

 - 관리 포인트의 부담 : DBA 입장에서는 관리포인트가 증가하는 것도 달갑지 않다. 

비슷한 이유로 (+보안적 이유) 디비링크도 사용을 제한 할 것을 권고해왔는데, 요즘은 서비스에서는 거의 사용 안하는 편이다.

대신, EAI 등을 통해서 조회를 하라고 권고한다. 

3. 트렌드

요즘은 프로시저나 함수도 다 걷어내고 AP에서 부하를 많이 가져가는 추세인데,  

덕분에 DB 의 사양을 낮출수 있어 클라우드 환경이라면 비용의 감소로 이어질수 있다. 

결국 DB 는 트랜잭션을 보장하고, 단순하게 데이터를 담는 테이블, 조회를 도와주는 인덱스, 이 두가지만 사용하는 추세이다.  

오라클에서는 DBMS_RANDOM 패키지를 통해서 랜덤한 숫자를 추출하는 기능을 제공해주고 있다.

https://docs.oracle.com/database/121/ARPLS/d_random.htm#ARPLS040

해당패키지의 RANDOM 함수는 11G R1 부터 deprecated 되었다.
대신 VALUE 함수를 가공해서 원하는 결과가 나오게 수정하면된다.


Q) 총 100 건 데이터 중, 랜덤하게 10건 가져오기

랜덤숫자를 뽑고 그것으로 정렬한 10건을 가져오는 것으로 sql을 작성한다면..
중복 가능성이 있다.

SELECT *
FROM ( SELECT A, B, DBMS_RANDOM.VALUE(1,100) RN FROM EMP ORDER BY RN )
WHERE ROWNUM <= 10

레코드들 간 중복없는 랜덤정렬은
아래처럼 해야 한다

SELECT *
FROM ( SELECT A, B FROM EMP ORDER BY DBMS_RANDOM.VALUE )
WHERE ROWNUM <= 10

개발자로 부터 db 가 접속이 안되니 확인요청이 들어오는 일이 종종있다. 

개발자들은 보통 sqldeveloper 나 기타 무료 툴들을 쓰는데, 클라이언트에서 서버로 접속하는 환경은

일시적인 통신환경의 이상이나 작업등으로 불안정할수 있다. (실제로 서버에 문제가 생기지 않는한)

 이때는 클라이언트 프로그램을 재기동하거나 재접속하게 되면 대부분 정상적으로 접속이 되기도 한다. 

그래도 접속이 안된다고 하면, 아래의 흐름을 따라서 어느 구간이 문제인지 확인을 해보자. 

 client 의pc (개발자) 에서 cmd 터미널을 열어 tnsping "tnsnames.ora의 name" 을 쳐본다.

수초 내로 ok 가 떨어지면 통신환경에는 이상이 없는 것이다. 그렇지 않고 응답없는 상태가 된다면 방화벽을 다시 확인해본다. 

tnsping 도 되고, sqlplus 로도 접속이 된다면 사용중인 client tool 의 문제일 가능성이 크다. 

tool 에서 실제로 접속하는 경로가 맞는지 확인해본다. 

오라클DB 아카이브 모드 변경에 대해서 알아보자

1. no arch => arch

SQL>archive log list 
SQL>shutdown immediate
SQL>startup mount;

SQL>alter database archivelog;
SQL>alter database open;
SQL>show parameter log_archive_dest_1;

SQL>alter system switch logfile;
SQL>alter system switch logfile;
SQL>alter system switch logfile;

2. arch => no arch

SQL>archive log list 
SQL>shutdown immediate
SQL>startup mount;

SQL>alter database noarchivelog;
SQL>alter database open;

재기동이 필요하다. 

오라클은 DBMS_STATS 패키지로 통계정보 관리를 지원한다. 
하위에 여러 프로시져가 있는데 자주 쓰는 프로시져 몇 가지에 대해서 정리해보자. 
 
- DBMS_STATS.CREATE_STAT_TABLE : 통계정보를 담을 테이블 생성  
 사용법 : begin dbms_stats.create_stat_table('SCOTT','STAT_TABLE','USERS'); end;
 scott 소유의 테이블 stat_table 을 users 테이블스페이스에 생성. 
 이 테이블은 아래쪽의 통계정보 export 할때 사용될수 있다. 

- DBMS_STATS.DROP_STAT_DROP : 통계정보 테이블 삭제 
 사용법 : begin dbms_stats.drop_stat_table('SCOTT','STAT_TABLE'); end;

- DBMS_STATS.GATHER_TABLE_STAT : 통계정보 수집
 사용법 : begin dbms_stats.gather_table_stats(OWNNAME=>'SCOTT', TABNAME=>'TEST_TAB', partname=>'PT_2000', estimate_percent=>5, degree=>1, granularity=>'PARTITION', cascade=>TRUE, no_invalidate=>FALSE ); end;  

- DBMS_STATS.EXPORT_TABLE_STATS : 통계정보 백업 (export) **
사용법 : begin dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'TEST_TAB', stattab=>'STAT_TAB' 

- DBMS_STATS.IMPORT_ TABLE_STATS : 통계정보 복구 (import) **
사용법 : begin dbms_stats.import_table_stats(ownname=>'SCOTT', tabname=>'TEST_TAB', stattab=>'STAT_TAB' 

- DBMS_STATS.DELETE_TABLE_STATS  : 통계정보 삭제
사용법 : (일반테이블) begin dbms_stats.delete_table_stats(ownname=>'SCOTT', tabname=>'TEST_TAB'); end;
 (파티션) begin dbms_stats. delete_table_stats(ownname=>'SCOTT', tabname=>'TEST_TAB', partname=>'PT_2000'); end;
select owner, table_name, partition_name, 
       num_rows, last_analyzed, global_stats, stattype_locked
  from dba_tab_statistics
 where owner='SCOTT'; 

- DBMS_STATS.COPY_STAT_TABLE : 통계정보 복사
사용법 : begin dbms_stats.copy_stat_table(ownname=>'SCOTT', tabname=>'TEST_TAB', srcpartname=>'PT_2000', dstpartname=>'PT_2001'); end;

- DBMS_STATS.LOCK_STAT_TABLE : 통계정보 잠금
사용법 : begin dbms_stats.lock_stat_table(ownname=>'SCOTT', tabname=>'TEST_TAB'); end;

- DBMS_STATS.UNLOCK_STAT_TABLE : 통계정보 잠금해제 
사용법 : begin dbms_stats.unlock_stat_table(ownname=>'SCOTT', tabname=>'TEST_TAB'); end;
확인 : DBA_TAB_STATISTICS => stattype_locked 

** 딕셔너리 통계정보 
DBMS_STAT.GATHER_DICTIONARY_STATS
DBMS_STAT.GATHER_FIXED_OBJECTS_STATS

** 자동 통계정보 수집 
오라클을 설치하면 디폴트로 자동 통계수집 되는 것이 있다. 

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
 client_name => 'auto optimizer stat collection'
,operation => null
,window_name => null
);
 

DBMS_SCHEDULER 를 활용하면 서버에서 cron job 을 쓰지 않고, 정기적인 작업을 실행할수 있다. 

이것의 장점은 .. 

  - 굳이 서버에 접속하지 않고도 오렌지 같은 툴로 DB에만 접속해서 결과 확인이 가능. 

  - Active-stand by 구성된 경우 failover 되어 master 가 변경이 되는것에 신경 쓸 필요가 없다.

관리의 포인트가 DB 로 넘어온다는 것이 부담스럽긴 하지만 잘 애플리케이션 측면에서 활용도가 높으니 

알아두는 것이 좋다. 

1. 스케쥴 등록

BEGIN DBMS_SCHEDULER.CREATE_JOB(
  JOB_NAME => ＇TEST.TEST_JOB＇,  --잡이름
  JOB_TYPE => ＇PLSQL_BLOCK＇,   --PL/SQL로 만들어진 오브젝트 실행시 PLSQL_BLOCK를 지정
  JOB_ACTION => ＇BEGIN TEST.PC_JOBTEST; END;＇,  --실행 할 오브젝트명 등록
  START_DATE => TO_DATE(＇2022/03/03 10:10:00＇,＇YYYY/MM/DD HH24:MI:SS＇),  --실행을 시작 할 일시 지정
  REPEAT_INTERVAL => ＇FREQ=MINUTELY; INTERVAL=10＇, -- 반복 실행 주기 설정
  COMMENTS => ＇여기에 주석 남기기!!＇

);   

  DBMS_SCHEDULER.SET_ATTRIBUTE('TEST.JB_TEST_JOB','INSTANCE_ID',2);  --수행할 노드지정(RAC환경에서) 

** 인터벌 example

REPEAT_INTERVAL => 'FREQ=HOURLY; interval=1'          <- 1시간 간격 수행 
REPEAT_INTERVAL => 'FREQ=MINUTELY; interval=30'       <- 30분 간격 수행 
REPEAT_INTERVAL => 'FREQ=SECONDLY; interval=5'        <- 5초 간격 수행    
REPEAT_INTERVAL => 'FREQ=WEEKLY; interval=2'          <- 2주 간격 수행

REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=1; BYHOUR=09;BYMINUTE=0;' <- 매일 09시 
REPEAT_INTERVAL => 'FREQ=DAILY; INTERVAL=1; BYHOUR=09,12,15,18;' <- 매일 특정시간 
REPEAT_INTERVAL => 'FREQ=WEEKLY; INTERVAL=2; BYDAY= MON;' <- 격주차 월요일 
REPEAT_INTERVAL => 'FREQ=MONTHLY; BYMONTHDAY=1  INTERVAL=1'   <- 매달 1일 

 

2. 스케쥴

EXEC DBMS_SCHEDULER.DISABLE ('스케줄명') ;   -- 비활성화
EXEC DBMS_SCHEDULER.ENABLE ('스케줄명') ;    -- 활성화
EXEC DBMS_SCHEDULER.RUN_JOB('스케줄명') ;   -- 실행
EXEC DBMS_SCHEDULER.DROP_JOB('스케줄명') ;   -- 삭제

JOB 을 등록했으면 ENABLE 까지 해주어야 JOB 이 스케쥴링대로 돌아간다.

3. 등록현황 조회

SELECT P.OWNER,
       P.JOB_NAME,
       P.STATE,
       P.ENABLED,
       P.COMMENTS,
       CAST(P.NEXT_RUN_DATE AS DATE) NEXT_RUN_DATE,
       P.SCHEDULE_TYPE,
       P.REPEAT_INTERVAL,
       P.JOB_ACTION
FROM   DBA_SCHEDULER_JOBS P
WHERE  P.OWNER = 'AAA'

4. 실행로그 확인

SELECT LOG_ID,LOG_DATE,OWNER,JOB_NAME,JOB_SUBNAME,JOB_CLASS,OPERATION,STATUS
  FROM DBA_SCHEDULER_JOB_LOG WHERE JOB_NAME = '스케줄명 '
 ORDER BY LOG_DATE DESC;
 
5. 실행 상세로그 확인

SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = '스케줄명'
ORDER BY LOG_DATE DESC;

ASM 환경에서 테이블스페이스를 관리하는 것은 파일시스템에서 하는것과 조금은 다르다.
컨셉은 사용자(DBA) 가 데이터파일에 대한 관리(데이터파일명, 디스크 IO 등) 에 대해서 신경쓰지 말고 ASM 에게 위임을 하라는 것이다.
1. 테이블스페이스 생성
# F/S
CREATE TABLESPACE TS_TEST_MN01
DATAFILE '/oradata/ORCL/ts_test_mn01_001.dbf' SIZE 30G AUTOEXTEND OFF;
,'/oradata/ORCL/ts_test_mn01_002.dbf' SIZE 30G AUTOEXTEND OFF;

#ASM
CREATE TABLESPACE TS_TEST_MN01
DATAFILE '+DATA1' SIZE 30G AUTOEXTEND OFF;

2. 데이터파일 추가
* UNDO
ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DATA1' SIZE 20G AUTOEXTEND OFF;
* TEMP
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA1' SIZE 20G AUTOEXTEND OFF;
* DATA
ALTER TABLESPACE TS_TEST_MN01 ADD DATAFILE '+DATA1' SIZE 20G AUTOEXTEND OFF;

3. RESIZE

ALTER DATABASE DATAFILE '+DATA/PIMG/DATAFILE/ts_xvarm_mn01.268.1093358285' RESIZE 10G;

4. 삭제 (F/S 과 동일)

DROP TABLESPACE TS_TEST INCLUDING CONTENTS AND DATAFILES;

5. 온라인무브

ALTER DATABASE MOVE DATAFILE '###' TO '***';

sql loader 로 작업시 실제 데이터 길이보다 컬럼의 길이가 작아서 안들어가는 경우

alter 구문으로 길이를 늘려주고 다시 loader 작업을 해주면 된다.

로그 파일에 필요한 정보(테이블명, 컬럼명, 원본길이) 가 다 있으니 로그를 읽어서

자동으로 alter 스크립트를 생성할수 있다.  

SQL LOADER 테스트
create user test identified by test;
alter user quota unlimite on users;
grant resource, connect to test;

create table test(a char(3), b varchar2(3),c char(2));
insert into test values('aaa','bbb','ccc');
commit;


-- test.dat ---
fff,100000000000000,Y
ggg,100,N
iss,chchchchcc,N
aaaaaa,ch,C
aaaaaaaa,bbbbbbbbbbbb,ccccccccc
aa,bb,dd
-- test.dat ---
-- test.ctl ---
LOAD DATA
INFILE 'test.dat'
BADFILE 'test.bad'
APPEND
INTO TABLE test
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
a
,b
,c
)
-- test.ctl ---

-- ldr.sh --
sqlldr userid=test/test control=test.ctl
sqlldr userid=test/test control=tesa.ctl
...
-- ldr.sh --

sh ./ldr.sh > ldr.log

1. loader 결과에서 ora에러 추출
: ORA-12899: value too large for column "TEST"."TEST"."B" (actual: 15, maximum: 3)
=> cat ldr.log | grep ORA > ldr_ext.log
2. 에러 메세지에서 sql 에 쓸부분 (오너,테이블/컬럼명, 길이) 만 추출
: "TEST"."TEST"."B" 3)
=> awk '{print $7, $11}' ldr_ext.log > ldr_ext.v2.log
3. 불필요 문자 제거 ( ".", ")" )
: "TEST" "TEST" "B" 3
=> awk -F '.' '{ print $1, $2, $3 }' ldr_ext.v2.log | awk -F ')' '{ print $1 }' > ldr_ext.v3.log
4. alter 구문 생성 (원본 길이 1.5배 & 반올림 )
: alter table "TEST" ."TEST" modify "B" varchar2(5);
=> awk '{ print "alter table " $1,"." $2, "modify " $3, "varchar2(" int($4*1.5 +0.5) ");" }' ldr_ext.v3.log
## 실패한 sqlldr 다시 실행
5. bad 파일에서 리스트 추출
=> ls -l *.bad | awk '{print $9}' | awk -F '.bad' '{print $1}' > rerun.log

6. 추출 결과로 스크립트 생성
: sqlldr userid=test/test control=tesa.ctl
=> awk '{ print "sqlldr userid=test/test control=" $1 ".ctl" }' rerun.log > ldr.v2.sh

DBMS_XMLGEN.GETXMLTYPE 는 동적쿼리 처럼 쓸수 있어서 활용도가 높다.
lob 타입의 경우 가공이 불가한 제약이 있는데 이 함수를 써서 타입을 변경 할 수 있다.

-- 테이블 정의서 gen
SELECT rownum as "순번"
, A.COMMENTS AS "항목명"
, A.COLUMN_NAME AS "항목ID"
, ( SELECT 'PK'
FROM DBA_IND_COLUMNS IC
WHERE IC.TABLE_OWNER = :OWNER
AND IC.TABLE_NAME = A.TABLE_NAME
AND IC.COLUMN_NAME = A.COLUMN_NAME
AND IC.INDEX_NAME LIKE 'PK#_%' ESCAPE '#'
) AS "KEY"
, A.DATA_TYPE AS "TYPE"
, A.DATA_LENGTH AS "길이"
, ' ' AS "항목설명"
,(SELECT '시퀀스 '||'SQ_'||Q.TAB_NM||'_01'
FROM SEQ_INFO Q
WHERE Q.DBNAME = A.OWNER
AND Q.TAB_NM = A.TABLE_NAME
AND Q.COL_NM = A.COLUMN_NAME
) AS "COLUMN RULE"
, ' ' AS "개인신용정보구분"
, DECODE(A.NULLABLE,'N', 'NOT NULL','')||' '||DATA_DEFAULT AS "NULL (DEFAULT)"
FROM (
SELECT T.OWNER,
T.TABLE_NAME,
M.COMMENTS,
C.COLUMN_NAME,
C.DATA_TYPE,
C.DATA_LENGTH,
C.NULLABLE,
CASE WHEN DATA_DEFAULT IS NOT NULL THEN
'('|| REPLACE(DBMS_XMLGEN.GETXMLTYPE('SELECT DATA_DEFAULT FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='
||''''||C.TABLE_NAME||''''||' AND COLUMN_NAME='||''''||C.COLUMN_NAME||'''').EXTRACT('//text()')
,'&'||'apos;','''') || ')' END DATA_DEFAULT,
C.COLUMN_ID
FROM DBA_TABLES T
,DBA_TAB_COLUMNS C
,DBA_COL_COMMENTS M
WHERE 1=1
AND T.TABLE_NAME = :TAB_NM
AND T.TABLE_NAME = C.TABLE_NAME
AND C.TABLE_NAME = M.TABLE_NAME(+)
AND C.COLUMN_NAME = M.COLUMN_NAME(+)
ORDER BY COLUMN_ID
) A



-- 전체 테이블 rows 조회
SELECT OWNER,
TABLE_NAME,
DBMS_XMLGEN.GETXMLTYPE('SELECT COUNT(*) CNT FROM '
||T.OWNER||'.'||TABLE_NAME ).EXTRACT('//text()') CNT
FROM DBA_TABLES T
WHERE TABLE_NAME LIKE 'T%'
AND OWNER ='AA'