DataBase] Oracle 테이블에 대한 정보 조회 쿼리

-- 테이블에 대한 컬럼명, 컬럼 타입, 컬럼 길이, 코멘트 보는 쿼리.
SELECT T.TABLE_NAME, T.COLUMN_NAME, T.DATA_TYPE, T.DATA_LENGTH, C.COMMENTS
FROM ALL_TAB_COLUMNS T JOIN ALL_COL_COMMENTS C
    ON T.TABLE_NAME = C.TABLE_NAME
        AND T.COLUMN_NAME = C.COLUMN_NAME
WHERE T.TABLE_NAME = 'TABLE_NAME'
;

DB2] DB2에서 특정 컬럼명을 가지고 있는 테이블 리스트 찾기.

IBM DB2 에서 특정 컬럼명을 가진 테이블 리스트를 구하는 방법.

1.
select * 
from syscat.columns 
where colname = '확인할컬럼명' -- 컬럼명은 대문자
;

2.
select * 
from syscat.columns 
where remarks like 'comment한 이름' -- 컬럼명은 대문자
;

1. 은 컬럼명을 알 때 사용 가능하고, 2. 는 테이블 생성시 remarks 에 코멘트를 달아 놓은 경우에 해당 작업으로 확인이 가능하다.

** 해당 스키마를 알면 해당 스키마에서만 찾고자 하는 컬럼명을 사용하는 테이블을 조회가 가능하다.

DB2] DB2 에서 Assignment of a NULL value to a NOT NULL column 의 오류 발생시

DB2 로 프로그램 개발시 ,
Assignment of a NULL value to a NOT NULL column 메시지와 함께
"TBSPACEID=XX, TABLEID=XX, COLNO=XX" 라는 오류 메시지를 받게 되는 경우 아래의 방법으로 확인가능.

1.
select * from syscat.tables 
where tableid = xx 
;

로 해당 테이블 명을 확인.

2.
select * 
from syscat.columns 
where tabname = '확인한 테이블명'
and colno = xx;

로 해당 컬럼을 확인하여 조치를 취할 수 있다. 

** 참조 사이트 : http://www.dbforums.com/db2/1658501-ql0407n-assignment-null-value-not-null-column-tbspaceid-8-tableid-2580-a.html

DB2] Alter table ... drop column

보통 DB에서 alter table  작업은 다음과 같이 한다.

alter table table_name 

drop column column 

;

위의 문장은 table_name 의 column 컬럼을 제거하겠다는 명령이다. 

하지만 db2에서 위 명령을 실행한 후, 

select * from table_name; 

명령을 실행하면 

SQL0668N Operation not allowed for reason code "7"

과 같은 오류를 발생시키면서 select 쿼리를 실행하지 못한다. 

이런 경우, 

reorg table table_name ;

을 실행하면 위의 오류가 해결 된다.

***
column 추가
==>
    alter table table_name
        add column column_name data_type ;


column 명 변경
==>
    alter table table_name
        alter column column_name_org
              set default 'modified_column_name' ;

pk 삭제
==>
    alter table table_name
        drop primary key

pk 추가
==>
    alter table table_name
        add primary key field_name

column 의 data type 변경
==>
   alter table table_name
         alter column column_name
             set data type 변경할 데이터 타입.


=========================================

pk 변경하기 위한 작업절차


-- pk 에 추가할 컬럼 생성. (pk 이기 때문에 not null 로 생성해야 됨)
alter table <테이블명>
    add column <컬럼명> <데이터타입> not null default 1
;

-- 테이블 데이터 확인.
select *
from <테이블명>
;

commit;

-- reorg 작업.
reorg table <테이블명> ;

-- pk 제거
ALTER TABLE <테이블명>
  drop PRIMARY KEY
;

-- pk 재생성
ALTER TABLE <테이블명>
  ADD PRIMARY KEY
    (pk 컬럼1, pk 컬럼2, ... , pk 컬럼N  )
;



CREATE SEQUENCE <Sequence명>
  AS INTEGER
  START WITH 1
  CACHE 20
  ORDER
;

---- 

sequence 삭제

drop SEQUENCE <Sequence명>

DB2] 문자열을 인자로 받아 테이블 형태로 보여주는 user defined function

db2로 작업하다가 문자열을 parameter로 넣어 주면 table 형태의 데이터로 반환해 주는 사용자 정의 함수를 발견하게 되었다.

해당 소스는 아래 주소를 참조하여 상황에 맞게 수정하여 컴파일 후 사용하면 된다.

ELEMIDX :: 인자로 주어진 문자열 중 ',' 의 위치를 반환.
http://www.ibm.com/developerworks/data/library/techarticle/0303stolze/0303stolze1.html

ELEMENTS :: 인자로 주어진 문자열을 ELEMIDX() 를 사용하여 java의 split(',') 의 기능과 유사한 내용을 테이블 형태로 반환하는 함수.
http://www.ibm.com/developerworks/data/library/techarticle/0303stolze/0303stolze1.html


위 내용의 함수는 join을 이용하여 sql문을 작성할 경우 유용하게 사용할 수 있을 듯 하다.

Select 에서 여러 행을 하나의 행으로 합쳐서 뽑고자 하는 경우

Select 쿼리를 사용하여 나온 결과가 여러행인 경우 키값이 같은 경우 하나의 행으로 보여주고자 하는 경우, 아래의 쿼리를 사용하여 간단하게 원하는 결과값을 얻을 수가 있다.

아래 쿼리는 DB2에서 재현을 해본 결과 해당 쿼리가 무난하게 돌아가는것을 확인하였다.


실행 쿼리 :
=================================
with tb(docno, seqno, prot, empno) as
(
select '1111' docno, 1 seqno, 1 prot, 'AAAA' empno from dual union all
select '1111' , 1 , 2 , 'BBBB' from dual union all
select '5555' , 3 , 1 , 'KKKK' from dual union all
select '5555' , 3 , 2 , 'MMMM' from dual union all
select '5555' , 3 , 3 , 'PPPP' from dual
)
select docno
, seqno
, listagg(empno,' ') WITHIN GROUP (ORDER BY seqNO, prot) as empno
from tb
group by docno
, seqno
;


실행 결과 :
=================================
docno seqno   empno
1111 1     AAAA BBBB
5555 3     KKKK MMMM PPPP


내용 출처 : http://www.oracleclub.com/article/51669

아래의 링크도 참조하기 바랍니다.
 http://blog.naver.com/PostView.nhn?blogId=jauroy&logNo=50044746370

위에 사용된 listagg() 란 함수에 대해 찾아보다가 발견한 아래의 페이지도 참고 바랍니다.
http://blog.naver.com/PostView.nhn?blogId=joonaha&logNo=70108717698

SQL] DB2 date 를 milliseconds 로 변환하기.

DB2
=======
select to_number(sysdate - to_date('1970-01-01','YYYY-MM-DD')) * (24 * 60 * 60 * 1000)
FROM sysibm.sysdummy1 ;


ORACLE
========

select to_number(sysdate - to_date('1970-01-01','YYYY-MM-DD')) * (24 * 60 * 60 * 1000)
FROM dual;

DB2] locking 정보 확인

-- db2 locking 정보 확인.  

SELECT lock_name,
  hld_member,
  lock_status,
  hld_application_handle
FROM  TABLE (MON_GET_APPL_LOCKWAIT(NULL, -2))

DB2] 프로시저 script 확인하는 쿼리

-- procedure명으로 조회.
select * from SYSIBM.SYSPROCEDURES where PROCNAME like '%프로시저 명%';

-- oracle 의 dual 에 해당하는 테이블 :: sysibm.sysdummy1
select current_timestamp from sysibm.sysdummy1;

오라클] 프로시저 내용 확인하는 sql

select * from all_source where name = upper('프로시저명');

select * from user_source where name = upper('프로시저명');

DB/MSSQL] convert/ cast 함수

 mssql의 convert()/cast() 함수 

convert(), cast() 는 둘다 데이터를 표현할때 변환해서 보여주는 함수로 cast()는 ansi sql을 지원한다.

convert(datatype[length], expression, [style])

cast(expression as datatype)
   expression : 표현식, 테이블의 필드, 문자열 등이 올 수 있다.
   datatype : sql에서 지원하는 데이터타입을 말한다. int, char(), varchar() 등등...
   style : expression이 datetime 형식일 경우 주로 사용되는 것으로 각 나라의 표현 형식을 지정할 때 사용한다. 
한국의 경우 주로 121, 21이 사용되는 경우가 많다.


예제) titles 테이블에서 제목과 판매일자를 가지고 온다.

SELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))
FROM titles
WHERE type = ''trad_cook''

DB / MSSQL ] 인덱스를 이용한 조건검색

인덱스의 유무에 따라서 데이터를 찾는 성능의 차이가 천차만별이 될 수도 있으나 또한 너무 남용을 하게 되면 데이터의 크기보다 인덱스의 크기가 커지는 배보다 배꼽이 더 큰경우가 발생하는 경우도 있으니 유의해서 사용을 해야된다.

인덱스는 테이블에서 어느 하나의 필드, 혹은 필드들의 조합으로 생성을 할 수 있다.
이 생성된 인덱스를 사용하게 하는 것이 쿼리 작성자의 능력이다.

이제 조건 검색에서 주의할 사항을 알아보자.

1. 조건검색시 ''='' 은 모든 인덱스검색중 최우선 순위를 가진다.

2. 조건 검색시 좌변인덱스는 가공하지 말고, 우변을 가공해서 조건을 생성할 것.
    단, 의도적인 인덱스 사용을 막고자 하는 경우는 예외.

3. 조건 검색시 부정의 표현을 사용하지 말고 긍정표현 및 범위 조건을 사용할것.
     다시말해,  ''not'', ''<>'', ''or'' 이런 조건들은 될 수 있으면 긍정적인 표현으로 바꾸어서 사용할 것.
     ''<>'' => exists로 대체,  ''not'' A => not (A) 대체 
4. 인덱스가 있는 곳과 없는곳의 테이블을 조인하여 결과 검색시, 인덱스가 없는곳의 데이터가 먼저 읽혀지고 다음에 인덱스가 있는 테이블의 데이터가 읽혀진다.

5. 가급적 조건에 ''Null'', ''Not Null'' 을 없애기 위해 테이블 설계시, 결합인덱스의 구성컬럼이 된다면 Not Null로, 또한 입력조건값으로 자주 사용되는 것이면 Not Null로 설계를 하여서 조건검색시 Not Null, Null이란 조건을 가급적 없앤다.


위의 사항만을 유념해서 검색을 하더라도 select 쿼리 수행 시간을 줄일 여지가 많이 있다.

===========================================================================

쿼리문의 속도 향상 방법.

부분범위처리를 사용한다.

부분범위처리란

 - 조건을 만족하는 전체집합이 아닌 일부분만을 Access 하게 하는것
 - Data량이 많아도 퍼포먼스에는 지장이 없고, 오히려 향상을 가져올 수도 있다.
 - 인덱스나 클러스트를 적절히 활용하여 sort를 대체가능하다.
 - table은 액세스하지 않고 index만 사용하도록 유도
 - exists를 활용한다.
 - query를 이원화 하여 일부분씩 scan하도록 유도

위의 것들을 조합하여 활용하면 쿼리문을 작성하여 대량의 데이터를 처리할 경우에도 무리없이 원하는 속도를 얻을 수 있을 것이다.

DB] DB2에서 merge into 사용

merge 문장의 문법 :

merge into tableA  a
using (
select * from tableB
) b
  on (   a.aaa = b.bbb  --// 조회 조건.
     )
 when matched then
                      update 문장. <== tableA에 업데이트할 항목 나열
when not matched then
                      insert 문장.   <== tableA에 insert 할 항목 나열.
else ignore;


설명 :
 merge into 문장은 insert / update 작업을 한번에 할 수 있는 장점이 있는 sql 구문이다.
그러나, 사용상 주의점은 b 로 묶인 select 문장의 조회 결과가 없는 경우, when 조건절에 걸리지 않는 상황이 발생하는걸 경험했다.


현재까지의 결론.
 1. merge into 는 tableA 에 대해 insert / update 작업을 한번에 수행한다. 
    단, a.aaa = b.bbb 가 일치하는 항목에 대해.
 2. merge into 는 "select * from tableB" 에서 조회 결과가 없으면 when ... 을 무시한다. 
    결과적으로 else ignore 가 실행되는 듯 하다. 

DBMS/오라클] 스키마 정보 확인

 
  select * --column_name, data_type, data_length, data_precision, nullable, default_length, data_default
  from user_tab_columns@db_name
  where table_name='NPORDERMST'
  order by column_id
  ;
 
  /
 
  select table_name,column_name from user_tab_columns
  where table_name='EMP';  

DBMS/오라클] job 수행 시간 및 간격, 실행 건수 확인

-- sys user에서 작업
-- job 수행 시간 및 간격, 실행 건수 확인
--
select schema_user db_user
    , what as job_proc
    , last_date
    , last_sec
    , next_date
    , next_sec
    , total_time pros_time
    , broken
    , interval
    , failures
from dba_jobs
order by schema_user, what
;

DBMS/오라클] copy_t 테이블 및 데이터 생성

--// 오라클에서 copy_t 테이블 및 데이터 입력 쿼리문
create table copy_t(no number, no2 varchar2(2)) nologging;

insert into copy_t values (1 ,'01');
insert into copy_t values (2 ,'02');
insert into copy_t values (3 ,'03');
insert into copy_t values (4 ,'04');
insert into copy_t values (5 ,'05');
insert into copy_t values (6 ,'06');
insert into copy_t values (7 ,'07');
insert into copy_t values (8 ,'08');
insert into copy_t values (9 ,'09');
insert into copy_t values (10 ,'10');
insert into copy_t values (11 ,'11');
insert into copy_t values (12 ,'12');
insert into copy_t values (13 ,'13');
insert into copy_t values (14 ,'14');
insert into copy_t values (15 ,'15');
insert into copy_t values (16 ,'16');
insert into copy_t values (17 ,'17');
insert into copy_t values (18 ,'18');
insert into copy_t values (19 ,'19');
insert into copy_t values (20 ,'20');
insert into copy_t values (21 ,'21');
insert into copy_t values (22 ,'22');
insert into copy_t values (23 ,'23');
insert into copy_t values (24 ,'24');
insert into copy_t values (25 ,'25');
insert into copy_t values (26 ,'26');
insert into copy_t values (27 ,'27');
insert into copy_t values (28 ,'28');
insert into copy_t values (29 ,'29');
insert into copy_t values (30 ,'30');
insert into copy_t values (31 ,'31');
insert into copy_t values (32 ,'32');
insert into copy_t values (33 ,'33');
insert into copy_t values (34 ,'34');
insert into copy_t values (35 ,'35');
insert into copy_t values (36 ,'36');
insert into copy_t values (37 ,'37');
insert into copy_t values (38 ,'38');
insert into copy_t values (39 ,'39');
insert into copy_t values (40 ,'40');
insert into copy_t values (41 ,'41');
insert into copy_t values (42 ,'42');
insert into copy_t values (43 ,'43');
insert into copy_t values (44 ,'44');
insert into copy_t values (45 ,'45');
insert into copy_t values (46 ,'46');
insert into copy_t values (47 ,'47');
insert into copy_t values (48 ,'48');
insert into copy_t values (49 ,'49');
insert into copy_t values (50 ,'50');
insert into copy_t values (51 ,'51');
insert into copy_t values (52 ,'52');
insert into copy_t values (53 ,'53');
insert into copy_t values (54 ,'54');
insert into copy_t values (55 ,'55');
insert into copy_t values (56 ,'56');
insert into copy_t values (57 ,'57');
insert into copy_t values (58 ,'58');
insert into copy_t values (59 ,'59');
insert into copy_t values (60 ,'60');
insert into copy_t values (61 ,'61');
insert into copy_t values (62 ,'62');
insert into copy_t values (63 ,'63');
insert into copy_t values (64 ,'64');
insert into copy_t values (65 ,'65');
insert into copy_t values (66 ,'66');
insert into copy_t values (67 ,'67');
insert into copy_t values (68 ,'68');
insert into copy_t values (69 ,'69');
insert into copy_t values (70 ,'70');
insert into copy_t values (71 ,'71');
insert into copy_t values (72 ,'72');
insert into copy_t values (73 ,'73');
insert into copy_t values (74 ,'74');
insert into copy_t values (75 ,'75');
insert into copy_t values (76 ,'76');
insert into copy_t values (77 ,'77');
insert into copy_t values (78 ,'78');
insert into copy_t values (79 ,'79');
insert into copy_t values (80 ,'80');
insert into copy_t values (81 ,'81');
insert into copy_t values (82 ,'82');
insert into copy_t values (83 ,'83');
insert into copy_t values (84 ,'84');
insert into copy_t values (85 ,'85');
insert into copy_t values (86 ,'86');
insert into copy_t values (87 ,'87');
insert into copy_t values (88 ,'88');
insert into copy_t values (89 ,'89');
insert into copy_t values (90 ,'90');
insert into copy_t values (91 ,'91');
insert into copy_t values (92 ,'92');
insert into copy_t values (93 ,'93');
insert into copy_t values (94 ,'94');
insert into copy_t values (95 ,'95');
insert into copy_t values (96 ,'96');
insert into copy_t values (97 ,'97');
insert into copy_t values (98 ,'98');
insert into copy_t values (99 ,'99');

commit;


=================================================================
copy_ymd 테이블 생성 및 데이터 입력 스크립트 ( 오라클용)

create table copy_ymd nologging
as
select to_char(sysdate+rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate+rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from tab a, tab b, tab c
where rownum < 100000
union all
select to_char(sysdate+rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate+rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from dual
union all
select to_char(sysdate-rownum, 'YYYYMMDD') ymd,
to_date(to_char(sysdate-rownum, 'YYYYMMDD'), 'YYYY-MM-DD') ymd_date
from tab a, tab b, tab c
where rownum < 100000
union all
select '00010101' ymd, to_date('00010101') ymd_date from dual
union all
select '99991231' ymd, to_date('99991231') ymd_date from dual;

COMMIT;
=================================================================
copy_yy 테이블 생성 및 데이터 입력 스크립트 ( 오라클용)

create table copy_yy nologging
as
select to_char(1900+rownum) yy from all_objects where rownum < 200


commit;



=================================================================
잘못된 날짜 데이터 찾기

SELECT A.EMPNO, A.ENAME, A.HIREDATE, B.YMD -- TO_DATE(HIREDATE, 'YYYY-MM-DD')
FROM TEST20 A, COPY_YMD B
WHERE A.HIREDATE = B.YMD(+)
AND B.YMD IS NULL;

혹은

SELECT * FROM (
SELECT A.EMPNO, A.ENAME, A.HIREDATE,
(SELECT B.YMD FROM COPY_YMD B WHERE A.HIREDATE = B.YMD ) YMD
FROM TEST20 A
)
WHERE YMD IS NULL;

( TEST20 테이블의 HIREDATE는 VARCHAR2(8) 로 구성되어 있으며, 중간중간 잘못된 데이터가 입력되어 있음)

DBMS/오라클] 오라클 트리거 작성 예

CREATE SEQUENCE 이름
[ INCREMENT BY n ]
[ START WITH n ]
[ {MAXVALUE n | NOMAXVALUE} ]
[ {MINVALUE n | NOMINVALUE} ]
[ {CYCLE | NOCYCLE} ]
[ {CACHE n | NOCACHE} ]
-----------------------------------------------------


SQL> CREATE [ PUBLIC ] SYNONYM 시노님 명
FOR 오브젝트 명 ;
-----------------------------------------------------


CREATE [OR REPLACE] TRIGGER trigger명
timing event1 [OR event2 ...] OF 칼럼명 ON 테이블명
[FOR EACH ROW]
PL/SQL 블록
-----------------------------------------------------

실예:]

CREATE OR REPLACE TRIGGER time_emp
BEFORE insert OR update OR delete ON s_emp
BEGIN
IF to_char(sysdate,'hh24') NOT BETWEEN '09' AND '12' THEN
raise_application_error(-20201,'시간이 끝났음');
END IF;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER up_emptemp
AFTER UPDATE OF SALARY ON s_emp
FOR EACH ROW
BEGIN
UPDATE EMPTEMP
SET salary =:NEW.salary
WHERE id = :OLD.id ;
END;
/
==============================================================
CREATE OR REPLACE TRIGGER ord_total
AFTER INSERT OR DELETE OR
UPDATE OF price, quantity ON s_item
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE s_ord
SET total = NVL(total,0)+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
ELSIF DELETING THEN
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
WHERE id = :OLD.ord_id;
ELSE
UPDATE s_ord
SET total = NVL(total,0)-NVL(:OLD.price * :OLD.quantity,0)
+NVL(:NEW.price * :NEW.quantity,0)
WHERE id = :NEW.ord_id;
END IF;
END;
/

DBMS/오라클] 테이블 리스트 보기

select *
from dictionary



select *
from all_all_tables

select *
from user_tables;

--//현재 사용자 정보
select *
from user_users


--// 사용자 테이블의 이름과 설명.
select *
from user_tab_comments


select *
from user_tab_privs

--// 사용자테이블리스트와 해당 테이블의 컬럼명 및 속성.
select *
from user_tab_cols

select *
from user_tab_columns

select *
from user_tab_col_statics




--// 오라클에서 데이터딕셔너리에서 사용자 테이블의 이름 가져오는 쿼리
select rownum, aat.*
from all_all_tables aat
where aat.owner ='LASADM'  


select table_name, column_name, data_type, data_length, nullable,  data_default
from cols  --// user_tab_columns  ==> cols  와 동일
where table_name = 'BL_TB_LAW_CONTRACT_MF'


--// 사용자 테이블의 이름과 설명.
select *
from user_tab_comments


--// 오라클에서 데이터딕셔너리에서 사용자 테이블의 정보 가져오는 쿼리

select rownum, utc.table_name, utc.column_name, utc.data_type, utc.data_length, utc.nullable,  utc.data_default, acc.comments
from cols utc --// user_tab_columns utc ==> cols utc 와 동일
join all_col_comments acc
on utc.table_name = acc.table_name
and utc.column_name = acc.column_name
--where utc.table_name = 'BL_TB_LAW_CONTRACT_MF'

DBMS/오라클] 주차 구하기

--// 오라클 주차 구하는 쿼리
SELECT TO_CHAR(SYSDATE,'WW') AS WEEK FROM DUAL;

DBMS/오라클] 오라클_테이블및_오브젝트_생성일자확인쿼리

DBA 계정으로 접속
다음의 명령을 실행 하면 생성한 오브젝트(테이블, 프로시저, 트리거 등) 의 
생성일자(CREATED), 최종 수정일자(LAST_DDL_TIME)를 확인 가능합니다. 

SELECT * 
FROM SYS.DBA_OBJECTS
WHERE OWNER LIKE DBUSER_NAME