Database(23)
-
날짜의 일자 및 시간 차이 구하기
SELECT TRUNC(EndDate - StartDate) as "일", TRUNC(((EndDate - StartDate) - TRUNC(EndDate - StartDate)) * 24) as "시간", FLOOR(((((EndDate - StartDate) -TRUNC(EndDate - StartDate)) * 24) - TRUNC(((EndDate - StartDate)-TRUNC(EndDate - StartDate)) * 24)) * 60) as "분" FROM ( SELECT TO_DATE('2013-08-07','yyyy-mm-dd') AS STARTDATE, -- 시작일 TO_DATE('2013-12-30','yyyy-mm-dd') AS ENDDATE -- 종료일 FROM DUAL );
2014.02.17 -
[Oracle] 행걸린 사용자 찾기
select b.machine "hostname", b.username "username", b.osuser "OS User", b.program "Program", a.tablespace_name "TableSpace", row_wait_file# "File Number", row_wait_block# "Block Number", c.owner, c.segment_name, c.segment_type from dba_data_files a, v$session b, dba_extents c where b.row_wait_file# = a.file_id and c.file_id = row_wait_file# and row_wait_block# between c.block_id and c.block_id +..
2013.10.08 -
[ORACLE] 모든 테이블 삭제 및 시퀀스 삭제
테이블 삭제 Begin for c in (select table_name from user_tables) loop execute immediate 'drop table '||c.table_name||' cascade constraints'; end loop; End; 시퀀스 삭제 Begin for c in (SELECT * FROM all_sequences WHERE SEQUENCE_OWNER='DBUSER') loop execute immediate 'drop SEQUENCE '||c.sequence_name; end loop; End;
2013.10.08 -
DATABASE별 결과 값 RANDOM 으로 나오게 하기
ORACLE SELECT * FROM TABLE_NAME SAMPLE (3) SELECT * FROM ( SELECT * FROM TABLE_NAME ORDER BY DBMS_RANDOM.VALUE ) WHERE ROWNUM < 3 MSSQL SELECT TOP 3 * FROM TABLE_NAME ORDER BY NEWID() MYSQL SELECT * FROM TABLE_NAME ORDER BY RAND() LIMIT 0,3
2013.10.08 -
[ORACLE] 프로시져 리턴값 확인
declare ret VARCHAR2(30); BEGIN GET_DOCUMENT_CD_P(ret, 'TBL_MEMO'); dbms_output.put_line('ret = ' || ret); END;
2013.10.08