ORA-01000: maximum open cursors exceeded
ORA-01000: 최대 열기 커서 수를 초과했습니다

--sys나 system 유저로 접속하여 현재 설정된 커서 개수를 확인 한다.
show parameter open_cursors 

--현재 사용중인 섹션의 커서 (100개 초과) 확인
select a.sid, s.process, s.machine, s.module, s.action, count(*) as cnt             
from V$OPEN_CURSOR a, V$SESSION s
where s.sid = a.sid
group by a.sid, s.process, s.machine, s.module, s.action
having count(*) > 100
order by count(*) desc;

--특정 SID 기준 조회
select * from V$OPEN_CURSOR where sid = 4;

--커서 개수 재설정
alter system set open_cursors = 4000 scope=both;

출처 : https://jack-of-all-trades.tistory.com/398

--//Oracle 11.2.0 for Windows 버전 기준
--//SYSDBA로 접속 ex) C:\>SQLPLUS "/as sysdba"
--//이하 SQL-Plus : SQL>

@"C:\oracle\product\11.2.0\dbhome_1\sqlplus\admin\plustrce.sql"

grant plustrace to [사용자];

--//=======================================

conn [사용자]/[패스워드];
@"C:\oracle\product\11.2.0\dbhome_1\rdbms\admin\utlxplan.sql"

--//PLAN 확인
set linesize 120;
set autot on;
select * from tab where rownum=1;

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

AUTOTRACE 주요 명령어

참조 : https://coding-factory.tistory.com/745

 문자열 값(통화, 자리수 구분)을 숫자타입으로 변경

※ 자세한 설명은 생략 (정규식은 10g 이상 가능)

SELECT TO_NUMBER( REGEXP_REPLACE('$-1,234,567.89', '[^0-9.-]', '') ) NUM FROM DUAL

오라클의 여러행을 하나의 컬럼으로 합치는 쿼리를 메모하고져 글을 남깁니다.

자세한 설명은 하지 않겠습니다.

(XMLAGG 및 정규식은 10g부터 사용 가능)

• WM_CONCAT

SELECT
    WM_CONCAT(job) AS WM_JOBS
  FROM emp

• LISTAGG

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
FROM emp

• LISTAGG (deptno 기준)

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) OVER(PARTITION BY deptno) AS AGG_JOBS2
FROM emp

• LISTAGG (중복제거)

SELECT
    LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
    , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,\1)*(,|$)', '\1\3') AS AGG_JOBS3
FROM emp

• XMLAGG, XMLELEMENT (CLOB타입 - WM_CONCAT, LISTAGG : VARCHAR2라 최대 4000byte)

SELECT
    XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS
FROM emp A

• XMLAGG, XMLELEMENT (CLOB타입, 중복제거, 시작/끝 구분자 삭제)

WITH A AS
(
SELECT
    XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
    , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT 
    SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
    , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
    , SUBSTR(REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,\1)*(,|$)', '\1\3'), 2) AS SUB_JOB3
FROM A

• 통합 (LISTAGG -PARTITION 제외)

WITH A AS
(
SELECT
    WM_CONCAT(job) AS WM_JOBS
    , LISTAGG(job, ',') WITHIN GROUP(ORDER BY job) AS AGG_JOBS
    , REGEXP_REPLACE( LISTAGG(job, ',') WITHIN GROUP(ORDER BY job), '([^,]+)(,\1)*(,|$)', '\1\3') AS AGG_JOBS3
    , XMLAGG(XMLELEMENT(A, A.job || ',') ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS1
    , XMLAGG(XMLELEMENT(A, ',' || A.job ) ORDER BY A.job).EXTRACT('//text()').GETCLOBVAL() AS CLOB_JOBS2
FROM emp A
)
SELECT
    A.*
    , SUBSTR(A.CLOB_JOBS1, 1, LENGTH(A.CLOB_JOBS1) - 1) AS SUB_JOBS1
    , SUBSTR(A.CLOB_JOBS2, 2) AS SUB_JOBS2
    , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS1, '([^,]+)(,\1)*(,|$)', '\1\3'), '(,)$', '') AS SUB_JOB3
    , REGEXP_REPLACE( REGEXP_REPLACE ( A.CLOB_JOBS2, '([^,]+)(,\1)*(,|$)', '\1\3'), '^(,)', '') AS SUB_JOB4
FROM A

※ ORA-01489 문자열 연결의 결과가 너무 깁니다.

 : 오류 발생시 XMLAGG를 이용하여 CLOB 사용.

ORA-12541 : TNS 리스너가 없습니다.

갑자기 "ORA-12541"이라는 오류가 발생하였다.

기존에 접속자는 이상이 없는데, 신규로 접속할려고 하니 위와 같은 오류를 발생시킨다.

클라이언트인 내 컴퓨터도 꺼보고, 서버도 리스타트 해보고 신규 접속을 할려고하니,

서버를 리부팅해서 그런지 모두 접속이 되지 않는다.

서버에서 "C:\>lsnrctl status"를 실행시켜 보니......멍청히 있다.

그래서 구글링을 해본 결과, 간혹 "listener.log" 파일이 4GB를 넘으면 접속이 안된다는 글을 본다.

그래서 위치를 찾는다.

난 윈도우버전을 이용하므로 윈도우 버전으로 위치를 기입하겠다.

"{드라이브명}:\{ORACLE 설치 ROOT 폴더}\diag\tnslsnr\{서버명}\listener\trace\listener.log"

ex) 폴더 위치 : D:\oracle\diag\tnslsnr\TESTSVR\listener\trace\

1. 오라클 스톱 그런거 귀찮고 "lsnrctl stop"을 날려도 함흥 차사이니 그냥 실행명령 "servcies.msc" 또는 제어판 "서비스"를 실행하여 서비스를 제어하는 Dialog를 실행한다.
   (혹시 모르니 오라클 서비스는 먼저 정지 시켜놓고 하자~)
   
   
2. 이름 중 "OracleOraDb11g_home1TNSListener"를 찾아서 서비스를 중지 시킨다.
   
   
   
   
3. 설정 폴더로 이동하여 기존 4GB가 넘은 "listener.log"의 이름을 변경 또는 삭제한다.
   (신규 파일은 안만들어도 나중에 lsnrctl이 정상 가동 되면서 시스템이 알아서 새로 만든다.)
   
   
   
   
4. 다시 시작하고 그런거 귀찮고 서비스가 꼬일질 모르니 이번 참에 서버를 한번 리부팅해준다.
   
   
5. 모든 서비스가 정상적으로 접속되는지 확인한다.

※ 오라클 버전 11.2.0 x64bit 기준

자작이며, PHP Library중 하나인 ADOdb의 crypt.inc.php의 MD5Crypt Class를 C#으로 변경 후 Oracle용 PL/SQL에 맞게 변경하였습니다.

MS-SQL, SQLite는 C#을 이용하여 DLL로 제작하여 로딩하시면 사용 가능합니다.

패키지명은 HxCrypt라고 하였습니다.

별도로 비트연산용 PKG_UTILS( http://overoid.tistory.com/35 ) / 첨부(PKG_UTILS_Oracle.sql) 참조를 이용하였습니다.

자세한 내용은 첨부파일을 참고하시기 바랍니다.

(ADODB가 GNU 라이센스이므로 공유는 당연하다 판단했습니다. / C#과 Oracle용으로 마이그레이션한 라이블러리를 공개합니다.)

PHP용 소스 출처 : http://adodb.org/

- 암호화 : HxCrypt.Encrypt(문자열, 키값) //호출 할 때마다 다른 값이 리턴됨

- 복호화 : HxCrypt.Decrypt(암호 문자열, 키값)

[CODE]

CREATE OR REPLACE PACKAGE HxCrypt AS
/******************************************************************************
   // 출처 : userpark.net / userpark@userpark.net
   // 배포 라이센스 : GNU
   // 원 소스 출처 : http://adodb.org/ , crypt.inc.php, MD5Crypt
   NAME:       HxCrypt
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2018/05/10      userpark       1. Created this package.
******************************************************************************/

  FUNCTION base64_encode(inputString VARCHAR2) RETURN VARCHAR2;
  FUNCTION base64_decode(inputString VARCHAR2) RETURN VARCHAR2;
  FUNCTION Md5(inputString VARCHAR2) RETURN VARCHAR2;
  FUNCTION keyED(inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2;
  FUNCTION Encrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2;
  FUNCTION Decrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2;
  
END HxCrypt;
/



CREATE OR REPLACE PACKAGE BODY HxCrypt AS
  outputString VARCHAR2(2000);

FUNCTION base64_encode(inputString VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    outputString := utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(inputString)));
    RETURN outputString;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END base64_encode;
  
  FUNCTION base64_decode(inputString VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    outputString := utl_raw.cast_to_varchar2(utl_encode.base64_decode(utl_raw.cast_to_raw(inputString)));
    RETURN outputString;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END base64_decode;
  
  FUNCTION Md5(inputString VARCHAR2) RETURN VARCHAR2 AS
  BEGIN
    --outputString := Md5(inputString);
    outputString := LOWER(RAWTOHEX(UTL_RAW.CAST_TO_RAW(sys.dbms_obfuscation_toolkit.md5(input_string => inputString))));
    RETURN outputString;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
  END Md5;
  
  FUNCTION KeyED (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2 IS
ctr NUMBER := 0;
i NUMBER := 0;
nInput NUMBER := 0;
nKey NUMBER := 0;
keyValue VARCHAR2(2000);
iTxt NUMBER;
iKey NUMBER;
iVal NUMBER;
BEGIN
    i := 1;
    keyValue := Md5(inputKey);
    nInput := LENGTH(inputValue);
    nKey := LENGTH(keyValue);
    ctr := 1;
    outputString := null;
    WHILE i <= nInput LOOP
        IF ctr = nKey THEN 
           ctr := 1;
        END IF;
        --utl_raw.cast_to_raw(inputString)
        iTxt := ASCII(SUBSTR(TO_CHAR(inputValue), i, 1));
        --iTxt := utl_raw.cast_to_raw(SUBSTR(TO_CHAR(inputString), i + 1, 1));
        iKey := ASCII(SUBSTR(TO_CHAR(keyValue), ctr, 1));
        --iKey := utl_raw.cast_to_raw(SUBSTR(TO_CHAR(keyValue), ctr + 1, 1));
        --iVal := iTxt ^ iKey;
        iVal := PKG_UTILS.BITXOR(iTxt,iKey);
        outputString := outputString || CHR(iVal);
        --outputString := outputString || utl_raw.cast_to_varchar2(TO_CHAR(iVal));
        ctr := ctr + 1;
        i := i + 1;
        EXIT WHEN i > nInput; 
    END LOOP;
    RETURN outputString;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END KeyED;

FUNCTION Encrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2 IS
Result VARCHAR2(2000);
ctr NUMBER := 0;
i NUMBER := 0;
n NUMBER := 0;
keyValue VARCHAR2(2000);
nKey NUMBER := 0;
iTxt NUMBER;
iKey NUMBER;
iVal NUMBER;
cKey VARCHAR2(8);
BEGIN
    i := 1;
    ctr := 1;
    keyValue := Md5(TO_CHAR(CEIL(DBMS_RANDOM.VALUE(0,32000))));
    n := LENGTH(inputValue);
    nKey := LENGTH(keyValue);
    Result := null;
    WHILE i <= n LOOP
        IF ctr = nKey THEN 
           ctr := 1;
        END IF;
        cKey := SUBSTR(TO_CHAR(keyValue), ctr, 1);
        iTxt := ASCII(SUBSTR(TO_CHAR(inputValue), i, 1));
        iKey := ASCII(cKey);
        --iVal := iTxt ^ iKey;
        iVal := PKG_UTILS.BITXOR(iTxt,iKey);
        Result := Result || cKey || CHR(iVal);
        ctr := ctr + 1;
        i := i + 1;
        EXIT WHEN i > n; 
    END LOOP;
    outputString := base64_encode(KeyED(Result, inputKey));
    RETURN outputString;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END Encrypt;

FUNCTION Decrypt (inputValue VARCHAR2, inputKey VARCHAR2) RETURN VARCHAR2 IS
i NUMBER := 0;
n NUMBER := 0;
keyValue VARCHAR2(2000);
iTxt NUMBER;
iKey NUMBER;
iVal NUMBER;
BEGIN
    i := 1;
    keyValue := KeyED(base64_decode(inputValue), inputKey);
    n := LENGTH(keyValue);
    outputString := null;
    WHILE i <= n LOOP
        iKey := ASCII(SUBSTR(TO_CHAR(keyValue), i, 1));
        i := i + 1;
        iTxt := ASCII(SUBSTR(TO_CHAR(keyValue), i, 1));
        --iVal := iTxt ^ iKey;
        iVal := PKG_UTILS.BITXOR(iTxt,iKey);
        outputString := outputString || CHR(iVal);
        i := i + 1;
        EXIT WHEN i > n; 
    END LOOP;
    
    RETURN outputString;
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
           
END Decrypt;


END HxCrypt;
/

HxCrypt_Oracle_userpark.sql

PKG_UTILS_Oracle.sql

[PHP / Crypt / MD5Crypt]암호화, 복호화 Class (ADOdb의 crypt.inc.php

http://userpark.net/124

[C# / .NET / Crypt / HxCrypt]암호화, 복호화 Class

http://userpark.net/125

[ORACLE / PL/SQL / Crypt / HxCrypt]암호화, 복호화 PACKAGE(Class)

http://userpark.net/126