整理Oracle常用命令及Sql.docx
《整理Oracle常用命令及Sql.docx》由会员分享，可在线阅读，更多相关《整理Oracle常用命令及Sql.docx（102页珍藏版）》请在冰豆网上搜索。
整理Oracle常用命令及Sql
分页
selectt2.*from（selectrownumrowno,t1.*from（
select*fromAC_CUSTOMER
）t1whererownum<=10）t2where（rowno>0）
selectt2.*from（selectrownumrowno,t1.*from（
）t1whererownum<=10）t2where（rowno>0）
左连接
sql的leftjoin命令详解
表a
aidadate
1a1
2a2
3a3
表b
bidbdate
1b1
2b2
4b4
两个表a,b相连接,要取出id相同的字段
select*fromainnerjoinbona.aid=b.bid这是仅取出匹配的数据.
此时的取出的是:
1a1b1
2a2b2
那么leftjoin指:
select*fromaleftjoinbona.aid=b.bid
首先取出a表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1a1b1
2a2b2
3a3空字符
同样的也有rightjoin
指的是首先取出b表中所有数据,然后再加上与a,b匹配的的数据
此时的取出的是:
1a1b1
2a2b2
4空字符b4
LEFTJOIN或LEFTOUTERJOIN。
左向外联接的结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。
如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
三范式
第一范式：
1）任何给定行的列必须是只包含一个值；
2）表中的每一行必须有相同数量的列；
3）表中的每一行必须是唯一的即是不相同的；
第二范式：
1）必须满足第一范式；
2）表中的所有非主键必须依赖一整个主键；
第三范式：
1）必须满足第二范式；
2）表中的所有非主键必须相互独立；
范式中还有复合主键的用法；
查看表
SQL>select*fromtab;
查看表结构
SQL>describe表名
简写以上命令
SQL>desc表名
系统时间
selectto_char（sysdate,'yyyymmddhhss'）fromdual;
selectto_char（sysdate,'yyyy-mm-dd,hh24:
mi:
ss'）fromdual;
导入/导出
导入：
impcrddev/amc@amcfile=c:
\amc.dmp
导出：
expcrddev/amc@amcfile=c:
\amc.dmp
如何单独备份一个或多个用户：
D:
\>expscott/tigerfile=导出文件
D:
\>expsystem/managerowner=（用户1,用户2,…,用户n）file=导出文件
如何单独备份一个或多个表：
D:
\>exp用户/密码tables=表
D:
\>exp用户/密码tables=（表1,…,表2）
D:
\>expsystem/managertables=（用户1.表）
D:
\>expsystem/managertables=（用户1.表1,…,用户2.表1）
如何导入指定表
D:
\>expscott/tigerfile=a.dmp
D:
\>imptest/testfromuser=scotttables=empfile=a.dmp
D:
\>imptest/testtables=deptfile=a.dmp
如果导出用户没有DBA权限，则导入用户可以不用指定fromuser、touser参数
如果导出用户拥有DBA权限，则导入用户也必须拥有DBA权限）
注释
SQL>commentontable表is'表注释';
注释已创建。
SQL>commentoncolumn表.列is'列注释';
注释已创建。
查询表注释
SQL>select*fromuser_tab_commentswherecommentsisnotnull;
查询列注释
SQL>select*fromuser_col_commentswherecommentsisnotnull;
查看表空间
查看表空间名字
selectdistinctTABLESPACE_NAMEfromtabs;
查看几个表空间
selectcount（distinctTABLESPACE_NAME）fromtabs;
selectb.file_id 文件ID,
b.tablespace_name 表空间,
b.file_name 物理文件名,
b.bytes 总字节数,
（b.bytes-sum（nvl（a.bytes,0））） 已使用,
sum（nvl（a.bytes,0）） 剩余,
sum（nvl（a.bytes,0））/（b.bytes）*100 剩余百分比
fromdba_free_spacea,dba_data_filesb
wherea.file_id=b.file_id
groupbyb.tablespace_name,b.file_name,b.file_id,b.bytes
orderbyb.tablespace_name
--dba_free_space--表空间剩余空间状况
--dba_data_files--数据文件空间占用情况
查看表空间是否自动扩展
selectfile_name,autoextensible,increment_byfromdba_data_files;
表空间自动扩展
alterdatabasedatafile'path:
\datafilename'autoextendonnext1Mmaxsize100M;
表空间大小
selecttablespace_name,count（*）,sum（blocks）,sum（bytes）/1024/1024
fromdba_data_filesgroupbytablespace_name;
使用情况
selectdf.tablespace_name"表空间名",totalspace"总空间M",freespace"剩余空间M",round（（1-freespace/totalspace）*100,2）"使用率%"from
（selecttablespace_name,round（sum（bytes）/1024/1024）totalspacefromdba_data_filesgroupbytablespace_name）df,（selecttablespace_name,round（sum（bytes）/1024/1024）freespacefromdba_free_space
groupbytablespace_name）fswheredf.tablespace_name=fs.tablespace_name;
使用情况
SELECTa.tablespace_name"表空间名",total表空间大小,free表空间剩余大小,
（total-free）表空间使用大小,
ROUND（（total-free）/total,4）*100"使用率%"
FROM（SELECTtablespace_name,SUM（bytes）freeFROMDBA_FREE_SPACE
GROUPBYtablespace_name）a,
（SELECTtablespace_name,SUM（bytes）totalFROMDBA_DATA_FILES
GROUPBYtablespace_name）b
WHEREa.tablespace_name=b.tablespace_name
控制
DDL数据定义语言：
create、alter、drop、truncate（创建、修改结构、删除、截断）（其他：
rename）
DML数据操纵语言：
insert、delete、select、update（增、删、查、改）
DCL数据控制语言：
grant、revoke（授权、回收）、setrole
事务控制：
commit、rollback、savepoint（其他：
locktable、setconstraint（s）、settransaction）
审计控制：
audit、noaudit
系统控制：
altersystem
会话控制：
altersession
其他语句：
comment（添加注释）、explainplan、analyze（收集统计）、validate、call
表结构复制
SQL>createtablebasselect*fromawhere1=2;
SQL>createtableb（b1,b2,b3）asselecta1,a2,a3fromawhere1=2;
查看回滚段
SQL>SELECTSEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBACK_SEGS;
查看数据文件放置路径
SQL>selecttablespace_name,file_id,bytes/1024/1024,file_namefromdba_data_filesorderbyfile_id;
查看当前连接用户
SQL>showuser
计算
SQL>select100*20fromdual;
连接字符串
SQL>select列1||列2from表1;
SQL>selectconcat（列1,列2）from表1;
复制数据
用户间复制数据
SQL>copyfromuser1touser2createtable2usingselect*fromtable1;
视图groupby
视图中不能使用orderby，但可用groupby代替来达到排序目的
SQL>createviewaasselectb1,b2frombgroupbyb1,b2;
创建用户
通过授权的方式来创建用户
SQL>grantconnect,resourcetotestidentifiedbytest;
备份
createtableyhda_bakasselect*fromyhda;
分组统计
selectsid,count（*）fromyhdaagroupbysidhavingcount（*）>1
select函数
ORDERBY–按照指定列排序返回结果的子句
DISTINCT–只返回结果集合内唯一行的关键词
COUNT--返回匹配查询的数据行总数数值的函数
AVG–该函数返回指定列的平均值
SUM–该函数把指定的列中的数字加起来
MIN–该函数返回列中最小的非NULL值
MAX–该函数返回列中的最大值
GROUPBY–按列汇集查询函数结果的子句
存储查询结果
利用Oracle中的Spool缓冲池技术可以实现Oracle数据导出到文本文件。
在OraclePL/SQL中输入缓冲开始命令，并指定输出的文件名：
spoold:
\output.txt
在命令行中随便输入你的SQL查询：
selectmobilefromcustomer;
selectmobilefromclient;
……
在命令行中输入缓冲结果命令：
spooloff;
则系统将缓冲池中的结果都输出到"output.txt"文件中。
以TAB键分隔
删除表注意事项
在删除一个表中的全部数据时，须使用TRUNCATETABLE表名;因为用DROPTABLE，DELETE*FROM表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了。
having用法
having子句对groupby子句所确定的行组进行控制,having子句条件中只允许涉及常量,聚组函数或groupby子句中的列.
外联接"+"用法
外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的notin运算,大大提高运行速度.例如,下面这条命令执行起来很慢
用外联接提高表连接的查询速度
在作表连接（常用于视图）时，常使用以下方法来查询数据:
SELECTPAY_NO,PROJECT_NAME
FROMA
WHEREA.PAY_NONOTIN（SELECTPAY_
NOFROMBWHEREVALUE>=120000）;
----但是若表A有10000条记录，表B有10000条记录，则要用掉30分钟才能查完，主要因为NOTIN要进行一条一条的比较，共需要10000*10000次比较后，才能得到结果。
该用外联接后，可以缩短到1分左右的时间:
SELECTPAY_NO,PROJECT_NAME
FROMA,B
WHEREA.PAY_NO=B.PAY_NO（+）
ANDB.PAY_NOISNULL
ANDB.VALUE>=12000;
selecta.empnofromempawherea.empnonotin（selectempnofromemp1wherejob=’SALE’）;
倘若利用外部联接,改写命令如下:
selecta.empnofromempa,emp1b
wherea.empno=b.empno（+）
andb.empnoisnull
andb.job=’SALE’;
可以发现,运行速度明显提高.
settransaction用法
在执行大事务时,有时oracle会报出如下的错误:
ORA-01555:
snapshottooold（rollbacksegmenttoosmall）
这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如
settransactionuserollbacksegmentroll_abc;
deletefromtable_namewhere...
commit;
回滚段roll_abc被指定给delete事务,commit命令则在事务结束之后取消了回滚段的指定.
库重建注意事项
在利用import进行数据库重建过程中,有些视图可能会带来问题,因为结构输入的顺序可能造成视图的输入先于它低层次表的输入,这样建立视图就会失败.要解决这一问题,可采取分两步走的方法:
首先输入结构,然后输入数据.
命令举例如下（uesrname:
jfcl,password:
hfjf,hoststing:
ora1,数据文件:
expdata.dmp）:
impjfcl/hfjf@ora1file=empdata.dmprows=N
impjfcl/hfjf@ora1file=empdata.dmpfull=Ybuffer=64000
commit=Yignore=Y
第一条命令输入所有数据库结构,但无记录.第二次输入结构和数据,64000字节提交一次.ignore=Y选项保证第二次输入既使对象存在的情况下也能成功.
删除重复记录:
--查找表中num列重复的，列出重复的记录数，并列出他的name属性
selectcount（num）,max（name）fromstudent
groupbynum
havingcount（num）>1
--按num分组后找出表中num列重复，即出现次数大于一次
deletefromstudent（上面Select的）
这样的话就把所有重复的都删除了。
-----慎重
----执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录
SELECT*FROMEM5_PIPE_PREFAB
WHEREROWID!
=（SELECTMAX（ROWID）FROMEM5_PIPE_PREFABD--D相当于First,Second
WHEREEM5_PIPE_PREFAB.DRAWING=D.DRAWINGAND
EM5_PIPE_PREFAB.DSNO=D.DSNO）;
----执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录
DELETEFROMEM5_PIPE_PREFAB
WHEREROWID!
=（SELECTMAX（ROWID）FROMEM5_PIPE_PREFABD
WHEREEM5_PIPE_PREFAB.DRAWING=D.DRAWINGAND
EM5_PIPE_PREFAB.DSNO=D.DSNO）;
返回表中[N，M]条记录：
取得某列中第N大的行
selectcolumn_namefrom
（selecttable_name.*,dense_rank（）over（orderbycolumndesc）rankfromtable_name）
whererank=&N；
假如要返回前5条记录：
select*fromtablenamewhererownum<6;（或是rownum<=5或是rownum!
=6）
假如要返回第5-9条记录：
select*fromtablename
where…
andrownum<10
minus
select*fromtablename
where…
andrownum<５
orderbyname
选出结果后用name排序显示结果。
（先选再排序）
注意：
只能用以上符号（<、<=、!
=）。
select*fromtablenamewhererownum!
=10;返回的是前９条记录。
不能用：
>,>=,=,Between...and。
由于rownum是一个总是从1开始的伪列，Oracle认为这种条件不成立，查不到记录.
另外，这个方法更快：
select*from（
selectrownumr,afromyourtable
whererownum<=20
orderbyname）
wherer>10
这样取出第11-20条记录!
（先选再排序再选）
要先排序再选则须用select嵌套：
内层排序外层选。
rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!
rownum是在查询集合产生的过程中产生的伪列，并且如果where条件中存在rownum条件的话，则:
1：
假如判定条件是常量，则：
只能rownum=1,<=大于1的自然数，=大于1的数是没有结果的，大于一个数也是没有结果的
即当出现一个rownum不满足条件的时候则查询结束 thisisstopkey!
2:
当判定值不是常量的时候
若条件是=var,则只有当var为1的时候才满足条件，这个时候不存在stopkey,必须进行fullscan,对每个满足其他where条件的数据进行判定
选出一行后才能去选rownum=2的行……
函数大全
快速编译所有视图
----当在把数据库倒入到新的服务器上后（数据库重建），需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。
SQL>SPOOLON.SQL
SQL>SELECT‘ALTERVIEW‘||TNAME||’
COMPILE;’FROMTAB;
SQL>SPOOLOFF
然后执行ON.SQL即可。
SQL>@ON.SQL
当然，授权和创建同义词也可以快速进行，如：
SQL>SELECT‘GRANTSELECTON’
||TNAME||’TOUSERNAME;’FROMTAB;
SQL>SELECT‘CREATESYNONYM
‘||TNAME||’FORUSERNAME.’||TNAME||’;’FROMTAB;
读写文本型操作系统文件
----在PL/SQL3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。
如下：
DECALRE
FILE_HANDLEUTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:
=UTL_FILE.FOPEN（
‘C:
\’,’TEST.TXT’,’A’）;
UTL_FILE.PUT_LINE（FILE_HANDLE,’
HELLO,IT’SATESTTXTFILE’）;
UTL_FILE.FCLOSE（FILE_HANDLE）;
END;
1、ORACLE数据库的启动和关闭
对于DBA们来说，关闭和重新启动数据库以便优化、调整应用的运行是经常碰到的事情。
如果用
户已经log进如了数据库，你用SHUTDOWNIMMEDIATE或SHUTDOWNABORT命令来执行关闭数据库，
那用户将不能连接，直到数据库重新启动，用户时常会抱怨：
怎么又要重起。
其实，每次这样的
启动关闭都是为了数据库能更好的运做。
这篇文章将具体介绍shutdown/startup操作时应想到的
步骤和许多注意事项，或许这些能对你有所帮助。
NOTE:
在执行第一步前，SHUTDOWN数据库，不要提前关闭SQL*NET，直到你确认关闭了SERVER上
的数据库后再关闭SQL*NET。
因为一旦你关闭了SQL*NET，用户将将失去和数据库的会话。
第一步---存档或删除老的tracefiles和logs
当你启动ORACLE的一个实例（INSTANCE）时，ORACLE把关于该实例的诊断信息写入指定的trace
和log文件。
每个后台进程都增加一个T
升级会员 