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 기준

2012/09/26 - [DBMS] - 순진한 트리(문서) - 재귀쿼리 방법론 에서 언급된 재귀적호출 중 MS-SQL을 이용한 MSDN자료 입니다.

원문 : http://msdn.microsoft.com/ko-kr/library/ms186243(v=sql.105).aspx

재귀적 CTE 구조

---

Transact-SQL의 재귀적 CTE 구조는 다른 프로그래밍 언어의 재귀 루틴과 비슷합니다. 다른 언어의 재귀 루틴은 스칼라 값을 반환하지만 재귀적 CTE는 여러 행을 반환할 수 있습니다.

재귀적 CTE는 다음 세 요소로 구성됩니다.

1. 루틴의 호출

   재귀적 CTE의 첫 번째 호출은 UNION ALL, UNION, EXCEPT 또는 INTERSECT 연산자로 조인된 하나 이상의 CTE_query_definitions로 구성됩니다. 이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버라고 합니다.

   CTE_query_definitions는 CTE 자체를 참조하지 않는 경우 앵커 멤버로 간주됩니다. 모든 앵커 멤버 쿼리 정의를 첫 번째 재귀 멤버 정의 앞에 배치하고 UNION ALL 연산자를 사용하여 마지막 앵커 멤버를 첫 번째 재귀 멤버와 조인해야 합니다.

2. 루틴의 재귀 호출

   재귀 호출에는 CTE 자체를 참조하는 UNION ALL 연산자로 조인된 하나 이상의 CTE_query_definitions가 포함됩니다. 이러한 쿼리 정의를 재귀 멤버라고 합니다.

3. 종료 확인

   종료 확인은 암시적으로 수행됩니다. 이전 호출에서 반환되는 행이 없을 때 재귀가 중지됩니다.

참고
잘못 구성된 재귀적 CTE로 인해 무한 루프가 발생할 수 있습니다. 예를 들어 재귀 멤버 쿼리 정의가 부모 열과 자식 열 모두에 대해 동일한 값을 반환하면 무한 루프가 생성된 것입니다. 재귀 쿼리의 결과를 테스트할 때 INSERT, UPDATE, DELETE 또는 SELECT 문의 OPTION 절에서 MAXRECURSION 힌트 및 0과 32,767 사이의 값을 사용하여 특정 문에 허용되는 재귀 수준의 수를 제한할 수 있습니다. 자세한 내용은 쿼리 힌트(Transact-SQL) 및 WITH common_table_expression(Transact-SQL)을 참조하십시오.

의사 코드 및 의미 체계

재귀적 CTE 구조에는 앵커 멤버와 재귀 멤버가 적어도 하나씩 포함되어야 합니다. 다음 의사 코드에서는 단일 앵커 멤버와 단일 재귀 멤버가 포함된 간단한 재귀적 CTE의 구성 요소를 보여 줍니다.

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition –- Anchor member is defined.

UNION ALL

CTE_query_definition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

재귀 실행의 의미 체계는 다음과 같습니다.

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할합니다.

2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만듭니다.

3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행합니다.

4. 빈 집합이 반환될 때까지 3단계를 반복합니다.

5. 결과 집합을 반환합니다. 이것은 T0에서 Tn까지의 UNION ALL입니다.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
	EmployeeID smallint NOT NULL,
	FirstName nvarchar(30)  NOT NULL,
	LastName  nvarchar(40) NOT NULL,
	Title nvarchar(50) NOT NULL,
	DeptID smallint NOT NULL,
	ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

순진한트리(계층쿼리).pdf

참고하기 좋은듯....

MSDN 참고 자료 : 공통 테이블 식을 사용하는 재귀 쿼리 (http://msdn.microsoft.com/ko-kr/library/ms186243(v=sql.90).aspx)