SQLPLUS是 Oracle 資料庫最原始的工具，它提供了一個與 Oracle 資料庫溝通的介面，當我們沒有一些工具例如 sqldeveloper 、 PL/SQL Developer 、 TOAD …等輔助時，就必須使用 SQLPLUS 來操作，因此身為 DBA 還是必須要熟悉 SQLPLUS 。
SQLPLUS 這項工具只要安裝 Oracle Server 或是 Client 軟體就可以使用，連線資料庫的方式有兩種，一種是透過 Oracle Net 的方式，也就是使用 sqlplus <帳號>/<密碼>@<tnsnames> 登入， @ 後面是 tnsnames.ora 裡面設定的名稱；另外一種方式是直接在 Oracle Server 上設定環境變數 ORACLE_SID ，例如登入 orcl 這個資料庫就必須在 Server 上設定 ORACLE_SID=orcl ，然後使用 sqlplus <帳號>/<密碼> 進行登入的動作。
對於 DBA 來說，最常使用的莫過於在 Oracle Server 上使用 sqlplus / as sysdba 進行登入，這個動作就是直接以 sys 最高權限的方式進行登入，但是要求作業系統的使用者必須是具有 dba 群組的權限才可以使用，否則就會出現 ORA-01017 invalid username/password 的錯誤，這邊所謂的 dba 群組指的是在 ORACLE 軟體安裝的時候，有一個步驟要輸入 dba 群組的名稱，例如安裝的時候將 dba 群組設定為 oinstall ，那麼作業系統的使用者就必須屬於 oinstall 的群組才可以使用 sqlplus / as sysdba 。一般來說， dba 群組的設定都是設定為 dba ，透過查詢 $ORACLE_HOME/rdbms/lib 底下的 config.c 這個檔案，就可以知道當初安裝的時候所設定的群組名稱為何:
config.c 這個檔案在不同的平台名稱也不同，如果是 Linux 、 HPUX ，就是查詢 config.c ； 如果是 AIX 、 Solaris ，就查詢 config.s 。
SQLPLUS 的命令結尾都以分號 “ ; ” 或者是斜線 “ / ” 來表示，如果是 PL/SQL 程式相關的語法，則最後都以斜線 “ / ” 來表示結束。執行 script 的話，則是使用 “ @ ” 代表執行，例如 @utlrp.sql ，表示執行 utlrp.sql 這個 script 。另外雙橫線 “ -- “ 表示註釋符號，在每一行前面增加 “ -- “ 表示不執行此行，如果是整段的話則是以斜線星號開頭 “ /* ” 以及星號斜線結尾 “ */ ” 來註釋整段。要檢視上一次 SQLPLUS 執行的命令，則是使用 “ l “ 或者是 “ list “ 指令，如果要重複執行上一次的命令，則是直接使用斜線 “ / “ 就可以重複執行上一次的命令。
透過 product_user_profile 可以用來限制使用者於 SQLPLUS 所使用的命令，如果 product_user_profile 不存在的話，可以透過執行 $ORACLE_HOME/sqlplus/admin 底下的 pupbld.sql 來建立，例如要限制 scott 這個使用者不能執行 truncate 這個命令，那麼就可以在 product_user_profile 新增一筆資料來表示 scott 不能使用 truncate :
在 product_user_profile 限制了之後，使用 scott 就無法進行 truncate 命令了:
如果要解除這個限制只要刪除 product_user_profile 裡面的紀錄就行了:
這邊要注意的是， product_user_profile 是屬於 system 使用者，要新增或刪除資料時，必須使用 system 登入操作才可以。
除了限制使用者個命令之外， SQLPLUS 也可以設定 login script ，位於 ORACLE Server 上的 $ORACLE_HOME/sqlplus/admin 目錄下有一個 glogin.sql ，只需把需要在 login 時執行的命令設定在 glogin.sql ，那麼只要登入 SQLPLUS 就會自動執行此命令，如果不想在 Server 端設定，也可以在 Client 端的 $ORACLE_HOME/sqlplus/admin 底下，設定 login.sql 。
SQLPLUS 預設的提示符號為 SQL> ，要更改的話可以使用 set sqlprompt 命令來更改，例如:
由於 SQLPLUS 算是一個比較陽春的工具，所以撈出來的資料有可能雜亂無章，這時候就需要設定一些環境變數來格式化輸出的結果，例如執行一段腳本如下:
其中 col … format 表示用來格式化這個欄位的長度， format a12 表示此欄位為文字型態，長度為 12 ， format 999999 表示此欄位為數字型態，長度為 6 位數。將欄位格式化之後，執行 SQL 所輸出的結果就會比較整齊。
除了格式化欄位長度之外，還有比較一些常用的環境變數如下:
set linesize <n> : 用來設定一行的長度， set linesize 180 表示一行輸出的長度為 180 。
set pagesize <n> : 用來設定輸出一頁有幾行。
set timing on / off : 用來設定是否顯示 SQL 語法的執行時間。
set echo on / off : 在執行腳本時，用來設定是否將腳本的命令顯示出來。
set head on / off : 用來設定是否顯示欄位名稱。
set feedback on / off : 例如執行一段 select 查詢 ，最後 SQLPLUS 都會顯示出查詢了多少個 rows ，透過腳本執行時，可使用feedback on / off 來設定是否顯示出這一段文字。
set markup html on : 設定輸出為 HTML 格式。
在實務上來說，使用 SQLPLUS 有幾個比較常用的命令如下:
describe (desc) : 用來顯示 Table 欄位資訊或者是 Package 的 spec 資訊，例如要知道 dba_tables 有哪些欄位則執行 desc dba_tables 。
host (!) : 在 SQLPLUS 裡面執行 host 或是驚嘆號 “ ! “ ，可以回到作業系統的 shell 當中，雖然直接 exit 退出 SQLPLUS 就好，不過如果當 SQLPLUS 裡面已經設定了一些環境變數，或者已經格式化一些查詢，此時想要保留設定又需要暫時回到 OS 查詢一些東西，就可以使用 host 指令暫時跳出 SQLPLUS 。
spool : 將 SQLPLUS 的查詢結果輸出到一個檔案，先使用 spool <檔案名稱> ，然後執行 SQL 查詢，最後再 spool off 。
save <filename> : 將 SQLPLUS 所執行的查詢 SQL 語法，儲存到一個檔案當中。
edit : 在 SQLPLUS 裡面啟用文字編輯器，這是一個非常好用的功能，當我們輸入一段冗長的 SQL 語法，一執行下去發現某個地方寫錯了，最令人頭疼的是要重新再輸入一次這麼長的一段語法，其實此時只要輸入 “ edit “ ，那麼 SQLPLUS 就會開啟預設的文字編輯器，只要使用 edit 打開文字編輯器，那麼我們就可以很輕鬆地修改語法中錯誤的地方，不用重新輸入了。預設文字編輯器在 Windows 平台會開啟記事本，而 Unix 平台則是會開啟 vi ，如果 edit 無法進入編輯模式，那麼就必須使用 define_editor 來設定文字編輯器，例如要設定 vi 為文字編輯器，那麼就執行 define_editor=vi ，之後再使用 edit 就可以開啟編輯模式了。
set autotrace on / off / traceonly : 用來檢視 SQL 語法的執行計畫，如果是 set autotrace on ，那麼此 SQL 語法會真正的執行然後顯示出執行計畫；如果使用 set autotrace traceonly ，那麼 SQL 語法只會顯示出執行計畫，不會真正執行。
copy from : 用來複製 Table 的資料，例如將 orcl 資料庫裡面的 HR 使用者的 employees 這個 Table 複製過來:
雖然現在有很多資料庫工具用起來很方便，但學習 Oracle 資料庫還是必須熟悉這些最基本的東西，這樣功夫才會紮實。
沒有留言:
張貼留言