적고 쓰고 끄적이며 남기자

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

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

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

또는 

BEGIN

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

END;

-- degree는 parallel 과 같은 의미

-- estimate_percent 는 전체 중 샘플링하고자 하는 데이터의 양(%). 일반적으로 5~10%로 설정하면 되고 데이터의 양이 클수록 적은 %로도 충분함. 엄청 많을땐 1%만 설정해도 통계정보 생성하는데 꽤 걸릴 수 있음

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

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

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

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

SELECT A.TABLESPACE_NAME AS "TABLESPACE"

     , A.BYTES / 1024 / 1024  AS "TOTAL(MB)"

     , (A.BYTES - NVL(B.FREEBYTES,0)) / 1024 / 1024 AS "Used(MB)"

     , NVL(B.FREEBYTES,0) / 1024 / 1024 AS "Free(MB)"

     , ROUND(NVL(B.FREEBYTES,0)*100/A.BYTES,2) AS "Free(%)"

FROM (

      SELECT TABLESPACE_NAME

           , SUM(BYTES) BYTES 

        FROM DBA_DATA_FILES 

       GROUP BY TABLESPACE_NAME

      ) A

   , (

      SELECT TABLESPACE_NAME

           , SUM(NVL(BYTES,0)) FREEBYTES 

        FROM DBA_FREE_SPACE 

       GROUP BY TABLESPACE_NAME

      ) B

WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)

ORDER BY 5 ASC;

좀 오래 걸리긴 함. ...

SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", 

 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 

 TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') "Used (M)", 

 TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %" 

 FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, 

 (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f 

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) 

 AND NOT (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY') 

 UNION ALL 

 SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", 

 TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 

 TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Used (M)", 

 TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, 

 (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, 

 (select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t 

 WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) 

 AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

테스트 데이터 생성하려고 LOCAL 오라클(12c)에 기동했는데... 

오라클이 메모리를 너무 많이 잡아먹네? 2.5G ?? 

이걸 어떻게 줄이지? 다른사람들은 300~500M 메모리 사용하던데... 난 왜...? 

이거 줄여보겠다고 

AMM방식 (잘 모르면서 섣불리...)

세팅하다가... 

기동이 안돼 ㅡㅡ

SQL> startup mount

ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 2533359616 bytes

Fixed Size                  3048824 bytes

Variable Size             671091336 bytes

Database Buffers         1845493760 bytes

Redo Buffers               13725696 bytes

데이터베이스가 마운트되었습니다.

SQL> alter system set sga_max_size=1G scope=spfile;

시스템이 변경되었습니다.

SQL> alter system set memory_max_target=1G scope=spfile;

시스템이 변경되었습니다.

SQL> alter system set memory_target=1G scope=spfile;

시스템이 변경되었습니다.

SQL> shutdown immediate;

ORA-01109: 데이터베이스가 개방되지 않습니다

데이터베이스가 마운트 해제되었습니다.

ORACLE 인스턴스가 종료되었습니다.

SQL> startup mount;

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3224M

SQL> alter system set memory_max_target=3G scope=spfile;

alter system set memory_max_target=3G scope=spfile

*

1행에 오류:

ORA-01034: ORACLE not available

프로세스 ID: 0

세션 ID: 0 일련 번호: 0

SQL> alter system set memory_target=3G scope=spfile;

alter system set memory_target=3G scope=spfile

*

1행에 오류:

ORA-01034: ORACLE not available

오 마이갓

젠장

STARTUP을 해야 세팅을 바꾸지.... 근데 기동이 안돼 ㅋㅋㅋ

망했다

구글링 결과... 

SQL> create pfile from spfile;

파일이 생성되었습니다.

이렇게 INITorcl.ORA 파일을 생성한 후 (FROM SPFILEORCL.ORA파일)

D:\app\oracle\product\12.1.0\dbhome_1\database 폴더의  INITorcl.ORA 파일을 열어서 

*.memory_max_target=3380609024

*.memory_target=3380609024

...

*.sga_max_size=3380609024

*.sga_target=2416m

이 부분을 수정한다. 수정 전에는 아까 설정한 memory_max_target=1G 로 되어있었다. ㅋㅋ

SQL> create spfile from pfile;

파일이 생성되었습니다.

이렇게 다시 SPFILEORCL.ORA 파일을 생성해주고 

SQL> startup mount

ORACLE 인스턴스가 시작되었습니다.

Total System Global Area 3380609024 bytes

Fixed Size                  3080344 bytes

Variable Size            1518341992 bytes

Database Buffers         1853882368 bytes

Redo Buffers                5304320 bytes

데이터베이스가 마운트되었습니다.

SQL> alter database open;

데이타베이스가 변경되었습니다.

SQL>

올레~