turtle

SELECT B.TYPE, C.OBJECT_NAME, A.SID, A.SERIAL#    FROM  V$SESSION A, V$LOCK B, DBA_OBJECTS C    WHERE A.SID=B.SID     AND B.ID1=C.OBJECT_ID     AND B.TYPE='TM'     AND C.OBJECT_NAME='테이블명';         ALTER SYSTEM KILL SESSION '1348, 16425';

error :  SELECT 10/0 FROM dual SELECT 10/ NULLIF(0,0) FROM dual

nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2 2개 이상의 결과가 나와 에러

ORA-01775 : 동의어가 순환 고리 유형으로 정의되어 있습니다 ORA-01775 - Looping chain of synonyms   삭제한 테이블인데 조회 하면 ora-01775 에러가 나온다. SELECT * FROM DBA_SYNONYMS WHERE TABLE_NAME LIKE '%TH_USE_LOG%' 삭제한 테이블에 시노님이 있어 발생했다.

-------------------------------------------------------------------------------- SELECT LV, SUM(LV) OVER(PARTITION BY FLAG ORDER BY RN) SUMOVER   FROM (SELECT ROWNUM RN, '99' AS FLAG, LEVEL LV           FROM DUAL         CONNECT BY LEVEL < 100          ORDER BY 3)

SELECT     SUBSTR(a.tablespace_name,1,30) TABLESPACE,     ROUND(SUM(a.total1)       /1024/1024,1) "TotalMB",     ROUND(SUM(a.total1)       /1024/1024,1)-ROUND(SUM(a.sum1)/1024/1024,1) "UsedMB",     ROUND(SUM(a.sum1)         /1024/1024,1) "FreeMB",     ROUND((ROUND(SUM(a.total1)/1024/1024,1)-ROUND(SUM(a.sum1)/1024/1024,1))/ROUND(SUM(a.total1)/1024/1024,1)*100,2) "Used%"   FROM       (       SELECT tablespace_name,              0 total1,              SUM(bytes) sum1,              MAX(bytes) MAXB,              COUNT(bytes) cnt         FROM dba_free_space        GROUP BY tablespace_name       UNION       SELECT tablespace_name,  ...

DECLARE    JOBNO   NUMBER; BEGIN    DBMS_JOB.SUBMIT       (JOB  => JOBNO       ,WHAT => 'BEGIN PROCEDURE_NAME; END;'       ,NEXT_DATE => SYSDATE       ,INTERVAL  => 'SYSDATE+365');    COMMIT; END; DBMS_JOB.submit: 등록 DBMS_JOB.remove: 삭제 DBMS_JOB.change: 변경 DBMS_JOB.next_date: 다음 JOB 실행시간 DBMS_JOB.interval: 실행 Cycle DBMS_JOB.what: 프로시저 변경 DBMS_JOB.run: 수동 실행

Alternative quote (q) Operator Specify your own quotation mark delimiter- Choose any delimiter- Increase readability and usability-

NVL SELECT NVL(NULL,'want') from dual; NVL2 SELECT NVL2('not','one','two') from dual UNION ALL SELECT NVL2(NULL,'one','two') from dual;

VAR jobno NUMBER BEGIN   DBMS_JOB.SUBMIT(:jobno ,'PROC_BATCH;',SYSDATE,'SYSDATE+1/60/24'); END; / COMMIT; 1분마다 PROC_BATCH실행

set echo off set null @ set pages 0 set colsep ',' set trimspool on set lines 30000 set termout off spool ./20170222.csv select * from 테이블1 spool off

CREATE MATERIALIZED VIEW MV_EVT_LEDG BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND START WITH TO_DATE('16-02-2017 07:00:00', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1 AS SELECT BULD_NM,        a.ELEVATOR_NO,        ELVTR_ASIGN_NO,        ELVTR_STTS_CD,        C.COMPANY_NM,        C.sub_COMPANY_NM,        C.CONT_BEGIN_DATE,        C.CONT_END_DATE,            AREA_NO,        SIGUNGU_CD,        SEL_CHK_MM,        A.ELVTR_MGT_NO1, A.ELVTR_MGT_NO2, DECODE(A.ELVTR_STTS_CD,'01','정상','휴지') ELVTR_STTS_NM,B.BULD_MGT_NO1,B.BULD_MGT_NO2,B.ADDRESS1||B.ADDRESS2 as address  from tn_elvtr_ledg a, tn_buld_ledg b, (SELECT ELEVATOR_NO, A.KEEP_MNT_CONT_NO,                        CONT_BEGIN_DATE, ...

MERGE INTO TOT_INFOA USING TOT_INFO_TEMP B ON (A.info_no = B.info_no AND A.CHK_YM = B.CHK_YM AND A.CHK_DEG = B.CHK_DEG) WHEN MATCHED THEN  UPDATE SET A.CHK_ID = B.CHK_ID

Alter Table table_name Modify(column column_type DEFAULT NULL);

sql : SELECT replace(buld_nm,CHR(39),'') from dual; jstl '${fn:replace(result.buldNm,"'","")}'

- odbc 데이터 원본 실행 - driver do Microsoft Excel(*.xls)  선택 - 데이터 원본 이름 입력 - PL/SQL Developer>Tools>ODBC Importer 선택 - User/System DSN 에서 OBDC에서 만든 데이터원본 이름 선택

오라클 삭제 커밋 후 복구 SELECT * FROM 테이블명  AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '60' MINUTE) --60은 시간

create index buld_nm_idx1 on building_ledg(buld_nm)   indextype is ctxsys.context ; select * from tn_buld_ledg where contains(buld_nm, '%lott%') >0 ;

1. 파이썬 및 오라클 클라이언트 설치 2. cx_oracle 홈페이지 에서 다운로드 cx_oracle  - url :  http://cx-oracle.sourceforge.net/ 3. 설치  후  import cx_Oracle con = cx_Oracle.connect('pythonhol/welcome@127.0.0.1/orcl') #id, pass, serverip,oracleSID print con.version con.close() ---- print con.version에러 발생시 python 3.5부터 print (con.version) 으로 ( )를 해줘야한다.

alter table table_name drop column column_name <insert id="insep.insertkeepCont"  parameterClass="java.util.List"> <![CDATA[ INSERT ALL ]]> <dynamic> <iterate conjunction="  "> <![CDATA[ INTO table_name (KEEP_NO , MGT_NO1 , MGT_NO2 , e_NO , SEL_CHK_MM ) VALUES ]]> (#[].keepNo# ,#[].mgtNo1# ,#[].mgtNo2# ,#[].eNo# ,#[].selChkMm# ) </iterate> <![CDATA[ SELECT * FROM DUAL ]]> </dynamic> </insert>