이슈를 공유하자

● 테이블 헤더 내용

● 쉬트 새로 생성

Function worksheet_create(sheet_name)

  worksheet_exists = False
  
  For Each ws In ActiveWorkbook.Worksheets
     If ws.Name = sheet_name Then worksheet_exits = True
  Next ws
  
  ' '이미 존재하면 삭제하고 다시 만듬
  If worksheet_exists Then 
    Application.DisplayAlerts = False 
    Worksheets(sheet_name).Delete 
    Worksheets.Add after:=Worksheets(6) 
    ActiveSheet.Name = sheet_name 
  Else
    Worksheets.Add after:=Worksheets(6) 
    ActiveSheet.Name = sheet_name 

End Function

● 표테두리 그리고 자동 행간격 맞춤

Public Function set_layout(sheet_name As String, start_row As Integer)

  Set xlsh = Worksheets(sheet_name)
  
  For i = start_row To xlsh.UsedRange.Rows.Count 
     For j = 0 To xlsh.UsedRange.Columns.Count - 1
         If i = start_rows Then
            xlsh.Cells(i, j + 1).Interior.ColorIndex = 6
            xlsh.Cells(i, j + 1).HorizontalAlignment = xlCenterAcrossSelection
            xlsh.Cells(i, j + 1).VerticalAlignment = xlCenter
            
         Else
            xlsh.Cells(i, j + 1).Interior.ColorIndex = 0
         End If
         
         xlsh.Cells(i, j + 1).Borders.Color = vbBlack
    Next j
  Next i

  xlsh.Columns.AutoFit
  
End Function

● Ceil 함수

Function Ceil&(n#)

  Ceil = -Int(-n)
  
  If n < 0 Then Ceil = Fix(n)
   
End Function

● Sheet 존재 여부 확인 함수 

Public Function IsExistSheet(sheetName As String) As Boolean
  
  Dim ws As Worksheet
  
  For Each ws In Sheets
  
     If ws.Name = sheetName Then
        IsExistSheet = True
        Exit Function
        
     End If
     
     Next
     IsExistSheet = False      

End Function

● 테이블 DDL 생성

Function crt_tbl_sql(sheet_name As String)
  
  Dim TB_NM As String
  Dim SH_NM As String
  Dim COL_NM As String
  Dim D_TYP As String
  Dim D_LEN As Integer
  Dim Def As String
  Dim Not_null As String
  Dim CR_TB_SQL As String
      
  CR_TB_SQL = ""
 
  Set xlsh = Worksheets(sheet_name)

  xlsh.Range("L:O").Clear
  
  For i = 2 To xlsh.UsedRange.Rows.Count
  
'      ' 셀에서 속성 값 가져오기
       SH_NM = xlsh.Cells(i, 1).Value
       TB_NM = xlsh.Cells(i, 3).Value
       COL_NM = xlsh.Cells(i, 6).Value
       D_TYP = xlsh.Cells(i, 7).Value
       D_LEN = xlsh.Cells(i, 8).Value
       
'       ' 널 속성 가져오기
       If xlsh.Cells(i, 9).Value = "NULL" Then
         Not_null = ""
       Else
         Not_null = xlsh.Cells(i, 9).Value
       End If
         
'      ' 테이블 DDL 생성
       If xlsh.Cells(i, 4).Value = 1 Then
           
           If xlsh.Cells(i, 7).Value <> "DATE" Then
           
'            ' Fist Colum
             
             CR_TB_SQL = "CREATE TABLE " & SH_NM & "." & TB_NM & " (" & Chr(10) & " " & COL_NM & " " & D_TYP & "(" & D_LEN & ") " & Not_null & ","

             ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
             ddl_fm.TextBox1.SelText = CR_TB_SQL
              
          Else
             
             CR_TB_SQL = "CREATE TABLE " & SH_NM & "." & TB_NM & " ( " & Chr(10) & " " & COL_NM & " " & D_TYP & " " & Not_null & ","
             ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
             ddl_fm.TextBox1.SelText = CR_TB_SQL
             
           End If
       Else
       
           If xlsh.Cells(i, 3).Value = xlsh.Cells(i + 1, 3).Value Then
           
              If xlsh.Cells(i, 7).Value <> "DATE" Then
                
                CR_TB_SQL = "" & Chr(10) & " " & COL_NM & " " & D_TYP & "(" & D_LEN & ") " & Not_null & ","
            
                ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
                ddl_fm.TextBox1.SelText = CR_TB_SQL
                
             Else
             
                CR_TB_SQL = "" & Chr(10) & " " & COL_NM & " " & D_TYP & " " & Not_null & ","
                
                ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
                ddl_fm.TextBox1.SelText = CR_TB_SQL
                
             End If

           Else
              
              CR_TB_SQL = "" & Chr(10) & " " & COL_NM & " " & D_TYP & " " & Not_null & " )" & Chr(10) & "TABLESPACE CJENM_DATA_TS;" & Chr(10) & "" & Chr(10) & ""
              
              ddl_fm.TextBox1.SelStart = Len(ddl_fm.TextBox1.Text)
              ddl_fm.TextBox1.SelText = CR_TB_SQL

           End If
           
       End If
       
  Next i

End Function

● 테이블/컬럼 Comment 쿼리 생성

Function crt_comment_sql(sheet_name As String)
  
  Dim TB_NM As String
  Dim SH_NM As String
  Dim COL_NM As String
  Dim TB_COMT As String
  Dim COL_COMT As String
  Dim COMT_SQL As String
  
  COMT_SQL = ""

  Set xlsh = Worksheets(sheet_name)

  For i = 2 To xlsh.UsedRange.Rows.Count
  
        SH_NM = xlsh.Cells(i, 1).Value
        TB_NM = xlsh.Cells(i, 3).Value
        COL_NM = xlsh.Cells(i, 6).Value
        TAB_COMT = xlsh.Cells(i, 2).Value
        C_COMT = xlsh.Cells(i, 5).Value

       If xlsh.Cells(i, 4).Value = 1 Then
           
'            ' Fist Colum
            COMT_SQL = "COMMENT ON TABLE " & SH_NM & "." & TB_NM & " IS '" & TAB_COMT & "' ;" & Chr(10) & "COMMENT ON COLUMN " & SH_NM & "." & TB_NM & "." & COL_NM & " IS '" & C_COMT & "';" & Chr(10)
            ddl_fm.TextBox4.SelStart = Len(ddl_fm.TextBox4.Text)
            ddl_fm.TextBox4.SelText = COMT_SQL

       Else
          
            COMT_SQL = "COMMENT ON COLUMN " & SH_NM & "." & TB_NM & "." & COL_NM & " IS '" & C_COMT & "';" & Chr(10)
            ddl_fm.TextBox4.SelStart = Len(ddl_fm.TextBox4.Text)
            ddl_fm.TextBox4.SelText = COMT_SQL
              
       End If
       
  Next i
  
End Function

● PK  생성 쿼리

Function crt_pk_sql(sheet_name As String)
  
  Dim TB_NM As String
  Dim SH_NM As String
  Dim COL_NM As String
  Dim P_SEQ As Integer
  Dim C_ID As Integer
  Dim UK_SQL As String
  Dim PK_SQL As String

  Set xlsh = Worksheets(sheet_name)
  
  UK_SQL = " "
  PK_SQL = " "
   
  For i = 2 To xlsh.UsedRange.Rows.Count
  
        SH_NM = xlsh.Cells(i, 1).Value
        TB_NM = xlsh.Cells(i, 3).Value
        C_ID = xlsh.Cells(i, 4).Value
        COL_NM = xlsh.Cells(i, 6).Value
        P_SEQ = xlsh.Cells(i, 10).Value

       If C_ID = 1 Then
           
            If P_SEQ = 1 Then
             
              UK_SQL = "CREATE UNIQUE INDEX PK_" & TB_NM & " ON " & SH_NM & "." & TB_NM & "(" & COL_NM & ""
              PK_SQL = "ALTER TABLE " & SH_NM & "." & TB_NM & " ADD CONSTRAINT PK_" & TB_NM & " PRIMARY KEY (" & COL_NM & ""

            End If
            
        Else
        
            If P_SEQ > 0 Then
            
               UK_SQL = UK_SQL + ", " & COL_NM & ""
               PK_SQL = PK_SQL + ", " & COL_NM & ""
            
            Else
             
              If xlsh.Cells(i - 1, 10).Value > 0 Then
              
                UK_SQL = UK_SQL + ") TABLESPACE CJENM_INDEX_TS;"
                PK_SQL = PK_SQL + ") USING INDEX;" & Chr(10) & Chr(10)
                
                ddl_fm.TextBox2.SelStart = Len(ddl_fm.TextBox2.Text)
                ddl_fm.TextBox2.SelText = UK_SQL + Chr(10) + PK_SQL

              Else
              
                UK_SQL = ""
                PK_SQL = ""
                
              End If
               
            End If
   
       End If
       
  Next i
  
End Function

● Snonym 생성 쿼리

Function crt_syn_sql(sheet_name As String)
  
  Dim TB_NM As String
  Dim SH_NM As String
  Dim SYN_GT_SQL As String
 
  SYN_GT_SQL = ""
  
  Set xlsh = Worksheets(sheet_name)

  For i = 2 To xlsh.UsedRange.Rows.Count
  
        SH_NM = xlsh.Cells(i, 1).Value
        TB_NM = xlsh.Cells(i, 3).Value

       If xlsh.Cells(i, 4).Value = 1 Then
           
'           ' Fist Colum
            SYN_GT_SQL = "CREATE OR REPLACE PUBLIC SYNONYM " & TB_NM & " FOR " & SH_NM & "." & TB_NM & " ;" & Chr(10)
            ddl_fm.TextBox3.SelStart = Len(ddl_fm.TextBox3.Text)
            ddl_fm.TextBox3.SelText = SYN_GT_SQL
            
       ElseIf xlsh.Cells(i, 4).Value = 2 Then
           
           SYN_GT_SQL = "GRANT SELECT,UPDATE,INSERT,DELETE ON " & SH_NM & "." & TB_NM & " TO ENM_APP ;" & Chr(10) & "GRANT SELECT ON " & SH_NM & "." & TB_NM & " TO RL_ZFI_SEL ;" & Chr(10)
           ddl_fm.TextBox3.SelStart = Len(ddl_fhttp://m.TextBox3.Text)
           ddl_fm.TextBox3.SelText = SYN_GT_SQL

       Else
          
           SYN_GT_SQL = ""
              
       End If
       
  Next i
  
End Function

● 전체 함수 실행 / 팝업 폼실행

Sub crt_table()

  crt_tbl_sql ("Table_Define")
  crt_comment_sql ("Table_Define")
  crt_syn_sql ("Table_Define")
  crt_pk_sql ("Table_Define")
  ddl_fm.Show

End Sub

Sub show_form()

ddl_fm.Top = 60
ddl_fm.Left = 200
ddl_fm.Show

End Sub

• File & Directory Tree

find . -print | sed -e 's;[^/]*/;|--;g;s;--|; |;g'
find ./201902 -print | sed -e 's;[^/]*/;|--;g;s;--|; |;g'

• Diretory Tree

find . -type d -print | sed -e 's;[^/]*/;|--;g;s;--|; |;g'
ls -R |grep "^[.]/" | sed -e "s/:$//" -e "s/[^/]*[/]/--/g" -e "s/^/  |/"

• 깨지는 파일 / 특수문자 파일명 삭제 하기

ls -li <- 파일 inodenumber 확인
find ./ -inum [inodenumber] -exec rm -i {} \;

• directory 별 사이즈 확인

find ./ -type d -exec du -sk {} \;

• 사이즈 순 정렬

find ./ -type d -exec du -sk {} \;| sort -rnk1
find ./ -type f -exec du -sk {} \;| sort -rnk1

• 1일 이하 전 생성된(1일전부터 현재까지)

find ./ -type f -name '*.*' -ctime -1 -exec ls -l {} \;

• 1일 이하 전 수정된(1일전부터 현재까지)

find ./ -type f -name '*.*' -mtime -1 -exec ls -l {} \;
find ./ -type f -name '*.*' -mtime 0 -exec ls -l {} \;

• 1일 초과 2일 이하된 파일 만

find ./ -type f -name "*.*" -mtime 1 -exec ls -l {} \;

• 1일 초과 된(1일 이전 까지)

find ./ -type f -name "*.tr*" -mtime +1 -exec rm -rf {} \;

• 해당 파일보다 최근

find ./ -type f -newer TESTDB_cjq0_13125.trc -exec ls -l {} \;

• windows program에 연결된 DLL 찾기

C:\Windows\system32>tasklist /FI "PID eq 6288" /M

이미지 이름                    PID 모듈
========================= ======== ============================================
explorer.exe                  6288 ntdll.dll, KERNEL32.DLL, KERNELBASE.dll,
                                   msvcrt.dll, combase.dll, ucrtbase.dll,
                                   RPCRT4.dll, bcryptPrimitives.dll,
                                   OLEAUT32.dll, msvcp_win.dll, shcore.dll,
                                   advapi32.dll, sechost.dll, powrprof.dll,
                                   user32.dll, win32u.dll, GDI32.dll,
                                   gdi32full.dll, shlwapi.dll,
                                   windows.storage.dll, kernel.appcore.dll,

• SQL 문 찾기 (SELECT문)

-- Shell 스크립트
##
# Used : line_search.sh [file_name]
# SELECT로 시작하고 "\"로 끝나는 쿼리만 추출.
##
#파일 라인수
file_end=`wc -l $1|awk '{print $1}'`
#검색시작 라인
line_start=1  

#범위 검색 반복 Loop
while true
do
#추출 시작점 가져오기
head_start=`cat -n $1 | sed -n ${line_start},${file_end}p | grep SELECT | head -1 | awk '{print $1}'`
#추출 끝점 가져오기
head_end=`cat -n $1 | sed -n ${head_start},${file_end}p | grep \; | head -1 | awk '{print $1}'`

echo $head_start
echo $head_end

sed -n $head_start,${head_end}p $1
# 검색시작 라인 +1 (다음 검색시작점)
# line_start=`expr ${head_end} + 1`
line_start=$((head_end+1))

#파일끝과 검색시작이 같으면 종료
if [ ${file_end} -eq ${line_start} ]
then
        echo ${file_end}
        echo ${line_start}
        exit
fi
done

• 업그레이드 한 내용

#!/bin/sh

file_end=`wc -l $1|awk '{print $1}'`
line_start=1  
head_start=${line_start}
head_end=${file_end}

get_line ()
{
        set -A arr_st
        set -A arr_en
        arr_st[0]=1

        i=1
        j=0
        for a in `grep -n -E "SELECT|UPDATE|INSERT" $1|cut -d ':' -f1`
        do
                arr_st[i]=`grep -n -E "SELECT|UPDATE|INSERT" $1|cut -d ':' -f1`
                arr_en[j]=`grep -n -E "param|dual|[)];" $1|cut -d ':' -f1`
                #i=`expr ${i} + 1`
                i=$((i+1))
                #j=`expr ${j} + 1`
                j=$((j+1))
        done
}

while true
do

    head_start=`cat -n $1 | sed -n ${line_start},${file_end}p | grep -i -E "SELECT|UPDATE|INSERT|DELETE" |grep -v EXECUTE | head -1 | cut -f1 | tr -d ' '`

    if [[ "${head_start}" = "" ]];
    then
            echo "2: head_start empty !"
            echo ${head_start}
            exit
    else
            #echo "->|"${head_start}"|"
            head_end=`cat -n $1 | sed -n ${head_start},${file_end}p | grep -i -E "order|group|where|[)]|dual|[)];"| head -1 | awk '{print $1}' | tr -d ' '`
            if [[ "${head_start}" = "${head_end}" ]];
            then
              #head_end=`expr ${head_start} + 0`
              head_end=$((head_start+0))
            else
              #head_end=`expr ${head_end}`
              head_end=$((head_start))
            fi
    fi

    sed -n $head_start,${head_end}p $1 | sed 's/^ *//g'

     if [[ "${head_start}" = "${head_end}" ]];
     then
       line_start=$((head_end+1))
     else
       line_start=$((head_end+2))
     fi

    if [ ${file_end} -eq ${line_start} ]
    then
            echo ${file_end}
            echo ${line_start}
            exit
    fi

done

#!/bin/sh

print_usage() {
echo "Usage : $0 [interval][count]"
echo "interval : Number"
echo "count : Number"
exit 0;
}

result_tmp_file="/tmp/vmstat_result.$$"
result_file="vmstat_result.txt"
result_directory="./vmlog/"

if [ "$1" = "-help" ]
then
print_usage
fi

# interval check
if [ -z "$1" ]
then
interval=30
else
interval $1

if [ -n "`echo $interval | tr -d [0-9]`" ]
then
print_usage
else
interval=$1
fi
fi

if [ -n "$2" ]
then
if [ -n "`echo $2 | tr -d [0-9]`" ]
then
print_usage
else
count=$2
fi
fi

echo "####################################################################################################"
echo "# $0 Script"
echo "# Interval\t: $interval"
echo "# Count\t\t: $count"
echo "####################################################################################################"
# run...
echo "Running Start...."

cnt=0
while true
do
if [ ! -z "$count" ]
then
if [ $cnt -ge $count ]
then
break;
fi
fi

curr_date=`date +%Y/%m/%d`
filename_date=`date +%Y%m%d`

if [ -z "$old_date" -o "$old_date" != "$curr_date" ]
then
old_date=$curr_date
if [ !-d"$result_directory" ]
then
mkdir -p "$result_directory"
fi

echo "Date\tProc:r\tMEM:swap\tMEM:free\tPage:sr\tCPU:id\n">${result_directory}${filename_date}_${result_file}
fi

curr_time=` date +%H:%M:%S`
vmstat $interval 2 > $result_tmp_file



/* 각 OS의 결과에 맞게 수정이 필요함 */
cat $result_tmp_file | sed 1,3d | awk '($1+0==$1) {printf("%s%s\t%s\t%s\t%s\t%s\t%s\t%s\t%s\n","'"$curr_date"'","'"$curr_time"'", $1,$4,$5,$12,$16,$17,$18)}' >> ${result_directory}${filename_date}_${result_file}

echo "[$0] $curr_date$curr_time"

cnt=`expr $cnt + 1`
done

echo "Running End..."

-- 포트포워딩 설정

C:\Users\Administrator>netsh
netsh>interface
netsh interface>portproxy
netsh interface portproxy>add v4tov4 listenport=80 listenaddress=0.0.0.0 connectport=8080 connectaddress=10.10.10.1

-- 설정업로드

netsh interface portproxy>commit

--설정 확인

netsh interface portproxy>show all
ipv4 수신 대기:             ipv4에 연결:

주소            포트        주소            포트
--------------- ----------  --------------- ----------
0.0.0.0         80          10.10.10.1  8080

-- 설정 삭제

netsh interface portproxy>reset

-- 종료

netsh interface portproxy>exit

리눅스에서 백스페이스가 안먹을 경우 .bash_profile 수정 이외에 해결방법 공유

프로젝트 수행 시 .bash_profile이나 설정 같은걸 임의로 함부로 바꿀 수 없기 때문에 이런식으로 해줘야 한다

-- erase 이후 이상하게 입력되는 특수기호 쓰기
stty erase ^?

물론 위에 stty는 접속할 때마다 다시 입력해줘야 한다ㅜㅜ

이게 귀찮다면 shift + backspace 같이 눌러주자!