sqlplus命令学习

sqlplus 命令学习

成都创新互联于2013年开始，先为赤坎等服务建站，赤坎等地企业，进行企业商务咨询服务。为赤坎企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

$ sqlplus / as sysdba

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

sqlplus 一个允许你输入sql语句地方 sql语句控制数据库的存取

1 查出scott.emp表的编号，姓名

SQL> select empno , ename from scott.emp; 分号结束回车执行

EMPNO ENAME

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

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

2 如何查看当前sql buffer 的语句

? 命令 -----> 看帮助

? list

SQL> ? list

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]

输入 l

3 我想修改一buffer的语句，比如加一个条件ename ='SCOTT';

SQL> ? EDIT

EDIT

----

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

SQL> ed

Wrote file afiedt.buf

q 退出

一定在linux指定使用哪一个文本编辑器

windows用的记事本

怎么指定用那个编辑器-----》》》》？？？变量指定

方法1 ：临时指定

define _editor=vi

输入ed

方法2：永久指定把变量放在.bash_profile

[oracle@yutianedu ~]$ vim /home/oracle/.bash_profile

加入： export EDITOR=vi

[oracle@yutianedu ~]$ source /home/oracle/.bash_profile

[oracle@yutianedu ~]$ echo $EDITOR

ed 修改

SQL> l 查看

1 select empno ,ename from scott.emp

2* where ename ='SCOTT'

4 如何执行buffer cache 中的语句

SQL> ? /

/ (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. The buffer

has no command history and does not record SQL*Plus commands.

SQL> l

1 select empno ,ename from scott.emp

2* where ename ='SCOTT'

SQL> / 执行sql buffer 中的sql语句

EMPNO ENAME

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

7788 SCOTT

5 保存代码一个脚本，下次执行

SQL> ? SAVE

SAVE

----

Saves the contents 内容 of the SQL buffer in 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]]

save 文件命令 create /replace /append

默认创建

替换

追加

注意：脚本一定存在有权限的目录（oracle用户有权限写）

save /u01/app/oracle/1.sql

6 保存脚本后，我想到操作系统去看一下，然后再回来执行sqlplus命令（不退出sqlplus 到系统上执行命令）

SQL> ? 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]

用法1 ：host 回车 ----》操作系统-----》exit -----》sqlplus

用法2： host 操作系统命令

SQL> host ls -l /u01/app/oracle

total 24

-rw-r--r-- 1 oracle oinstall 58 Oct 11 15:26 1.sql

drwxr-x--- 3 oracle oinstall 4096 Oct 11 14:10 admin

清屏clear ---->host clear

复制1.sql到/tmp目录

SQL> host cp /u01/app/oracle/1.sql /tmp

SQL> host ls -l /tmp

total 524

-rw-r--r-- 1 oracle oinstall 58 Oct 11 15:30 1.sql

删除 /tmp下的1.sql

host rm /tmp/1.sql

host 所有系统都支持

在linux平台下可以用！替换host

7 退出sqlplus

exit

quit

8 在/u01/app/oracle 有一个sql脚本在sqlplus 执行脚本？？？

SQL> ? @

@ ("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.

或者用

SQL> ? start

START

-----

Runs the SQL*Plus statements in the specified script. The script can be

called from the local file system or a web server.

STA[RT] {url|file_name[.ext]} [arg ...]

@/u01/app/oracle/1.sql

start /u01/app/oracle/1.sql

9 会把屏幕上的输出都保存到一个文件里-----》做日志用

? spool

用法;

spool 一个文件

执行命令

spool off 结束

10 如何在用户之间切换

conn 账户名/密码

SQL> show user;

USER is "SYS"

SQL> conn scott/oracle

sConnected.

SQL>show user;

USER is "SCOTT"

SQL> conn hr/hr

Connected.

SQL> conn / as sysdba

Connected.

SQL> show user;

USER is "SYS"

sqlplus /nolog 登录需要connect 连接数据库

SQL> conn scott/oracle

Connected.

SQL> show user;

USER is "SCOTT"

分享文章：sqlplus命令学习
分享路径：http://scyanting.com/article/geheoj.html

sqlplus命令学习

其他资讯