Oracle學習筆記：sqlplus 使用精講

一、串連資料庫

　　os：僅僅進入sqlplus環境，不串連資料庫

sqlplus /nolog

　　os：本機串連

　　　　方式1：作業系統認證登入：其後的角色不可省略

set ORACLE_SID=TEST_SIDsqlplus /  as { sysdba | sysoper | sysasm }

　　　　方式2：標準使用者登入：密碼、連接埠（預設1521）、角色 均可省略。如果密碼省略則提示輸入密碼

set ORACLE_SID=TEST_SIDsqlplus 使用者[/密碼] [ as { sysdba | sysoper | sysasm } ]或sqlplus 使用者[/密碼]@本地oracle服務名 [ as { sysdba | sysoper | sysasm } ]或sqlplus 使用者[/密碼]@主機[:連接埠]/oracle服務名 [ as { sysdba | sysoper | sysasm } ]或sqlplus 使用者[/密碼]@//主機[:連接埠]/oracle服務名 [ as { sysdba | sysoper | sysasm } ]

　　os：遠端連線

同“本機串連”的“方式2”（除了set ORACLE_SID方式）

　　sqlplus：退出到os：預設行為為先commit、再成功退出

{ exit | quit } [ success | failure | warning | n | variable | :bindvariable ]      [ commit | rollback ]

　　sqlplus：中斷連線

disc[onnect]

　　sqlplus：建立串連

-- 提示使用者名稱和密碼：僅限本機登入conn[ect]  或和“os串連到db”一樣

　　切換使用者：

先斷開，再重連啊或先退出，再登入啊

 

二、設定sqlplus執行環境

　　設定環境變數：set

set environment_variable {?}

　　

　　查看環境變數的設定：show

-- 查看所有環境變數的設定show all--查看指定的環境變數的設定show environment_variable或者--查看指定的環境變數的設定：使用setset environment --它會給出相關的文法提示

　　

　　設定資訊顯示模式

　　　　提示符

set sqlp[rompt] "_user'@'_connect_identifier>"

　　　　當前os時間

--顯示當前的執行時間
set time on

--關閉當前的執行時間
set time off

　　　　頁數、行數

set pages[ize]=500set lin[esize]=3000

 　　　　dbms_output的資訊顯示

--開啟顯示set serverout[put] on [ size { n | unlimited } ] [ format { wrapped | word_wrapped | truncated  } ]--關閉顯示set serverout[put] off

 　　

　　設定事務控制：

--開啟 自動認可事務：立即提交set auto[commit] { on | imm[ediate] | n }
--開啟 自動認可事務：累計n個事務後提交
set auto[commit] n

--關閉 自動認可事務 
set auto[commit] off

 

　　執行時間跟蹤

set timi[ng] on

 

　　影響的行數

--開啟 顯示影響行數set feed[back] { 1 | on | n }--關閉 影響行數set feed[back] { 0 | off }

 

　　執行計畫

--顯示執行計畫set autot[race] { on | trace[only] } [ exp[lain] ] [ stat[istics] ]--關閉執行計畫set autot[race] off

 

 

三、緩衝區執行語句（sql、pl/sql）的編輯處理

　　調用外部編輯器：ed【it】

ed[it]

 

　　使用sqlplus的編輯命令：增、刪、改：a【ppend】、del、c【hange】、i【nput】

a[ppend] --在當前行後直接輸入字元創，不換行del --和list類似，見下面c[hange] --很牛的東東。格式 “ change  sep_charold[sep_char[new[sep_char]]]”：
　　1）sep_char為不在old和new中的非字母數字字元（基本上只能是標點符號）
　　2）最後的分隔字元sep_char可以省略
　　3）old不區分大小寫，即大小寫不敏感
　　4）old可以指定萬用字元“...”，用來匹配範圍：...old(開頭 .. 第一次出現)、old...old (前後全匹配)、old...(第一次出現 .. 最後)
　　5）如果省略第二個分隔字元sep_char（自然也省略後面）、或者省略new（自然也包括後面），則相當於刪除old
i[nput] --在當前行後，輸入新的字串，換行

　　更改當前行：預設總是最後一行：l【ist】

--查看所有的行{ list | ; }--查看指定的行：* 為當前行；n在緩衝區行數內list { n | * | last }--查看指定範圍內的行 從 n 到 m 行：m>=nlist { n | * } { m | * | last}

　　

　　儲存sql語句：sav【e】：預設副檔名為sql，預設行為為create。注意：儲存後，末尾的分號“；”會自動去掉；而且緩衝區有且僅有一個sql語句或pl/sql複合陳述式

sav[e]  file_name[.ext] [ create | replace | append  ]

　　載入sql語句：get：與save相反

--與 save 命令相反

　　

　　輸入sql語句的過程中，臨時退出編輯模式 ：以井號“#”臨時退出編輯模式

--在輸入sql的模式下，如果輸入未結束。可以再新行中以井號“#”臨時退出編輯模式，進入普通sqlplus的互動模式

　　

四、執行結果、替換變數、綁定變數等的 定義、互動、顯示、處理

 

　　sqlplus的注釋命令：rem【mark】

rem[ark] 任何文字  -- 類似於os的rem，或者sql的行注釋 “--”

　　

　　執行os命令：{ host | ！}：具體和os有關

--切換退出sqlplus，進入os的shell環境{ host | ! | $ }  --不切換sqlplus，執行os命令{ host | ! | $ } command

　　

 　　清理各種記錄資料：cl【ear】

--清理 螢幕輸出資訊，類似於os的clscl[ear] scr[een]--清理 緩衝區sql語句cl[ear] buff[er]--清理 sql 緩衝池記錄cl[ear] sql--清理 其他cl[ear] { breaks | computer | columns | timing }

　　

　　轉存執行結果：spo【ol】

--輸出 執行結果到 檔案。注意：雖然spool的檔案第一時間產生，但其中的結果內容到spool off 才寫入！
spo[ol] filename[.ext]  [ create | replace | append ]--關閉 spoolspo[ol] off

　　

　　替換變數：sqlplus的【先行編譯】宏功能，本質很簡單：def【ine】、acc【ept】、unde【fine】、pro【mpt】、pau【se】

--定義、重新設定新值方式1：def[ine] substitution_var = text方式2：acc[ept] substitution_var [ char | date | [ num[ber] ] | binary_float | binary_double  ] --變數類型                                               [ for[mat] format_str ] --格式                                               [ default default_value ] --變數的預設值                                               [ { prompt prompt_text } | nopr[ompt] ] --提示資訊                                               [ hide ] --不顯示使用者輸入，類似於輸入不回顯密碼的輸入--顯示指定的替換變數def[ine] substitution_var--顯示 所有的替換變數def[ine]--sqlplus的提示資訊：類似於os的echopro[mpt] [message_text]

--sqlplus的暫停提示資訊：類似於os的pause
pau[se] [message_text]

　　

　　sqlplus【預定義的替換變數】：一定要注意：這些預定義替換變數都是可以重新定義或刪除定義的。是sqlplus的會話級的

_EDITOR：sqlplus的edit命令調用的os文字編輯器_USER：sqlplus的當前登入使用者名稱_CONNECT_IDENTIFIER：sqlplus的當前串連串_DATE：sqlplus的當前os日期

　　

　　綁定變數：sqlplus中？或者說是oracle引擎的【會話變數？】更為合適

 

--定義：綁定變數：bind_variablevar[iable] bind_variable { number | char | char(n  [byte | char ] )  | varchar2(n  [byte | char ] ) | nchar | nchar(n) | nvarchar(n) |  clob | nclob | binary_float | binary_double | refcursor }--查看：所有定義的綁定變數：定義var[iable]--查看：指定名稱的綁定變數：定義var[iable] bind_variable--列印、輸出：所有綁定變數的：內容print--列印、輸出：指定名稱綁定變數的：內容print bind_variable--列印、輸出：自動列印所涉及的綁定變數的：內容 ： 允許自動列印、輸出set autoprint on--列印、輸出：自動列印所涉及的綁定變數的：內容 ： 禁止自動列印、輸出set autoprint off

　　

五、執行os指令碼

　　os啟動sqlplus時，執行指定的os中sql指令碼：sqlplus的啟動參數

sqlplus 【options】【logon】 @{URL | filename [.ext] [args...] }
--注意“@”符號

　

　　sqlplus內：運行os中的sql指令碼：{ start | @ | @@ }：“@”和“@@”的區別在於“@@”指定sql指令碼（父指令碼）中的要啟動並執行sql指令碼（子指令碼），（子指令碼）的查詢路徑就在（父指令碼）相同的目錄下。當然，如果都用絕對路徑，就沒區別了。

{ sta[rt] | @ | @@ } { url | os_filename[.ext] } [ arg... ]

　　

　　sqlplus內：運行緩衝區內的指令碼內容：{ run | /}

{ r[un] | / }

　　

 

六、DBA實用功能

　　顯示初始化參數：show parameters

--顯示 所有的初始化參數 資訊show parameters--顯示 指定的初始化參數資訊：like %？%show parameters {？}

　　顯示SGA資訊

show sga

　　

　　顯示oracle執行錯誤資訊

--顯示oracle錯誤資訊代碼show sqlcode--顯示【所有oracle錯誤資訊】show err[ors] --顯示【指定類型的對象】的【錯誤資訊】show err[ors]  { function | procedure | package | package body | trigger | view | type | type body | dimension | java class }   [schema.]name

　　

　　顯示oracle資源回收筒資訊

--顯示【所有的】資源回收筒資訊show recyc[lebin] --顯示 【指定的原始對象名】的資源回收筒資訊show recyc[lebin] original_name

　　

 　　startup：文法

startup [  [ force ] [ restrict ]  [ pfile=filename ] [ quiet ] [ { nomount | mount [dbname] | open  [ read { only | write [recover] } | recover ][dbname] } ]     ]

--force：強制關閉（abort模式）正啟動並執行oracle資料庫執行個體，之後重啟。警告：危險的操作，生產環境慎用
--restrict：進入restrict session 許可權的串連模式
--pfile：以指定的pfile啟動
　　[filename]：替代預設的啟動spfile或預設pfile
--quiet：啟動後，不顯示sga資訊
[dbname]：替代初始化參數中的 DB_NAME 配置

 　　啟動：僅執行個體，nomount資料庫

startup nomount

 　　啟動：執行個體，mount資料庫

startup mount

 　　啟動：執行個體，read only唯讀開啟資料庫

startup open read only

 　　啟動：執行個體，read write 開啟資料庫：即正常的開啟檔案

startup open read write
startup open

 　　啟動：執行個體，介質復原模式開啟：等價於：recover database 命令 + startup命令

startup open recover ？？？？？？？？？？？？

　

　　關閉：shutdown

--異常關閉：類似於斷電：（1）不徵求用戶端同意，立即中斷連線；（2）啟動必須恢複shutdown abort--立即關閉：（1）立即斷開用戶端串連；（2）啟動無需恢複shutdown immediate--正常關閉：（1）等待用戶端主動中斷連線；（2）啟動無需恢複；（3）未完成交易回復；shutdown [ normal ]--謹慎關閉：（1）等待用戶端主動中斷連線；（2）啟動無需恢複；（3）且等待事務完成；shutdown transaction

　　

　　恢複：recover

　　日誌歸檔：archive log

　　copy資料：copy

　　修改目前使用者密碼：不指定使用者名稱，即為目前使用者

passw[ord]

　　修改其他指定使用者密碼：指定使用者名稱

passw[ord] { username }

 

報表功能