SQLPLUS应用简介（二）

Oracle学习笔记：sqlplus 使用精讲

一、连接数据库

　　os：仅仅进入sqlplus环境，不连接数据库

sqlplus /nolog

　os：本机连接

　　　　方式1：操作系统认证登录：其后的角色不可省略

set ORACLE_SID=TEST_SID
sqlplus /  as { sysdba | sysoper | sysasm }

　　　　方式2：标准用户登录：密码、端口（默认1521）、角色 均可省略。如果密码省略则提示输入密码

set ORACLE_SID=TEST_SID
sqlplus 用户[/密码] [ as { sysdba | sysoper | sysasm } ]

或

sqlplus 用户[/密码]@本地oracle服务名 [ as { sysdba | sysoper | sysasm } ]

或

sqlplus 用户[/密码]@主机[:端口]/oracle服务名 [ as { sysdba | sysoper | sysasm } ]

或

sqlplus 用户[/密码]@//主机[:端口]/oracle服务名 [ as { sysdba | sysoper | sysasm } ]

　os：远程连接

同“本机连接”的“方式2”（除了set ORACLE_SID方式）

　　sqlplus：退出到os：默认行为为先commit、再成功退出

{ exit | quit } [ success | failure | warning | n | variable | :bindvariable ]      [ commit | rollback ]

　　sqlplus：断开连接

disc[onnect]

　　sqlplus：新建连接

-- 提示用户名和密码：仅限本机登录
conn[ect]  

或

和“os连接到db”一样

　　切换用户：

先断开，再重连啊

或

先退出，再登录啊

 

二、设置sqlplus执行环境

　　设置环境变量：set

set  environment_variable {?}

　　

　　查看环境变量的设置：show

-- 查看所有环境变量的设置 show all   --查看指定的环境变量的设置 show environment_variable   或者   --查看指定的环境变量的设置：使用 set set  environment --它会给出相关的语法提示

　　

　　设置信息显示模式

　　　　提示符

set sqlp[rompt] "_user'@'_connect_identifier>"

　　　　当前os时间

--显示当前的执行时间
set time on

--关闭当前的执行时间
set time off

　　　　页数、行数

set pages[ize]=500
set lin[esize]=3000

 　　　　dbms_output的信息显示

--打开显示
set serverout[put] on [ size { n | unlimited } ] [ format { wrapped | word_wrapped | truncated  } ]

--关闭显示
set serverout[put] off

--打开 自动提交事务：立即提交
set auto[commit] { on | imm[ediate] | n }

--打开 自动提交事务：累计n个事务后提交
set auto[commit] n

--关闭 自动提交事务 
set auto[commit] off

　　执行时间跟踪

set timi[ng] on

　　影响的行数

--打开 显示影响行数
set feed[back] { 1 | on | n }

--关闭 影响行数
set feed[back] { 0 | off }

　　执行计划

--显示执行计划
set autot[race] { on | trace[only] } [ exp[lain] ] [ stat[istics] ]

--关闭执行计划
set autot[race] off

--打开 自动提交事务：立即提交
set auto[commit] { on | imm[ediate] | n }

--打开 自动提交事务：累计n个事务后提交
set auto[commit] n

--关闭 自动提交事务 
set auto[commit] off

 

　　执行时间跟踪

set timi[ng] on

 

　　影响的行数

--打开 显示影响行数
set feed[back] { 1 | on | n }

--关闭 影响行数
set feed[back] { 0 | off }

 

　　执行计划

--显示执行计划
set autot[race] { on | trace[only] } [ exp[lain] ] [ stat[istics] ]

--关闭执行计划
set autot[race] off

三、缓冲区执行语句（sql、pl/sql）的编辑处理

　　调用外部编辑器：ed【it】

ed[it]

 

　　使用sqlplus的编辑命令：增、删、改：a【ppend】、del、c【hange】、i【nput】

a[ppend] --在当前行后直接输入字符创，<span style= "background-color: #ff0000;" >不换行</span> del --和list类似，见下面 c[hange] --很牛的东东。格式 “ change  sep_char<strong>old</strong>[sep_char[<strong> new </strong>[sep_char]]]”：<br>　　1）sep_char为不在old和 new 中的非字母数字字符（基本上只能是标点符号）<br>　　2）最后的分隔符sep_char可以省略<br>　　3）old不区分大小写，即大小写不敏感<br>　　4）old可以指定通配符“...”，用来匹配范围：...old(开头 .. 第一次出现)、old...old (前后全匹配)、old...(第一次出现 .. 最后)<br>　　5）如果省略第二个分隔符sep_char（自然也省略后面）、或者省略 new （自然也包括后面），则相当于删除old<br>i[nput] --在当前行后，输入新的字符串<span style= "background-color: #ff0000;" ><span style= "background-color: #ffffff;" >，</span>换行</span>

　　更改当前行：默认总是最后一行：l【ist】

--查看所有的行 { list | ; }   --查看指定的行：* 为当前行；n在缓冲区行数内 list { n | * | last }   --查看指定范围内的行 从 n 到 m 行：m>=n list { n | * } { m | * | last}

　　

　　保存sql语句：sav【e】：默认扩展名为sql，默认行为为create。注意：保存后，末尾的分号“；”会自动去掉；而且缓冲区有且仅有一个sql语句或pl/sql复合语句

sav[e] [file] file_name[.ext] [ create | replace | append  ]

　　载入sql语句：get：与save相反

--与 save 命令相反

--打开 自动提交事务：立即提交
set auto[commit] { on | imm[ediate] | n }

--打开 自动提交事务：累计n个事务后提交
set auto[commit] n

--关闭 自动提交事务 
set auto[commit] off

　　执行时间跟踪

set timi[ng] on

　　影响的行数

--打开 显示影响行数
set feed[back] { 1 | on | n }

--关闭 影响行数
set feed[back] { 0 | off }

　　执行计划

--显示执行计划
set autot[race] { on | trace[only] } [ exp[lain] ] [ stat[istics] ]

--关闭执行计划
set autot[race] off

　　输入sql语句的过程中，临时退出编辑模式 ：以井号“#”临时退出编辑模式

--在输入sql的模式下，如果输入未结束。可以再新行中以井号“#”临时退出编辑模式，进入普通sqlplus的交互模式

四、执行结果、替换变量、绑定变量等的 定义、交互、显示、处理

　　sqlplus的注释命令：rem【mark】

rem[ark] 任何文字  -- 类似于os的rem，或者sql的行注释 “--”

　　执行os命令：{ host | ！}：具体和os有关

--切换退出sqlplus，进入os的shell环境 { host | ! | $ }     --不切换sqlplus，执行os命令 { host | ! | $ } command

 　　清理各种历史记录数据：cl【ear】

--清理 屏幕输出信息，类似于os的cls cl[ear] scr[een]   --清理 缓冲区sql语句 cl[ear] buff[er]   --清理 sql 缓冲池记录 cl[ear] sql   --清理 其他 cl[ear] { breaks | computer | columns | timing }

　　转存执行结果：spo【ol】

--输出 执行结果到 文件。<span style= "color: #ff0000;" >注意：虽然spool的文件第一时间生成，但其中的结果内容到spool off 才写入！</span><br>spo[ol] filename[.ext]  [ create | replace | append ]   --关闭 spool spo[ol] off

　　替换变量：sqlplus的【预编译】宏功能，本质很简单：def【ine】、acc【ept】、unde【fine】、pro【mpt】、pau【se】

--定义、重新设置新值 方式1：def[ine] substitution_var = text 方式2：acc[ept] substitution_var [ char  | date | [ num[ber] ] | binary_float | binary_double  ] --变量类型                                                 [ for [mat] format_str ] --格式                                                 [ default  default_value ] --变量的默认值                                                 [ { prompt prompt_text } | nopr[ompt] ] --提示信息                                                 [ hide ] --不显示用户输入，类似于输入不回显密码的输入     --显示指定的替换变量 def[ine] substitution_var   --显示 所有的替换变量 def[ine]   --sqlplus的提示信息：类似于os的echo pro[mpt] [message_text]<br><br>--sqlplus的暂停提示信息：类似于os的pause<br>pau[se] [message_text]

　　

　　sqlplus【预定义的替换变量】：一定要注意：这些预定义替换变量都是可以重新定义或删除定义的。是sqlplus的会话级的

_EDITOR：sqlplus的edit命令调用的os文本编辑器 _USER：sqlplus的当前登录用户名 _CONNECT_IDENTIFIER：sqlplus的当前连接串 _DATE：sqlplus的当前os日期

--打开 自动提交事务：立即提交
set auto[commit] { on | imm[ediate] | n }

--打开 自动提交事务：累计n个事务后提交
set auto[commit] n

--关闭 自动提交事务 
set auto[commit] off

 

　　执行时间跟踪

set timi[ng] on

 

　　影响的行数

--打开 显示影响行数
set feed[back] { 1 | on | n }

--关闭 影响行数
set feed[back] { 0 | off }

 

　　执行计划

--显示执行计划
set autot[race] { on | trace[only] } [ exp[lain] ] [ stat[istics] ]

--关闭执行计划
set autot[race] off

绑定变量：sqlplus中？或者说是oracle引擎的【会话变量？】更为合适

 

--定义：绑定变量：bind_variable var [iable] bind_variable { number | char  | char (n  [ byte  | char  ] )  | varchar2(n  [ byte  | char  ] ) | nchar | nchar(n) | nvarchar(n) |  clob | nclob | binary_float | binary_double | refcursor }   --查看：所有定义的绑定变量：定义 var [iable]   --查看：指定名称的绑定变量：定义 var [iable] bind_variable   --打印、输出：所有绑定变量的：内容 print   --打印、输出：指定名称绑定变量的：内容 print bind_variable   --打印、输出：自动打印所涉及的绑定变量的：内容 ： 允许自动打印、输出 set  autoprint on   --打印、输出：自动打印所涉及的绑定变量的：内容 ： 禁止自动打印、输出 set  autoprint off

　　

五、执行os脚本

　　os启动sqlplus时，执行指定的os中sql脚本：sqlplus的启动参数

sqlplus 【options】【logon】 @{URL | filename [.ext] [args...] }<br>--注意“@”符号

　

　　sqlplus内：运行os中的sql脚本：{ start | @ | @@ }：“@”和“@@”的区别在于“@@”指定sql脚本（父脚本）中的要运行的sql脚本（子脚本），（子脚本）的查询路径就在（父脚本）相同的目录下。当然，如果都用绝对路径，就没区别了。

{ sta[rt] | @ | @@ } { url | os_filename[.ext] } [ arg... ]

　　

　　sqlplus内：运行缓冲区内的脚本内容：{ run | /}

{ r[un] | / }

六、DBA实用功能

　　显示初始化参数：show parameters

--显示 所有的初始化参数 信息 show parameters   --显示 指定的初始化参数信息：like %？% show parameters {？}

　　显示SGA信息

show sga

　　显示oracle执行错误信息

--显示oracle错误信息代码 show sqlcode   --显示【所有oracle错误信息】 show err[ors]   --显示【指定类型的对象】的【错误信息】 show err[ors]  { function | procedure | package | package body | trigger | view | type | type body | dimension | java class  }   [schema.<span style= "background-color: #ffffff;" >]<strong>name </strong></span>

　　

　　显示oracle回收站信息

--显示【所有的】回收站信息 show recyc[lebin]   --显示 【指定的原始对象名】的回收站信息 show recyc[lebin] original_name

 　　startup：语法

startup [  <strong>[ force ] [ restrict ]  [ pfile=filename ] [ quiet ]</strong> [ { nomount | mount [dbname] | open  [ read { only | write [recover] } | recover ][dbname] } ]     ]<br><br>--force：强制关闭（abort模式）正运行的oracle数据库实例，之后重启。警告：危险的操作，生产环境慎用<br>--restrict：进入restrict session 权限的连接模式<br>--pfile：以指定的pfile启动<br>　　[filename]：替代默认的启动spfile或缺省pfile<br>--quiet：启动后，不显示sga信息<br>[dbname]：替代初始化参数中的 DB_NAME 配置

 　　启动：仅实例，nomount数据库

startup nomount

 　　启动：实例，mount数据库

startup mount

 　　启动：实例，read only只读打开数据库

startup open read only

 　　启动：实例，read write 打开数据库：即正常的打开方式

startup open read write<br><strong>startup open</strong>

 　　启动：实例，介质恢复模式打开：等价于：recover database 命令 + startup命令

startup open recover ？？？？？？？？？？？？

　

　　关闭：shutdown

--异常关闭：类似于断电：（1）不征求客户端同意，立即断开连接；（2）启动必须恢复 shutdown abort   --立即关闭：（1）立即断开客户端连接；（2）启动无需恢复 shutdown immediate   --正常关闭：（1）等待客户端主动断开连接；（2）启动无需恢复；（3）未完成事务回滚； <strong>shutdown</strong> [ normal ]   --谨慎关闭：（1）等待客户端主动断开连接；（2）启动无需恢复；（3）且等待事务完成； shutdown transaction

　　

　　恢复：recover

　　日志归档：archive log

　　copy数据：copy

　　修改当前用户密码：不指定用户名，即为当前用户

passw[ord]

　　修改其他指定用户密码：指定用户名

passw[ord] { username }

--打开 自动提交事务：立即提交
set auto[commit] { on | imm[ediate] | n }

--打开 自动提交事务：累计n个事务后提交
set auto[commit] n

--关闭 自动提交事务 
set auto[commit] off

　　执行时间跟踪

set timi[ng] on

　　影响的行数

--打开 显示影响行数
set feed[back] { 1 | on | n }

--关闭 影响行数
set feed[back] { 0 | off }

　　执行计划

--显示执行计划
set autot[race] { on | trace[only] } [ exp[lain] ] [ stat[istics] ]

--关闭执行计划
set autot[race] off

fFROM http://www.cnblogs.com/jinzhenshui/archive/2012/12/05/2802601.html