본문 바로가기

전체 글

(127)
[xcopy] 작업폴더 일자별 백업하기 xcopy "c:\_project" "d:\_backup\_project\%date%\" /c /d /s /r /y
[vbs] 파일내에 문자열 치환하기 // replace용 vbs 파일 생성 (파일명 : replace.vbs) Const ForReading = 1 Const ForWriting = 2 strFilenName = Wscript.Arguments(0) Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile(strFilenName, ForReading) strText = objFile.ReadAll objFile.Close strNewText = strText '변경할 문자열 등록 strNewText = Replace(strNewText, "AAAA" , "BBBB" ) strNewText = Replace(strNewText, "CCCC..
[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' ;
[MSSQL] 테이블 레이아웃 조회 SELECT A.NAME AS TABLEID , (SELECT VALUE FROM SYS.EXTENDED_PROPERTIES WITH(NOLOCK) WHERE MAJOR_ID = A.ID AND MINOR_ID = 0) AS TABLENAME , B.colid AS COLNO , B.name AS COLID , (SELECT VALUE FROM SYS.EXTENDED_PROPERTIES WITH(NOLOCK) WHERE MAJOR_ID = A.ID AND MINOR_ID = B.colid) AS COLNAME , D.KEYNO AS PKORDER , C.name AS DATATYPE , CASE WHEN C.xprec = 0 THEN RTRIM(CONVERT(CHAR,B.LENGTH)) ELSE RTRI..
[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 , &#03..
테이블 이관 후 건수 및 값 검증 --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&..

반응형