sqlplus 命令简单整理

最新推荐文章于 2021-04-03 06:27:54 发布

ctfmxd8196

最新推荐文章于 2021-04-03 06:27:54 发布

阅读量416

点赞数

如果环境变量写在一个文件当中可以使用

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,

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(*)

----------

SQL> c/session/process

1* select count(*) from v$process

SQL> /

COUNT(*)

----------

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(*)

----------

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(*)

----------

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;

会话总数

------------

SQL> SET UNDERLINE '+'

SQL>

SQL> select count(*) from v$session;

会话总数

++++++++++++

SQL> select count(*) from v$session;

会话总数

============

SQL> col count(*) heading '会话|总数';

SQL> select count(*) from v$session;

会话

总数

==========

SQL> COLUMN count(*) CLEAR

SQL> select count(*) from v$session;

COUNT(*)

==========

总的来说对会话列来说其名称可以使用其他的名称进行格式化但是需要指定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

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

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

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

smon timer BACKGROUND

rdbms ipc message BACKGROUND

Page: 3

EVENT STATUS TYPE

----------------------------------- ---------------- --------------------

rdbms ipc message ACTIVE BACKGROUND

rdbms ipc message BACKGROUND

Streams AQ: qmn coordinator idle wa BACKGROUND

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

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

smon timer

rdbms ipc message

Page: 3

EVENT STATUS TYPE

----------------------------------- ---------------- --------------------

rdbms ipc message ACTIVE BACKGROUND

rdbms ipc message

Streams AQ: qmn coordinator idle wa

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

用于计算

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

smon timer

rdbms ipc message

Page: 3

EVENT STATUS TYPE

----------------------------------- ---------------- --------------------

rdbms ipc message ACTIVE BACKGROUND

rdbms ipc message

Streams AQ: qmn coordinator idle wa

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

----------------------------------- **************** ********************

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删除每个显示或假脱机行末尾的尾随空白。

---未发现有啥用处

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31369696/viewspace-2305593/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31369696/viewspace-2305593/