Oracle 小知识 总结一.docx
《Oracle 小知识 总结一.docx》由会员分享，可在线阅读，更多相关《Oracle 小知识 总结一.docx（22页珍藏版）》请在冰豆网上搜索。
Oracle小知识总结一
Oracle是一个庞大的系统，里面的知识点很多，在学习的时候，看到一些知识点，就贴了下来，尽不知中贴了这么多，就先做个小结吧。
里面有的知识点已经单独拿出来写成单篇的blog（好记性不如烂笔头，不过我用的不是笔。
^_^...
这里面大部分内容都是我泡CSDN论坛的时候整理的，现在要忙其他的事，就很少去了。
泡论坛的那段时间，是我进步最快的时间，所以如果想在Oracle上有所发展的朋友，建议也可以去论坛泡泡。
看到自己会的知识点，就帮别人解答，不会的就自己研究学习，一段时间下来，会有很大的进步。
而且还能认识很多朋友。
实在是一举两得的事。
1. 每天的8：
00到23：
00每隔5分钟执行一个sql语句的JOB
--建立一个存储过程
CREATEORREPLACEPROCEDUREp_jobtestIS
v_hhVARCHAR2
（2）;
BEGIN
v_hh:
=to_char（SYSDATE,'hh24'）;
IFv_hh>='08'ANDv_hh<='22'THEN
--你的sql语句
NULL;
ENDIF;
END;
/
--提交一个JOB
DECLARE
v_jobnoNUMBER;
BEGIN
dbms_job.submit（v_jobno,
'p_jobtest;',
trunc（SYSDATE,'mi'）+1/1440,
'trunc（SYSDATE,''mi''）+5/1440'）;
END;
/
2. RMAN 中的list 命令显示的信息是从控制文件里获取的，如果使用rm等命令手工的删除备份文件，这个动作不会同步到控制文件，造成不一致，这种不一致会导致使用rman时报错。
可以使用delete 删除这些过期的记录，在用就不会报错了。
RMAN>crosscheckcopy;
RMAN>listcopy;
RMAN>deleteexpiredcopy;
3. 触发LGWR进程的条件有：
1.用户提交
2.有1/3重做日志缓冲区未被写入磁盘
3.有大于1M的重做日志缓冲区未被写入磁盘
4.3秒超时
5.DBWR需要写入的数据的SCN大于LGWR记录的SCN，DBWR触发LGWR写入。
4. 触发DBWR进程的条件有：
1.DBWR超时，大约3秒
2.系统中没有多余的空缓冲区来存放数据
3.CKPT进程触发DBWR
5. 每隔3秒钟ckpt会去更新控制文件和数据文件，记录checkpoint执行的情况。
当发生checkpoint时，会把SCN写到四个地方去。
三个地方于controlfile内，一个在datafileheader。
6. 触发CheckPoint（检查点） 条件有很多，比如：
1.通过正常事务处理或者立即选项关闭例程时（shutdownimmediate或者Shutdownnormal）,
2.当通过设置初始化参数:
LOG_CHECKPOINT_INTERVAL,
LOG_CHECKPOINT_TIMEOUT,
FAST_START_IO_TARGET强制时；
3.当数据库管理员手动请求时:
ALtersystemcheckpoint;
altertablespace...offline;
4.每次日志切换时;
altersystemswitchlogfile
注意：
1.altersystemswitchlogfile也将触发完全检查点的发生。
2.alterdatabasedatafile...offline不会触发检查点进程。
7. RECOVER DATABASE UNTIL CANCEL 和 RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE; 区别
1）RECOVERDATABASEUNTILCANCEL==>OPENDATABASERESETLOG
==>DATAFILEHEADERSCN一定会小于CONTROLFILE的DATAFILESCN
如果你有进行RESTOREDATAFILE，则该RESTORE的DATAFILEHEADERSCN一定会小于目前CONTROLFILE的DATAFILESCN，此时会无法开启数据库，必须进行mediarecovery。
重做archivelog直到该datafileheader的SCN=currentscn
8. 建表前判断表是否存在的存储过程。
在Oracle中没有droptable...ifexists语法。
所以我们可以在创建表之前用如下存储过程来判断。
createorreplaceprocedureproc_dropifexists（
p_tableinvarchar2
）is
v_countnumber（10）;
begin
selectcount（*）
intov_count
fromuser_objects
whereobject_name=upper（p_table）;
ifv_count>0then
executeimmediate'droptable'||p_table||'purge';
endif;
end;
9. 表属性中pctused,和 pctfree 作用
表示数据块什么时候移入和移出freelist。
pctused：
如果数据块的使用率小于pctused的值，则该数据块重新加入到fresslist中。
pctfree：
如果数据块的使用率高于pctfree的值，则该数据块从freelist中移出。
10. oracle表空间大小没有限制，根存储空间而定。
oracle9i或以下,单个数据文件最大32G（对于8K的数据块）,整个数据库最多有64K个数据文件.单个表空间的数据文件数量没有具体的限制,也应该是在64K以下.oracle10g以上,引入了bigfiletablespace,bigfiletablespace只有一个数据文件,最大为4G*8k=32Tdatabasefilesize:
Operatingsystemdependent.Limitedbymaximumoperatingsystemfilesize;typically222（2的22次方）or4Mblocks
11. Oracle利用现有的表创建一张新表，只要表结构相同
createtabledavidasselect*fromall_userswhere1<>1;
12. 循环插入数据
declareiinteger;
begin
foriin1..100000loop
insertintotestvalues（i）;
endloop;
commit;
end;
13. 开发人员通常习惯赋予所有用户DBA权限，查看权限
Select*FromUser_Role_Privs
Select*FromUser_Sys_Privs
14. 看数据文件大小,单位是M
selectround（bytes/（1024*1024）,0）total_spacefromdba_data_files
selectsum（bytes/（1024*1024））total_spacefromdba_data_files
15 控制文件大小
selectsum（block_size*file_size_blks）/1024/1024fromv$controlfile
16. 建立表空间
CREATETABLESPACEdata01
DATAFILE'/oracle/oradata/db/DATA01.dbf'SIZE500M
UNIFORMSIZE128k;#指定区尺寸为128k,如不指定，区尺寸默认为64k删除表空间
DROPTABLESPACEdata01INCLUDINGCONTENTSANDDATAFILES;
修改表空间大小
alterdatabasedatafile'/path/NADDate05.dbf'resize100M
查看表空间：
selecttablespace_name,file_name,sum（bytes）/1024/1024table_sizefromdba_data_filesgroupbytablespace_name,file_name;
17. 有没有被lock，可以通过这2张dynamic view来确定：
v$locked_object,V$session
可以把该session杀掉。
selectsid,serial#fromv$sessionwhereusername='XXXX'
把得到的sid，serial#号替换到下面的语句中：
altersystemkillsession'SID,SERIAL#'
18. PL/SQL oracle 查询前10条信息
SELECT*FROMtableWHEREROWNUM<11
select*from（select*fromtableorderbydesc）whererownum<=5
==selecttop5*fromtable;
19. 查看表上是否存在的索引
select*fromuser_indexeswheretable_name='yourtablename'
createindexIX_Tablename_columnontablename（column）
20. select id, id2, round（（id/id2）*100,2） || '%' percent from test;
21. 查询表的行数
selectcount（*）fromtable_name;全表扫描，会自已找表有索引列并且该列为非空的（因为只有非空才能确保记录数是全的）,走INDEX_FFS.
selectcount
（1）fromtable_name;不走索引，效率要高，但在表中有非空索引时也是走INDEX_FFS的
22. 用function来查看当前session的trace文件的文件名
如下
createorreplacefunctiongettracenamereturnvarchar2is
v_resultvarchar2（200）;
begin
SELECTd.VALUE
||'/'
||LOWER（RTRIM（i.INSTANCE,CHR（0）））
||'_ora_'
||p.spid
||'.trc'intov_result
FROM（SELECTp.spid
FROMv$mystatm,v$sessions,v$processp
WHEREm.statistic#=1ANDs.SID=m.SIDANDp.addr=s.paddr）p,
（SELECTt.INSTANCE
FROMv$threadt,v$parameterv
WHEREv.NAME='thread'
AND（v.VALUE=0ORt.thread#=TO_NUMBER（v.VALUE）））i,
（SELECTVALUE
FROMv$parameter
WHERENAME='user_dump_dest'）d;
returnv_result;
endgettracename;
运行SQL>selectgettracename（）fromdual;即可
GETTRACENAME（）
-----------------------------------------------------------------------
F:
/DEVELOPER/ORACLE/PRODUCT/10.2.0/ADMIN/ORCL/UDUMP/orcl_ora_3800.trc
23 . select case when（a>b） then a else b end from TableA;
selectgreatest（a,b）fromtb
24. TRUNCATE TABLE Table_name
使用truncate时，速度比delete，但是系统不做Log。
清空表的数据，仅保留类别结构，被删除的数据不能ROLLBACK，这点是与DELETE的主要差别.不能Rollback也就是会删除log文件.在SQLServer里面如果你用Truncate，自动增长的行又会从1开始
25. select substrb（'大小abc',1,6） from dual;
26. 表中的数据如下图所示
abc
1PP41982SO90029
2PP41982SO90029
只取出字段b，c不重复的字段，
selectb,cfromtgroupbyb,chaving（count（b）<2）
27. 查询锁的情况
1）.insertintotestvalues
（1）;
2）.selectuserenv（'sid'）fromdual;
3）.select*fromv$lockwheresid=''
sid在v$session中有这个列，可以结合v$session查询更多的信息
28. 删除重复行：
SQL>DELETEFROMaWHEREROWIDIN（SELECTMAX（ROWID）FROMa）;
已删除1行。
SQL>select*froma;
NAMEIDORDER_TITLE
--------------------------------------------------
中国科学技术大学1科学
中国科学技术大学1科学
SQL>
29. 查看索引信息
--查看索引名称
SELECT*FROMUSER_INDEXES;
--查看索引列名
SELECT*FROMDBA_IND_COLUMNS;
SELECT*FROMUSER_IND_COLUMNS;
SELECT*FROMALL_IND_COLUMNS;
30. oracle trunc（）函数用法
1.TRUNC（fordates）
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如下：
TRUNC（date[,fmt]）
其中：
date一个日期值
fmt日期格式，该日期将由指定的元素格式所截去。
忽略它则由最近的日期截去
下面是该函数的使用情况：
TRUNC（TO_DATE（’24-Nov-199908:
00pm’,’dd-mon-yyyyhh:
miam’））
=’24-Nov-199912:
00:
00am’
TRUNC（TO_DATE（’24-Nov-199908:
37pm’,’dd-mon-yyyyhh:
miam’,’hh’））=’24-Nov-199908:
00:
00am’
2.TRUNC（fornumber）
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
其具体的语法格式如下
TRUNC（number[,decimals]）
其中：
number待做截取处理的数值
decimals指明需保留小数点后面的位数。
可选项，忽略它则截去所有的小数部分
下面是该函数的使用情况：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：
第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。
31. local是局部有序，整体无序，global是有序的，所以local可能会比global慢，得看你的sql语句怎么写的，需求是什么样的
global索引->自己想怎么玩就怎么玩
local索引->表怎么玩它就怎么玩
32， 查看磁盘物理读写情况：
SELECTNAME,phyrds,phywrts,readtim,writetimFROMv$filestata,v$datafilebWHEREa.FILE#=b.FILE#
ORDERBYreadtimDESC;
33. 从表中筛选出所有能被5整除的value值数据
select*fromtablewheremod（datavalue,5）=0;
34. Union与Union All的区别
如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者unionall关键字。
union（或称为联合）的作用是将多个结果合并在一起显示出来。
union和unionall的区别是,union会自动压缩多个结果集合中的重复结果，而unionall则将所有的结果全部显示出来，不管是不是重复。
Union：
对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
UnionAll：
对两个结果集进行并集操作，包括重复行，不进行排序；
selectempno,enamefromemp
union
selectdeptno,dnamefromdept
我们没有必要在每一个select结果集中使用orderby子句来进行排序，我们可以在最后使用一条orderby来对整个结果进行排序。
例如：
selectempno,enamefromemp
union
selectdeptno,dnamefromdept
orderbyename;
35. 查看看到A用户下的所有数据量>100万的表的信息
select*fromuser_all_tablesa
wherea.num_rows>1000000
前提是a用户下所有表的统计信息都是最新的。
保险的办法是所有表都count一遍:
select'select'||''''||table_name||''','||'count（*）from'||table_namefromuser_all_tables;
把上面这段sql的执行结果拷贝出来执行即可
36. SQLPLUS 里执行 EXPLAIN PLAN
SQL>EXPLAINPLANFOR你的sql语句;
如
SQL>EXPLAINPLANFORSELECT*FROMEMPWHEREEMPNO=7369;
然后
SELECTplan_table_output
FROMTABLE（DBMS_XPLAN.DISPLAY（'PLAN_TABLE'））;
查看结果就是前面SQL语句的执行计划。
37. nvarchar2（2000） 不区别汉字和字母
varchar2（4000） 只能存储2000 个汉字
nvarchar2最大2000
varchar2最大4000
38. 更新表被锁，KILL spid后，select * from tabname for update 独占资源。
v$locked_objectdba_objects联合可以知道锁表的session
39. PGA中sort_area_size大小不够时，用到临时表空间。
40. oracle没有标识列，自动增长的这个概念
需要用序列来实现
CREATESEQUENCEsid
INCREMENTBY1
STARTWITH1
MAXVALUE99999999
selectsid.nextval,--取下一个序列
sid.currval--取当前序列
fromdual;
41. IMP/EXP 的buffer达到1M以后，性能的提升并不大，5M或者10M的足够用
42. 如果null参与聚集运算，则除count（*）之外其它聚集函数都忽略null.
如：
IDDD
1e
2null
selectcount（*）fromtable--结果是2
selectcount（DD）fromtable---结果是1
count
（1）和count（主键）这两个只扫描主键Index就可以得到数据，
count（*）是扫描表的。
所以count
（1）和count（主键）这两个效率高。
还有一种写法是count（ROWID）这也是只扫描Index的，效率高。
43. linux 挂在windows 共享的盘
1.启动nfs服务:
servicenfsstart
2.mount-ousername=user,password=123456//10.85.2.194/share/mnt
44. Kill session 并使状态直接变成killed
EXECUTEIMMEDIATE''ALTERSYSTEMKILLSESSION'''':
sid,:
serial#''''IMMEDIATE''
EXECUTEIMMEDIATE''ALTERSYSTEMKILLSESSION'''':
sid,:
serial#''''
immediate是立即kill，不会有status会变成killed状态的，清楚了在v$session里的信息
45. truncate 只是删除了表中的记录，并不会改变表的结构及依赖约束，所以truncate表后表的索引依然存在,但是表和索引所占用的空间会恢复到初始大小
46. 这是看高速缓存命中率小于80%的SQL
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
round（（BUFFER_GETS-DISK_READS）/BUFFER_GETS,2）Hit_radio,
ROUND（DISK_READS/EXECUTIONS,2）Reads_per_run,
SQL_TEXT
FROMV$SQLAREA
WHEREEXECUTIONS>0
ANDBUFFER_GETS>0
AND（BUFFER_GETS-DISK_READS）/BUFFER_GETS<
升级会员 