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

오라클 에러 정보 (ORA-01000) - maximum open cursors exceeded.pdf
0.11MB

--//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 주요 명령어

명령 수행 결과 실행 계획 실행 통계 plustrace 권한 여부
set autotrace on O O O O
set autotrace on explain O O X X
set autotrace on statistics O X O O
set autotrace traceonly X O O O
set autotrace traceonly explain X O X X
set autotrace traceonly statistics X X O O

참조 : 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

 

 


출처 : http://www.oracleclub.com/article/55741

설명은 안 하겠습니다.

 

WITH t AS
( 
SELECT XMLTYPE('
<ROOT>
<META use="report">
<META-NAME>Type1</META-NAME>
<META-VALUE>REQ1</META-VALUE>
</META>
<META use="report">
<META-NAME>Type2</META-NAME> 
<META-VALUE>REQ3</META-VALUE> 
</META>
<META use="report">
<META-NAME>Type3</META-NAME>
<META-VALUE>REQ4</META-VALUE>
</META>
</ROOT>
') v 
FROM dual 
) 
SELECT ExtractValue(Value(p), '/META/META-VALUE') "META-VALUE"
  FROM t 
     , TABLE(XMLSequence(Extract(v, '/ROOT/META[@use="report"]'))) p 
 WHERE ExtractValue(Value(p), '/META/META-NAME') = 'Type1'
; 
 

Oracle User/Role 생성

 SQLPlus를 이용하겠습니다.(기타 다른 툴들을 이용하여도 무방합니다.)

자세한 옵션 및 설명은 다른 사이트 메뉴얼을 참고하시기 바랍니다.

 

  1. SQLPLUS 접속(DBA 권한을 가진 계정으로)
    SQLPLUS {사용자ID}/{사용자 PASSWORD}@{TNSNAME or SID}
    or
    SQLPLUS {사용자ID}@{TNSNAME or SID}
  2. Tablespace(테이블스페이스) 생성
  3. MAPINFO User 생성(MAPINFO_MAPCATALOG를 만들어 사용하기 위한 사용자)
    ※ 다른 유저로 만들어 본적은 없다. 다른 유저로 만들었을시 오류가 있을련지 모르겠다
        심심하면 한번 만들어서 테스트하고 결과를 알려주기 바란다...(나도 궁금 하긴하다)
        그게 귀찮으면 그냥 본 필자처럼 MAPINFO란 유저로 만들길 바란다.
  4. MAPINFO User 권한(Role) 부여
  5. MAPUSER(가칭) User생성(실제 MAPINFO를 이용할 실 사용자)
  6. MAPUSER(가칭) User 권한(Role) 부여

Oracle_MI_Role_01.gif 

이 글은 스프링노트에서 작성되었습니다.

'GIS > MapInfo 강좌' 카테고리의 다른 글

02.04 Oracle Mapinfo_Mapcatalog 생성  (0) 2009.02.26
02.02 Oracle Database 설치  (0) 2009.02.26
02.01 Mapinfo Professional 설치  (0) 2009.02.26
01. 시스템 개요  (0) 2009.02.26
MapInfo 강좌  (0) 2009.02.26


ORA-01991 에러 발생시#


발생조건#

 OraData\{SID}폴더만 복사해서 복구할경우


장애내용#

Startup 시 ORA-01991: invalid password file '...PWD{SID}.ora' 이러면서 해당 파일 경로가 나타남.

해결 방법#

>orapwd file={경로}\PWD{SID}.ora password={패스워드}


기타 참고사항#

관리자 모드 접근 방법#

ORACLE_SID 지정#

Windows

>SET ORACLE_SID={SID}

or

UNIX

>export ORACLE_SID={SID}

관리자 모드#

>SQLPLUS "/as sysdba"
or
-----------------------------------------------
>SQLPLUS /nolog
SQL>conn sys/{패스워드} as sysdba
----------------------------------------------
or
-----------------------------------------------
>SQLPLUS /nolog
SQL>conn as sysdba
사용자명 입력 : sys/{패스워드}


이 글은 스프링노트에서 작성되었습니다.

'DBMS > Oracle' 카테고리의 다른 글

ODP.NET 설치 안하고 클라이언트 배포  (0) 2012.07.13
Oracle XML 쿼리  (0) 2012.06.11
정렬 조건 부여  (0) 2008.07.16
오라클 접속 포트 고정시키기  (0) 2008.07.16
로그마이너  (0) 2008.07.16

레이스터에 등록(regedit)

\\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 에 "확장 가능한 문자열 값(REG_EXPAND_SZ)"로 추가

USE_SHARED_SOCKET=TRUE

 

간혹 클라이언트 연결포트가 1521(기본포트)가 아닌 랜덤포트를 이용하는 경우가 있을때 이용 

이 글은 스프링노트에서 작성되었습니다.

'DBMS > Oracle' 카테고리의 다른 글

ORA-01991 에러 발생시  (0) 2008.09.08
정렬 조건 부여  (0) 2008.07.16
로그마이너  (0) 2008.07.16
SQL*Plus Instant Client  (0) 2008.07.16
Oracle Object For OLE(OO4O)  (0) 2008.07.16

+ Recent posts