ORACLE SQL*PLUS 命令大全

简介:

其实网上已经有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]---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
     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

---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
 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 ...]


---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 

---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
 
 格式掩码
 
 字符              示例                      说明
                999                       查询结果中数字替换格式中的掩码
                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]}

相关文章
|
9天前
|
SQL 关系型数据库 MySQL
SQL命令
【5月更文挑战第21天】SQL命令
21 3
|
1天前
|
SQL 关系型数据库 MySQL
sql数据库命令
SQL(Structured Query Language,结构化查询语言)是用于管理关系数据库的标准编程语言。以下是一些常用的 SQL 数据库命令: 1. **创建数据库**: ``
18 7
|
6天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之如何SQL同步数据到Oracle数据库中
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
15天前
|
机器学习/深度学习
T-sql 各种查询命令
T-sql 各种查询命令
|
15天前
|
SQL XML 数据库
sql导入数据库命令
在SQL Server中,数据库导入可通过多种方式实现:1) 使用SSMS的“导入数据”向导从各种源(如Excel、CSV)导入;2) BULK INSERT语句适用于导入文本文件;3) bcp命令行工具进行批量数据交换;4) OPENROWSET函数直接从外部数据源(如Excel)插入数据。在操作前,请记得备份数据库,并可能需对数据进行预处理以符合SQL Server要求。注意不同方法可能依版本和配置而异。
|
15天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标自定义异常:数据探险家的“专属警示灯”
【4月更文挑战第19天】Oracle PL/SQL中的游标自定义异常是处理数据异常的有效工具,犹如数据探险家的警示灯。通过声明异常名(如`LOW_SALARY_EXCEPTION`)并在满足特定条件(如薪资低于阈值)时使用`RAISE`抛出异常,能灵活应对复杂业务规则。示例代码展示了如何在游标操作中定义和捕获自定义异常,提升代码可读性和维护性,确保在面对数据挑战时能及时响应。掌握自定义异常,让数据管理更从容。
|
15天前
|
SQL Oracle 安全
Oracle的PL/SQL游标异常处理:从“惊涛骇浪”到“风平浪静”
【4月更文挑战第19天】Oracle PL/SQL游标异常处理确保了在数据操作中遇到的问题得以优雅解决,如`NO_DATA_FOUND`或`TOO_MANY_ROWS`等异常。通过使用`EXCEPTION`块捕获并处理这些异常,开发者可以防止程序因游标问题而崩溃。例如,当查询无结果时,可以显示定制的错误信息而不是让程序终止。掌握游标异常处理是成为娴熟的Oracle数据管理员的关键,能保证在复杂的数据环境中稳健运行。
|
15天前
|
SQL Oracle 安全
Oracle的PL/SQL异常处理方法:守护数据之旅的“魔法盾”
【4月更文挑战第19天】Oracle PL/SQL的异常处理机制是保障数据安全的关键。通过预定义异常(如`NO_DATA_FOUND`)和自定义异常,开发者能优雅地管理错误。异常在子程序中抛出后会向上传播,直到被捕获,提供了一种集中处理错误的方式。理解和善用异常处理,如同手持“魔法盾”,确保程序在面对如除数为零、违反约束等挑战时,能有效保护数据的完整性和程序的稳定性。
|
SQL Oracle 关系型数据库
Oracle SQL优化之多表连接
Oracle SQL优化之多表连接
452 0
Oracle SQL优化之多表连接
|
SQL 关系型数据库 Oracle
ORACLE SQL优化之ORA-03150&ORA-02055&ORA-02063
                                                                                                             >   
4783 0