본문 바로가기

전산Tip/Oracle

 (56)
[Oracle] 서브쿼리 값을 order by 기준으로 가져오기 WITH W_TEST AS ( SELECT 1 AS NO, 'A' AS VAL FROM DUAL UNION ALL SELECT 2 AS NO, 'C' AS VAL FROM DUAL UNION ALL SELECT 3 AS NO, 'B' AS VAL FROM DUAL ) SELECT (SELECT MAX(VAL) FROM W_TEST) AS VAL_1 , (SELECT MAX(VAL) KEEP (DENSE_RANK FIRST ORDER BY NO DESC) FROM W_TEST) AS VAL_2 FROM DUAL ; ----- 결과 ----- VAL_1 VAL_2 C B
[ORACLE] 통계정보 조회 /* 테이블 */ SELECT TABLE_NAME, NUM_ROWS, CHAIN_CNT, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN FROM ALL_TABLES A WHERE OWNER = 'XXX' ; /* 인덱스 */ SELECT TABLE_NAME, INDEX_NAME, STATUS, NUM_ROWS, LEAF_BLOCKS, BLEVEL FROM ALL_INDEXES WHERE OWNER = 'XXX' ;
[ORACLE] 스키마 이행 검증 WITH ORA_TABLE AS ( SELECT D.TABLE_TYPE , A.TABLE_NAME AS TBLID , D.COMMENTS AS TBLNM , CASE WHEN E.PARTITIONED = 'YES' THEN 'Y' END AS PARTITIONED , E.STATUS AS STATUS , A.COLUMN_ID AS COLNO , A.COLUMN_NAME AS COLID , TRIM(B.COMMENTS) AS COLNM , C.COLUMN_POSITION AS PK , REPLACE(A.DATA_TYPE,'TIMESTAMP(6)','TIMESTAMP') AS "TYPE" , DECODE(A.DATA_TYPE , ..
테이블 이관 후 건수 및 값 검증 --ORACLE SELECT 'TEST_TABLE' AS TBLID , COUNT(*) AS CNT , 0 + NVL(SUM(LENGTH(TRIM(COL_CHR))),0) + NVL(ROUND(SUM(COL_NUM)),0) + NVL(ROUND(SUM(TO_DATE(TO_CHAR(COL_DAT,'YYYYMMDD'),'YYYYMMDD') - TO_DATE('20000101','YYYYMMDD'))),0) + NVL(SUM(LENGTH(CAST(DBMS_LOB.SUBSTR(COL_CLOB,2000,1) AS NVARCHAR2(2000)))),0) AS CHK_SUM FROM TEST_TABLE ; * 오라클내에서 cha..
ORACLE TABLE PRIVILEGE ----- ORACLE OBJECTS PRIVILEGE ----- WITH W_PRIVS AS ( SELECT GRANTEE , TABLE_NAME , NVL(MAX(CASE WHEN PRIVILEGE = 'SELECT' THEN 'S' END),'_') ||NVL(MAX(CASE WHEN PRIVILEGE = 'INSERT' THEN 'I' END),'_') ||NVL(MAX(CASE WHEN PRIVILEGE = 'UPDATE' THEN 'U' END),'_') ||NVL(MAX(CASE WHEN PRIVILEGE = 'DELETE&..
[ORACLE] 문자열을 구분자 기준으로 ROW로 변환하는 테이블 함수 CREATE OR REPLACE TYPE EUS3_TYP_TO_ROW AS OBJECT ( IDX NUMBER , VAL VARCHAR2(4000) ); CREATE OR REPLACE TYPE EUS3_TYP_TO_ROW_TBL AS TABLE OF EUS3_TYP_TO_ROW ; CREATE OR REPLACE FUNCTION EUS3_FUNC_TO_ROW(IN_STR IN CLOB, IN_DELI IN VARCHAR2 DEFAULT ',') RETURN EUS3_TYP_TO_ROW_TBL PIPELINED AS V_DELI VARCHAR2(10) := IN_DELI; V_DELI_LEN INT := LENGTH(V_DELI); V_STR CLOB := IN_STR||V_DELI; ..
[빈도추출] 오라클로 범주형, 연속형 빈도 추출 view 만들기 1) 아래 view를 생성 2) 해당 view where 조건의 TBLID 컬럼에 테이블명을 입력 후 조회3) SQL_FREQ_ORACLE 컬럼에 빈도추출하는 sql문장에 생성됨, 복사 후 실행 CREATE OR REPLACE VIEW VW_SQL_FREQ AS /* * PGM ID : VW_SQL_FREQ * TITLE : 컬럼값의 범주형, 연속형 빈도추출 SQL문장 생성 * AUTHOR : 임상우 grapro.lim@gmail.com * DATE : 2013.02.21 * VERSION : 1.0 * CONTENTS : * RUNCYCLE : * RUNTIME : * MODIFIED : */SELECT D.TABLE_TYPE , A.TNAME AS TBLID , D.COMMENTS AS TBLNM ,..
[ORACLE] 테이블 레이아웃 추출 수정.... 기존 소스 수정바람........... WITH V_INDEX AS ( SELECT A.PNO , B.TABLE_NAME , B.COLUMN_NAME , B.COLUMN_POSITION FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY A.TABLE_NAME ORDER BY A.INDEX_NAME) AS PNO , A.INDEX_NAME , A.TABLE_NAME FROM USER_INDEXES A WHERE 1=1 AND A.UNIQUENESS = 'NONUNIQUE' ) A , USER_IND_COLUMNS B WHERE A.INDEX_NAME = B.INDEX_NAME ) SELECT A.TNAME AS TBLID , A.COLNO AS COLNO , A.CNAME ..
반응형

티스토리툴바