ORACLE SQL*PLUS 命令大全
2011-06-09 23:00 潇湘隐者 阅读(19487) 评论(1) 编辑 收藏 举报其实网上已经有SQL*PLUS命令大全这方面的资料了,不过大都不完整，只覆盖了一些常用的命令，本着学习、实践的原则，也为了梳理、总结SQL*PLUS方面的知识，自己也整理了一份这方面的文档资料,不足之处还请大家多多指教。
---1： HELP INDEX
HELP 命令查询相关命令的信息。
你可以从下面看到大概有54个SQL*PLUS命令。每个命令都有大量的参数选项，你不可能清楚的记得每个命令
以及它相关的参数细节，所以当你遗忘时，就可以用HELP命令来查看相关命令的命令信息
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
CONNECT PASSWORD SHOW
HELP [TOPIC] 来查看相关命令介绍以及语法。 可以用？代替HELP
例子：
HELP DESCRIBE
? COPY
---2: @
运行调入内存的sql文件（文件可以是本机脚本文件或web服务器上的脚本文件)
@跟START命令具有相同功能。可以用START替代。可以不指定该文件扩展名
@ ("at" sign)
-------------
Runs the SQL*Plus statements in the specified script. The script can be
called from the local file system or a web server. Only the url form is
supported in iSQL*Plus.
@ {url|file_name[.ext]} [arg ...]
where url supports HTTP and FTP protocols in the form:
http://host.domain/script.sql
例子：执行test。sql脚本文件
SQL> @ F:/test.sql
其中test.sql中脚本如下所示
PROMPT
PROMPT '显示5条员工信息'
PROMPT
SELECT * FROM SCOTT.EMP WHERE ROWNUM <= 5
/
运行结果如图所示。
---3 @@
@@ 运行调入内存的sql文件，跟@命令很相像。如果运行的脚本文件中有嵌入脚本，则@@比较有用。
@@ (double "at" sign)
---------------------
Runs the specified script. This command is almost identical to
the @ command. It is useful for running nested scripts because it
has the additional functionality of looking for the nested script
in the same url or path as the calling script. Only the url form
is supported in iSQL*Plus.
@@ {url|file_name[.ext]} [arg ...]
---4 /
执行当前缓冲区的命令（运行上一次执行的SQL语句）.
SQL> HELP /
/ (slash)
---------
Executes the most recently executed SQL command or PL/SQL block
which is stored in the SQL buffer. Use slash (/) at the command
prompt or line number prompt in SQL*Plus command line, or use
slash (/) in the iSQL*Plus Workspace. The buffer has no command
history and does not record SQL*Plus commands.
例子：如下图所示：
---5 ACCEPT
SQL> HELP ACCEPT
ACCEPT可以修改既有变量，也可定义一个新变量并等待用户输入初始值。
ACCEPT
------
Reads a line of input and stores it in a given substitution variable.
In iSQL*Plus, displays the Input Required screen for you to enter a
value for the substitution variable.
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
[FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
HELP 命令查询相关命令的信息。
你可以从下面看到大概有54个SQL*PLUS命令。每个命令都有大量的参数选项，你不可能清楚的记得每个命令
以及它相关的参数细节，所以当你遗忘时，就可以用HELP命令来查看相关命令的命令信息
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
CONNECT PASSWORD SHOW
HELP [TOPIC] 来查看相关命令介绍以及语法。 可以用？代替HELP
例子：
HELP DESCRIBE
? COPY
---2: @
运行调入内存的sql文件（文件可以是本机脚本文件或web服务器上的脚本文件)
@跟START命令具有相同功能。可以用START替代。可以不指定该文件扩展名
@ ("at" sign)
-------------
Runs the SQL*Plus statements in the specified script. The script can be
called from the local file system or a web server. Only the url form is
supported in iSQL*Plus.
@ {url|file_name[.ext]} [arg ...]
where url supports HTTP and FTP protocols in the form:
http://host.domain/script.sql
例子：执行test。sql脚本文件
SQL> @ F:/test.sql
其中test.sql中脚本如下所示
PROMPT
PROMPT '显示5条员工信息'
PROMPT
SELECT * FROM SCOTT.EMP WHERE ROWNUM <= 5
/
运行结果如图所示。
---3 @@
@@ 运行调入内存的sql文件，跟@命令很相像。如果运行的脚本文件中有嵌入脚本，则@@比较有用。
@@ (double "at" sign)
---------------------
Runs the specified script. This command is almost identical to
the @ command. It is useful for running nested scripts because it
has the additional functionality of looking for the nested script
in the same url or path as the calling script. Only the url form
is supported in iSQL*Plus.
@@ {url|file_name[.ext]} [arg ...]
---4 /
执行当前缓冲区的命令（运行上一次执行的SQL语句）.
SQL> HELP /
/ (slash)
---------
Executes the most recently executed SQL command or PL/SQL block
which is stored in the SQL buffer. Use slash (/) at the command
prompt or line number prompt in SQL*Plus command line, or use
slash (/) in the iSQL*Plus Workspace. The buffer has no command
history and does not record SQL*Plus commands.
例子：如下图所示：
---5 ACCEPT
SQL> HELP ACCEPT
ACCEPT可以修改既有变量，也可定义一个新变量并等待用户输入初始值。
ACCEPT
------
Reads a line of input and stores it in a given substitution variable.
In iSQL*Plus, displays the Input Required screen for you to enter a
value for the substitution variable.
ACC[EPT] variable [NUM[BER] | CHAR | DATE | BINARY_FLOAT | BINARY_DOUBLE]
[FOR[MAT] format] [DEF[AULT] default] [PROMPT text | NOPR[OMPT]] [HIDE]
---6 APPEND
将text附加到当前行之后。
SQL> HELP APPEND
APPEND
------
Adds text to the end of the current line in the SQL buffer.
A[PPEND] text
Not available in iSQL*Plus
---7 ARCHIVE LOG
启动或停止归档在线重做日志文件。
SQL> HELP ARCHIVE LOG
ARCHIVE LOG
-----------
Starts or stops automatic archiving of online redo log files,
manually (explicitly) archives specified redo log files, or
displays information about redo log files.
ARCHIVE LOG {LIST|STOP} | {START|NEXT|ALL|integer} [TO destination]
---8 ATTRIBUTE
SQL> HELP ATTRIBUTE
ATTRIBUTE
---------
Specifies display characteristics for a given attribute of an Object Type
column, such as the format of NUMBER data. Columns and attributes should
not have the same names as they share a common namespace. Lists the
current display characteristics for a single attribute or all attributes.
ATTRIBUTE [type_name.attribute_name [option ... ]]
where option represents one of the following terms or clauses:
ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name | alias}
ON|OFF
---9 BREAK
break命令抑制重复的列或表达你的名字的默认值。因此，要制止重复在ORDER BY子句中指定的列值，使用最简单的形式break命令：
SQL> HELP BREAK
BREAK
-----
Specifies where changes occur in a report and the formatting
action to perform, such as:
- suppressing display of duplicate values for a given column
- skipping a line each time a given column value changes
(In iSQL*Plus, only when Preformatted Output is ON)
- printing computed figures each time a given column value
changes or at the end of the report.
Enter BREAK with no clauses to list the current BREAK definition.
BRE[AK] [ON report_element [action [action]]] ...
where report_element has the following syntax:
{column | expression | ROW | REPORT}
and where action has the following syntax:
[SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]]
The SKIP option is not supported in iSQL*Plus
---10 BTITLE
设置格式化页脚
SQL> HELP BTITLE ;
BTITLE
------
Places and formats a specified title at the bottom of each report
page, or lists the current BTITLE definition.
BTI[TLE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
将text附加到当前行之后。
SQL> HELP APPEND
APPEND
------
Adds text to the end of the current line in the SQL buffer.
A[PPEND] text
Not available in iSQL*Plus
---7 ARCHIVE LOG
启动或停止归档在线重做日志文件。
SQL> HELP ARCHIVE LOG
ARCHIVE LOG
-----------
Starts or stops automatic archiving of online redo log files,
manually (explicitly) archives specified redo log files, or
displays information about redo log files.
ARCHIVE LOG {LIST|STOP} | {START|NEXT|ALL|integer} [TO destination]
---8 ATTRIBUTE
SQL> HELP ATTRIBUTE
ATTRIBUTE
---------
Specifies display characteristics for a given attribute of an Object Type
column, such as the format of NUMBER data. Columns and attributes should
not have the same names as they share a common namespace. Lists the
current display characteristics for a single attribute or all attributes.
ATTRIBUTE [type_name.attribute_name [option ... ]]
where option represents one of the following terms or clauses:
ALI[AS] alias
CLE[AR]
FOR[MAT] format
LIKE {type_name.attribute_name | alias}
ON|OFF
---9 BREAK
break命令抑制重复的列或表达你的名字的默认值。因此，要制止重复在ORDER BY子句中指定的列值，使用最简单的形式break命令：
SQL> HELP BREAK
BREAK
-----
Specifies where changes occur in a report and the formatting
action to perform, such as:
- suppressing display of duplicate values for a given column
- skipping a line each time a given column value changes
(In iSQL*Plus, only when Preformatted Output is ON)
- printing computed figures each time a given column value
changes or at the end of the report.
Enter BREAK with no clauses to list the current BREAK definition.
BRE[AK] [ON report_element [action [action]]] ...
where report_element has the following syntax:
{column | expression | ROW | REPORT}
and where action has the following syntax:
[SKI[P] n | [SKI[P]] PAGE] [NODUP[LICATES] | DUP[LICATES]]
The SKIP option is not supported in iSQL*Plus
---10 BTITLE
设置格式化页脚
SQL> HELP BTITLE ;
BTITLE
------
Places and formats a specified title at the bottom of each report
page, or lists the current BTITLE definition.
BTI[TLE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
TAB n R[IGHT]
---11 CHANGE
将SQL*Plus缓冲区当前行的old_value替换为new_value;
Changes the first occurrence of the specified text on the current
line of the SQL buffer. The buffer has no command history list and
does not record SQL*Plus commands.
C[HANGE] sepchar old [sepchar [new[sepchar]]]
Not available in iSQL*Plus
---12 CLEAR
清除或重设当前指定配置的值。CLEAR BUFFER 即清空缓存区所有行。
Resets or erases the current value or setting for the specified option.
CL[EAR] option ...
where option represents one of the following clauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SCR[EEN]
SQL
TIMI[NG]
CLEAR SCREEN is not available in iSQL*Plus
例子：
CLEAR BUFFER;
CLEAR SQL;
---13 COLUMN
用于格式化列标题和列数据的现实格式。
Specifies display attributes for a given column, such as:
- text for the column heading
- alignment for the column heading
- format for NUMBER data
- wrapping of column data
Also lists the current display attributes for a single column
or all columns.
COL[UMN] [{column | expr} [option ...] ]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
例子：
---14 COMPUTE
In combination with the BREAK command, calculates and prints
summary lines using various standard computations. Also lists
all COMPUTE definitions.
COMP[UTE] [function [LAB[EL] text] ...
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...]
---15 CONNECT
连接数据数据库命令.如果是SYSDBA或SYSOPER账号，则必须在后面加上AS {SYSOPER | SYSDBA}，否则会报错。
CONNECT
-------
Connects a given username to the Oracle Database. When you run a
CONNECT command, the site profile, glogin.sql, and the user profile,
login.sql, are processed in that order. CONNECT does not reprompt
for username or password if the initial connection does not succeed.
CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA}]]
where logon has the following syntax:
username[/password][@connect_identifier]
例子：
SQL>CONNECT SYSDBA/U*jd24))(&^ AS SYSDBA;
SQL>CONN SYSTEM/KERRY123456
已连接。
SQL> HELP CONN
---16 COPY
COPY可以完成远程数据库、本地数据库或ORACLE数据库与非ORACLE 数据库之间的数据复制
Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
where database has the following syntax:
username[/password]@connect_identifier
---17 DEFINE
定义替换变量并附值给它，或列出指定赋值变量值或所有变量
Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.
DEF[INE] [variable] | [variable = text]
例子：
SQL> DEFINE
DEFINE _DATE = "09-6月 -11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYSTEM" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
DEFINE _RC = "1" (CHAR)
SQL> DEFINE _EDITOR
DEFINE _EDITOR = "Notepad" (CHAR)
---18 DEL
DEL删除命令缓冲区的某一行。 DEL 删除当前行 DEL n 删除第n行。
Deletes one or more lines of the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.
DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Not available in iSQL*Plus
例子：
DEL 1;
---19 DESCRIBE
返回数据库所存储对象的描述。这个命令大家用得相当多，比如查看表的数据字典等，在此不细说。
Lists the column definitions for a table, view, or synonym
or the specifications for a function or procedure.
DESC[RIBE] {[schema.]object[@connect_identifier]}
例子：
DESC SCOTT.EMP;
SQL> DESC SCOTT.EMP;
名称 是否为空? 类型
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
---20 DISCONNECT
让当前用户从ORACLE数据库断开，但是不退出 SQL*PLUS
Commits pending changes to the database and logs the current
user out of Oracle, but does not exit SQL*Plus.
In SQL*Plus command line, use EXIT or QUIT to log out of Oracle
and return control to your computer''s operating system.
In iSQL*Plus, click the Logout button to log out of Oracle.
DISC[ONNECT]
例子
SQL> DISCONNECT;
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
如果想退出SQL*PLUS程序，可以用EXIT或QUIT
将SQL*Plus缓冲区当前行的old_value替换为new_value;
Changes the first occurrence of the specified text on the current
line of the SQL buffer. The buffer has no command history list and
does not record SQL*Plus commands.
C[HANGE] sepchar old [sepchar [new[sepchar]]]
Not available in iSQL*Plus
---12 CLEAR
清除或重设当前指定配置的值。CLEAR BUFFER 即清空缓存区所有行。
Resets or erases the current value or setting for the specified option.
CL[EAR] option ...
where option represents one of the following clauses:
BRE[AKS]
BUFF[ER]
COL[UMNS]
COMP[UTES]
SCR[EEN]
SQL
TIMI[NG]
CLEAR SCREEN is not available in iSQL*Plus
例子：
CLEAR BUFFER;
CLEAR SQL;
---13 COLUMN
用于格式化列标题和列数据的现实格式。
Specifies display attributes for a given column, such as:
- text for the column heading
- alignment for the column heading
- format for NUMBER data
- wrapping of column data
Also lists the current display attributes for a single column
or all columns.
COL[UMN] [{column | expr} [option ...] ]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
例子：
---14 COMPUTE
In combination with the BREAK command, calculates and prints
summary lines using various standard computations. Also lists
all COMPUTE definitions.
COMP[UTE] [function [LAB[EL] text] ...
OF {expr|column|alias} ...
ON {expr|column|alias|REPORT|ROW} ...]
---15 CONNECT
连接数据数据库命令.如果是SYSDBA或SYSOPER账号，则必须在后面加上AS {SYSOPER | SYSDBA}，否则会报错。
CONNECT
-------
Connects a given username to the Oracle Database. When you run a
CONNECT command, the site profile, glogin.sql, and the user profile,
login.sql, are processed in that order. CONNECT does not reprompt
for username or password if the initial connection does not succeed.
CONN[ECT] [{logon|/|proxy} [AS {SYSOPER|SYSDBA}]]
where logon has the following syntax:
username[/password][@connect_identifier]
例子：
SQL>CONNECT SYSDBA/U*jd24))(&^ AS SYSDBA;
SQL>CONN SYSTEM/KERRY123456
已连接。
SQL> HELP CONN
---16 COPY
COPY可以完成远程数据库、本地数据库或ORACLE数据库与非ORACLE 数据库之间的数据复制
Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.
COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query
where database has the following syntax:
username[/password]@connect_identifier
---17 DEFINE
定义替换变量并附值给它，或列出指定赋值变量值或所有变量
Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.
DEF[INE] [variable] | [variable = text]
例子：
SQL> DEFINE
DEFINE _DATE = "09-6月 -11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SYSTEM" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000100" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
With the Partitioning, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE = "1002000100" (CHAR)
DEFINE _RC = "1" (CHAR)
SQL> DEFINE _EDITOR
DEFINE _EDITOR = "Notepad" (CHAR)
---18 DEL
DEL删除命令缓冲区的某一行。 DEL 删除当前行 DEL n 删除第n行。
Deletes one or more lines of the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.
DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Not available in iSQL*Plus
例子：
DEL 1;
---19 DESCRIBE
返回数据库所存储对象的描述。这个命令大家用得相当多，比如查看表的数据字典等，在此不细说。
Lists the column definitions for a table, view, or synonym
or the specifications for a function or procedure.
DESC[RIBE] {[schema.]object[@connect_identifier]}
例子：
DESC SCOTT.EMP;
SQL> DESC SCOTT.EMP;
名称 是否为空? 类型
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
---20 DISCONNECT
让当前用户从ORACLE数据库断开，但是不退出 SQL*PLUS
Commits pending changes to the database and logs the current
user out of Oracle, but does not exit SQL*Plus.
In SQL*Plus command line, use EXIT or QUIT to log out of Oracle
and return control to your computer''s operating system.
In iSQL*Plus, click the Logout button to log out of Oracle.
DISC[ONNECT]
例子
SQL> DISCONNECT;
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
如果想退出SQL*PLUS程序，可以用EXIT或QUIT
---21 EDIT
调用操作系统文本编辑器打开指定文件或编辑缓冲区的内容。如果不指定文件名,例如 EDIT F:/，则会提示您创建新的文件。
文本编辑器在Windows下是Notepad， 在Unix或Linux下是Vi或emacs，可以用DEFINE _EDITOR = ‘编辑器名字’改变默认编辑器
Invokes an operating system text editor on the contents of the
specified file or on the contents of the SQL buffer. The buffer
has no command history list and does not record SQL*Plus commands.
ED[IT] [file_name[.ext]]
Not available in iSQL*Plus
例子：不指定文件名
SQL> EDIT F:/
会创建一个*。sql文件，如下图所示
SQL> EDIT F:/123.TXT 创建123.txt文件。如下图所示
---22 EXECUTE
执行存储过程或PL/SQL块
Executes a single PL/SQL statement or runs a stored procedure.
EXEC[UTE] statement
---23 EXIT
退出SQL*PLUS
Commits or rolls back all pending changes, logs out of Oracle,
terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops
processing the current iSQL*Plus script and returns focus to the
Workspace. There is no way to access the return code in iSQL*Plus.
In iSQL*Plus click the Logout button to log out of Oracle Database.
{EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]
---24 GET
将SQL文件的内容读入SQL*Plus缓冲区。查看保存的脚本
Loads a SQL statement or PL/SQL block from a script into the SQL buffer.
In iSQL*Plus click the Load Script button to load a script into the
Workspace. The buffer has no command history list and does not record
SQL*Plus commands.
GET [FILE] file_name[.ext] [LIST | NOLIST]
Not available in iSQL*Plus
例子：SQL> GET FILE F:/11.EXT LIST
1 1111111
2* 111111
其中1,2所列内容是我放在11.EXT中的内容
---25 HOST
HOST
----
Executes an operating system command without leaving SQL*Plus.
Enter HOST without command to display an operating system prompt.
You can then enter multiple operating system commands.
HO[ST] [command]
Not available in iSQL*Plus
---26 INPUT
INPUT
-----
Adds one or more new lines of text after the current line in the
SQL buffer. The buffer has no command history list and does not
record SQL*Plus commands.
I[NPUT] [text]
Not available in iSQL*Plus
---27 LIST
列出SQL*Plus缓存区的所有行或指定的N行语句。例如 LIST N；
LIST
----
Lists one or more lines of the most recently executed SQL command
or PL/SQL block which is stored in the SQL buffer. Enter LIST with
no clauses to list all lines. In SQL*Plus command-line you can also
use ";" to list all the lines in the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.
L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
例子：
SQL> LIST
1* SELECT * FROM SCOTT.EMP
---28 PASSWORD;
更改数据库用户密码，不指定用户，则默认跟改SYS用户账号
PASSWORD
--------
Allows you to change a password without displaying it on an input device.
In iSQL*Plus, use the Password screen to change your password.
PASSW[ORD] [username]
Not available in iSQL*Plus
例子：
SQL> PASSWORD
更改 SYS 的口令
旧口令: ******
新口令:******
---29 PAUSE;
PAUSE
-----
Displays the specified text then waits for the user to press RETURN.
In iSQL*Plus, displays the Next Page button which the user must click
to continue.
PAU[SE] [text]
---30 PRINT;
显示当前绑定变量值或列出所有绑定变量
PRINT
-----
Displays the current values of bind variables, or lists all bind
variables.
PRI[NT] [variable ...]
调用操作系统文本编辑器打开指定文件或编辑缓冲区的内容。如果不指定文件名,例如 EDIT F:/，则会提示您创建新的文件。
文本编辑器在Windows下是Notepad， 在Unix或Linux下是Vi或emacs，可以用DEFINE _EDITOR = ‘编辑器名字’改变默认编辑器
Invokes an operating system text editor on the contents of the
specified file or on the contents of the SQL buffer. The buffer
has no command history list and does not record SQL*Plus commands.
ED[IT] [file_name[.ext]]
Not available in iSQL*Plus
例子：不指定文件名
SQL> EDIT F:/
会创建一个*。sql文件，如下图所示
SQL> EDIT F:/123.TXT 创建123.txt文件。如下图所示
---22 EXECUTE
执行存储过程或PL/SQL块
Executes a single PL/SQL statement or runs a stored procedure.
EXEC[UTE] statement
---23 EXIT
退出SQL*PLUS
Commits or rolls back all pending changes, logs out of Oracle,
terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops
processing the current iSQL*Plus script and returns focus to the
Workspace. There is no way to access the return code in iSQL*Plus.
In iSQL*Plus click the Logout button to log out of Oracle Database.
{EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]
---24 GET
将SQL文件的内容读入SQL*Plus缓冲区。查看保存的脚本
Loads a SQL statement or PL/SQL block from a script into the SQL buffer.
In iSQL*Plus click the Load Script button to load a script into the
Workspace. The buffer has no command history list and does not record
SQL*Plus commands.
GET [FILE] file_name[.ext] [LIST | NOLIST]
Not available in iSQL*Plus
例子：SQL> GET FILE F:/11.EXT LIST
1 1111111
2* 111111
其中1,2所列内容是我放在11.EXT中的内容
---25 HOST
HOST
----
Executes an operating system command without leaving SQL*Plus.
Enter HOST without command to display an operating system prompt.
You can then enter multiple operating system commands.
HO[ST] [command]
Not available in iSQL*Plus
---26 INPUT
INPUT
-----
Adds one or more new lines of text after the current line in the
SQL buffer. The buffer has no command history list and does not
record SQL*Plus commands.
I[NPUT] [text]
Not available in iSQL*Plus
---27 LIST
列出SQL*Plus缓存区的所有行或指定的N行语句。例如 LIST N；
LIST
----
Lists one or more lines of the most recently executed SQL command
or PL/SQL block which is stored in the SQL buffer. Enter LIST with
no clauses to list all lines. In SQL*Plus command-line you can also
use ";" to list all the lines in the SQL buffer. The buffer has no
command history list and does not record SQL*Plus commands.
L[IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]
例子：
SQL> LIST
1* SELECT * FROM SCOTT.EMP
---28 PASSWORD;
更改数据库用户密码，不指定用户，则默认跟改SYS用户账号
PASSWORD
--------
Allows you to change a password without displaying it on an input device.
In iSQL*Plus, use the Password screen to change your password.
PASSW[ORD] [username]
Not available in iSQL*Plus
例子：
SQL> PASSWORD
更改 SYS 的口令
旧口令: ******
新口令:******
---29 PAUSE;
PAUSE
-----
Displays the specified text then waits for the user to press RETURN.
In iSQL*Plus, displays the Next Page button which the user must click
to continue.
PAU[SE] [text]
---30 PRINT;
显示当前绑定变量值或列出所有绑定变量
-----
Displays the current values of bind variables, or lists all bind
variables.
PRI[NT] [variable ...]
---31 PROMPT;
使用PROMPT命令可以在屏幕上输出一行数据。这种方式有助于在脚本文件中向用户
传递相应信息。比如我一个脚本文件Test.sql里面有下面这一段脚本：
PROMPT
PROMPT '显示5条员工信息'
PROMPT
SELECT * FROM SCOTT.EMP WHERE ROWNUM < 5;
PROMPT
------
Sends the specified message or a blank line to the users screen.
PRO[MPT] [text]
例子：
SQL> PROMPT HELLO KERRY
HELLO KERRY
---32 QUIT;
提交或回滚所有的变化，退出ORACLE，中断SQL*PLUS。
QUIT (Identical to EXIT)
----
Commits or rolls back all pending changes, logs out of Oracle,
terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops
processing the current iSQL*Plus script and returns focus to the
Workspace. There is no way to access the return code in iSQL*Plus.
In iSQL*Plus click the Logout button to log out of Oracle Database.
{QUIT|EXIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]
---33 RECOVER;
RECOVER
-------
Performs media recovery on one or more tablespaces, one or more
datafiles, or the entire database.
Because of possible network timeouts, it is recommended that you
use SQL*Plus command-line, not iSQL*Plus, for long running DBA
operations such as RECOVER.
RECOVER {general | managed} | BEGIN BACKUP | END BACKUP}
---34 REMARK
REMARK
------
Begins a comment in a script. SQL*Plus does not interpret the comment
as a command.
REM[ARK]
---35 REPFOOTER;
REPFOOTER
---------
Places and formats a footer at the bottom of a report, or lists the
REPFOOTER definition.
REPF[OOTER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
TAB n R[IGHT]
---36 REPFOOTER;
REPFOOTER
---------
Places and formats a footer at the bottom of a report, or lists the
REPFOOTER definition.
REPF[OOTER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
TAB n R[IGHT]
--37 HELP RESERVED WORDS (SQL);
查看SQL中保留关键字。有时候在写脚本时应该避免使用SQL保留关键字做变量、表名、字段名等
如果你不确认自己定义的变量是否是保留关键字，就可以用这个命令看看。如果你只想查看SQL的保留
关键字 可以用 HELP RESERVED WORDS (SQL),如果想查看两者就直接输入HELP RESERVED WORDS 即可。
RESERVED WORDS (SQL)
-----------------------
PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
for identifier names (unless enclosed in "quotes").
---38 HELP RESERVED WORDS (PL/SQL);
查看PL/SQL中保留关键字。类似上面命令。
RESERVED WORDS (SQL)
--------------------
SQL Reserved Words have special meaning in SQL, and may not be used for
identifier names unless enclosed in "quotes".
An asterisk (*) indicates words are also ANSI Reserved Words.
Oracle prefixes implicitly generated schema object and subobject names
with "SYS_". To avoid name resolution conflict, Oracle discourages you
from prefixing your schema object and subobject names with "SYS_".
---39 RUN
运行缓存区中保存的语句。
RUN
---
Lists and executes the most recently executed SQL command or
PL/SQL block which is stored in the SQL buffer. The buffer has
no command history list and does not record SQL*Plus commands.
R[UN]
---40 SAVE;
将SQL*Plus缓冲区的内容保存到所指定的文件中。如果用户没有指定文件扩展名，则默认
扩展名为SQL，后面的CREATE表示如果文件不存在创建一个文件。REPLACE表示如果文件不
存在则创建，否则替换覆盖文件中的内容。 APPEND把缓冲区的内容追加到文件末尾。
SAVE
----
Saves the contents of the SQL buffer in a script. In iSQL*Plus, click
the Save Script button to save the Workspace contents to a script. The
buffer has no command history list and does not record SQL*Plus commands.
SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Not available in iSQL*Plus
例子：
SQL> save F:/ttt.sql
使用PROMPT命令可以在屏幕上输出一行数据。这种方式有助于在脚本文件中向用户
传递相应信息。比如我一个脚本文件Test.sql里面有下面这一段脚本：
PROMPT
PROMPT '显示5条员工信息'
PROMPT
SELECT * FROM SCOTT.EMP WHERE ROWNUM < 5;
PROMPT
------
Sends the specified message or a blank line to the users screen.
PRO[MPT] [text]
例子：
SQL> PROMPT HELLO KERRY
HELLO KERRY
---32 QUIT;
提交或回滚所有的变化，退出ORACLE，中断SQL*PLUS。
QUIT (Identical to EXIT)
----
Commits or rolls back all pending changes, logs out of Oracle,
terminates SQL*Plus and returns control to the operating system.
In iSQL*Plus, commits or rolls back all pending changes, stops
processing the current iSQL*Plus script and returns focus to the
Workspace. There is no way to access the return code in iSQL*Plus.
In iSQL*Plus click the Logout button to log out of Oracle Database.
{QUIT|EXIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK]
---33 RECOVER;
RECOVER
-------
Performs media recovery on one or more tablespaces, one or more
datafiles, or the entire database.
Because of possible network timeouts, it is recommended that you
use SQL*Plus command-line, not iSQL*Plus, for long running DBA
operations such as RECOVER.
RECOVER {general | managed} | BEGIN BACKUP | END BACKUP}
---34 REMARK
REMARK
------
Begins a comment in a script. SQL*Plus does not interpret the comment
as a command.
REM[ARK]
---35 REPFOOTER;
REPFOOTER
---------
Places and formats a footer at the bottom of a report, or lists the
REPFOOTER definition.
REPF[OOTER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
TAB n R[IGHT]
---36 REPFOOTER;
REPFOOTER
---------
Places and formats a footer at the bottom of a report, or lists the
REPFOOTER definition.
REPF[OOTER] [PAGE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
TAB n R[IGHT]
--37 HELP RESERVED WORDS (SQL);
查看SQL中保留关键字。有时候在写脚本时应该避免使用SQL保留关键字做变量、表名、字段名等
如果你不确认自己定义的变量是否是保留关键字，就可以用这个命令看看。如果你只想查看SQL的保留
关键字 可以用 HELP RESERVED WORDS (SQL),如果想查看两者就直接输入HELP RESERVED WORDS 即可。
RESERVED WORDS (SQL)
-----------------------
PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
for identifier names (unless enclosed in "quotes").
---38 HELP RESERVED WORDS (PL/SQL);
查看PL/SQL中保留关键字。类似上面命令。
RESERVED WORDS (SQL)
--------------------
SQL Reserved Words have special meaning in SQL, and may not be used for
identifier names unless enclosed in "quotes".
An asterisk (*) indicates words are also ANSI Reserved Words.
Oracle prefixes implicitly generated schema object and subobject names
with "SYS_". To avoid name resolution conflict, Oracle discourages you
from prefixing your schema object and subobject names with "SYS_".
---39 RUN
运行缓存区中保存的语句。
RUN
---
Lists and executes the most recently executed SQL command or
PL/SQL block which is stored in the SQL buffer. The buffer has
no command history list and does not record SQL*Plus commands.
R[UN]
---40 SAVE;
将SQL*Plus缓冲区的内容保存到所指定的文件中。如果用户没有指定文件扩展名，则默认
扩展名为SQL，后面的CREATE表示如果文件不存在创建一个文件。REPLACE表示如果文件不
存在则创建，否则替换覆盖文件中的内容。 APPEND把缓冲区的内容追加到文件末尾。
SAVE
----
Saves the contents of the SQL buffer in a script. In iSQL*Plus, click
the Save Script button to save the Workspace contents to a script. The
buffer has no command history list and does not record SQL*Plus commands.
SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Not available in iSQL*Plus
例子：
SQL> save F:/ttt.sql
---41 SET
设置系统变量以及SQL*PLUS环境变量
----------------------------------------SET 命令选项--------------------------------------
---SET TIME {ON | OFF}
控制当前时间的显示。取值为ON时，表示在每个命令提示符前显示当前系统时间；取值为OFF时不显示当前的系统时间。
---SET PAUSE {OFF| ON | TEXT}
设置SQL*Plus输出结果时是否滚动显示。
当取值为NO时表示输出结果每一页都暂停，用户按回车键后继续显示； 按CTRL + C退出每一页显示。
当取值为OFF时表示一次性全部输出
当取值为字符串时，每次暂停都显示该字符串
SET PAUSE ON;
SET PAUSE '按回车键继续'
SELECT * FROM EMP;
---SET PAGESIZE {14 | N};
设置每页打印的行数，该值包括NEWPAGE 设置的空行数
---SET NEWPAGE {1| N | NONE}
设置每页打印标题前的空行数，默认值为1
---SET LINESIZE
设置每行打印字符数，默认每行打印80个字符。可以设置LINESIZE，以避免折叠显示。
---SET NUMFORMAT
格式掩码
字符 示例 说明
9 999 查询结果中数字替换格式中的掩码
0 999.00 格式中的掩码屏蔽掉查询结果中的数字。
$ $999 在查询结果中的数字添加美元前缀。
S S999 为数字显示符号类型，通常用于显示查询结果中的正负数字
PR 999PR 在尖括号中显示数字。
D OR . 99D99.99 在字符D或字符小数点"."位置上放置小数点
, 999,99 在字符","位置上放置逗号
RN OR rn RN 根据字符的大小写形式，以大写或者小写的形式显示罗马数字。
---SET TIMING { ON | OFF }
显示SQL命令消耗的系统时间。
---42 SHOW
显示SQL*Plus系统变量值或当前SQL*Plus的环境，
SHOW
----
Shows the value of a SQL*Plus system variable, or the current
SQL*Plus environment. SHOW SGA requires a DBA privileged login.
SHO[W] option
例子：
SQL> SHOW SGA;
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 272632708 bytes
Database Buffers 331350016 bytes
Redo Buffers 7135232 bytes
---43 SHUTDOWN
关闭数据库实例，使用SHUTDOWN时有5个参数可以选择。
SHUTDOWN
--------
Shuts down a currently running Oracle Database instance, optionally
closing and dismounting a database.
SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]
SHUTDOWN ABORT 强制关闭，可能会丢失部分数据。如果数据库重启实例并打开数据库时，后台进程SMON会执行恢复操作
以终止方式关闭时， ORACLE将执行以下操作
1：阻止任何用户建立新的连接。同时阻止当前连接的用户开始新的事务。
2：立即终止当前正在执行的SQL语句。
3：任何未提交的事务均不被回退。
4：立即断开所有用户的连接，关闭、卸载数据库，并终止实例。
SHUTDOWN NORMAL 正常关闭方式
以正常方式关闭时，ORACLE将执行以下操作
1：阻止任何用户建立新的连接
2：等待当前所有正在连接的用户主动断开连接，已经连接的用户能够继续当前的操作
3：一旦所有的用户都断开连接，即立即关闭、卸载数据库，并终止实例。
SHUTDOWN IMMEDIATE 立即关闭方式
以立即关闭方式时，ORACLE将执行以下操作
1：阻止任何用户建立新的连接。同时阻止当前连接的用户开始任何新的事务。
2：任何未提交的事务均被回退。
3：ORACLE不再等待用户主动断开连接，而是直接关闭、卸载数据库，并终止实例。
SHUTDOWN TRANSACTIONAL 事务关闭方式介于正常关闭方式与立即关闭方式之间。它能够在尽可能短的时间内
关闭数据库，但是能够保证当前所有活动事务都可以被提交。
以事务关闭方式时，ORACLE将执行以下操作
1：阻止任何用户建立新的连接，同时阻止当前连接的用户开始任何新的事务。
2；等待所有未提交的活动事务提交完毕，然后立即断开用户连接。
3：关闭、卸载数据库，并终止实例。
---44 SPOOL;
将SQL*Plus中输出的结果复制到filename所指定的文件中。
SPOOL
-----
Stores query results in a file, or optionally sends the file to a printer.
In iSQL*Plus, use the Preferences screen to direct output to a file.
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Not available in iSQL*Plus
例子：
SQL> SPOOL F:/TEMP.TEXT;
SQL> SELECT * FROM SCOTT.EMP;
SQL> SPOOL OFF;
---45 SQLPLUS
运行SQL*Plus
SQLPLUS
-------
Starts SQL*Plus from the operating system prompt.
SQLPLUS [ [option] [logon] [start] ]
where option has the following syntax:
-H[ELP]
| -V[ERSION]
| [[-C[OMPATIBILITY] x.y[.z]] [-L[OGON]]
[-M[ARKUP] markup_option] [-R[ESTRICT] {1|2|3}] [-S[ILENT]]]
and where markup_option has the following syntax:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
and where logon has the following syntax:
{username[/password][@connect_identifier] | /}
[AS {SYSOPER|SYSDBA}] | /NOLOG
and where start has the following syntax:
@{url|filename[.ext]} [arg ...]
---46 START
将filename文件的内容读入SQL*Plus缓冲区，然后执行缓冲区的脚本（运行脚本文件）
START
-----
Runs the SQL*Plus statements in the specified script. The script can be
called from the local file system or a web server. Only the url form is
supported in iSQL*Plus.
STA[RT] {url|file_name[.ext]} [arg ...]
where url supports HTTP and FTP protocols in the form:
http://host.domain/script.sql
---47 STARTUP
启动ORACLE实例。
STARTUP
-------
Starts an Oracle instance with several options, including mounting,
and opening a database.
STARTUP options | upgrade_options
where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] |
NOMOUNT ]
where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER
and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
--- 48 STORE
STORE
-----
Saves attributes of the current SQL*Plus environment in a script.
STORE {SET} file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Not available in iSQL*Plus
--- 49 TIMING
TIMING
------
Records timing data for an elapsed time period, lists the current
timer''s name and timing data, or lists the number of active timers.
TIMI[NG] [START text|SHOW|STOP]
---50 TTITLE;
设置页眉选项。
TTITLE
------
Places and formats a title at the top of each report page.
Enter TTITLE with no clause to list its current definition.
The old form of TTITLE is used if only a single word or
a string in quotes follows the TTITLE command.
TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
TAB n R[IGHT]
例子：
TTITLE ON 开启页眉
TTITLE OFF 关闭页眉
SQL> TTITLE CENTER '职工信息表'
SQL> SELECT * FROM SCOTT.EMP;
设置系统变量以及SQL*PLUS环境变量
----------------------------------------SET 命令选项--------------------------------------
---SET TIME {ON | OFF}
控制当前时间的显示。取值为ON时，表示在每个命令提示符前显示当前系统时间；取值为OFF时不显示当前的系统时间。
---SET PAUSE {OFF| ON | TEXT}
设置SQL*Plus输出结果时是否滚动显示。
当取值为NO时表示输出结果每一页都暂停，用户按回车键后继续显示； 按CTRL + C退出每一页显示。
当取值为OFF时表示一次性全部输出
当取值为字符串时，每次暂停都显示该字符串
SET PAUSE ON;
SET PAUSE '按回车键继续'
SELECT * FROM EMP;
---SET PAGESIZE {14 | N};
设置每页打印的行数，该值包括NEWPAGE 设置的空行数
---SET NEWPAGE {1| N | NONE}
设置每页打印标题前的空行数，默认值为1
---SET LINESIZE
设置每行打印字符数，默认每行打印80个字符。可以设置LINESIZE，以避免折叠显示。
---SET NUMFORMAT
格式掩码
字符 示例 说明
9 999 查询结果中数字替换格式中的掩码
0 999.00 格式中的掩码屏蔽掉查询结果中的数字。
$ $999 在查询结果中的数字添加美元前缀。
S S999 为数字显示符号类型，通常用于显示查询结果中的正负数字
PR 999PR 在尖括号中显示数字。
D OR . 99D99.99 在字符D或字符小数点"."位置上放置小数点
, 999,99 在字符","位置上放置逗号
RN OR rn RN 根据字符的大小写形式，以大写或者小写的形式显示罗马数字。
---SET TIMING { ON | OFF }
显示SQL命令消耗的系统时间。
---42 SHOW
显示SQL*Plus系统变量值或当前SQL*Plus的环境，
SHOW
----
Shows the value of a SQL*Plus system variable, or the current
SQL*Plus environment. SHOW SGA requires a DBA privileged login.
SHO[W] option
例子：
SQL> SHOW SGA;
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 272632708 bytes
Database Buffers 331350016 bytes
Redo Buffers 7135232 bytes
---43 SHUTDOWN
关闭数据库实例，使用SHUTDOWN时有5个参数可以选择。
SHUTDOWN
--------
Shuts down a currently running Oracle Database instance, optionally
closing and dismounting a database.
SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]
SHUTDOWN ABORT 强制关闭，可能会丢失部分数据。如果数据库重启实例并打开数据库时，后台进程SMON会执行恢复操作
以终止方式关闭时， ORACLE将执行以下操作
1：阻止任何用户建立新的连接。同时阻止当前连接的用户开始新的事务。
2：立即终止当前正在执行的SQL语句。
3：任何未提交的事务均不被回退。
4：立即断开所有用户的连接，关闭、卸载数据库，并终止实例。
SHUTDOWN NORMAL 正常关闭方式
以正常方式关闭时，ORACLE将执行以下操作
1：阻止任何用户建立新的连接
2：等待当前所有正在连接的用户主动断开连接，已经连接的用户能够继续当前的操作
3：一旦所有的用户都断开连接，即立即关闭、卸载数据库，并终止实例。
SHUTDOWN IMMEDIATE 立即关闭方式
以立即关闭方式时，ORACLE将执行以下操作
1：阻止任何用户建立新的连接。同时阻止当前连接的用户开始任何新的事务。
2：任何未提交的事务均被回退。
3：ORACLE不再等待用户主动断开连接，而是直接关闭、卸载数据库，并终止实例。
SHUTDOWN TRANSACTIONAL 事务关闭方式介于正常关闭方式与立即关闭方式之间。它能够在尽可能短的时间内
关闭数据库，但是能够保证当前所有活动事务都可以被提交。
以事务关闭方式时，ORACLE将执行以下操作
1：阻止任何用户建立新的连接，同时阻止当前连接的用户开始任何新的事务。
2；等待所有未提交的活动事务提交完毕，然后立即断开用户连接。
3：关闭、卸载数据库，并终止实例。
---44 SPOOL;
将SQL*Plus中输出的结果复制到filename所指定的文件中。
SPOOL
-----
Stores query results in a file, or optionally sends the file to a printer.
In iSQL*Plus, use the Preferences screen to direct output to a file.
SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Not available in iSQL*Plus
例子：
SQL> SPOOL F:/TEMP.TEXT;
SQL> SELECT * FROM SCOTT.EMP;
SQL> SPOOL OFF;
---45 SQLPLUS
运行SQL*Plus
SQLPLUS
-------
Starts SQL*Plus from the operating system prompt.
SQLPLUS [ [option] [logon] [start] ]
where option has the following syntax:
-H[ELP]
| -V[ERSION]
| [[-C[OMPATIBILITY] x.y[.z]] [-L[OGON]]
[-M[ARKUP] markup_option] [-R[ESTRICT] {1|2|3}] [-S[ILENT]]]
and where markup_option has the following syntax:
HTML [ON|OFF] [HEAD text] [BODY text] [TABLE text]
[ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]
and where logon has the following syntax:
{username[/password][@connect_identifier] | /}
[AS {SYSOPER|SYSDBA}] | /NOLOG
and where start has the following syntax:
@{url|filename[.ext]} [arg ...]
---46 START
将filename文件的内容读入SQL*Plus缓冲区，然后执行缓冲区的脚本（运行脚本文件）
START
-----
Runs the SQL*Plus statements in the specified script. The script can be
called from the local file system or a web server. Only the url form is
supported in iSQL*Plus.
STA[RT] {url|file_name[.ext]} [arg ...]
where url supports HTTP and FTP protocols in the form:
http://host.domain/script.sql
---47 STARTUP
启动ORACLE实例。
STARTUP
-------
Starts an Oracle instance with several options, including mounting,
and opening a database.
STARTUP options | upgrade_options
where options has the following syntax:
[FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] |
[ OPEN [open_options] [dbname] ] |
NOMOUNT ]
where open_options has the following syntax:
READ {ONLY | WRITE [RECOVER]} | RECOVER
and where upgrade_options has the following syntax:
[PFILE=filename] {UPGRADE | DOWNGRADE} [QUIET]
--- 48 STORE
STORE
-----
Saves attributes of the current SQL*Plus environment in a script.
STORE {SET} file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Not available in iSQL*Plus
--- 49 TIMING
TIMING
------
Records timing data for an elapsed time period, lists the current
timer''s name and timing data, or lists the number of active timers.
TIMI[NG] [START text|SHOW|STOP]
---50 TTITLE;
设置页眉选项。
TTITLE
------
Places and formats a title at the top of each report page.
Enter TTITLE with no clause to list its current definition.
The old form of TTITLE is used if only a single word or
a string in quotes follows the TTITLE command.
TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]
where printspec represents one or more of the following clauses:
COL n LE[FT] BOLD
S[KIP] [n] CE[NTER] FORMAT text
TAB n R[IGHT]
例子：
TTITLE ON 开启页眉
TTITLE OFF 关闭页眉
SQL> TTITLE CENTER '职工信息表'
SQL> SELECT * FROM SCOTT.EMP;
---51 UNDEFINE;
删除一个或多个你定义的替换变量。
UNDEFINE
--------
Deletes one or more substitution variables that you defined either
explicitly (with the DEFINE command), or implicitly (with a START
command argument).
UNDEF[INE] variable ...
---52 VARIABLE;
VARIABLE
--------
Declares a bind variable that can be referenced in PL/SQL, or
lists the current display characteristics for a single variable
or all variables.
VAR[IABLE] [variable [type]]
where type represents one of the following:
NUMBER CHAR CHAR (n [CHAR|BYTE])
NCHAR NCHAR (n) VARCHAR2 (n [CHAR|BYTE])
NVARCHAR2 (n) CLOB NCLOB
REFCURSOR BINARY_FLOAT BINARY_DOUBLE
---53 SQL> WHENEVER OSERROR
WHENEVER OSERROR
----------------
Performs the specified action (exits SQL*Plus by default) if an
operating system error occurs (such as a file writing error).
In iSQL*Plus, performs the specified action (stops the current
script by default) and returns focus to the Workspace if an
operating system error occurs.
WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]
[COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}
---54
WHENEVER SQLERROR
-----------------
Performs the specified action (exits SQL*Plus by default) if a
SQL command or PL/SQL block generates an error.
In iSQL*Plus, performs the specified action (stops the current
script by default) and returns focus to the Workspace if a SQL
command or PL/SQL block generates an error.
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}
删除一个或多个你定义的替换变量。
UNDEFINE
--------
Deletes one or more substitution variables that you defined either
explicitly (with the DEFINE command), or implicitly (with a START
command argument).
UNDEF[INE] variable ...
---52 VARIABLE;
VARIABLE
--------
Declares a bind variable that can be referenced in PL/SQL, or
lists the current display characteristics for a single variable
or all variables.
VAR[IABLE] [variable [type]]
where type represents one of the following:
NUMBER CHAR CHAR (n [CHAR|BYTE])
NCHAR NCHAR (n) VARCHAR2 (n [CHAR|BYTE])
NVARCHAR2 (n) CLOB NCLOB
REFCURSOR BINARY_FLOAT BINARY_DOUBLE
---53 SQL> WHENEVER OSERROR
WHENEVER OSERROR
----------------
Performs the specified action (exits SQL*Plus by default) if an
operating system error occurs (such as a file writing error).
In iSQL*Plus, performs the specified action (stops the current
script by default) and returns focus to the Workspace if an
operating system error occurs.
WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable]
[COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}
---54
WHENEVER SQLERROR
-----------------
Performs the specified action (exits SQL*Plus by default) if a
SQL command or PL/SQL block generates an error.
In iSQL*Plus, performs the specified action (stops the current
script by default) and returns focus to the Workspace if a SQL
command or PL/SQL block generates an error.
WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
[COMMIT|ROLLBACK] | CONTINUE [COMMIT|ROLLBACK|NONE]}
扫描上面二维码关注我
如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
