sqlplus 命令简单整理

如果环境变量写在一个文件当中可以使用 START plusenv 设定格式 显示页行数 SHOW PAGESIZE SET PAGESIZE 60 将当前的sqlplus 的环境变量保存到一个文件中可以使用下面的命令 STORE SET plusenv sqlplus 的帮助命令 help index 罗列出所有的sqlplus的命令 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 connect  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|SYSASM}] [edition=value]]  where logon has the following syntax:      username[/password][@connect_identifier]  where proxy has the syntax:      proxyuser[username][/password][@connect_identifier]  NOTE: Brackets around username in proxy are required syntax crtl+D 退出sqlplus unix crtl +Z windows  可以使用下面的命令看创建的存储过程中编译错误 SHOW ERRORS PROCEDURE ASSIGNVL COLUMN SALARY FORMAT $99,999 HEADING 'MONTHLY SALARY' 如果是一个比较长的sqlplus 命令可以通过-进行连接 COLUMN SALARY FORMAT $99,999 - HEADING 'MONTHLY SALARY'  CTRL+C 停止sql运行    设置自动提交  终止自动提交  COMMIT COMPLETE   ---在11g 中该命令时不存在的在12c的时候才有  自动提交 下面的10行数据  SET AUTOCOMMIT 10 关闭自动提交 SET AUTOCOMMIT OFF 检查是否开启自动提交 SHOW AUTOCOMMIT 开启自动提交 SET AUTOCOMMIT ON 或者是 SET AUTOCOMMIT IMMEDIATE 编辑一个.sql的文档并执行 DEFINE _EDITOR = vi  EDIT SALES  select count(*) from v$session;  SQL> @SALES 更改原始执行sql SQL> select count(*) from v$session;   COUNT(*) ----------         31 SQL> c/session/process   1* select count(*) from v$process SQL> /   COUNT(*) ----------         34 SQL> l   1* select count(*) from v$process SQL> c/count(*)/spid   1* select spid from v$process SQL> ! [oracle@rac3 ~]$ exit SQL> / 添加 SQL> l   1* select spid from v$process SQL> i where background !=1; SQL> / no rows selected SQL> l   1  select spid from v$process   2* where background !=1 SQL>   SQL> l   1  select spid from v$process   2* where background !=1 SQL> del 2 SQL> / SPID ------------------------------------------------ 2220 SQL> CLEAR BUFFER buffer cleared SQL>  SQL> l SP2-0223: No lines in SQL buffer. SQL>  APPEND  DESC SQL> l   1   select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,   2   DECODE (BLOCK, 0, '', 'blocker') blocker,   3   DECODE (request, 0, '', 'waiter') waiter   4   from gv$lock where (ID1,ID2,TYPE) in   5   (select ID1,ID2,TYPE from gv$lock where request>0)   6*  order by blocker SQL> list 6   6*  order by blocker SQL> a desc   6*  order by blockerdesc SQL> a  desc   6*  order by blockerdesc desc SQL> list 1   1*  select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK, SQL> c/select/update   1*  update INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK, SQL> l   1   update INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,   2   DECODE (BLOCK, 0, '', 'blocker') blocker,   3   DECODE (request, 0, '', 'waiter') waiter   4   from gv$lock where (ID1,ID2,TYPE) in   5   (select ID1,ID2,TYPE from gv$lock where request>0)   6*  order by blocker desc SQL> l 3   3*  DECODE (request, 0, '', 'waiter') waiter SQL> c/decode/decodes   3*  decodes (request, 0, '', 'waiter') waiter SQL> l   1   update INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK,   2   DECODE (BLOCK, 0, '', 'blocker') blocker,   3   decodes (request, 0, '', 'waiter') waiter   4   from gv$lock where (ID1,ID2,TYPE) in   5   (select ID1,ID2,TYPE from gv$lock where request>0)   6*  order by blocker desc SQL>  备注信息： remark ---只备注一行  /* */  ---可以备注多行信息 QL> /* 使用说明  SQL> select count(*) form v$session */ SQL> remark 查询会话信息 SQL> ---数据库 set echo on  显示调用sql 的全部信息 get 本地sql名称 获取该本地sql 的内容 set verify on   命令当你的本地sql 有多个变量名称且变量名称相同的时候 需要多次输入变量赋值 set verify off 则只需要赋值一次就可以 prompt 和accept的用法  SQL> get test   1  prompt -------查询数据库的会话信息   2  accept status prompt "请输入会话状态(ACTIVE/INACTIVE):"   3* select count(*) from v$session where status='&status'; SQL> @test -------查询数据库的会话信息 请输入会话状态(ACTIVE/INACTIVE):ACTIVE old   1: select count(*) from v$session where status='&status' new   1: select count(*) from v$session where status='ACTIVE'   COUNT(*) ----------         27 SQL> @test -------查询数据库的会话信息 请输入会话状态(ACTIVE/INACTIVE):INACTIVE old   1: select count(*) from v$session where status='&status' new   1: select count(*) from v$session where status='INACTIVE'   COUNT(*) ----------          4 SQL> TTITLE right "my test" skip 3; SQL> select count(*) from dba_objects;                                                                          my test   COUNT(*) ----------      86463 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] 另外如果不关闭title 则会在下面中仍会使用该title  SQL> set lines 150 SQL> tti col 150 s3 tab 75  "my test"  SQL> select * from dba_objects where object_id=10;                                                                            my test OWNER ------------------------------------------------------------ OBJECT_NAME ------------------------------------------------------------------------------------------------------------------------------------------------------ SUBOBJECT_NAME                                                OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE                            CREATED ------------------------------------------------------------ ---------- -------------- -------------------------------------- ------------ LAST_DDL_TIM TIMESTAMP                              STATUS         TE GE SE  NAMESPACE EDITION_NAME ------------ -------------------------------------- -------------- -- -- -- ---------- ------------------------------------------------------------ SYS COLUMN column_name HEADING column_heading SQL> col count(*) heading "会话总数" SQL> select count(*) from v$session; 会话总数 ------------           32 SQL> SET UNDERLINE '+' SQL>  SQL> select count(*) from v$session; 会话总数 ++++++++++++           32 SQL> select count(*) from v$session; 会话总数 ============           33                     SQL> col count(*) heading '会话|总数'; SQL> select count(*) from v$session;     会话     总数 ==========         33 SQL> COLUMN count(*) CLEAR SQL> select count(*) from v$session;   COUNT(*) ==========         33 总的来说对会话列来说其名称可以使用其他的名称进行格式化但是需要指定heading  sqlplus 默认对数字的显示为10为 其控制参数为 SQL> show  num numwidth 10 如果超过这10为则使用科学计数法进行代替， 因此在数据库中显示的时候可以修改该值，当然也可以使用下面的方式对列中的数字进行格式化 COLUMN column_name FORMAT model 例如COLUMN SALARY FORMAT $99,990 这些 LONG, BLOB, BFILE, CLOB, NCLOB or XMLType大数据类型的快递使用默认参数   SET LONGCHUNKSIZE or SET LONG SQL> show LONGCHUNKSIZE longchunksize 80 SQL> show long long 80 SQL>  日期格式的显示宽带受参数NLS_DATE_FORMAT影响 默认参数 nls_date_format       DD-MON-RR   因此在数据库当中使用 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 可以修改日期格式 对于varchar2类型的数据 可以使用 col column for a+数字的形式 例如  col event for a30 使用 like 可以将定义好的 格式进行copy 比如： COLUMN COMMISSION_PCT LIKE SALARY HEADING BONUS QL> col event for a30 SQL> col sql_id like event SQL> select event.sql_id from v$session; SQL> select event,sql_id from v$session; EVENT                          SQL_ID ------------------------------ ------------------------------ SQL*Net message from client pmon timer rdbms ipc message VKTM Logical Idle Wait rdbms ipc message DIAG idle wait rdbms ipc message DIAG idle wait rdbms ipc message rdbms ipc message rdbms ipc message EVENT                          SQL_ID ------------------------------ ------------------------------ 查看当前的列设置 SQL> col event COLUMN   event ON FORMAT   a30 SQL> col sql_id COLUMN   sql_id ON FORMAT   a30 SQL> col event for a30 heading "sql event" SQL> col event COLUMN   event ON HEADING  'sql event' FORMAT   a30 清除列格式 COLUMN column_name CLEAR SQL> col event COLUMN   event ON HEADING  'sql event' FORMAT   a20 truncate  SQL> col event clear SQL> col event SP2-0046: COLUMN 'event' not defined 清除所有的列 SQL> CLEAR COLUMNS columns cleared SQL>  SQL> col sql_d SP2-0046: COLUMN 'sql_d' not defined SQL>  如果将列的特性关掉 则在设置也不行 SQL> col event COLUMN   event OFF SQL> col event for a30 SQL> col event COLUMN   event OFF FORMAT   a30 SQL> select event from v$session   2  ; EVENT -------------------------------------------------------------------------------------------------------------------------------- SQL*Net message from client 开启之后其col就可以用了 SQL> col event on SQL> col event COLUMN   event ON FORMAT   a30 SQL> select event from v$session   2  ; EVENT ------------------------------ SQL*Net message from client pmon timer BREAK ON break_column 对于order by的情况特别有用 其用法如下 SQL> break on status SQL> / EVENT                          STATUS           TYPE ------------------------------ ---------------- -------------------- pmon timer                     ACTIVE           BACKGROUND rdbms ipc message                               BACKGROUND VKTM Logical Idle Wait                          BACKGROUND rdbms ipc message                               BACKGROUND EVENT                          STATUS           TYPE ------------------------------ ---------------- -------------------- DIAG idle wait                 ACTIVE           BACKGROUND rdbms ipc message                               BACKGROUND DIAG idle wait                                  BACKGROUND rdbms ipc message                               BACKGROUND rdbms ipc message                               BACKGROUND Streams AQ: qmn coordinator id                  BACKGROUND le wait SQL*Net message to client                       USER SQL*Net message from client    INACTIVE         USER SQL*Net message from client                     USER SQL*Net message from client                     USER EVENT                          STATUS           TYPE ------------------------------ ---------------- -------------------- SQL*Net message from client    INACTIVE         USER SQL*Net message from client                     USER SQL> BREAK ON status SKIP 5 在一页页显示的时候通过skip 插入空白行 SQL> BREAK ON status SKIP PAGE SQL>  SQL> TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO ----在每一个换页符上添加数字  SQL> col event for a35 SQL> select event ,status,type from v$session order by status;                                   Page: 1 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- pmon timer                          ACTIVE           BACKGROUND Streams AQ: qmn slave idle wait                      BACKGROUND Space Manager: slave idle wait                       BACKGROUND rdbms ipc message                                    BACKGROUND jobq slave wait                                      USER Streams AQ: waiting for time manage                  BACKGROUND ment or cleanup tasks rdbms ipc message                                    BACKGROUND VKTM Logical Idle Wait                               BACKGROUND                                   Page: 2 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- rdbms ipc message                   ACTIVE           BACKGROUND DIAG idle wait                                       BACKGROUND rdbms ipc message                                    BACKGROUND DIAG idle wait                                       BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND smon timer                                           BACKGROUND rdbms ipc message                                    BACKGROUND                                   Page: 3 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- rdbms ipc message                   ACTIVE           BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND rdbms ipc message                                    BACKGROUND Streams AQ: qmn coordinator idle wa                  BACKGROUND it                                   Page: 4 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- SQL*Net message to client           ACTIVE           USER rdbms ipc message                                    BACKGROUND jobq slave wait                                      USER Space Manager: slave idle wait                       BACKGROUND                                   Page: 5 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- SQL*Net message from client         INACTIVE         USER SQL*Net message from client                          USER SQL*Net message from client                          USER SQL*Net message from client                          USER SQL*Net message from client                          USER BREAK ON break_column_1 SKIP PAGE ON break_column_2 SKIP 1 SQL>  BREAK ON status SKIP PAGE  on TYPE skip 2 SQL> /                                   Page: 1 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- pmon timer                          ACTIVE           BACKGROUND Streams AQ: qmn slave idle wait rdbms ipc message Space Manager: slave idle wait rdbms ipc message Streams AQ: waiting for time manage ment or cleanup tasks rdbms ipc message VKTM Logical Idle Wait                                   Page: 2 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- rdbms ipc message                   ACTIVE           BACKGROUND DIAG idle wait rdbms ipc message DIAG idle wait rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message smon timer rdbms ipc message                                   Page: 3 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- rdbms ipc message                   ACTIVE           BACKGROUND rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message Streams AQ: qmn coordinator idle wa it                                   Page: 4 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- SQL*Net message to client           ACTIVE           USER                                   Page: 5 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- SQL*Net message from client         INACTIVE         USER SQL*Net message from client SQL*Net message from client SQL*Net message from client SQL*Net message from client 用于计算 COMPUTE function LABEL label_name OF column column column SUM Sum of the values in the column. MINIMUM Minimum value in the column. MAXIMUM Maximum value in the column. AVG Average of the values in the column. STD Standard deviation of the values in the column. VARIANCE Variance of the values in the column. COUNT Number of non-null values in the column. NUMBER Number of rows in the column. SQL> COMPUTE count OF EVENT ON STATUS SQL>  SQL> /                                   Page: 1 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- pmon timer                          ACTIVE           BACKGROUND Streams AQ: qmn slave idle wait rdbms ipc message Space Manager: slave idle wait rdbms ipc message Streams AQ: waiting for time manage ment or cleanup tasks rdbms ipc message VKTM Logical Idle Wait                                   Page: 2 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- rdbms ipc message                   ACTIVE           BACKGROUND DIAG idle wait rdbms ipc message DIAG idle wait rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message smon timer rdbms ipc message                                   Page: 3 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- rdbms ipc message                   ACTIVE           BACKGROUND rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message rdbms ipc message Streams AQ: qmn coordinator idle wa it                                   Page: 4 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- SQL*Net message to client           ACTIVE           USER ----------------------------------- **************** ********************                                  27 count                                   Page: 5 EVENT                               STATUS           TYPE ----------------------------------- ---------------- -------------------- SQL*Net message from client         INACTIVE         USER SQL*Net message from client SQL*Net message from client SQL*Net message from client SQL*Net message from client ----------------------------------- **************** ********************                                   5 count 关闭掉break 和compute CLEAR BREAKS CLEAR COMPUTES 设置顶部和底部标题以及页眉和页脚 如您所见，您可以将标题设置为显示在报表每页的顶部。您还可以设置标题以显示在每页的底部。TTITLE命令定义顶部标题; BTITLE 命令定义底部标题。 您还可以为每个报告设置页眉和页脚。该REPHEADER命令定义报告头; 该REPFOOTER命令定义报告页脚。 TTITLE，BTITLE，REPHEADER或REPFOOTER命令由命令名称后跟一个或多个子句组成，这些子句指定您希望放置在该位置或给出该格式的位置或格式和CHAR值。您可以包含多组子句和CHAR值： TTITLE position_clause（s）char_value  position_clause（s）char_value ... BTITLE position_clause（s）char_value  position_clause（s）char_value ... REPHEADER position_clause（s）char_value  position_clause（s）char_value ... REPFOOTER position_clause（s）char_value  position_clause（s）char_value ... 设置行数和页数长度 SET NEWPAGE number_of_lines SET PAGESIZE number_of_lines SET LINESIZE number_of_characters 如果需要输入长度超过500个字符的标题，可以使用SQL * Plus命令DEFINE将标题每行的文本放在单独的替换变量中： DEFINE LINE1 ='这是第一线...' DEFINE LINE2 ='这是第二行...' DEFINE LINE3 ='这是第三行......' 然后，在TTITLE或BTITLE命令中引用变量，如下所示： TTITLE CENTER LINE1 SKIP 1 CENTER LINE2 SKIP 1  -  CENTER LINE3  _DATE 可以讲日期添加到报告上     如果想要生成一个html格式的报表的话可以使用下面的命令 SET MARKUP HTML ON SPOOL ON SET MARKUP HTML ON SPOOL ON仅指定SQL * Plus输出将是HTML编码的，它不会创建或开始写入输出文件。必须使用SQL * Plus SPOOL命令开始生成假脱机文件。然后，此文件包含HTML标记，包括<HTML>和</ HTML>。 SET ECHO OFF来禁止在使用START命令执行的脚本中显示每个命令 关于sql 的执行计划 set autotrace  on 在其他用户下查看执行计划需要执行下面的命令 CONNECT HR  @$ORACLE_HOME/rdbms/admin/utlxplan.sql  CONNECT / AS SYSDBA  @$ORACLE_HOME/sqlplus/admin/plustrce.sql  drop role plustrace; 显示sql的执行时间 set timming  on  col EMPNO NOPRINT 该命令的作用是不显示或者说是不打印某一个列 作用不太的 如果是不想显示某一个列 可以不在select 中显示 其打开用  SQL> column empno PRINT SET APPINFO OFF 该命令的作用是禁用调用包DBMS_APPLICATION_INFO  该包的作用是通过将应用的信息的信息记录到v$session； 然后可以通过v$session监控 资源的消耗情况脚本的调用情况 应用程序开发人员可以将该DBMS_APPLICATION_INFO程序包与Oracle Trace和SQL跟踪工具一起使用，以记录数据库中执行模块或事务的名称，以便以后在跟踪各种模块的性能和调试时使用。 注册应用程序允许系统管理员和性能调优专家按模块跟踪性能。系统管理员还可以使用此信息来跟踪模块的资源使用情况。当应用程序向数据库注册时，其名称和操作将记录在V$SESSION和V$SQLAREA视图中。 设置为off 则阻止管理员监控资源的使用情况和性能 SET ARRAYSIZE arraysize 的作用取决于oracle 数据库的包大小和网络的吞吐量和速度， 在最新版本的sqlplus 中其参数作用越来越小 SET DEFINE OFF SET DEFINE OFF disables the parsing of commands to replace substitution variables with their values. 禁用命令解析替代替换变量的值 没啥用  SET FLUSH OFF SET FLUSH OFF enables the operating system to buffer output 设置操作系统缓存输出，收益很小 SET LINESIZE 保持LINESIZE尽可能小，以避免额外的内存分配和内存复制。 但是如果设置过小则会出现显示问题 SET LONGCHUNKSIZE SET SERVEROUTPUT off 则会进制显示DBMS_OUTPUT.PUT_LINE 的输出值 SQL> set  sqlprompt "====>" ====> ====> ====> ====> ====> ====>show sqlp sqlprompt "====>" ====>set sqlprompt "SQL>" SQL> set tab on 设置TAB ON会导致在终端输出中压缩多个空格。除非这显着减少了书面数据，否则所需的处理可能会略微超过任何好处。  SET TERMOUT SET TERMOUT OFF suppresses the display so that you can spool output from a script without seeing it on the screen. If both spooling to file and writing to terminal are not required, use SET TERMOUT OFF in SQL scripts to disable terminal output. SET TRIMOUT ON SET TRIMSPOOL ON TRIMOUT ON或SET TRIMSPOOL ON删除每个显示或假脱机行末尾的尾随空白。 ---未发现有啥用处