Oracle不包含的命令,Oracle：常用命令整理汇总，不包含RMAN

drop table tt purge;

l //显示缓存中的sql

del 行号// 删除缓存中对应的行号

c //change

=====================================

q'[...]'

||tablename||

=====================================

常用sql：

连接到局域网中的其他oracle数据库

格式：  conn

数据库用户名/密码@目标IP地址:端口号/目标数据库全名(不是SID，在net manager内设置)：

conn scott/tiger@192.168.1.133:1526/Oracle8

注意：当对方在net manager内更改了数据库名称后，要对方重启下服务才能在本机上使用对方设置的新数据库名称

监听程序需要开启TNSListening以及对应的监听程序名称(lsnrctl start 服务名)，然后tnsping

IP:端口/实例名测试是否通

select instance_name,status from v$instance;

select host_name from v$instance;//查看主机名

select table_name from user_tables;

select distinct object_type from

dba_objects;//查看oracle到底有多少种对象

oracle中很多的表名都在数据字典dictionary中，比如要获得关于视图的表名

select table_name from dictionary where table_name like

'%VIEW%' //VIEW要大写,这里可以获得user_views表名

查找用于视图：

select view_name,text from

user_views;//这里如果要筛选，view_name要大写；text可以获得定义语句

显示控制文件目录:select name from v$controlfile;

显示数据文件目录:select name from v$datafile;

//表空间存放路径也是这个

显示日志文件目录:select name from v$logfile;

查看后台正在运行的进程：select name from v$bgprocess where

paddr<>'00';//paddr表示后台进程地址

数据库开关操作：(DB启动过程：shutdown-nomount-mount-open)

shutdown immediate/shutdown abort

startup mount;

alter database open;

startup force;//重启

=====================================

功能函数

substr('str',2) //表示从第二位开始一直截取到最后，即tr，这里的索引号是从1开始

substr('str',2,1)

//表示从第二位开始往后截取一位，即t，如果最后一位长度位大于实际字符串长度，那么就是截取到最后

lower('str')  //STR

upper('STR')  //str

length('AAA') //3，用于计算长度

instr('abcdefg'，'c'，3)//查找相应的字符在第几位，这里表示从第三位开始查找c在字符串中第几位

这里要注意下，索引从1开始，比如上面的例子返回的是3，如果是instr('abcdefg','f',5)，返回的是6，注意这里是区分大小写的

另外如果是instr('helloworld','l',2,2)表示的是'l'从第二位开始第二次出现是在第几位，这里为4

lpad和rpad：左填充和右填充

eg:lpad('AAA',10,'*')表示一共10位，不足的以*号填充，这里为左填充，所以最终效果为'*******AAA'

trim：默认用法是去头去尾，比如trim('a' from 'aaaabbbaaabbaaaa')

//最终显示bbaaabb

当然也可以指定是去头还是去尾：leading，tailing

比如trim(leading 'a' from 'aaaabbaaabbaaaa') //bbaaabbaaaa

trim(trailing 'a' from

'aaaabbaaabbaaaa') //aaaabbaaabb

replace('helloworld','l','*') //把helloworld中的l替换成*

//he**owor*d

SQL> select replace(tel,substr(tel,4,5),'*****') from

tb_01;

REPLACE(TEL,SUBSTR(TEL,4,5),'*****')

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

135*****123

138*****297

138*****057

135*****084

158*****001

159*****203

concat:连接2个字符串 concat('hello','world') //helloworld

initcap：首字母大写，多个单词的话以空格为分割依据

initcap('hello world')  //Hello World

trunc：截取，无四舍五入

round：四舍五入，round(45.962)等效于round(45.962，0)//46

round(45.962，-1)//50 round(45.962，-2) //0 round(45.962，2)

//45.96

mod：取余函数 mod(1,3) //1 mod(3,3)//0 mod(4,3) //1

to_char(sysdate,'yyyy-mm-dd') //格式化字符串

to_char(1123123,'999,999,999.99') // 1,123,123.00

to_char(1123123,'000,000,000.00') // 001,123,123.00

另外to_char在转换数字格式的时候，如果前面的数字超过最终格式化的长度，那么会显示全#

to_char(123123123123123,'999,999,999.99') //############

to_char格式化金钱符号，这里的金钱符号貌似是根据系统所属的区域来判断的

to_char(12312,'L000,000.00')

//￥012,312.00

to_char(12312,'L999,999.99)  //￥12,312.00

next_day(sysdate,'星期二')：表示当前日期后的一个星期二是几号

nvl，nvl2和nullif及coalesce

nvl:只能判断2个表达式，返回2个表达式中第一个非空表达式

nvl2：只能判断3个表达式，返回第二个非空表达式

若前2个都为null,而最后个非null，那么直接返回最后一个nvl2(null,null，2) //返回2

但如果第一个和最后一个是null，而中间一个非null，那么此时什么都不返回:nvl2(null,2,null)

同样的，如果第一个和最后一个非null，而中间一个是null，那么也是什么都不返回:nvl2(1,null,2)

nullif：用于判断2个表达式是否相同，若相同不返回，若不同返回第一个表达式

coalesce：在多个表达式中返回第一个非空表达式

nvl等的用场主要是用于组函数的计算，比如计算平均收入，计算列(工资+奖金)，那么有的人可能在奖金一列中是null值，那么计算平均的话这行是不被包含在内的，就会造成数据不对，另外

1+null 最终返回的是null，比如：

select ename,sal,comm,sal+comm income from emp;

如果某员工的comm为null，那么他的income也会为null

解决方法：select ename,comm,sal+nvl(comm,0) income from emp;

对于平均工资，比如有5个人，其中只有3个人有奖金，另外2个人奖金列为null，那么理应计算公式为(总奖金/5)，但是oracle实际的计算公式会排除null的2列，变为(总奖金/3)，这里就不对了，∴可以改为avg(nvl(comm0))

case和decode

case：

select ename,sal,

case when sal>1500 and sal<=2000 then 'good'

when sal>2000 then

'best'

else 'come on' end

info

from emp;

这里的case如果只是对比一个字符串的话可以这样 case ename when 'smith' then ... else

... end

decode：

SQL> select

ename,sal,decode(ename,'SMITH',sal+200,'JONES',sal+500,sal)

info

2  from emp where

rownum<10;

ENAME  SAL

INFO

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

SMITH  800

1000

ALLEN  1600

1600

WARD  1250

1250

JONES  2975

3475

MARTIN  1250

1250

BLAKE  2850

2850

CLARK  2450

2450

SCOTT  3000

3000

KING  5000

5000

这里的SMITH和JONES要大写，否则无效，另外如果没有在最后指定默认值sal，那么ename不等于SMITH或JONES的其他人在info这一列中为null

补充一个例子：

select product_id,

sum(decode(month,'1',total,0)) '一月',

sum(decode(month,'2',total,0)) '二月',

...

FROM products;

这个最终显示出来的就是列分别是product_id,一月，二月...十二月

统计一年12个月每个月的销售总额

union和union all

union是合并结果集，其中有重复的只会显示一条

union all同样是合并结果集，但是不管有没有重复，他都会显示在结果中

intersect：列出数据交集

minus：连接后的2张表所列出的信息是第一张表中的信息在第二张表中不存在相同项的内容

比如：A(1，2，3，4) B(2，3，5，7) 则minus后显示的数据为(1，4)

eg：

SELECT * FROM A

union/union all/minus/intersect

SELECT * FROM B

=====================================

设置

set ORACLE_SID=orcl这个是在cmd下设置，用于设置当前oracle实例名

sqlplus /

as sysdba

col col_1 for a10//设置col_1列的长度为10

col rownum noprint //rownum为系统函数，这里为在select时，就算select

rownum..了，也不显示rownum列，显示为print

set feedback off  //去"已选择8行"

set lines 1000;  //设置列宽

set pagesize 2000;//设置行数  show pagesize可以查看当前值

set verify off;  //取消原值和新值

set time on;  //用来将SQL执行的时间显示出来

alter session set nls_date_format='yyyy-mm-dd';

alter session set nls_language=american;

//当错误提示信息显示为?号时，则设置下这个

archive log list：查看归档模式

alter database archivelog设置为归档模式

=====================================

批量删除表

select 'drop table '||tablename||' purge;' from

user_tables;

可以先 set feedback off，然后再执行上面sql前，再先执行：spool

d:\dropsql.sql

最后再spool off，就可以保存脚本了

eg：

set feedback off;

spool d:\dropsql.sql

select 'drop table '||table_name||' purge;' from

user_tables;

spool off;

执行脚本：@d:\dropsql.sql;

set autot trace exp;//查看执行函数，如果要查看先前的表记录，则可以直接进行'/'

=====================================

可以用&来定义一个变量

&&是全局变量，范围在session之上

定义全局变量也可以用define来定义，用undefine来取消定义

另外单一个define可以查看所有定义的全局变量，范围为session级别

eg：

define tname='emp';

要使用这个tname，写法为&&tname

=====================================

创建用户、角色及授权

一般授权：create table, create view，create session，create

sequence，select on/any scott.tbName

直接grant 权限 to sb

如果需要授权该用户可以再授权给其他用户：grant create table to user001 with admin

option

而如果是授权select on tbName权限给该用户并允许该用户可以继续授权给其他用户则是with grant

option

收回权限：revoke 权限 from 用户：revoke create table from scott;

另外权限分为系统权限(不级联收回，比如create table)和对象权限(级联收回，比如select on)

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

创建用户：

create user user001

identified by user001

account lock

default tablespace tbs01

temporary tablespace tbs02

password expire;//用于第一次登陆时是否提示修改密码

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

创建角色：

create role r1;

grant create table to r1;

grant r1 to user001;

另外如果提示对USERS表空间权限不足等情况，需要alter user user001 quota unlimited on

USERS即可，或者开辟指定空间

创建一个加密的角色：

create role r1 identified by rr;

grant create table to r1;

grant r1 to user001;

这个时候如果用user001登录并且进行create table操作会提示权限不足，这是由于设置了密码

解决：在user001连接下：set role r1 identified by rr;之后再创建就可以了

=====================================

表操作：

copy表：

create table B

as

select name '名字',age '年龄' FROM A WHERE

age>10;

//可以根据其他表选择性选择相关列，然后创建表，新建表B的列名可以不跟原表A相同，只要在select的时候就设定好别名即可

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

修改现有列，并添加默认值

alter table t001

modify(hiredate date default sysdate);

//之后在insert操作的时候如果不填就默认使用default的值，当然也可以直接在insert的时候使用"default"来显式调用

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

插入：

insert into

(select * from t001 where deptno=10)

values(111,'newName',10)

//这里指明了deptno=10，并且插入的部门也是10，但是当指明的deptno=20，而实际插入的deptno=10的时候，虽然仍然可以插入，但是结果并不是我们所期望，那么解决这个问题可以使用with

check option，比如：

insert into

(select * from t001 where deptno=10 with check option)

values(111,'newName',20) //这个时候就会报错

另外需要注意，values后面的参数个数跟select中的列数要保持一致

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

添加新列

alter table tb_01  //需要指出是table

add colName varchar(50);  //add后面直接列名

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

删除现有列

alter table tb_01

drop column colName;  //删除的时候需要加上column

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

约束：

主键约束：

alter table tb_01

add constraint pk_id primary key(id);

外键约束：

alter table tb_01

add constraint fk_cid foreign key(cid) referneces

class(id);//可以在后面加上on delete set null或on delete cascade

删除约束

alter table tb_01

drop constraint fk_cid;

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

创建表空间：

create tablespace tbs01

datafile 'd:\...\tbs01.dbf' size 2m;

获得表空间存放路径：select name from v$datafile where rownum<2;

扩充表空间容量：alter database datafile 'd:\...\tbs01.dbf' resize

30m;

自动扩充：alter database datafile 'd:\...\tbs01.dbf' autoextend on

next 1m maxsize 30m;

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

添加注释

comment on table tb_01 IS '测试表01'

comment on column tb_01.id IS '01表编号ID'

====================================

保存点

DML.....

savepoint a;  //设定保存点a

rollback to a;回滚到保存点a之前

====================================

synonym同义词

在sys下如果要调用scott下的emp表，则为scott.emp

当然也可以设置同义词来调用，比如：

create synonym se for scott.emp

====================================

触发器：

create or replace trigger t_test

after update or delete or insert on scott.emp

for each row

begin

insert into result values(user,sysdate,:old.val,:new.val

end;

/

====================================

监听服务：

开启net manager进行相关设置，数据定位到10.2.0\db_1下，然后要开启服务：lsnrctl start

listener1

测试是否监听成功：tnsping 192.168.1.3:1521/orcl

连接到局域网中的其他oracle数据库

格式：  conn

数据库用户名/密码@目标IP地址:端口号/目标数据库全名(不是SID，在net manager内设置)：

conn scott/tiger@192.168.1.133:1526/Oracle8

(这个Oracle8就是目标数据库全名)

注意：当对方在net manager内更改了数据库名称后，要对方重启下服务才能在本机上使用对方设置的新数据库名称

监听程序需要开启TNSListening以及对应的监听程序名称(lsnrctl start 服务名)，然后tnsping

IP:端口/实例名测试是否通

====================================

回闪

flashback table t01 to before drop;//直接闪回，回复被删的t01

另外闪回操作默认的是闪回最近一次删除(如果删除后再创建一张相同的表的话，但是内容改变，删除了这2张同名表以后利用闪回则恢复的是最近的一次删除)

但也可以根据时间来恢复不同时间段的表，可以从recyclebin查看：show recyclebin;

recyclbin里面有一个列：RECYCLEBIN

NAME，他里面的内容类似于:BIN$jz53sHuTQleZNelg8USEHg==$0

可以通过desc "BIN$jz53sHuTQleZNelg8USEHg==$0"  来查看表结构(注意必须用双引号括起来)

然后根据这个recycle name来恢复表，如下：

flashback table "BIN$jz53sHuTQleZNelg8USEHg==$0" to before

drop;有的时候恢复的时候可能该recycle

name对应的表名已经存在，这个时候只要在恢复的时候重新命名下即可，比如：

flashback table "BIN$jz53sHuTQleZNelg8USEHg==$0" to before

drop rename to fbTB_01;

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

闪回的也可以只是数据，而不是表

这里的sql写法比较特殊，比如修改了emp表的SMITH的sal，通过versions_xid事务ID来进行闪回

select versions_xid,ename,sal

from emp

versions between timestamp minvalue and maxvalue;

//versions_xid为事务编号，注意versions为复数，只要有修改，那么versions_xid列就不为空，根据事务号进行回滚：

通过查看desc

flashback_transaction_query可以看到有一个undo_sql列以及xid列

select undo_sql

from flashback_transaction_query

where xid='0A00290046010000'

//xid为上面的versions_xid

上面的sql可以获得用于撤销更新的sql(其实就是再update。。。)

====================================

导出数据表：

exp scott/tiger@orcl file=e:\1.dmp tables='tb_01';

导入数据表：

imp scott/tiger@orcl file=e:\1.dmp full=yes ignore=yes