最近做資料批量加載的時候,是通過pl/sql嵌在shell腳本裡執行的。
腳本運作後生成的日志類似如下的格式
Get Dump file for APP_TMP.TESTRESS_NAME_LINK...
Elapsed: 00:00:00.64
.
DB details is accessible from source schema ...
DB details is accessible from target schema ...
Directory ext_datapump_dir has read,write permission ,proceed...
SYNONYM TESTEEMENT exists in CONNECT account,proceed...
Get Dump file for APP_TMP.TESTEEMENT...
Elapsed: 00:00:00.49
SYNONYM TESTEEMENT_RESOURCE exists in CONNECT account,proceed...
因為表比較多,是以日志看起來不是很清晰,想生成類似報表的格式來檢視每個表耗費了多長時間,就一目了然了。
期望的結果類似下面的樣子。
#########################################################################
Table_name Elapsed time
APP_TMP.TESTRESS_DATA... Elapsed: 00:00:01.13
APP_TMP.TESTRESS_NAME_LINK... Elapsed: 00:00:00.64
APP_TMP.TESTEEMENT... Elapsed: 00:00:00.49
APP_TMP.TESTEEMENT_RESOURCE... Elapsed: 00:00:00.74
APP_TMP.TEST_RES_HISTORY... Elapsed: 00:00:00.82
APP_TMP.TEST_ACCOUNT... Elapsed: 00:00:01.03
APP_TMP.TEST_ADDRESS_NAME... Elapsed: 00:00:00.78
APP_TMP.TEST_AGED_TRIAL_BALANCE... Elapsed: 00:00:01.16
APP_TMP.TEST_BILLING_ARRANGEMENT... Elapsed: 00:00:00.61
APP_TMP.TEST_CHARGE_GROUP... Elapsed: 00:00:01.66
APP_TMP.TEST_CHARGES... Elapsed: 00:00:06.73
APP_TMP.TEST_CREDIT_DEBIT_LINK... Elapsed: 00:00:01.67
APP_TMP.TEST_CUSTOMER_CREDIT... Elapsed: 00:00:00.40
APP_TMP.TEST_DEPOSIT_REQUEST... Elapsed: 00:00:00.10
APP_TMP.TEST_DIRECT_DEBIT_REQUEST... Elapsed: 00:00:00.67
APP_TMP.TEST_INVOICE... Elapsed: 00:00:01.98
APP_TMP.TEST_PAY_CHANNEL... Elapsed: 00:00:00.53
APP_TMP.TEST_PAYMENT... Elapsed: 00:00:01.28
APP_TMP.TEST_PAYMENT_ACTIVITY... Elapsed: 00:00:00.19
首先是根據關鍵字找到對應的行,下一行就是耗費的時間。想通過指令簡單的實作。最後grep幫了大忙。
grep -A1 --color=auto "Get Dump file for " extract.log
輸出類似下面的樣子。
--
Get Dump file for APP_TMP.TESTTOMER...
Elapsed: 00:00:00.91
Get Dump file for APP_TMP.TESTNT_DISTRIBUTE...
Elapsed: 00:00:00.84
Get Dump file for APP_TMP.TEST_MEMO...
Elapsed: 00:00:22.27
Get Dump file for APP_TMP.TESTE_DATA...
Elapsed: 00:00:01.55
達到了初步效果。就需要把備援的資訊去除“Get Dump file for ",然後能讓對應的執行時間橫向顯示。
可以使用sed來做。
sed 's/Get Dump file for //' |sed 'N;N;s/\n/ /g'
APP_TMP.TESTRESS_DATA... Elapsed: 00:00:01.13 --
APP_TMP.TESTRESS_NAME_LINK... Elapsed: 00:00:00.64 --
APP_TMP.TESTEEMENT... Elapsed: 00:00:00.49 --
APP_TMP.TESTEEMENT_RESOURCE... Elapsed: 00:00:00.74 --
效果基本達到了,但是顯示比較粗糙,不規整,這個時候awk能幫上大忙了。
使用如下的腳本來格式化輸出
awk '
BEGIN{
print "#########################################################################"
printf "%-50s %8s %11s \n", "Table_name","Elapsed","time"
}
{printf "%-50s %8s %11s \n", $1,$2,$3,$4
}'
這樣環環相扣,就可以輸出基本完整的報告了。完整指令如下:
grep -A1 --color=auto "Get Dump file for " extract.log |sed 's/Get Dump file for //' |sed 'N;N;s/\n/ /g' |awk '
輸出: