적고 쓰고 끄적이며 남기자

SELECT TO_CHAR(TO_DATE('20200107','YYYYMMDD'), 'D')
FROM DUAL

요일 1(일)~7(토)이 나오는 이걸 이용하면 

아래와 같이 휴일정보만 직접 입력해서 영업일을 뽑아낼 수 있다. 

SELECT COUNT(A.DT)
  FROM (    SELECT TO_CHAR (SDT + LEVEL - 1, 'YYYYMMDD') DT,
                   TO_CHAR (SDT + LEVEL - 1, 'D') D
              FROM (SELECT TO_DATE ('20200101', 'YYYYMMDD') SDT,
                           TO_DATE ('20201130', 'YYYYMMDD') EDT
                      FROM DUAL)
        CONNECT BY LEVEL <= EDT - SDT + 1) A
     LEFT OUTER JOIN   (
        SELECT '20200301' DT, '3.1절' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200101' DT, '신정' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200124' DT, '구정' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200127' DT, '신정대휴' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200415' DT, '총선' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200430' DT, '석탄일' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200505' DT, '키즈' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200815' DT, '광복절' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200817' DT, '대휴' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20200930' DT, '추석' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20201001' DT, '추석' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20201002' DT, '추석' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL
        SELECT '20201009' DT, '한글날' CMT FROM DUAL -- 휴일정보(테이블)
        UNION ALL 
        SELECT '20201225' DT, '성탄절' CMT FROM DUAL -- 휴일정보(테이블) 
       ) B
       ON A.DT = B.DT
 WHERE 2=2
    AND A.D NOT IN ('1', '7')  -- 토/일요일 제외
    AND B.DT IS NULL
ORDER BY 1

컬럼이 400개 이하라는 가정 하에...

오너명 테이블명1 컬럼명1

오너명 테이블명1 컬럼명2

오너명 테이블명1 컬럼명3

오너명 테이블명1 컬럼명4

오너명 테이블명2 컬럼명1

오너명 테이블명2 컬럼명2

오너명 테이블명2 컬럼명3

오너명 테이블명2 컬럼명4

오너명 테이블명2 컬럼명5

오너명 테이블명2 컬럼명6

.

.

.

이런 걸

오너명 테이블명1 컬럼명1 컬럼명2 컬럼명3 컬럼명4

오너명 테이블명2 컬럼명1 컬럼명2 컬럼명3 컬럼명4 컬럼명5 컬럼명6

이렇게 나오게 하는 예제

WITH COLUMNS AS
(
SELECT OWNER, TABLE_NAME, COLUMN_NAME||' '|| CASE WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE = 0 THEN DATA_TYPE || '(' || DATA_PRECISION || ')'
                                                  WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE IS NOT NULL THEN DATA_TYPE || '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
                                                  WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL AND DATA_SCALE IS NULL THEN DATA_TYPE || '(' || DATA_PRECISION || ')'
                                                  WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NULL THEN DATA_TYPE
                                                  ELSE DATA_TYPE || '(' || DATA_LENGTH || ')'
                                                  END AS COLUMN_NAME
, COLUMN_ID
FROM DBA_TAB_COLS
WHERE OWNER = '오너다'
)
SELECT * FROM COLUMNS
PIVOT ( MAX(COLUMN_NAME) FOR COLUMN_ID IN (1 AS COL1, 2 AS COL2, 3 AS COL3, 4 AS COL4, 5 AS COL5, 6 AS COL6, 7 AS COL7, 8 AS COL8, 9 AS COL9, 10 AS COL10, 11 AS COL11, 12 AS COL12, 13 AS COL13, 14 AS COL14, 15 AS COL15, 16 AS COL16, 17 AS COL17, 18 AS COL18, 19 AS COL19, 20 AS COL20, 21 AS COL21, 22 AS COL22, 23 AS COL23, 24 AS COL24, 25 AS COL25, 26 AS COL26, 27 AS COL27, 28 AS COL28, 29 AS COL29, 30 AS COL30, 31 AS COL31, 32 AS COL32, 33 AS COL33, 34 AS COL34, 35 AS COL35, 36 AS COL36, 37 AS COL37, 38 AS COL38, 39 AS COL39, 40 AS COL40, 41 AS COL41, 42 AS COL42, 43 AS COL43, 44 AS COL44, 45 AS COL45, 46 AS COL46, 47 AS COL47, 48 AS COL48, 49 AS COL49, 50 AS COL50, 51 AS COL51, 52 AS COL52, 53 AS COL53, 54 AS COL54, 55 AS COL55, 56 AS COL56, 57 AS COL57, 58 AS COL58, 59 AS COL59, 60 AS COL60, 61 AS COL61, 62 AS COL62, 63 AS COL63, 64 AS COL64, 65 AS COL65, 66 AS COL66, 67 AS COL67, 68 AS COL68, 69 AS COL69, 70 AS COL70, 71 AS COL71, 72 AS COL72, 73 AS COL73, 74 AS COL74, 75 AS COL75, 76 AS COL76, 77 AS COL77, 78 AS COL78, 79 AS COL79, 80 AS COL80, 81 AS COL81, 82 AS COL82, 83 AS COL83, 84 AS COL84, 85 AS COL85, 86 AS COL86, 87 AS COL87, 88 AS COL88, 89 AS COL89, 90 AS COL90, 91 AS COL91, 92 AS COL92, 93 AS COL93, 94 AS COL94, 95 AS COL95, 96 AS COL96, 97 AS COL97, 98 AS COL98, 99 AS COL99, 100 AS COL100, 101 AS COL101, 102 AS COL102, 103 AS COL103, 104 AS COL104, 105 AS COL105, 106 AS COL106, 107 AS COL107, 108 AS COL108, 109 AS COL109, 110 AS COL110, 111 AS COL111, 112 AS COL112, 113 AS COL113, 114 AS COL114, 115 AS COL115, 116 AS COL116, 117 AS COL117, 118 AS COL118, 119 AS COL119, 120 AS COL120, 121 AS COL121, 122 AS COL122, 123 AS COL123, 124 AS COL124, 125 AS COL125, 126 AS COL126, 127 AS COL127, 128 AS COL128, 129 AS COL129, 130 AS COL130, 131 AS COL131, 132 AS COL132, 133 AS COL133, 134 AS COL134, 135 AS COL135, 136 AS COL136, 137 AS COL137, 138 AS COL138, 139 AS COL139, 140 AS COL140, 141 AS COL141, 142 AS COL142, 143 AS COL143, 144 AS COL144, 145 AS COL145, 146 AS COL146, 147 AS COL147, 148 AS COL148, 149 AS COL149, 150 AS COL150, 151 AS COL151, 152 AS COL152, 153 AS COL153, 154 AS COL154, 155 AS COL155, 156 AS COL156, 157 AS COL157, 158 AS COL158, 159 AS COL159, 160 AS COL160, 161 AS COL161, 162 AS COL162, 163 AS COL163, 164 AS COL164, 165 AS COL165, 166 AS COL166, 167 AS COL167, 168 AS COL168, 169 AS COL169, 170 AS COL170, 171 AS COL171, 172 AS COL172, 173 AS COL173, 174 AS COL174, 175 AS COL175, 176 AS COL176, 177 AS COL177, 178 AS COL178, 179 AS COL179, 180 AS COL180, 181 AS COL181, 182 AS COL182, 183 AS COL183, 184 AS COL184, 185 AS COL185, 186 AS COL186, 187 AS COL187, 188 AS COL188, 189 AS COL189, 190 AS COL190, 191 AS COL191, 192 AS COL192, 193 AS COL193, 194 AS COL194, 195 AS COL195, 196 AS COL196, 197 AS COL197, 198 AS COL198, 199 AS COL199, 200 AS COL200, 201 AS COL201, 202 AS COL202, 203 AS COL203, 204 AS COL204, 205 AS COL205, 206 AS COL206, 207 AS COL207, 208 AS COL208, 209 AS COL209, 210 AS COL210, 211 AS COL211, 212 AS COL212, 213 AS COL213, 214 AS COL214, 215 AS COL215, 216 AS COL216, 217 AS COL217, 218 AS COL218, 219 AS COL219, 220 AS COL220, 221 AS COL221, 222 AS COL222, 223 AS COL223, 224 AS COL224, 225 AS COL225, 226 AS COL226, 227 AS COL227, 228 AS COL228, 229 AS COL229, 230 AS COL230, 231 AS COL231, 232 AS COL232, 233 AS COL233, 234 AS COL234, 235 AS COL235, 236 AS COL236, 237 AS COL237, 238 AS COL238, 239 AS COL239, 240 AS COL240, 241 AS COL241, 242 AS COL242, 243 AS COL243, 244 AS COL244, 245 AS COL245, 246 AS COL246, 247 AS COL247, 248 AS COL248, 249 AS COL249, 250 AS COL250, 251 AS COL251, 252 AS COL252, 253 AS COL253, 254 AS COL254, 255 AS COL255, 256 AS COL256, 257 AS COL257, 258 AS COL258, 259 AS COL259, 260 AS COL260, 261 AS COL261, 262 AS COL262, 263 AS COL263, 264 AS COL264, 265 AS COL265, 266 AS COL266, 267 AS COL267, 268 AS COL268, 269 AS COL269, 270 AS COL270, 271 AS COL271, 272 AS COL272, 273 AS COL273, 274 AS COL274, 275 AS COL275, 276 AS COL276, 277 AS COL277, 278 AS COL278, 279 AS COL279, 280 AS COL280, 281 AS COL281, 282 AS COL282, 283 AS COL283, 284 AS COL284, 285 AS COL285, 286 AS COL286, 287 AS COL287, 288 AS COL288, 289 AS COL289, 290 AS COL290, 291 AS COL291, 292 AS COL292, 293 AS COL293, 294 AS COL294, 295 AS COL295, 296 AS COL296, 297 AS COL297, 298 AS COL298, 299 AS COL299, 300 AS COL300, 301 AS COL301, 302 AS COL302, 303 AS COL303, 304 AS COL304, 305 AS COL305, 306 AS COL306, 307 AS COL307, 308 AS COL308, 309 AS COL309, 310 AS COL310, 311 AS COL311, 312 AS COL312, 313 AS COL313, 314 AS COL314, 315 AS COL315, 316 AS COL316, 317 AS COL317, 318 AS COL318, 319 AS COL319, 320 AS COL320, 321 AS COL321, 322 AS COL322, 323 AS COL323, 324 AS COL324, 325 AS COL325, 326 AS COL326, 327 AS COL327, 328 AS COL328, 329 AS COL329, 330 AS COL330, 331 AS COL331, 332 AS COL332, 333 AS COL333, 334 AS COL334, 335 AS COL335, 336 AS COL336, 337 AS COL337, 338 AS COL338, 339 AS COL339, 340 AS COL340, 341 AS COL341, 342 AS COL342, 343 AS COL343, 344 AS COL344, 345 AS COL345, 346 AS COL346, 347 AS COL347, 348 AS COL348, 349 AS COL349, 350 AS COL350, 351 AS COL351, 352 AS COL352, 353 AS COL353, 354 AS COL354, 355 AS COL355, 356 AS COL356, 357 AS COL357, 358 AS COL358, 359 AS COL359, 360 AS COL360, 361 AS COL361, 362 AS COL362, 363 AS COL363, 364 AS COL364, 365 AS COL365, 366 AS COL366, 367 AS COL367, 368 AS COL368, 369 AS COL369, 370 AS COL370, 371 AS COL371, 372 AS COL372, 373 AS COL373, 374 AS COL374, 375 AS COL375, 376 AS COL376, 377 AS COL377, 378 AS COL378, 379 AS COL379, 380 AS COL380, 381 AS COL381, 382 AS COL382, 383 AS COL383, 384 AS COL384, 385 AS COL385, 386 AS COL386, 387 AS COL387, 388 AS COL388, 389 AS COL389, 390 AS COL390, 391 AS COL391, 392 AS COL392, 393 AS COL393, 394 AS COL394, 395 AS COL395, 396 AS COL396, 397 AS COL397, 398 AS COL398, 399 AS COL399, 400 AS COL400)
    )

잘 해석을 해보면

PIVOT ( MAX(COLUMN_NAME) FOR COLUMN_ID IN (1 AS COL1

주황색 부분은 PIVOT해서 보여주고 싶은 열을 지정

파란색 부분은 PIVOT해서 보여주고 싶은 열을 어떤 순으로 보여줄 지 기준(컬럼)을 지정.

안까먹으려고 다시 기록함. 

수십번을 써도... 한동안 안쓰면 자꾸 까먹네. ㅠㅠ

SELECT TABLE_OWNER, 

   TABLE_NAME, 

   INSERTS,

   UPDATES,

   DELETES,

   TIMESTAMP AS LAST_CHANGE

FROM  ALL_TAB_MODIFICATIONS

WHERE TO_CHAR(TIMESTAMP,'DD/MM/YYYY') = TO_CHAR(sysdate,'DD/MM/YYYY') 

AND TABLE_OWNER = '테이블오너'

ORDER BY LAST_CHANGE ASC;

CREATE TABLE COPY_T

AS

SELECT A.LV AS DUP_NO, B.LV AS NO

FROM (SELECT LEVEL LV

      FROM DUAL

      CONNECT BY LEVEL <= 200

      ) A

    INNER JOIN (SELECT LEVEL LV

                FROM DUAL

                CONNECT BY LEVEL <= 200

                ) B

        ON A.LV >= B.LV

< 중복제거 >

regexp_replace(LISTAGG(ACCT_ID, ',') WITHIN GROUP(ORDER BY ACCT_ID), '([^,]+)(,\1)+', '\1')

위 정규식을 이용하면 된다고 하는데... 

실제 되긴 하지만 

어떤 특정한 경우에는 잘 안되기도 한다. 

그래서 찾은게 아래.

regexp_replace(LISTAGG(ACCT_ID, ',') WITHIN GROUP(ORDER BY ACCT_ID), '([^,]+)(,\1)*(,|$)', '\1\3')

이게 잘 통하지만 이것도 안통하는 경우가 있을 수 있지 않을까...?

<4000BYTE 초과 시 생략 >

regexp_replace(LISTAGG(ACCT_ID, ',' ON OVERFLOW TRUNCATE '후략' WITH COUNT) WITHIN GROUP(ORDER BY ACCT_ID), '([^,]+)(,\1)*(,|$)', '\1\3')

TRUNCATE 뒤의 '후략' 은 생략된 부분을 보여주기 위해 추가한 문자이나 '후략' 없어도 됨

WITH COUNT는 생략된 ROW수를 표시해줌

EXEC dbms_stats.gather_table_stats(ownname => '오너명', tabname => '테이블명', cascade =>FALSE, estimate_percent => 10, degree => 4);

일단 내가 주로 쓰는 샘플 한 문장만... 

자세한건 나중에 더 작성 예정. :)

DB링크  이용 시 

DB connection 관련 정보는 아래에 설명되어 있다.

https://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_admin.htm#ADMIN12168

예전 10g에서 DB링크 이용 시 DB LINK Connection 이 full 나서 시스템을 사용하지 못하는 경우가 발생했다. 

처음엔 원인을 몰라 헤매었는데 

나중에 알고보니 

ALTER SESSION CLOSE DATABASE LINK linkname;

를 안해서 limit connection 수가 다 찼던 것. 

결론은 db링크를 쓰고 나면 session 을 close 해줘야 한다. 

commit 만 한다고 끝나는게 아니라는 거. 

안 쓸 수 있다면 db링크는 안쓰는게 더 좋... ㅎㅎ

길게 쓰자면 한없이 길 수 있는 주제다. 

숫자타입 컬럼의 데이터를 

INSERT

SELECT 하다 보면

소스 데이터의 숫자의 합과 

타겟 데이터의 숫자의 합이 다를 수 있다.

왜?

소스 컬럼 타입이 NUMBER 이고

타겟 컬럼 타입이 NUMBER(22) 일 때

NUMBER 는 소수점을 포함하고 

NUMBER(22)는 소수점을 포함하지 않는 정수형 타입이므로 

INSERT 하면서 반올림 해버린다. 

테스트해본 내용은 이렇다. 

CREATE TABLE HWON

(

BASIC_USE_QT NUMBER(22),

BASIC_USE_QT2 NUMBER

)

NOLOGGING;

INSERT INTO HWON

SELECT ODQT, ODQT

FROM TABLE

WHERE MOD(ODQT, 1) <> 0  -- 소수점 자리 값이 있는것만 

SELECT *

FROM HWON

이렇게 해보면 결과는

BASIC_USE_QT BASIC_USE_QT2 

1 0.5

1 0.5

1 0.5

2 1.5

1 0.5

1 0.5

1 0.5

1 0.5

1 0.5

이렇게 강제로 반올림 되어버린다. 

그러니 모르고 있으면... 값이 잘못 들어갈 수 있다.

select COLUMN_NAME, DATA_LENGTH, DATA_PRECISION, DATA_SCALE 

from all_tab_columns where table_name = 테이블명

해보면 DATA_SCALE 이 소수점 자리수를 의미한다. 0이면... 정수다. 

자세한 내용은 

https://m.blog.naver.com/PostView.nhn?blogId=hanccii&logNo=220733159291&proxyReferer=https%3A%2F%2Fwww.google.co.kr%2F

이 분 블로그 내용을 참고해보시라. 상당히 자세히 연구하신듯.