sqlplus 使用精讲

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

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

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

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

a[ppend] --在当前行后直接输入字符串

del --和list类似，见下面

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

更改当前行：默认总是最后一行：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 命令相反

输入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】

--输出 执行结果到 文件。
注意：虽然spool的文件第一时间生成，但其中的结果内容到spool off 才写入！
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]

--sqlplus的暂停提示信息：类似于os的pause
pau[se] [message_text]

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

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

绑定变量：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...] }
--注意“@”符号

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

显示oracle回收站信息

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

startup：语法

startup [ [ force ] [ restrict ]  [ pfile=filename ] [ quiet ] [ { nomount | mount [dbname] | open  [ read { only | write [recover] } | recover ][dbname] } ]    ]

--force：强制关闭（abort模式）正运行的oracle数据库实例，之后重启。警告：危险的操作，生产环境慎用

--restrict：进入restrict session 权限的连接模式

--pfile：以指定的pfile启动
[filename]：替代默认的启动spfile或缺省pfile

--quiet：启动后，不显示sga信息
[dbname]：替代初始化参数中的 DB_NAME 配置

启动：仅实例，nomount数据库
startup nomount

启动：实例，mount数据库
startup mount

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

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

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

关闭：shutdown

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

日志归档：archive log

copy数据：copy

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

passw[ord]
修改其他指定用户密码：指定用户名

passw[ord] { username }