본문 바로가기
반응형

너알나알/오라클[Oracle]25

[오라클]PIVOT 함수 가변 IN절 오라클 PIVOT 함수에 대한 포스팅에 이어 가별 IN절을 활용한 구문을 사용해보려고 합니다 [오라클]PIVOT 함수 사용 행+열 바꾸기 앞서 일정 갯수의 ROW 생성하기를 하여서 고정된 행을 기준으로 값을 표현 해보았습니다 테이블 "한글" 테이블 "영문" 1 ㄱ A 2 ㄴ B 3 ㄷ C 4 그렇다면 반대로 행을 열로 바꿀 수 있을까요? 정답은 일 dolphwtsanta.tistory.com IN절에 사용될 구문은 SYS.DBMS_DEBUG_VC2COLL 함수를 사용할 예정인데 이는 Oracle 데이터베이스에서 사용되는 내장 패키지인 dbms_debug의 일부 함수입니다 주로 디버깅 및 코드 실행 추적을 위해 사용되며 varchar2 형식의 값을 컬렉션 데이터 유형으로 변환하는데 사용됩니다. SELECT.. 2024. 3. 27.
[오라클]사용자 테이블의 건수 조회 사용하고 있는 계정에 존재하는 모든 테이블 List와 건수를 조회하고 싶을 경우 Oracle에서 제공하고 있는 DBMS_XMLGEN.GETXMLTYPE 기능을 통해 동적쿼리를 활용해 조회 할 수 있습니다 SELECT TABLE_NAME, NUM_ROWS, TO_NUMBER(DBMS_XMLGEN.GETXMLTYPE('SELECT COUNT(*) FROM ' || TABLE_NAME).EXTRACT('//text()')) AS NUM_ROWS2 FROM USER_TABLES 위 SQL을 실행해보면 NUM_ROWS와 NUM_ROWS2의 건수가 상이한 테이블이 존재하는데 NUM_ROWS의 경우 통계자료로 갱신되지 않은 데이터 건수가 조회 됩니다. 만약 이를 최신 통계 결과로 보고 싶으시다면 아래 SQL을 실행하.. 2024. 3. 21.
[오라클]CONNECT BY와 LEVEL 사용(메뉴트리) 앞선 포스팅에서 다루었던 N ROW생성하기에서 CONNECT BY와 LEVEL을 사용합니다 CONNECT BY CONNECT BY는 계층적인 데이터를 조회하기 위해 사용되는 SQL 연산자로 특히, 부-모 관계가 있는 데이터를 조회할 때 사용됩니다. CONNECT BY 연산자를 활용하면 특정 열과 그 열의 부모 열을 기준으로 계층적인 데이터를 조회 할 수 있습니다 SELECT 컬럼 FROM 테이블 CONNECT BY 조건1 START WITH 조건2; 조건1에서는 부-모 자식 관계를 정의하는 부분이며 조건2에서는 계층적인 조회의 시작점을 정의하는 부분입니다(START WITH와 같이 사용했을 경우) LEVEL LEVEL은 CONNECT BY와 함께 사용되는 가상 컬럼으로 계층 구조에서 각 레벨(깊이)를 나.. 2024. 3. 21.
[오라클]PIVOT 함수 사용 행+열 바꾸기 앞서 일정 갯수의 ROW 생성하기를 하여서 고정된 행을 기준으로 값을 표현 해보았습니다 테이블 "한글" 테이블 "영문" 1 ㄱ A 2 ㄴ B 3 ㄷ C 4 그렇다면 반대로 행을 열로 바꿀 수 있을까요? 정답은 일단 “가능하다” 입니다 여러 방법이 있겠지만 PIVOT함수를 사용하여 아래와 같이 조회 해보도록 하겠습니다 1 2 3 4 테이블 "영문" A B C PIVOT 함수 데이터를 변환하는 함수중 하나로 행 데이터를 열 데이터로 변환하여 집계결과를 보고서 형태로 출력할 때 주로 사용합니다. Pivot 함수의 기본적 문법은 다음과 같습니다 SELECT pivot 적용하지 않을 열들, 변환하고자 하는 열 FROM 테이블 PIVOT ( 변환된 열들에 대한 집계 함수(sum,avg,max,min 등) FOR 변.. 2024. 3. 21.
[오라클]일정 갯수의 ROW 생성 고정된 행을 가진 Grid를 조회 해야할 경우에 드라이빙 테이블용도로 원하는 갯수만큼 Row를 생성할 수 있습니다 예를 들어서 아래 테이블처럼 1,2,3,4는 고정되어 있고 A,B테이블에서 값을 가져와 순차적으로 표현해줘야 할 경우에 1,2,3,4에 대한 값을 임의로 생성해두고 SEQUENCE를 KEY로 잡고 LEFT JOIN으로 조회하여 표현해줄 수 있습니다 테이블 “한글” 테이블 “영문” 1 ㄱ A 2 ㄴ B 3 ㄷ C 4 CONNECT BY + LEVEL 활용 SEQUENCE생성 SELECT LEVEL AS SEQ FROM DUAL CONNECT BY LEVEL 2024. 3. 20.
[오라클]1개의 ROW값을 N개의 ROW로 조회(CSV DATA) 어제 포스팅하였던 내용( [오라클]N개의 ROW의 값을 1개의 ROW로 조회 )의 반대 상황이 생깁니다 CSV DATA와 같이 어떤 구분자 (ex, ‘,’ )로 이루어진 하나의 String을 기준으로 N Row로 만들어서 조회해야 할 때 ‘CONNET BY’와 ‘REGEXP-SUBSTR’ 함수를 조합하여 구현할 수 있습니다 ‘ , ‘ 로 구분된 문자열을 N개의 ROW로 분리 WITH T AS ( SELECT 'ㄱ,ㄴ,ㄷ,ㄹ' AS CSV_STRING FROM DUAL ) SELECT TRIM(REGEXP_SUBSTR(CSV_STRING, '[^,]+', 1, LEVEL)) AS VALUE FROM T CONNECT BY REGEXP_SUBSTR(CSV_STRING, '[^,]+', 1, LEVEL) IS.. 2024. 3. 20.
[오라클]오라클 데이터베이스 버전확인 오라클의 버전별로 문법과 기능들이 조금씩 변경이 되고 있습니다 11g부터 간략하게 이야기하면 11g : 그리드 컴퓨팅, 12g : 클라우드 컴퓨팅, 19g : AI 컴퓨팅, 21g : 블록체인 기술지원으로 볼 수 있습니다 이전 포스팅 [오라클]N개의 ROW의 값을 1개의 ROW로 조회 에서도 WM_CONCAT가 더이상 사용되지 않고 LISTAGG 함수가 권장되기도 하였습니다 그럼, 설치된 오라클의 VERSION 확인 하는 방법에 대해 알아보겠습니다 V$VERSION 뷰 SELECT * FROM V$VERSION BANNER 컬럼에 주요 버전 정보가 조회되며 1번째 Row인 “Oracle Database 11g Express ~”가 버전 정보입니다 PRODUCT_COMPONENT_VERSION SELECT.. 2024. 3. 20.
[오라클]N개의 ROW의 값을 1개의 ROW로 조회 데이터를 조회 하였을때 N개의 결과값이 조회 될때 이 값들을 1개의 ROW로 표현해야 할 경우가 있습니다 UI 설계에 따라서 그리그 형식이 아닌 하나의 TEXT에 표현학거나 특정 Component에서 입력값이 구분자 ‘,’로 된 값들일 경우가 있습니다 제가 아는 몇가지 기능들을 공유 해드립니다 그룹 내의 값을 하나의 문자열로 결합하는데 사용합니다 LISTAGG 함수 SELECT T.COL1, LISTAGG(T.COL2, ',') WITHIN GROUP (ORDER BY COL1) FROM ( SELECT 'A' AS COL1, 'ㄱ' AS COL2 FROM DUAL UNION ALL SELECT 'A' AS COL1, 'ㄴ' AS COL2 FROM DUAL UNION ALL SELECT 'A' AS CO.. 2024. 3. 18.
[오라클]데이터 복구, Flashback 오라클을 사용하는 어플리케이션에서 사용자에 의한 데이터가 삭제되었거나 시스템 운영자의 Migration 또는 데이터 보정작업중에 실수로 테이블에 데이터를 삭제 해버리는 경우가 발생합니다 이럴 경우, 처음에 조금 당황스러운데 오라클에 데이터를 복구하는 방법이 있습니다 물론, 데이터베이스 구성과 사용 가능한 백업에 따라서 다양하지만 일반적인 삭제된 데이터를 복구하는 방법에 대해 공유합니다 TIMESTAMP ( FLASHBACK QUERY ) Oracle에 Flashback Query 기능이 활성화 되어 있다면 ‘AS OF TIMESTAMP’ 절을 사용하여 특정 시점의 데이터를 조회할 수 있습니다 ( ORACLE 9i 이상부터 지원 ) SELECT * FROM STOCK_TRADE_DAILY AS OF TIM.. 2024. 3. 18.
[오라클]오라클을 만든 프로그래밍 언어 오라클 역사에 대한 포스팅( [오라클]오라클, 그리고 역사에 대해 (tistory.com) )과 함께 지인이 질문한 내용입니다 “유명한 오라클은 어떤 프로그래밍 언어로 만들어졌나요?” 네, 저도 잘 몰라서 한번 찾아 보았습니다 오라클을 개발한 프로그래밍 언어 오라클은 C와 C++ 프로그래밍 언어를 주로 사용하여 개발되었습니다 C언어는 높은 이식성과 성능을 제공하는 고급 프로그래밍 언어로 저도 제일 처음 접했던 프로그래밍 언어입니다 JAVA가 대중화 되기 전에는 대부분이 C을 주로 다루었죠 객체지향언어의 개념이 나오면서 C++, JAVA를 많이 배웠고요 ( C++은 객체 지향 프로그래밍을 지원하는 C언어의 확장 버전입니다 ) 오라클 초기에 데이터베이스의 핵심 부분(Oracle Server)을 구현하는데 C.. 2024. 3. 13.
[오라클]오라클, 그리고 역사에 대해 지인이 문득 기술적인 질문이 아닌 오라클에 대해 물었을때, 스스로가 잘 모른다는걸 깨닳았습니다 제가 정말 오라클을 사용하는 유저로써 부끄럽기도 했고요 그래서 오늘은 오라클의 역사에 대해 잠깐 알아보았습니다 오라클의 흥미로운 여정 1977년 : 래리 엘리슨, 밥 마이너, 애드 오츠가 Oracle 회사를 설립합니다. 그리고 연구를하여 관계형 데이터베이스 관리 시스템(RDBMS)를 개발합니다 1979년 : 오라클은 처음으로 상용 RDBMS인 Oracle Version2를 출시, 데이터베이스와 상호작용하기 위한 언어 SQL을 지원합니다 1983년 : Oracle Version3를 출시하고, 1984년 : Oracle은 IPO를 통해 공개되며 성장과 사업의 확장을 시작합니다 1986년 : Oracle Versio.. 2024. 3. 13.
[오라클]날짜 한글+영문 표기 다국어 적용 또는 사용자의 요청에 의해 날짜를 영문으로 표기해야 할 경우가 있습니다 오라클에서는 날짜의 DATE 타입을 다양한 형태로 형변환 하여 표기가 가능하며 NLS_DATE_LANGUAGE 옵션을 사용하여 영문으로 표현할 수 있습니다 날짜 한글+영문 조회 SELECT TO_CHAR(SYSDATE,'DAY', 'NLS_DATE_LANGUAGE=KOREAN') AS "요일 전체표기 한글", TO_CHAR(SYSDATE,'DY', 'NLS_DATE_LANGUAGE=KOREAN') AS "요일 단축표기 한글", TO_CHAR(SYSDATE,'DAY', 'NLS_DATE_LANGUAGE=ENGLISH') AS "요일 전체표기 영문", TO_CHAR(SYSDATE,'DY', 'NLS_DATE_LANGUAGE=.. 2024. 3. 13.
[오라클]필드+코멘트 사용처 조회 및 추가 시스템을 운영 하다보면 필요에 의해서 필드나 필드명이 사용되고 있는지 확인 해봐야 하는 경우가 있습니다 사용처 리스트나 신규 필드를 추가할때 유사한 필드로 기 생성되어 있는지 확인이 필요합니다 이럴때 간단하게 사용처를 SQL로 조회할 수 있습니다 필드+필드명 사용처 조회 SELECT * FROM ( SELECT USER_TAB_COLS.TABLE_NAME, USER_TAB_COLS.COLUMN_NAME, USER_COL_COMMENTS.COMMENTS FROM USER_TABLES, USER_TAB_COLS, USER_COL_COMMENTS WHERE USER_TABLES.TABLE_NAME = USER_TAB_COLS.TABLE_NAME AND USER_TAB_COLS.TABLE_NAME = USER_.. 2024. 3. 13.
[오라클]테이블 구조 조회+제약조건 확인 PL/SQL과 같이 CTRL 키를 누르고 테이블명을 클릭하면 테이블 구조를 확인 할 수 있지만 SQL로도 제약조건 및 구조를 파악할 수 있습니다 테이블 제약조건 SELECT OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION, STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 테이블명 ※ P : 기본키, C: NOT NULL, U : UNIQUE, R : 참조키 테이블에 적용된 제약조건 조회 SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 테이블명 테이블 구조 조회 SELECT USER_TAB_COLS.TABLE_NAME, USER_TAB_COLS.TAB.. 2024. 3. 12.
[오라클]계정 LOCK(ORA-28000) + 비밀번호 만료일 변경 오라클 계정 비밀번호 실패 or 변경 만료일이 지날 경우 설정된 값에 따라 계정에 LOCK이 발생합니다 ORACLE 오류메시지 코드로는 ORA-28000 설정되어 있는 값의 변경과 LOCK 해제 SQL문에 대해 알아 보겠습니다 LOCKED 상태의 계정 조회 SELECT USERNAME, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE, CREATED FROM DBA_USERS WHERE ACCOUNT_STATUS NOT IN ( 'OPEN' ) 계정 LOCK 해제 ALTER USER 계정명 ACCOUNT UNLOCK 계정 비밀번호 변경 ALTER USER 계정명 IDENTIFIED BY 새로운 비밀번호 비밀번호 관련 프로파일(DBA_PROFILE) 설정 확인 SELECT * FROM.. 2024. 3. 12.
[오라클]테이블 Lock 조회+Session Kill 종종 Table에 Lock이 잡혀 있을 경우가 있습니다 특정 사용자가 COMMIT을 하지 않았을 수도, 프로그램 수행 중 어떤 문제로 인하여 Lock이 발생하는 경우도 있습니다 당장 해당 TABLE에 대한 LOCK을 풀지 않으면 업무에 지장이 있을 경우 KILL 해야합니다 DB LOCK 조회 SELECT SESSION_ID, OWNER, NAME FROM DBA_DML_LOCKS; SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME, A.MACHINE, A.TERMINAL, A.PROGRAM -- 어떤PC 및 프로그램에서 Lock이 발생되었는지 확인 가능 FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C WHERE A.SID = B.SID AND .. 2024. 3. 12.
[오라클]테이블+FUNCTION(오브젝트) 권한 부여 TABLE, FUNCTION, PROCEDURE 와 같은 오브젝트를 생성하고 난 이후에 권한 부여가 필요합니다 Connect, Resource, DBA와 같은 Role 권한부여와 마찬가지로 DCL(Data Control Language)인 Grant, Revode 구문을 활용하여 계정에 권한 부여를 할 수 있습니다 [오라클]계정 생성 및 권한 부여 오라클을 설치하고 나면 테이블스페이스 생성과 함께 최우선으로 진행하는 것이 계정 생성과 권한을 부여입니다 관리자 권한으로 접속하여 작업을 진행할 수 있습니다 명령프롬프트(cmd)에서 dolphwtsanta.tistory.com 테이블 DML 권한 부여 GRANT INSERT ON 스키마.테이블 TO 계정 GRANT UPDATE ON 스키마.테이블 TO 계정 G.. 2024. 3. 11.
[오라클]오브젝트 INVALID 조회+활성화 시스템을 운영하다보면 여러 스케쥴러나 JOB을 설정하는 경우가 많은데 종종 정상적인 수행이 되지 않았을때 오브젝트가 비활성화 된 상태로 존재 하는 경우가 있습니다. FUNCTION, 프로시져 그리고 PACKAGE 등과 같은 오브젝트에 대한 비활성화 대상을 조회하고 SQL을 활용한 활성화 구문을 생성하고자 합니다 INVALID 상태의 오브젝트 조회 SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS WHERE STATUS = 'INVALID' SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS -- ALL_OBJECTS WHERE STATUS = 'INVALID' 활성화(VALID) 구문 조회.. 2024. 3. 11.
[오라클]프로시져+트리거+FUNCTION 내 STRING 찾기 오브젝트 내에서 원하는 구문(STRING)이 사용되는 곳을 찾아야 할 경우가 발생합니다(영향도 분석과 같은..) 이럴경우 사용할 수 있는 방법으로 ORACLE 내의 USER_SOURCE 라는 View 테이블을 사용하면 도움이 됩니다 원하는 STRING을 사용하고 있는 오브젝트 찾기 SELECT TYPE, NAME, LINE, TEXT FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '문구' 예를들어, UPDATE 구문을 사용하는 오브젝트를 찾고싶으면 문구에 ‘%UPDATE%’ 와 같이 입력하면 됩니다 2024. 3. 11.
[오라클]DDL 스크립트 조회하기 생성한 FUNCTION / PACKAGE 등의 오브젝트의 DDL(Data Definition Language)를 조회하고 싶을 때 Pl/SQL Developer 와 같은 Tool에서 간단히 선택하여 조회 할 수 있지만, SQL로 조회하기가 가능합니다 DBMS_METADATA.GET_DDL함수 DBMS_METADATA는 DB오브젝트에 메타데이터 정보를 조회할 수 있는 기본 패키지로 GET_DDL 함수를 사용하면 DLL문을 조회할 수 있습니다 (Return Type : CLOB) SELECT DBMS_METADATA.GET_DDL("오브젝트타입","오브젝트명","오브젝트OWNER") FROM DUAL; 오브젝트 리스트 조회 SELECT OBJECT_TYPE, OBJECT_NAME, OWNER FROM DBA.. 2024. 3. 11.
반응형

티스토리툴바