Oracle 小知识 总结一.docx

1、Oracle 小知识 总结一Oracle 是一个庞大的系统，里面的知识点很多，在学习的时候，看到一些知识点，就贴了下来，尽不知中贴了这么多，就先做个小结吧。 里面有的知识点已经单独拿出来写成单篇的blog( 好记性不如烂笔头，不过我用的不是笔。 _ . 这里面大部分内容都是我泡CSDN 论坛的时候整理的，现在要忙其他的事，就很少去了。 泡论坛的那段时间，是我进步最快的时间，所以如果想在Oracle 上有所发展的朋友，建议也可以去论坛泡泡。 看到自己会的知识点，就帮别人解答，不会的就自己研究学习，一段时间下来，会有很大的进步。而且还能认识很多朋友。 实在是一举两得的事。 1.每天的8：00到23

2、：00每隔5分钟执行一个sql语句的JOB-建立一个存储过程CREATE OR REPLACE PROCEDURE p_jobtest IS v_hh VARCHAR2(2);BEGIN v_hh := to_char(SYSDATE, hh24); IF v_hh = 08 AND v_hh crosscheck copy; RMANlist copy; RMANdelete expired copy; 3.触发LGWR进程的条件有： 1. 用户提交 2. 有1/3重做日志缓冲区未被写入磁盘 3. 有大于1M的重做日志缓冲区未被写入磁盘 4. 3秒超时 5. DBWR 需要写入的数据的SCN

3、大于LGWR记录的SCN，DBWR 触发LGWR写入。4.触发DBWR进程的条件有：1. DBWR超时，大约3秒 2. 系统中没有多余的空缓冲区来存放数据 3. CKPT 进程触发DBWR5.每隔3秒钟ckpt会去更新控制文件和数据文件，记录checkpoint执行的情况。 当发生checkpoint时，会把SCN写到四个地方去。 三个地方于control file内，一个在datafile header。6.触发CheckPoint(检查点)条件有很多，比如：1. 通过正常事务处理或者立即选项关闭例程时(shutdown immediate或者Shutdown normal), 2. 当通过

4、设置初始化参数:LOG_CHECKPOINT_INTERVAL,LOG_CHECKPOINT_TIMEOUT ,FAST_START_IO_TARGET 强制时；3. 当数据库管理员手动请求时:ALter system checkpoint;alter tablespace . offline;4. 每次日志切换时;alter system switch logfile注意：1. alter system switch logfile也将触发完全检查点的发生。2. alter database datafile . offline 不会触发检查点进程。7.RECOVERDATABASEUNTI

5、LCANCEL和RECOVERDATABASEUNTILCANCELUSINGBACKUPCONTROLFILE;区别1) RECOVER DATABASE UNTIL CANCEL = OPEN DATABASE RESETLOG= DATAFILE HEADER SCN一定会小于CONTROLFILE的DATAFILE SCN如果你有进行RESTORE DATAFILE，则该RESTORE的DATAFILE HEADER SCN一定会小于目前CONTROLFILE的DATAFILE SCN，此时会无法开启数据库，必须进行media recovery。 重做archive log直到该dat

6、afile header的SCN=current scn8.建表前判断表是否存在的存储过程。 在Oracle 中没有drop table. if exists语法。 所以我们可以在创建表之前用如下存储过程来判断。 create or replace procedure proc_dropifexists( p_table in varchar2 ) is v_count number(10);begin select count(*) into v_count from user_objects where object_name = upper(p_table); if v_count 0

7、then execute immediate drop table | p_table | purge; end if;end;9.表属性中pctused,和pctfree作用表示数据块什么时候移入和移出freelist。pctused：如果数据块的使用率小于pctused的值，则该数据块重新加入到fresslist中。pctfree：如果数据块的使用率高于pctfree的值，则该数据块从freelist中移出。10.oracle表空间大小没有限制，根存储空间而定。 oracle9i或以下,单个数据文件最大32G(对于8K的数据块),整个数据库最多有64K个数据文件.单个表空间的数据文件数量没

8、有具体的限制,也应该是在64K以下. oracle10g以上,引入了bigfile tablespace,bigfile tablespace只有一个数据文件,最大为4G*8k=32T database file size: Operating system dependent. Limited by maximum operating system file size; typically 222(2的22次方） or 4M blocks 11.Oracle利用现有的表创建一张新表，只要表结构相同 create table david as select * from all_users w

9、here 11;12.循环插入数据declare i integer; begin for i in 1.100000 loop insert into test values(i); end loop; commit; end;13.开发人员通常习惯赋予所有用户DBA权限，查看权限 Select * From User_Role_Privs Select * From User_Sys_Privs14.看数据文件大小,单位是Mselect round(bytes/(1024*1024),0) total_space from dba_data_filesselect sum(bytes/(1

10、024*1024) total_space from dba_data_files15控制文件大小select sum( block_size*file_size_blks )/1024/1024 from v$controlfile16.建立表空间CREATE TABLESPACE data01DATAFILE /oracle/oradata/db/DATA01.dbf SIZE 500MUNIFORM SIZE 128k; #指定区尺寸为128k,如不指定，区尺寸默认为64k删除表空间DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFIL

11、ES;修改表空间大小alter database datafile /path/NADDate05.dbf resize 100M查看表空间：select tablespace_name, file_name, sum(bytes)/1024/1024 table_size from dba_data_files group by tablespace_name,file_name;17.有没有被lock，可以通过这2张dynamicview来确定：v$locked_object,V$session可以把该 session杀掉。select sid,serial# from v$session

12、 where username =XXXX把得到的sid，serial#号替换到下面的语句中：alter system kill session SID,SERIAL#18.PL/SQLoracle查询前10条信息SELECT * FROM table WHERE ROWNUM 11select * from ( select * from table order by desc) where rownum select gettracename() from dual;即可GETTRACENAME()-F:/DEVELOPER/ORACLE/PRODUCT/10.2.0/ADMIN/ORCL

13、/UDUMP/orcl_ora_3800.trc23.selectcasewhen(ab)thenaelsebendfromTableA; select greatest(a,b) from tb24.TRUNCATETABLETable_name使用truncate时，速度比delete，但是系统不做Log。清空表的数据，仅保留类别结构，被删除的数据不能ROLLBACK，这点是与DELETE的主要差别.不能Rollback也就是会删除log文件.在SQL Server里面如果你用Truncate，自动增长的行又会从1开始25.selectsubstrb(大小abc,1,6)fromdual;

14、26.表中的数据如下图所示a b c1 PP41982 SO900292 PP41982 SO90029只取出字段b，c不重复的字段，select b,c from t group by b,c having(count(b) DELETE FROM a WHERE ROWID IN( SELECT MAX(ROWID) FROM a);已删除 1 行。SQL select * from a;NAME ID ORDER_TITLE- - -中国科学技术大学 1 科学中国科学技术大学 1 科学SQL29.查看索引信息-查看索引名称SELECT * FROM USER_INDEXES;- 查看索引

15、列名SELECT * FROM DBA_IND_COLUMNS;SELECT * FROM USER_IND_COLUMNS;SELECT * FROM ALL_IND_COLUMNS;30.oracletrunc()函数用法1.TRUNC(for dates)TRUNC函数为指定元素而截去的日期值。其具体的语法格式如下：TRUNC（date,fmt）其中：date 一个日期值fmt 日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去下面是该函数的使用情况：TRUNC（TO_DATE(24-Nov-1999 08:00 pm,dd-mon-yyyy hh:mi am)）=24-

16、Nov-1999 12:00:00 amTRUNC（TO_DATE(24-Nov-1999 08:37 pm,dd-mon-yyyy hh:mi am,hh)） =24-Nov-1999 08:00:00 am2.TRUNC(for number)TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。其具体的语法格式如下TRUNC（number,decimals）其中：number 待做截取处理的数值decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分下面是该函数的使用情况：TRUNC（

17、89.985，2）=89.98TRUNC（89.985）=89TRUNC（89.985，-1）=80注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。31.local是局部有序，整体无序，global是有序的，所以local可能会比global慢，得看你的sql语句怎么写的，需求是什么样的global索引-自己想怎么玩就怎么玩local索引-表怎么玩它就怎么玩32，查看磁盘物理读写情况：SELECT NAME,phyrds, phywrts,readtim,writetim FROM v$filestat a, v$datafile b WHERE a.FILE#

18、=b.FILE#ORDER BY readtim DESC; 33.从表中筛选出所有能被5整除的value值数据 select * from table where mod(datavalue,5) = 0; 34.Union与UnionAll的区别如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。union(或称为联合)的作用是将多个结果合并在一起显示出来。union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。 Union：对两个结

19、果集进行并集操作，不包括重复行，同时进行默认规则的排序； Union All：对两个结果集进行并集操作，包括重复行，不进行排序；select empno,ename from empunionselect deptno,dname from dept我们没有必要在每一个select结果集中使用order by子句来进行排序，我们可以在最后使用一条order by来对整个结果进行排序。例如：select empno,ename from empunionselect deptno,dname from deptorder by ename;35.查看看到A用户下的所有数据量100万的表的信息 s

20、elect * from user_all_tables a where a.num_rows1000000 前提是a用户下所有表的统计信息都是最新的。保险的办法是所有表都count一遍:select select |table_name |,|count(*) from |table_name from user_all_tables ;把上面这段sql的执行结果拷贝出来执行即可 36.SQLPLUS里执行EXPLAINPLAN SQLEXPLAIN PLAN FOR 你的sql语句;如SQLEXPLAIN PLAN FOR SELECT * FROM EMP WHERE EMPNO=736

21、9;然后SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(PLAN_TABLE);查看结果就是前面SQL语句的执行计划。 37.nvarchar2(2000)不区别汉字和字母varchar2(4000)只能存储2000个汉字 nvarchar2最大2000 varchar2 最大400038.更新表被锁，KILLspid后，select*fromtabnameforupdate独占资源。 v$locked_object dba_objects 联合可以知道锁表的session 39.PGA中sort_area_size大小不够时，用到

22、临时表空间。40.oracle没有标识列，自动增长的这个概念 需要用序列来实现 CREATE SEQUENCE sid INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 select sid.nextval, -取下一个序列 sid.currval -取当前序列from dual;41.IMP/EXP的buffer达到1M以后，性能的提升并不大，5M或者10M的足够用42.如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null. 如： ID DD 1 e 2 null select count(*) from table -结果是

23、2 select count(DD) from table -结果是1count(1)和count(主键) 这两个只扫描主键Index就可以得到数据， count(*)是扫描表的。 所以count(1)和count(主键)这两个效率高。 还有一种写法是count（ROWID）这也是只扫描Index的，效率高。43.linux挂在windows共享的盘 1. 启动nfs服务: service nfs start2.mount -o username=user,password=123456 /10.85.2.194/share /mnt44.Killsession并使状态直接变成killedEX

24、ECUTE IMMEDIATE ALTER SYSTEM KILL SESSION :sid,:serial# IMMEDIATEEXECUTE IMMEDIATE ALTER SYSTEM KILL SESSION :sid,:serial#immediate是立即kill，不会有status 会变成killed状态的，清楚了在v$session里的信息 45.truncate只是删除了表中的记录，并不会改变表的结构及依赖约束，所以truncate表后表的索引依然存在,但是表和索引所占用的空间会恢复到初始大小46.这是看高速缓存命中率小于80%的SQLSELECT EXECUTIONS , DISK_READS, BUFFER_GETS, round(BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS0 AND BUFFER_GETS 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS