SQLPLUS登陆命令「建议收藏」

大家好，又见面了，我是你们的朋友全栈君。

一.SQLPLUS登陆命令：
使用sqlplus：
10G之前的版本登陆时需要加引号（单、双引号皆可）如：sqlplus ” / as sysdba”
sqlplus -prelim / as sysdba     从Oracle10g开始，sqlplus提供了一个参数选项-prelim，用这个参数，在系统已经hang的时候可以连接到SGA而不是数据库，也就是说没有session被创建。
sqlplus /nolog
conn / as sysdba
connect / assysdba
conn sys/oracle assysdba
SQL>conn bys/bys
已连接。
SQL>show user
USER 为 “BYS”
使用服务名登陆，as sysdba即使用SYSDBA用户权限登陆
SQL>conn sys/oraclesys@orcl as sysdba
已连接。
查看用户权限：
SQL>show user
USER 为 “SYS”
SQL>conn sys/oraclesys@orcl as sysoper
已连接。
SQL>show user
USER 为 “PUBLIC”
登陆时可以使用IP+端口+服务名或主机名+端口+服务名
SQL>conn bys/bys@192.168.0.181:1521/orcl
已连接。
SQL>show user
USER 为 “BYS”
SQL>conn scott/tiger@xporacle:1521/orcl
已连接。
SQL>show user
USER 为 “SCOTT”
总结如下：
直接在SQLPLUS启动时输入用户名密码的多种格式。
sqlplusscott/tiger
sqlplus -prelim /nolog   –数据库hang住时，可以这样登陆。
sqlplusscott/tiger@orcl
sqlplus scott/tiger@192.168.2.18:1521/orcl
sqlplusscott/tiger@xporacle:1521/orcl
如果监听运行在默认的1521端口，可以不写端口：如下：
sqlplus bys/bys@192.168.1.211/bys3
如果不想在命令中输入密码，可以使用：或者为用监听名时：
[oracle@bys3 dbs]$ sqlplus bys
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 16 20:26:51 2014
Copyright © 1982, 2013, Oracle.  All rights reserved.
Enter password:
 
 
sqlplussys/oraclesys as sysdba

断开连接不退出SQLPLUS——disconnect，断开并退出SQLPLUS exit  —要注意：disconnect或exit，会隐式提交未提交的事务。
注：sqlplus scott/tiger@orcl登陆时：加@，是通过监听器（IPC或网络方式）连接到数据库。 
        sqlplus scott/tiger登陆时：不使用@时，SQLPLUS程序以IPC–进程间通信方式直接与本机的ORACLE实例进行通信

二.登陆后查询用户角色权限：–以什么角色登陆
查看当前用户
Show user
查询当前用户有哪些角色
SQL> select * from user_role_privs;
查询角色都有哪些权限
只能当前用户查询自己的权限，如BYS是DBA用户，也不能查出SCOTT用户的RESOURCE角色有哪些权限，SCOTT自己则可以。
SYS用户是可以查询的。
SCOTT用户查询
SQL>  select * from role_sys_privs whererole=‘RESOURCE’;

三.SQLPLUS文件操作命令：
SQLPLUS基本操作
1.spool将屏幕显示及查询结果输入到指定文本文件中
spool d:\1.txt
select * from emp;
spool off
2.执行和编辑指定位置的SQL脚本
start d:\test.sql
@ d:\test.sql
执行当前目录下的SQL脚本，即：登陆SQLPLUS时的目录，示例如下：
[oracle@bys001 oradiag_oracle]$ ls
a.sql  diag
[oracle@bys001 oradiag_oracle]$ cat a.sql 
select sysdate from dual;
[oracle@bys001 oradiag_oracle]$ sqlplus bys/bys
BYS@bys1>@a.sql

SYSDATE

05-SEP-13
编辑指定的SQL脚本
edit d:\test.sql
3.&替代变量
如：select * from emp where empno=&;
会提示输入EMPNO的值。交互式
4.将当前SQL缓冲构内SQL语句保存到指定文件
save d:\test2.sql
将文件中SQL语句调入SQLPLUS缓冲区
get d:\test2.sql
显示缓冲区内语句
list
注意：单引号和双引号内大小写敏感。
关键字不能缩写，也不能跨行书写，单引号内也不跨行写。
每个子句是一行。
字符串用单引，列别名用双引。
标点符号要用英文标点，中文出错。
 
三.SQLPLUS中显示设置-列长、分页

1.更改分页、行宽设置
show linesize
show pagesize
set linesize 100       ——默认80字符–这里应该根据实际需要设置。如设置过大，在desc objectname时，name列和type 列占位非常长，使用colnamefora7;语句无效，只能设置linesize变小。
set pagesize 20      ——默认14行
SQL> set numwidth     38好像设置数字型的默认显示宽度 set num 4 ，所以显示会有变 ##  
set serveroutput on
即打开oracle自带的输出方法dbms_output。
在执行set serveroutput on以后，使用dbms_output方法可以输出信息，例如：dbms_output.put_line(‘This is’); 
2.设置指定显示的列名的宽度–列是字符及数字
 col username format a20     设置 username列的列宽是20个字符—–字符列宽 
 col username for a4 
set feedback 1  设置执行完语句的回馈信息如：6 rows selected. 默认是6 大于6行才会显示。
BYS@bys1>col aa format 99,999,999      设置abc列的列宽是8位，并用逗号隔开—数字列宽
BYS@bys1>select 100100100 as aa from dual;

AA

1,000,000
BYS@bys1>col aa format 99999999
BYS@bys1>select 100100100 as aa from dual;

AA

1000000
清除指定显示的列名的宽度设置
SQL> col members clear    清除members列的列宽设置，恢复为默认值。
将所有列的显示属性设为缺省值 
SQL>CLEAR COLUMNS
显示列值时，如果列值为NULL值，用text值代替NULL值 
SQL>COL COMM NULL text 
显示列的当前的显示属性值 
SQL> COLUMN column_name 
清屏命令：
clear screen   –LINUX下
clear
或者使用系统的清屏命令如WINDOWS host cls。LINUX   !clear
四、将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库）
COPY {FROM database | TO database | FROM database TO database} 
{APPEND|CREATE|INSERT|REPLACE} destination_table 
[(column, column, column, …)] USING query 
  
sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST  
create emp_temp 
USING SELECT * FROM EMP 
 SQLPlus中的快速复制和粘贴技巧：
1] 鼠标移至想要复制内容的开始 
2] 用右手食指按下鼠标左键 
3] 向想要复制内容的另一角拖动鼠标，与Word中选取内容的方法一样 
4] 内容选取完毕后（所选内容全部反显），鼠标左键按住不动，用右手中指按鼠标右键 
5] 这时，所选内容会自动复制到SQL*Plus环境的最后一行

五、使用USERENV函数查询会话信息
USERENV(OPTION)
　　返回当前的会话信息.
　　OPTION=’ISDBA’若当前是DBA角色,则为TRUE,否则FALSE.
　　OPTION=‘LANGUAGE’返回数据库的字符集.
　　OPTION=‘SESSIONID’为当前会话标识符.
　　OPTION=‘ENTRYID’返回可审计的会话标识符.
　　OPTION=‘LANG’返回会话语言名称的ISO简记.
　　OPTION=‘INSTANCE’返回当前的实例.
OPTION=‘terminal’返回当前计算机名
使用示例：
　　SELECT USERENV(‘LANGUAGE’) FROM DUAL;
六、SQLPLUS中插入特殊字符
方法1：设置define
SQL> set define off
SQL> select * from dba_tables where table_name =’&123’;
no rows selected
SQL> set define on
SQL> select * from dba_tables where table_name =’&123’;
Enter value for 123:
方法2：
select chr(ascii(’&’)) from dual;