[20170617]vim中調用sqlplus.txt

--//以前寫過一篇emacs下調用sqlplus的文章,一直想學emacs,受限制自己掌握vim,對學習它沒有興趣,原鏈接如下:
--//http://blog.itpub.net/267265/viewspace-1309032/

--//實際上vim也有插件連接數據庫,我覺得不好用,一直沒這樣用.

--//今天在整理vim相關設置時,發現我自己以前也定義一些方法,自己也拿出來分享:

noremap? <Leader>q1 Yp!!sqlplus -s scott/btbtms@test01p<CR>
noremap? <Leader>q2 Yp!!sqlplus -s sys/btbtms@test01p as sysdba<CR>
vnoremap? ;q1?????? "ay<ESC>gv!sqlplus -s scott/btbtms@test01p<CR>
vnoremap? ;q2?????? "by<ESC>gv!sqlplus -s sys/btbtms@test01p as sysdba<CR>

--//將以上信息放在vim配置文件中,注意打入命令一定要正確,后面要有分號.當然錯誤不執行就是了.
--//根據自己需要修改數據庫連接串以及用戶,口令信息.當然這樣存在安全問題.^_^.

select * from dept;
--//移動到哪一行,打入\q1

??? DEPTNO DNAME????????? LOC
---------- -------------- -------------
??????? 10 ACCOUNTING???? NEW YORK
??????? 20 RESEARCH?????? DALLAS
??????? 30 SALES????????? CHICAGO
??????? 40 OPERATIONS???? DALLAS

@ checkpoint
--//打入q2

REDO:

?????????????????????????????????????????????????????? 檢查點隊列
? 檢查點隊列?????????????????????????????????????????? on disk rba??????????????????????????????????????? 檢查點隊列
??? 臟塊數量?????????????????????????????????????????? 時間戳????????????? 當前時間?????????????????????? on disk rba scn??? 檢查點心跳
?????? CPDRT low_rba????????????? on_disk_rba????????? CPODT?????????????? SYSDATE????????????? DIFF_DATE CPODS?????????????????? CPHBT? CURRENT_SCN???? DIFF_SCN???????? INDX
------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------
????????? 32 1470.13787.0???????? 1470.13971.0???????? 2017-06-17 22:08:11 2017-06-17 22:08:28????? 17.00 25428734??????????? 946961434???? 25428740??????????? 6??????????? 0

REDO ( Hexadecimal ):

?????????????????????????????????????????????????????? 檢查點隊列
? 檢查點隊列?????????????????????????????????????????? on disk rba??????????????????????????????????????? 檢查點隊列
??? 臟塊數量?????????????????????????????????????????? 時間戳????????????? 當前時間?????????????????????? on disk rba scn??? 檢查點心跳
?????? CPDRT low_rba16??????????? on_disk_rba16??????? CPODT?????????????? SYSDATE????????????? DIFF_DATE CPODS?????????????????? CPHBT? CURRENT_SCN???? DIFF_SCN???????? INDX
------------ -------------------- -------------------- ------------------- ------------------- ---------- ---------------- ------------ ------------ ------------ ------------
????????? 32 0x5be.35db.0???????? 0x5be.3693.0???????? 2017-06-17 22:08:11 2017-06-17 22:08:28????? 17.00 25428734??????????? 946961434???? 25428741??????????? 7??????????? 0

FULL CHECKPOINT:

rtckp_rba??????????? RTCKP_SCN???????? CURRENT_SCN???? DIFF_SCN RTCKP_TIM?????????? SYSDATE????????????? DIFF_DATE
-------------------- ---------------- ------------ ------------ ------------------- ------------------- ----------
1470.2.16??????????? 25426654???????????? 25428742???????? 2088 2017-06-17 21:39:53 2017-06-17 22:08:28??? 1715.00

v$instance_recovery:

???? INST_ID ACTUAL_REDO_BLKS TARGET_REDO_BLKS???? 90%_blks TIMEOUT_BLKS? TARGET_MTTR ESTIMATED_MTTR
------------ ---------------- ---------------- ------------ ------------ ------------ --------------
?????????? 1????????????? 183??????????? 30962?????? 165888??????? 30962??????????? 0???????????? 63

--//這種方式的缺點就是必須將sql語句寫一行內.
--//也可以這樣操作,按v或者V,選中文本,進入v模式,然后打入;q1.
--//提醒一下,如果使用shift+方向鍵選擇的進入的是"選擇模式",按ctrl+g就可以切換為"可視模式"

select
*
from
dept;

--//這樣顯示,缺點就是打入的sql語句消失了.你可以先copy 2份再執行.

??? DEPTNO DNAME????????? LOC
---------- -------------- -------------
??????? 10 ACCOUNTING???? NEW YORK
??????? 20 RESEARCH?????? DALLAS
??????? 30 SALES????????? CHICAGO
??????? 40 OPERATIONS???? DALLAS

--//另外我還把內容放在寄存器a,b,可以使用"ap ,"bp取出.

--//提醒注意一點執行的語句不要是dml語句,比如insert,update,delete語句,還有truncate語句.因為缺省就是退出就是commit.存在一定的風險.
--//執行select語句一般問題不大.作為測試學習也應該避免dml語句,不然把這種習慣帶到生產庫出問題就麻煩了.
--//補充1點,還可以適當設置大一點linesize,這樣避免出現折行想象.當然還有致命的缺點,每次執行都要打開與關閉數據庫的連接.而且連接串是寫死的.

--//附上checkpoint.sql腳本
$ cat checkpoint.sql
column low_rba format a20
column low_rba16 format a20
column on_disk_rba format a20
column on_disk_rba16 format a20
column rtckp_rba format a20
column diff_date format 999999.99
column CPOSD_ono_disk_rba_scn format 99999999999999999999999999999999
column cpdrt heading "檢查點隊列|臟塊數量|CPDRT"
column cpodt_on_disk_rba heading "檢查點隊列|on disk rba|時間戳|CPODT"
column cpods heading "檢查點隊列|on disk rba scn|CPODS"
column cphbt heading "檢查點心跳|CPHBT"
column current_sysdate heading "當前時間|SYSDATE"
set num 12
PROMPT
PROMPT REDO:
PROMPT
SELECT cpdrt ,
?????? cplrba_seq || '.' || cplrba_bno || '.' || cplrba_bof "low_rba",
?????? cpodr_seq || '.' || cpodr_bno || '.' || cpodr_bof "on_disk_rba",
?????? TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS') cpodt_on_disk_rba,
?????? SYSDATE current_sysdate,
?????? ROUND ( (SYSDATE - TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS')) * 86400,
????????????? 2)
????????? diff_date,
?????? CPODS ,
?????????? CPHBT,
?????? current_scn,
?????? current_scn - cpods diff_scn,
?????? indx
? FROM x$kcccp, v$database
WHERE CPLRBA_SEQ <> 0;

PROMPT
PROMPT REDO ( Hexadecimal ):
PROMPT

SELECT cpdrt ,
?????? '0x'||to_char(cplrba_seq,'FMxxxxxxxx') || '.' || to_char(cplrba_bno,'FMxxxxxxxx')|| '.' || to_char(cplrba_bof,'FMxxxx') "low_rba16",
?????? '0x'||to_char(cpodr_seq,'FMxxxxxxxx') || '.' || to_char(cpodr_bno,'FMxxxxxxxx') || '.' || to_char(cpodr_bof,'FMxxxx') "on_disk_rba16",
?????? TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS') cpodt_on_disk_rba,
?????? SYSDATE current_sysdate,
?????? ROUND ( (SYSDATE - TO_DATE (CPODT, 'MM-DD-YYYY HH24:MI:SS')) * 86400,
????????????? 2)
????????? diff_date,
?????? CPODS ,
?????????? CPHBT,
?????? current_scn,
?????? current_scn - cpods diff_scn,
?????? indx
? FROM x$kcccp, v$database
WHERE CPLRBA_SEQ <> 0;

PROMPT
PROMPT FULL CHECKPOINT:
PROMPT
SELECT rtckp_rba_seq || '.' || rtckp_rba_bno || '.' || rtckp_rba_bof
????????? "rtckp_rba",
?????? rtckp_scn,
?????? current_scn,
?????? current_scn - rtckp_scn diff_scn,
?????? TO_DATE (rtckp_tim, 'MM-DD-YYYY HH24:MI:SS') rtckp_tim,
?????? SYSDATE,
?????? ROUND (
????????? (SYSDATE - TO_DATE (rtckp_tim, 'MM-DD-YYYY HH24:MI:SS')) * 86400,
????????? 2) diff_date
? FROM x$kccrt, v$database;

PROMPT
PROMPT v$instance_recovery:
PROMPT
SELECT inst_id,
?????? actual_redo_blks,
?????? target_redo_blks,
?????? LOG_FILE_SIZE_REDO_BLKS AS "90%_blks",
?????? LOG_CHKPT_TIMEOUT_REDO_BLKS AS timeout_blks,
?????? target_mttr,
?????? estimated_mttr
? FROM gv$instance_recovery;