Sqlplus 常用命令

分两部分

第一部分：http://blog.csdn.net/waterxcfg304/article/details/29807995

show和set命令是两条用于维护SQLPlus系统变量的命令 ：

SQL> show all --查看所有系统变量值 
SQL>show user --显示当前连接用户 
SQL>show error --显示错误(编译function,procedure,package,pl/sql的时候，可以查看具体的错误提示)

SQL>set sqlprompt SQL  --设置默认提示符，默认值就是SQL 
SQL>set linesize 1000 --设置屏幕显示行宽，默认100 
SQL>set autocommit ON --设置是否自动提交，默认为OFF 
SQL>set pause on --默认为OFF，设置暂停，会使屏幕显示停止，等待按下ENTER键，再显示下一页 
SQL>set arraysize 1 --默认为15 
SQL>set long 1000 --默认为80 
SQL>set heading off --禁止输出列标题，默认值为ON 
SQL>set feedback off --禁止显示最后一行的计数反馈信息，默认值为对6个或更多的记录，回送ON 
SQL>set timing on --默认为OFF，设置查询耗时，可用来估计SQL语句的执行时间，测试性能 

问题提出： 
1、用户需要对数据库用户下的每一张表都执行一个相同的SQL操作，这时，一遍一遍的键入SQL语句是很麻烦的 
实现方法： 
SQL set heading off --禁止输出列标题 
SQL set feedback off --禁止显示最后一行的计数反馈信息 
列出当前用户下所有同义词的定义，可用来测试同义词的真实存在性 
SQL> select 'desc ' || tname from tab where tabtype='SYNONYM';

把所有符合条件的表的select权限授予为public 
select 'grant select on 'table_name' to public;' from user_tables where condition;
删除用户下各种对象

SQL> set heading off
SQL> set feedback off
SQL> select 'drop '|| tabtype,tname || ';' from tab;

drop TABLE   COUNTRIES;
drop TABLE   DEPARTMENTS;
drop TABLE   EMPLOYEES;
drop TABLE   JOBS;
drop TABLE   JOB_HISTORY;
drop TABLE   LOCATIONS;
drop TABLE   REGIONS;
drop VIEW    EMP_DETAILS_VIEW;
drop VIEW    EXTRA_PAY;
drop TABLE   EMP_PICTURE;
drop TABLE   PERSONNEL;
drop TABLE   REVIEW_TABLE;

删除符合条件用户

SQL> select 'drop user ' || username || ' cascade;' from all_users where user_id=33;

drop user HR cascade;

2，快速编译所有视图 
----当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍， 
----因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PLSQL的语言特性，快速编译。 
SQL> spool view.sql

select 'alter view ' ||tname ||' compile;' from tab;

SQL> spool off

然后执行ON.SQL即可。 
SQL>@view.SQL 
当然，授权和创建同义词也可以快速进行，如： 
select 'grant select on ' ||tname ||' to watson;' from tab;

3，命令列表： 
假设当前执行命令为：select  from tab where rownum<10; 
(a)ppend 添加文本到缓冲区当前行尾 a order by tname

SQL> a  order by tname;
结果：  1* select * from tab where rownum<10 order by tname

（注：a后面跟2个空格） 
(c)hangeoldnew 在当前行用新的文本替换旧的文本 ctname 结果：select tname from tab; 
(c)hangetext 从当前行删除文本 ctab 结果：select tname from ; 
del 删除当前行 
del n 删除第n行 
(i)nput 文本 在当前行之后添加一行 
(l)ist 显示缓冲区中所有行 
(l)ist n 显示缓冲区中第 n 行 
(l)ist m n 显示缓冲区中 m 到 n 行 
run 执行当前缓冲区的命令 
执行当前缓冲区的命令 
r 执行当前缓冲区的命令 
@文件名 运行调入内存的sql文件，如： 
SQL edit s回车 
如果当前目录下不存在s.sql文件，则系统自动生成s.sql文件， 
在其中输入select  from tab;，存盘退出。 
SQL @s回车 
系统会自动查询当前用户下的所有表、视图、同义词。 
@@文件名 在.sql文件中调用令一个.sql文件时使用 
save 文件名 将缓冲区的命令以文件方式存盘，缺省文件扩展名为.sql 
get 文件名 调入存盘的sql文件 
start 文件名 运行调入内存的sql文件 
spool 文件名 把这之后的各种操作及执行结果假脱机即存盘到磁盘文件上，默认文件扩展名为.lst 
spool 显示当前的假脱机状态 
spool off 停止输出 
例： 
SQL spool a 
SQL spool 
正假脱机到 A.LST 
SQL spool off 
SQL spool 
当前无假脱机

exit 退出SQLPLUS 
desc 表名 显示表的结构 
show user 显示当前连接用户 
show error 显示错误 
show all 显示所有系统变量值 
edit 打开默认编辑器，Windows系统中默认是notepad.exe，把缓冲区中最后一条SQL语句调入afiedt.buf文件中进行编辑 
edit 文件名 把当前目录中指定的.sql文件调入编辑器进行编辑 
clear screen 清空当前屏幕显示

二．Oracle sqlplus语句编辑命令 
首先我们输入这样一条指令： 
SELECT emp_id, emp_name 
FROM Employees 
input 命令可以接着上一条指令的后面添加语句，比如在上述语句运行后输入： 
input WHERE emp_age > 30 
便可得到如下指令： 
SELECT emp_id, emp_name 
FROM Employees 
WHERE emp_age  > 30 
ln 命令用于指定对以输入的第n行语句进行操作，比如在上述语句运行后输入： 
l1则当前选中的语句行即为 
SELECT emp_id, emp_name 
（语句前有表示） 
a 命令用于直接在当前行的末尾加上字符，比如在上述语句运行后输入： 
a , emp_dept 
则执行的指令变为： 
SELECT emp_id, emp_name, emp_dept 
FROM Employees 
WHERE emp_age  30 
c 命令用于修改当前语句中的字符，比如在上述语句运行后输入： 
c emp_nameemp_age则执行的指令变为： 
SELECT emp_id, emp_age, emp_dept 
FROM Employees 
WHERE emp_age  30 
del n 命令用于删除第n行指令，比如在上述语句运行后输入： 
DEL 3 
则执行的指令变为： 
SELECT emp_id, emp_age, emp_dept 
FROM Employees

第二部分：

Sqlplus常用命令 如下给出一些 最基础的 最常用的： 

 
1.登陆系统用户：  
sqlplus 然后输入系统用户名和密码  
 
  登陆别的用户：  
conn 用户名/密码;  
 
 
2.创建表空间  
 
create tablespace 空间名  
 
datafile 'c:"空间名' size 15M --表空间的存放路径,初始值为15M  
 
autoExtend on next 10M --空间的自动增长的值是10M  
 
permanent online; --永久使用  
 
 
 
3.创建用户  
 
create user jun --创建用户名为jun  
 
identified by syj --创建密码为syj  
 
default tablespace 表空间名--默认表空间名  
 
temporary tablespace temp --临时表空间为temp  
 
profile default --受profile文件的限制  
 
quota unlimited on 表空间名; --在表空间下面建表不受限制  
 
 
 
4.创建角色  
 
create role 角色名identified by 密码;  
 
 
 
5.给角色授权  
 
grant create session to 角色名；--给角色授予创建会话的权限  
 
grant 角色名to 用户名；--把角色授予用户  
 
 
 
6.给用户授予权限  
 
grant create session,resource to shi;--给shi用户授予所有权限  
 
grant create table to shi; --给shi用户授予创建表的权限  
 
 
 
7.select table_name from user_tables; 察看当前用户下的所有表  
 
 
 
8.select tablespace_name from user_tablespaces; 察看当前用户下的 表空间  
 
 
 
9.select username from dba_users;察看所有用户名称命令 必须用sys as sysdba登陆 （呃，网上是这么说，但是 我是用system 用户 也可以查看。） 
 
 
 
10.创建表  
 
create table 表名  
(  
id int not null,  
name varchar2(20) not null  
)tablespace 表空间名--所属的表空间  
storage  
(  
initial 64K --表的初始值  
minextents 1 --最小扩展值  
maxextents unlimited --最大扩展值  
);  
 
 
 
11.--为usrs表添加主键和索引  
 
alter table users  
 
add constraint pk primary key (ID);  
 
 
 
12.为已经创建users表添加外键  
 
alter table users  
 
add constraint fk_roleid foreign key (roleid)  
 
references role(role_id) on delete cascad; --下边写主表的列  
 
on delete cascad是创建级联  
 
 
 
 
13.把两个列连接起来  
 
select concat(name,id) from 表名；--把name和id连接起来  
 
 
 
14.截取字符串  
 
select column(name,'李') from 表名；--把name中的‘李’去掉  
 
 
 
 
15.运行事务之前必须写  
 
set serveroutput on; --打开输入输出（不写的话,打印不出信息）  
 
 
 
16.while的应用  
 
declare --声明部分  
 
ccc number:=1; --复职  
 
a number:=0;  
 
begin --事务的开始  
 
while ccc<=100 loop --循环  
 
if((ccc mod 3)=0) then --条件  
 
dbms_output.put_line(ccc||','); --打印显示  
 
a:=a+ccc;  
 
end if; --结束if  
 
ccc:=ccc+1;  
 
end loop; --结束循环  
 
dbms_output.put_line(a);  
 
end; --结束事务  
/  
 
 
 
 
17.select into 的用法--只能处理一行结果集  
 
declare  
 
name varchar(30);  
 
begin  
 
select username into name  
 
from users  
 
where id=2;  
 
dbms_output.put_line('姓名为：'||name);  
 
end;  
/  
 
 
 
18.利用%rowtype属性可以在运行时方便的声明记录变量和其他结构  
 
Set serveroutput on;  
 
Declare  
 
utype users%rowtype;  
 
Begin  
 
Select * into utype from users where id=20;  
 
Dbms_output.put_line('姓名'|| utype.username);  
 
Dbms_output.put_line('生日'|| utype.brithday);  
 
end;  
/ --%rowtype想当于复制一个表  
 
 
 
19.游标的定义和使用  
 
Declare  
 
Cursor ucur is select * from users; --声明游标  
 
Us users%rowtype;--定义与游标想匹配的变量  
 
Begin  
 
Open ucur;--打开游标  
 
Fetch ucur into us;  
 
While ucur %found loop --使用循环遍历游标的查询结果  
 
Dbms_output.put_line('姓名：'||us.username||'生日'||us.brithday);  
 
Fetch ucur into us;  
 
End loop;  
 
Close ucur; --关闭游标  
 
End;  
=======================================  
 
 
%found在前一条的fetch语句至少对应数据库的一行时，%found属性值为true，否则为false;  
 
% notfound 在前一条fetch语句没有对应的数据库行时，%notfound属性值为true，否则为false;  
 
%isopen 在游标打开时%isopen属性值为true；否则为false;  
 
%rowcount显示迄今为止从显示游标中取出的行数  
 
 
 
 
 
20.删除 
  
drop tablespace 空间名including contents; --删除表空间和里面的内容  
 
drop table 表名--删除表  
 
drop user 用户名--删除用户 
  
------------------------------------- 
  
使用Oracle用户 
 
# su - oracle 
 
创建表空间-> 创建新用户-> 用户授权 
 
一 创建表空间 
 
SQL> CREATE TABLESPACE test01 DATAFILE '/data/oracle/oradata/db/test01.dbf' SIZE 1024M UNIFORM SIZE 128k; 
 
1) DATAFILE: 表空间数据文件存放路径 
 
2) SIZE: 起初设置为1G就可以 
 
3) UNIFORM: 指定区尺寸为128k,如不指定，区尺寸默认为64k  
 
4) 空间名称test01 与 数据文件名称test01.dbf 不要求相同,可随意命名. 
 
通过ALTER DATABASE修改空间扩展大小 
 
 
SQL > ALTER DATABASE DATAFILE '/data/oracle/oradata/db/test01.dbf' ' AUTOEXTEND ON; 
 
 
1)AUTOEXTEND ON:在Oracle10g中,需要手动启动分区自动扩展功能. 
 
 
 
 
二 创建新用户 
 
SQL> CREATE USER test IDENTIFIED BY 123456 DEFAULT TABLESPACE test01; 
 
1) 创建用户同时分配表空间 
 
 
 
 
三 用户授权 
 
1)用户角色授权 
 
SQL> CRANT CONNECT,RESOURCE TO test; 
 
a) 我这里将CONNECT和RESOURCE角色赋给新用户test,test将拥有这两个角色的操作权限. 
 
 
2)直接授权 
 
多用户 
 
SQL> CRANT SELECT,INSERT,UPDATE,DELETE,ON USERS TO test,test1; 
 
 
 
 
下面我们可以使用两种方式登录Oracle db 
 
一SQLPLUS 
 
1)本地登录 
 
SQL> sqlplus test/123456 
 
 
2)远程登录 
 
假设db在另一台服务器 
 
SQL> sqlplus test/123456@db 
 
 
 
二PLSQL Developer 
 
上篇中也有讲述,输入账号口令就可以了. 
 
 
 
 
下面说些和上面相关,且工作中会需要用到的. 
 
一 删除用户 
 
SQL> DROP USER test CASCADE; 
 
 
二 删除表空间 
 
SQL> DROP TABLESPACE test01 INCLUDING CONTENTS AND DATAFILES; 
 
1) 删除表空间内容和数据文件. 
 
2) 一般无效表空间占用磁盘空间,所以这个应该很常用.  
 
 
 
 http://www.jb51.net/article/18251.htm
查看用户角色权限 
 
select * from user_role_privs; 
 
查看用户表权限 
 
select * from user_tab_privs;