關於sqlplus用法的一些總結
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
[oracle@localhost ~]$ cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.5 (Carthage)
Kernel \r on an \m
sqlplus是經常用的工具了,它提供了很多功能,用起來十分方便便捷。
1.獲取幫助
SQL> help
HELP
----
Accesses this command line help system. Enter HELP INDEX or ? INDEX
for a list of topics.
You can view SQL*Plus resources at
and the Oracle Database Library at
http://www.oracle.com/technology/documentation/
HELP|? [topic]
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
SQL> help set
SET
---
Sets a system variable to alter the SQL*Plus environment settings
for your current session. For example, to:
- set the display width for data
- customize HTML formatting
- enable or disable printing of column headings
- set the number of lines per page
SET system_variable value
where system_variable and value represent one of the following clauses:
APPI[NFO]{OFF|ON|text} NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n} NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n} NUMF[ORMAT] format
AUTOP[RINT] {OFF|ON} NUM[WIDTH] {10|n}
AUTORECOVERY {OFF|ON} PAGES[IZE] {14|n}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]} PAU[SE] {OFF|ON|text}
[EXP[LAIN]] [STAT[ISTICS]] RECSEP {WR[APPED]|EA[CH]|OFF}
BLO[CKTERMINATOR] {.|c|ON|OFF} RECSEPCHAR {_|c}
CMDS[EP] {;|c|OFF|ON} SERVEROUT[PUT] {ON|OFF}
COLSEP {_|text} [SIZE {n | UNLIMITED}]
CON[CAT] {.|c|ON|OFF} [FOR[MAT] {WRA[PPED] |
COPYC[OMMIT] {0|n} WOR[D_WRAPPED] |
COPYTYPECHECK {ON|OFF} TRU[NCATED]}]
DEF[INE] {&|c|ON|OFF} SHIFT[INOUT] {VIS[IBLE] |
DESCRIBE [DEPTH {1|n|ALL}] INV[ISIBLE]}
[LINENUM {OFF|ON}] [INDENT {OFF|ON}] SHOW[MODE] {OFF|ON}
ECHO {OFF|ON} SQLBL[ANKLINES] {OFF|ON}
EDITF[ILE] file_name[.ext] SQLC[ASE] {MIX[ED] |
EMB[EDDED] {OFF|ON} LO[WER] | UP[PER]}
ERRORL[OGGING] {ON|OFF} SQLCO[NTINUE] {> | text}
[TABLE [schema.]tablename] SQLN[UMBER] {ON|OFF}
[TRUNCATE] [IDENTIFIER identifier] SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
ESC[APE] {\|c|OFF|ON} SQLPRE[FIX] {#|c}
ESCCHAR {@|?|%|$|OFF} SQLP[ROMPT] {SQL>|text}
EXITC[OMMIT] {ON|OFF} SQLT[ERMINATOR] {;|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF} SUF[FIX] {SQL|text}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL} TAB {ON|OFF}
FLU[SH] {ON|OFF} TERM[OUT] {ON|OFF}
HEA[DING] {ON|OFF} TI[ME] {OFF|ON}
HEADS[EP] {||c|ON|OFF} TIMI[NG] {OFF|ON}
INSTANCE [instance_path|LOCAL] TRIM[OUT] {ON|OFF}
LIN[ESIZE] {80|n} TRIMS[POOL] {OFF|ON}
LOBOF[FSET] {1|n} UND[ERLINE] {-|c|ON|OFF}
LOGSOURCE [pathname] VER[IFY] {ON|OFF}
LONG {80|n} WRA[P] {ON|OFF}
LONGC[HUNKSIZE] {80|n} XQUERY {BASEURI text|
MARK[UP] HTML [OFF|ON] ORDERING{UNORDERED|
[HEAD text] [BODY text] [TABLE text] ORDERED|DEFAULT}|
[ENTMAP {ON|OFF}] NODE{BYVALUE|BYREFERENCE|
[SPOOL {OFF|ON}] DEFAULT}|
[PRE[FORMAT] {OFF|ON}] CONTEXT text}
2.檢視錶的結構:
SQL> desc v$process;
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
PID NUMBER
SPID VARCHAR2(24)
PNAME VARCHAR2(5)
USERNAME VARCHAR2(15)
SERIAL# NUMBER
TERMINAL VARCHAR2(30)
PROGRAM VARCHAR2(48)
TRACEID VARCHAR2(255)
TRACEFILE VARCHAR2(513)
BACKGROUND VARCHAR2(1)
LATCHWAIT VARCHAR2(8)
LATCHSPIN VARCHAR2(8)
PGA_USED_MEM NUMBER
PGA_ALLOC_MEM NUMBER
PGA_FREEABLE_MEM NUMBER
PGA_MAX_MEM NUMBER
3.編輯sql語句(下面羅列了一部分命令)
A[PPEND] text 將text附加到當前行之後
C[HANGE] /old/new 將當前行的old替換成old
CL[EAR]BUFF[ER] 清除緩衝區的所有行
DEL 刪除當前行
DEL x 刪除x行
L[IST] 列出緩衝區的所有行
L[IST] x 列出緩衝區的第x行
R[UN] 或者 / 執行緩衝區所儲存的語句
x 將x行作為當前行
SQL> LIST
1* select * from t
SQL> clear buffer;
buffer 已清除
SQL> list
SP2-0223: SQL 緩衝區中不存在行。
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> run
1* select * from v$version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
4.儲存,檢索並執行檔案
SAV[E] filename :將sqlplus裡的緩衝區的內容儲存到filename指定的檔案中,append將緩衝區的內容追加到現有的檔案之後,replace說明替換一個現有的檔案
[{REPLACE|APPEND}]
GET filename:將filename裡指定的內容讀入到sqlplus緩衝區中
STA[RT] filename:將filename裡指定的內容讀入到sqlplus緩衝區中,並執行
@filename:同start命令
ED[IT]:sqlplus裡緩衝區的內容複製到一個命為afiedt.buf的檔案中,然後啟動os預設的編輯器,在退出編輯器時,其中的內容被複制到sqlplus緩衝區中
ED[IT] filename:與edit相同,不同的是指定了檔名
SPO[OL] filename:sqlplus緩衝區的內容複製到filename檔案中
SPO[OL] OFF:停止sqlplus的輸出結果複製到filename指定的檔案中,並關閉該檔案
SQL> edit
已寫入 file afiedt.buf
1* select * from v$version
SQL> !cat afiedt.buf
select * from v$version
/
SQL> spool diy_os.lst
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> spool off;
SQL> !cat wang.lst
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> spool off;
5.格式化列,清除列格式
COL[UMN] {column | alias}[options]
column指定列名
alias 指定格式化列的別名
option 指定用於格式化列或者別名的一個或多個選項
格式化選項:
FOR[MATE] format:將列或別名的顯示格式設定為由format字串指定的格式
HEA[DING] heading:將列或別名的標題中的文字設定成有heading字串指定的格式
JUS[TIFY] [{LEFT|CENTER|RIGHT}] :將列輸出設定為左對齊,居中,或右對齊
WRA[PPED]:在輸出結果中將一個字串的末尾換行顯示,該選項可能導致單個單詞跨越多行
WOR[D_WRAPPED]:與WRAPPED選項類似,不同之處在於單個單詞不會跨越兩行
CLE[AR]:清除列的任何格式化(將格式設定成預設)=>colum id clear;
上面format可以使用很多格式化引數
比如:
COLUMN LAST_NAME FORMAT A20 HEADING 'EMPLOYEE|NAME'
COLUMN SALARY FORMAT $9,999,990.99
COLUMN SALARY+COMMISSION_PCT+BONUS-EXPENSES-INS-TAX ALIAS NET
COLUMN NET FORMAT $9,999,999.99 NULL '
COLUMN REMARKS FORMAT A20 WRAP
以下摘自官方文件:
The default width of CHAR, NCHAR, VARCHAR2 (VARCHAR) and NVARCHAR2 (NCHAR VARYING) columns is the width of the column in the database. SQL*Plus formats these datatypes left-justified. If a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of SET WRAP.
A LONG, BLOB, BFILE, CLOB, NCLOB or XMLType column's width defaults to the value of SET LONGCHUNKSIZE or SET LONG, whichever one is smaller.
SQL*Plus truncates or wraps XMLType columns after 2000 bytes. To avoid this you need to set an explicit COLUMN format for the XMLType column. A COLUMN format can be up to a maximum of 60000 per row.
To change the width of a datatype to n, use FORMAT An. (A stands for alphabetic.) If you specify a width shorter than the column heading, SQL*Plus truncates the heading.
DATE ColumnsThe default width and format of unformatted DATE columns in SQL*Plus is derived from the NLS_DATE_FORMAT parameter. The NLS_DATE_FORMAT setting is determined by the NLS territory parameter. For example, the default format for the NLS territory, America, is DD-Mon-RR, and the default width is A9. The NLS parameters may be set in your database parameter file, in environment variables or an equivalent platform-specific mechanism. They may also be specified for each session with the ALTER SESSION command. For more information about DATE formats, and about NLS parameters, see the .
You can change the format of any DATE column using the SQL function TO_CHAR in your SQL SELECT statement. You may also wish to use an explicit COLUMN FORMAT command to adjust the column width.
When you use SQL functions like TO_CHAR, Oracle Database automatically enables a very wide column. The default column width may also depend on the character sets in use in SQL*Plus and in the database. To maximize script portability if multiple characters sets are used, Oracle Database recommends using COLUMN FORMAT for each column selected.
To change the width of a DATE column to n, use the COLUMN command with FORMAT An. If you specify a width shorter than the column heading, the heading is truncated.
NUMBER Columns For numeric columns, COLUMN FORMAT settings take precedence over SET NUMFORMAT settings, which take precedence over SET NUMWIDTH settings.
See and .
To change a NUMBER column's width, use FORMAT followed by an element as specified in .
Table 12-1 Number Formats
Element | Examples | Description |
---|---|---|
, (comma) |
9,999 |
Displays a comma in the specified position. |
. (period) |
99.99 |
Displays a period (decimal point) to separate the integral and fractional parts of a number. |
$ |
$9999 |
Displays a leading dollar sign. |
0 |
0999 9990 |
Displays leading zeros Displays trailing zeros. |
9 |
9999 |
Displays a value with the number of digits specified by the number of 9s. Value has a leading space if positive, a leading minus sign if negative. Blanks are displayed for leading zeroes. A zero (0) is displayed for a value of zero. |
B |
B9999 |
Displays blanks for the integer part of a fixed-point number when the integer part is zero, regardless of zeros in the format model. |
C |
C999 |
Displays the ISO currency symbol in the specified position. |
D |
99D99 |
Displays the decimal character to separate the integral and fractional parts of a number. |
EEEE |
9.999EEEE |
Displays value in scientific notation (format must contain exactly four "E"s). |
G |
9G999 |
Displays the group separator in the specified positions in the integral part of a number. |
L |
L999 |
Displays the local currency symbol in the specified position. |
MI |
9999MI |
Displays a trailing minus sign after a negative value. Display a trailing space after a positive value. |
PR |
9999PR |
Displays a negative value in |
RN rn |
RN rn |
Displays uppercase Roman numerals. Displays lowercase Roman numerals. Value can be an integer between 1 and 3999. |
S |
S9999 9999S |
Displays a leading minus or plus sign. Displays a trailing minus or plus sign. |
TM |
TM |
Displays the smallest number of decimal characters possible. The default is TM9. Fixed notation is used for output up to 64 characters, scientific notation for more than 64 characters. Cannot precede TM with any other element. TM can only be followed by a single 9 or E |
U |
U9999 |
Displays the dual currency symbol in the specified position. |
V |
999V99 |
Displays value multiplied by 10n, where n is the number of 9's after the V. |
X |
XXXX xxxx |
Displays the hexadecimal value for the rounded value of the specified number of digits. |
SQL> set pagesize 100
SQL> set linesize 100
SQL> select * from dba_data_files where rownum<4;
FILE_NAME
----------------------------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT
---------- ------------------------------ ---------- ---------- --------- ------------ ---
MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_
---------- ---------- ------------ ---------- ----------- -------
/u01/app/oracle/oradata/orcl3939/user01.dbf
4 USERS 52428800 6400 AVAILABLE 4 NO
0 0 0 51380224 6272 ONLINE
/u01/app/oracle/oradata/orcl3939/undotbs01.dbf
3 UNDOTBS1 917504000 112000 AVAILABLE 3 YES
3.4360E+10 4194302 640 916455424 111872 ONLINE
/u01/app/oracle/oradata/orcl3939/sysaux01.dbf
2 SYSAUX 754974720 92160 AVAILABLE 2 YES
3.4360E+10 4194302 1280 753926144 92032 ONLINE
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29876893/viewspace-1684170/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於Mysql使用的一些總結MySql
- MySql關於鎖的一些總結MySql
- 關於繼承的一些小總結繼承
- 關於EM配置的一些總結
- 關於BUFFER POOL的一些總結
- 關於Oracle塊的一些總結Oracle
- 關於Code Review的一些思考總結View
- 關於angularJS的一些用法AngularJS
- PHP中關於foreach的簡單的用法總結PHP
- 總結關於CPU的一些基本知識
- 關於查詢轉換的一些總結
- 關於with 臨時表 as的一些用法
- 關於 變址影像(indexed image) 的一些總結Index
- 關於字元函式的一些應用總結字元函式
- 關於使用者體驗的一些總結
- 關於查詢最佳化的一些總結
- 關於DDD和COLA的一些總結和思考
- 關於學習 Linux 系統結構的一些總結Linux
- 關於程式和執行緒 自我的一些總結執行緒
- 關於分割槽表Local索引Rebuild的一些總結索引Rebuild
- 關於近期的總結
- 關於UIWebView的總結UIWebView
- 關於BeautifulSoup的總結
- 關於HTML的總結HTML
- 關於學習-Linux-系統結構的一些總結Linux
- css中的一些選擇器的用法總結CSS
- 微信小程式關於分享一些簡單總結微信小程式
- 關於前端工程化(基建)的一些總結和思考前端
- 關於集合中一些常考的知識點總結
- android 關於記憶體優化的一些總結Android記憶體優化
- 關於程式碼質量度量和分析的一些總結
- 關於電腦(window)後門檢視的一些總結
- 關於個人開源專案(vue app)的一些總結VueAPP
- sqlserver關於always on的總結SQLServer
- mysql關於variable的總結MySql
- ORACLE關於NULL的總結OracleNull
- 關於ORACLE鎖的總結Oracle
- 關於jboss配置的總結