SELECT TRIM(REGEXP_REPLACE(‘(010)-1!@1 1&%1-22**2 2’, ‘[^[:alnum:]+]’, ”))
FROM DUAL;
Category Archives: oracle query
V$SQL 이란?
1 2 3 4 5 6 7 8 9 10 |
select sql_id, child_number, sql_text, sql_fulltext, parsing_schema_name -----① , sharable_mem, persistent_mem ,runtime_mem -----② , loads, invalidations, parse_calls, executions, fetches, rows_processed -----③ , cpu_time, elapsed_time-----④ , buffer_gets, disk_reads, sorts-----⑤ , application_wait_time, concurrency_wait_time-----⑥ , cluster_wait_time , user_io_wait_time-----⑥ , first_load_time, last_active_time -----⑦ from v$sql where to_char(last_active_time,'yyyymmdd') = '20160908' |
오라클 V$SQL에 관련해서 잘 설명한 블로그가 있어서 링크해놓는다.
http://doraeul.tistory.com/80 – 도래울님 블로그
to_number(extractvalue~~!!)
select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(1) c from ‘||owner||’.’||table_name)),’/ROWSET/ROW/C’)) as count
from all_tables
where table_name like ‘%M40%’
;
테이블리스트를 가지고 테이블컬럼수를 가져오는 쿼리
COUNT(1) OVER()
SELECT
BB
,COUNT(1) AS COUNT_NORMAL
,COUNT(1) OVER(PARTITION BY CC) AS COUNT_OVER
,COUNT(CASE WHEN BB = CC THEN 1 ELSE NULL END) AS COUNT_CASE
FROM TABLE
GROUP BY BB
오라클 컬럼추가삭제수정, 행제거, 치환, 데이터입력, 다중테이블 다중행입력, pivoting, 데이터수정
[출처] 오라클 컬럼추가삭제수정, 행제거, 치환, 데이터입력, 다중테이블 다중행입력, pivoting, 데이터수정|작성자 josua21
http://blog.naver.com/PostView.nhn?blogId=kg00jh&logNo=220608266261
COMMENT ON TABLE tableuser.tablename IS ‘this is tablecomment’;
COMMENT ON COLUMN tableuser.tablename.columnname IS ‘this is columncomment’;
alter table em04 add (email varchar2(10)) ;
alter table em04 modify(email varchar2(50)) ;
alter table em04 drop column email ;
alter table em04 set unused (comm) ; — comm 사용하지 못하도록 설정
alter table em04 drop unused column ; –사용되지 않는 컬럼을 삭제하라
테이블의 컬럼/코멘트/컬럼사이즈/PK 뽑아내기
WITH ALL_TAB_PRIMARY_KEY AS (
SELECT — 테이블의 PK컬럼
CONS.OWNER, COLS.TABLE_NAME, COLS.COLUMN_NAME, COLS.POSITION, CONS.STATUS, ‘P’ AS PRIMARY_KEY
FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
WHERE CONS.CONSTRAINT_TYPE = ‘P’
AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
–AND CONS.TABLE_NAME = ‘(YOUR_TALBE_NAME)’
)
SELECT A.OWNER,A.TABLE_NAME,B.COMMENTS AS TABLE_DESC
,A.COLUMN_NAME,C.COMMENTS AS COLUMN_DESC,D.PRIMARY_KEY
,A.COLUMN_ID,A.DATA_TYPE,A.DATA_LENGTH,A.DATA_SCALE,A.NULLABLE
FROM ALL_TAB_COLUMNS A
,ALL_TAB_COMMENTS B
,ALL_COL_COMMENTS C
,ALL_TAB_PRIMARY_KEY D
WHERE A.OWNER = ‘(OWNER_NAME)’
AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME
AND A.OWNER = C.OWNER(+) AND A.TABLE_NAME = C.TABLE_NAME(+) AND A.COLUMN_NAME = C.COLUMN_NAME(+)
AND A.OWNER = D.OWNER(+) AND A.TABLE_NAME = D.TABLE_NAME(+) AND A.COLUMN_NAME = D.COLUMN_NAME(+)
AND A.TABLE_NAME IN ( ‘(YOUR_TALBE_NAME)’ )
LISTAGG AND DISTINCT
LISTAGG를 사용해서 JOHN,LEE,SMITH,LEE,PARK,LEE 형식으로 데이터를 만들수 있다.
중간에 LEE 값을 중복제거를 하려면 REGEXP_REPLACE(CONTENTS,'([^,]+)(,\1)+’, ‘\1’) 를 사용해서 중복제거를 뽑아내자.
SELECT
DEPTNO
,LISTAGG(EMPNO, ‘,’) WITHIN GROUP (ORDER BY ROWNUM) AS ORIGINAL_DATA
,REGEXP_REPLACE(
LISTAGG(EMPNO, ‘,’) WITHIN GROUP (ORDER BY ROWNUM)
,'([^,]+)(,\1)+’, ‘\1′) AS REMOVE_DUPLICATE_DATA
— REGEXP_REPLACE(CONTENTS,'([^,]+)(,\1)+’, ‘\1’) — REMOVE THE DUPLICATE
FROM EMP_DATA
GROUP BY DEPTNO
만약에 WM_CONCAT을 사용할 수 있다면 간단하게 아래와 같이 DISTINCT를 집어넣으면 된다.
SELECT
DEPTNO
,wm_concat(name) names
,wm_concat(DISTINCT name) REMOVE_DUPLICATE_names
FROM EMP_DATA
GROUP BY DEPTNO
RPAD와 TO_MULTI_BYTE
전문전송시 베트남어를 처리할 때 문제가 있음
,rpad(NVL(EMPLOYEE_NUMBER,’ ‘),10,’ ‘) AS EMPLOYEE
,SUBSTRB(RPAD(NVL(TO_MULTI_BYTE(EMPLOYEE_NAME),’ ‘),30 ,’ ‘) || RPAD(‘ ‘,30,’ ‘),1,30) AS EMPLOYEE_NAME
,SUBSTRB(RPAD(NVL(TO_MULTI_BYTE(EMPLOYEE_ENGLISH_NAME),’ ‘),150 ,’ ‘) || RPAD(‘ ‘,30,’ ‘),1,30) AS EMPLOYEE_ENGLISH_NAME
참고.
TO_MULTI_BYTE(), RPAD() 전각, 전문 채우기(http://dobioi.com/453)
PL/SQL 문자열 함수 정리 (http://deviant86.tistory.com/m/post/456)
WM_CONCAT 과 LISTAGG
oracle 10g
SELECT A2.TABLE_NAME, WM_CONCAT(A2.COLUMN_NAME) AS “COLUMN_NAME”
FROM ALL_CONSTRAINTS A1, ALL_CONS_COLUMNS A2
WHERE UPPER(A1.TABLE_NAME) LIKE ‘TB_M30%’
AND UPPER(A1.OWNER) = ‘MESUSER’
AND A1.TABLE_NAME = A2.TABLE_NAME
AND A1.OWNER = A2.OWNER
AND A1.CONSTRAINT_NAME = A2.CONSTRAINT_NAME
AND A1.CONSTRAINT_TYPE = ‘P’
GROUP BY A2.TABLE_NAME
———————————————————————-
oracle 11g
SELECT A2.TABLE_NAME, LISTAGG(A2.COLUMN_NAME, ‘,’) WITHIN GROUP (ORDER BY A2.POSITION) “COLUMN_NAME”
FROM ALL_CONSTRAINTS A1, ALL_CONS_COLUMNS A2
WHERE UPPER(A1.TABLE_NAME) LIKE ‘TB_M30%’
AND UPPER(A1.OWNER) = ‘MESUSER’
AND A1.TABLE_NAME = A2.TABLE_NAME
AND A1.OWNER = A2.OWNER
AND A1.CONSTRAINT_NAME = A2.CONSTRAINT_NAME
AND A1.CONSTRAINT_TYPE = ‘P’
GROUP BY A2.TABLE_NAME
ORA-01861: literal does not match format string
오렌지에서는 정상적으로 실행되는데 서버올려놓고 실행하면 발생하는 문제
java.sql.SQLException: ORA-01861: literal does not match format string
to_char(add_months(pln_horizon_str_dt,1),’YYYY’)
to_char, to_date를 사용할 때, 두번 째 인자값을 명시하지 않으면
DBMS의 NLS_DATE_FORMAT 값에 따라 위의 에러가 갑툭튀 한다고 한다.
그래서 쿼리를 아래와 같이 변경했더니 제대로 돌아간다.
to_char(add_months(to_date(pln_horizon_str_dt,’yyyymmdd’),1),’YYYY’)